spring-data-relational
spring-data-relational copied to clipboard
spring-projects
Support for Composite Keys using `@Embeded` and `@Id`
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
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
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.
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
Just in case if somebody wants to know @porfirioribeiro's workaround (no id property) does not work with R2dbcEntityTemplate
@porfirioribeiro your way also can't save any record, the application throws me "IllegalStateException: Required identifier property not found "
@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
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
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?
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.
This is almost on the top of the priority list. Right after the current stuff I'm working which unfortunately is beyond big.
Good to know @schauder ! Is there a roadmap somewhere that's publicly visible?
Thanks 😊👍
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.
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.
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.
@morfsnz Spring Data JDBC does not require an additional generated (or otherwise) key for m-to-n relationships.
@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?
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.
@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.
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 :)
