Support for Composite Keys using @Embeded and @Id

[spring-projects-issues]

alpet opened DATAJDBC-352 and commented

Enable usage of @Embdedded and @Id together. I see two scenarios here. One would be for read only repositories and the other for the CRUD repository. With respect of the legacy applications very often read only is enough. How would Embedded participate in a query ? 

 

One reason to use a lower level persistence framework in my opinion are legacy applications who very often have Composite Keys. Emabling such feature in spring data jdbc would increase the possible users of the framework

---

13 votes, 14 watchers

[spring-projects-issues]

Lovro Pandžić commented

Hello, Jens Schauder, any idea when this might get fixed? Composite keys seem to be a pretty fundamental feature for any persistence layer library. Since there are no workarounds (I don't consider adding a new unique key to a production table because of library limitations a viable workaround), users that do use composite keys can't use Spring Data JDBC

[spring-projects-issues]

Jens Schauder commented

This is a feature we want to have. Unfortunately we currently can't see far enough in the future to tell when it will happen.

[imbananko]

hi
any updates on this?

[jiahaolinTHG]

hi
any updates on this?

[ttnsgn]

meanwhile this feature unsupported yet is there a workaround for this?

[kao78]

Hi
any update on this feature?

[porfirioribeiro]

I managed to make it work, with a workaround.
Instead of having multiple @Id you specify none
Create a class for being your primary key (I guess you can use a some generic Pair or Tuple class)
Create methods on repository to get/delete byId, because the default ones will not work

Working example in Kotlin (it would work also in a Java equivalent):

// Database table model
@Table("policy")
class Policy(
  val userId: UUID,
  val entityId: UUID,
  val name: String,
)

// Composite primary key
data class PolicyPK(
  val userId: UUID? = null,
  val entityId: UUID? = null,
) : Serializable

// Repository
interface PolicyRepository : R2dbcRepository<Policy, PolicyPK> {
  /**
   * replaces: findById(id: PolicyPk)
   * Find by id does not work because of the composite id so we query with all id fields
   */
  fun findByUserIdAndEntityId(userId: UUID, entityId: UUID): Mono<Policy>
  
  /**
   * replaces: delete(policy)
   * Delete with entity does not work because of the composite id so we delete with all id fields
   */
  fun deleteByUserIdAndEntityId(userId: UUID, entityId: UUID): Mono<Void>
}

If you really need to have the default findById and others, I guess you can override them on your repository and specify a @Query

I hope this helps someone, spend a bunch of time to figure this out

[st0ke]

Just in case if somebody wants to know @porfirioribeiro's workaround (no id property) does not work with R2dbcEntityTemplate

[xuandungdoan]

@porfirioribeiro your way also can't save any record, the application throws me "IllegalStateException: Required identifier property not found "

[porfirioribeiro]

@xuandungdoan you're right .save() also does not work
You have to make your own save function using a query
In my case i needed a upsert function so i ended up with something like this

  @Modifying
  @Query(
    """
insert into policy.policy(user_id, entity_id, resource_id, resource_type, roles, created_at, updated_at)
values (:#{#policy.userId}, :#{#policy.entityId}, :#{#policy.resourceId}, :#{#policy.resourceType}, :#{#policy.roles}, :#{#policy.createdAt}, now())
on conflict (user_id, entity_id, resource_id) do update set roles=:#{#policy.roles}, updated_at=now()"""
  )
  fun upsert(policy: Policy): Mono<Int>

It kinda sucked to have to do all this workaround to make it work, but at least I could still be using R2DBC and part of the Repository stuff, and not have to change all this back to use JPA/Hibernate

[Kshitij09]

Any updates on this? I need this feature to be able refer an aggregate across multiple roots using a pojo class representing the composite key

[mjgp2]

Oh dear, this is a bit of an issue.

I am trying to move a legacy C# application into Spring and just came across this issue when trying to use Spring JDBC.

@Embedded.Nullable @Id seems like one approach.

One thing I have noticed is that using these two together may not work that well for other autogenerated methods - Perhaps having multiple @Id would be better.

I may be able to contribute a PR for this, can anyone from the Spring JDBC team give me any guidance on approach? Would org.springframework.data.jdbc.core.convert.DefaultDataAccessStrategy be a good place to start?

[jason-wilmans]

We have issues with this every day. I am slowly coming to terms with the fact that this is not coming any time soon. This clashes so much with sensible DDD decisions we made. Our company will monitor if there is any movement regarding this, and I'm pretty sure we are stepping away from Spring Data Relational, as promising as it was, because this breaks so much stuff.

[schauder]

This is almost on the top of the priority list. Right after the current stuff I'm working which unfortunately is beyond big.

[mjgp2]

Good to know @schauder ! Is there a roadmap somewhere that's publicly visible?

Thanks 😊👍

[jason-wilmans]

This is almost on the top of the priority list

That is great news in itself. Thank you very much for clearing that up! In that case, we're holding out a little longer.

Under different circumstances I personally would have also been interested in contributing, but unfortunately my schedule doesn't allow that this year.

[imdadareeph2016]

Is there a roadmap and plan to fix this any sooner?
Seems, this issue led to trade of the reactive flow with block API. we had to change an entire database design because of this missing feature.

[kdomagal]

This is absolutely required. 🙏

[morfsnz]

Support for composite keys in a database table is essential. Otherwise, this software just isn't suitable for enterprise database solutions. Forcing a generated unique surrogate key for every single database table is simply unacceptable in the real world. Take the example of a many-many relationship resolution table with PKs from other tables. Why do I need an additional generated key just for this? It's an utter waste of valuable resources.

Composite keys aren't just in use in "legacy" databases, they have perfectly valid uses in any physical database design depending on the business and performance requirements.

Please prioritise this or your software is not fit for purpose in the real world of physical database design.

[schauder]

@morfsnz Spring Data JDBC does not require an additional generated (or otherwise) key for m-to-n relationships.

[paulushcgcj]

@morfsnz Spring Data JDBC does not require an additional generated (or otherwise) key for m-to-n relationships.

Agree on that BUT as of the topic of this conversation is related to the fact that R2DBC does not support basic things such as composite keys for instance. If you compare with other implementations of reactive connection, such as the one used on quarkus, there are things there that are supported through the use of Hibernate for example.

Anyway, any timeline on when this will be worked on?

[mp911de]

basic things such as composite keys

This is a nice pun as a basic thing is a basic key. Anyway, we do not have a timeline except for "at some point in the future" as the team is currently busy with higher-priority topics.

We always welcome contributions that help us and the project to get things done and to speed things up.

[amundsor]

@morfsnz Would be interesting if you could provide som real world examples / use-cases so I could explore possible solutions using Spring Data JDBC.

In general, I have tried to give my 2 cents on possible handling this on StackOverflow.
Link to answer
Shortly - either fragment interface implementad by repository (for aggregate roots) or using Set/Map.

[nbir94]

Hey everyone! Just want to add a quick note.

As it was written above, @porfirioribeiro's workaround did not work for .safe() method of R2dbcRepository. But I tried the same with ReactiveCrudRepository and it worked. Firstly, I was happy with it. But then I found that it works only for INSERT operations. When I try to update an existing element in DB, I see an error because repo tries to make INSERT instead of UPDATE, that violates the PK constraint.

Unfortunately, it still doesn't work properly, and I could only write a custom UPSERT query. But anyway, thanks @porfirioribeiro for his workaround. And thanks to Spring developers for their product, I hope this feature will be implemented in the near future :)

[vcruzmj]

Any info on when this basic feature will be taken into consideration?

[KangoV]

It's been 5 years and this still has not been addressed! Why? It's such a core feature of any ORM.
We've moved to Micronaut and Micronaut Data JDBC which supports @Embeddable and @EmbeddedId. It works just as you would expect (and damned fast). Spring is being left behind imo.

[olamy]

this will be such a nice feature to have!

[francisco-bru]

Any news on this topic?

@schauder, I would like to add that in version 3.2.5 I tested composite keys using @Embedded and @Id and it seemed to work correctly but the same tests in 3.2.7 fail for not resolving the AggregatePath correctly.

java.lang.IllegalArgumentException: Cannot obtain ColumnInfo for embedded path
	at org.springframework.util.Assert.isTrue(Assert.java:129)
	at org.springframework.data.relational.core.mapping.AggregatePath$ColumnInfo.of(AggregatePath.java:332)
	at org.springframework.data.relational.core.mapping.DefaultAggregatePath.lambda$new$1(DefaultAggregatePath.java:45)
	at org.springframework.data.util.Lazy.getNullable(Lazy.java:135)
	at org.springframework.data.util.Lazy.get(Lazy.java:113)
	at org.springframework.data.relational.core.mapping.DefaultAggregatePath.getColumnInfo(DefaultAggregatePath.java:223)

Maybe it was a coincidence but it could be a start for the development of the feature.

[StefanHufschmidt]

With the recently released version 3.3.3 including changes from #1502 the last workaround for us - related to multiple columns as primary key - stopped to work.

We used something like the following to work with a transformation table of an many-to-many relation where two columns in combination are the primary key.

CREATE TABLE authors_books (
    author_id BIGINT,
    book_id BIGINT,
    PRIMARY KEY (author_id, book_id)
);

@Table("authors_books")
data class AuthorBookRelationEntity(
    @Column("author_id")
    val authorId: Long,
    @Column("book_id")
    val bookId: Long,
) : Persistable<AuthorBookRelationEntity> {

    @Transient
    var isNewInstance: Boolean? = false

    override fun getId(): AuthorBookRelationEntity {
        return this
    }

    override fun isNew(): Boolean {
        return isNewInstance ?: false
    }
}

With v3.3.2 it worked fine, but with v3.3.3 we get the exception Required identifier property not found. Since you can't annotate two columns with @Id we can't simply add the identifier property. From my point of view, it isn't even needed since the Persistable already has the getId() method which should return the identifier property.

Nevertheless, it would be awesome to have the possibility to use multiple columns as identifier.

[KangoV]

Any news on this one? I'd really like to switch to this from JPA.