Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Support for Composite Keys using @Embeded and @Id #574

Open
spring-projects-issues opened this issue Apr 4, 2019 · 30 comments · May be fixed by #1957
Open

Support for Composite Keys using @Embeded and @Id #574

spring-projects-issues opened this issue Apr 4, 2019 · 30 comments · May be fixed by #1957
Assignees
Labels
has: votes-jira in: jdbc Spring Data JDBC type: enhancement A general enhancement

Comments

@spring-projects-issues
Copy link

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
Copy link
Author

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
Copy link
Author

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
Copy link

hi
any updates on this?

1 similar comment
@jiahaolinTHG
Copy link

hi
any updates on this?

@ttnsgn
Copy link

ttnsgn commented Apr 12, 2021

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

@kao78
Copy link

kao78 commented Jul 7, 2021

Hi
any update on this feature?

@porfirioribeiro
Copy link

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
Copy link

st0ke commented Jul 21, 2021

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

@xuandungdoan
Copy link

xuandungdoan commented Aug 27, 2021

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

@porfirioribeiro
Copy link

@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
Copy link

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

youngvly added a commit to juniors-dev-study/domain-driven-design that referenced this issue Nov 14, 2021
youngvly added a commit to juniors-dev-study/domain-driven-design that referenced this issue Nov 18, 2021
* [#28] security 적용 (SecurityConfig.kt) , api mock 생성

* [#28] 가입 인증코드 기능구현

* [#28] 가입기능 구현

* [#28] authCode 복합키로 설정
- 복합키 미지원 확인 spring-projects/spring-data-relational#574

* [#28] spring security에서 swagger관련 제외

* [#28] 이벤트 통일 , exception 응답 설정, 트랜잭션 커밋안되는 이슈 수정

* [#28] 이전 상태 체크 추가

* Update user-api/src/main/kotlin/com/sns/user/core/config/SwaggerTag.kt

Co-authored-by: Chanhyeong Cho <chu7825@gmail.com>

* PR반영, 논의내용 반영
 - response는 json으로
 - crudRepository 활용
 - controller에 함께 aggregator 위치. (1:1 매칭)

* 코드정리, test fail 수정

Co-authored-by: Chanhyeong Cho <chu7825@gmail.com>
mp911de pushed a commit that referenced this issue Feb 21, 2022
io.r2dbc.postgresql.codec.Interval is now considered a simple type.

Closes #573
Original pull request: #574.
mp911de added a commit that referenced this issue Feb 21, 2022
Reformat integration test.

See #573
Original pull request: #574.
@schauder schauder added the in: jdbc Spring Data JDBC label Jul 8, 2022
@mjgp2
Copy link

mjgp2 commented Jan 30, 2023

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
Copy link

jason-wilmans commented Jun 1, 2023

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
Copy link
Contributor

schauder commented Jun 1, 2023

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

@mjgp2
Copy link

mjgp2 commented Jun 1, 2023

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

Thanks 😊👍

@jason-wilmans
Copy link

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
Copy link

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
Copy link

kdomagal commented Aug 2, 2023

This is absolutely required. 🙏

@mp911de mp911de changed the title Support for Composite Key via @Embeded plus @Id [DATAJDBC-352] Support for Composite Keys using @Embeded and @Id Sep 13, 2023
@morfsnz
Copy link

morfsnz commented Sep 26, 2023

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
Copy link
Contributor

schauder commented Sep 28, 2023

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

@paulushcgcj
Copy link

@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
Copy link
Member

mp911de commented Sep 29, 2023

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
Copy link

amundsor commented Nov 11, 2023

@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
Copy link

nbir94 commented Jan 2, 2024

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
Copy link

vcruzmj commented Jul 11, 2024

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

@KangoV
Copy link

KangoV commented Aug 1, 2024

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
Copy link

olamy commented Aug 2, 2024

this will be such a nice feature to have!

@francisco-bru
Copy link

francisco-bru commented Aug 21, 2024

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
Copy link

StefanHufschmidt commented Sep 2, 2024

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.

schauder added a commit that referenced this issue Oct 25, 2024
Non trivial aggregates work with single value wrapped PK.
Simple aggregates work with composite id for insert, update, delete and exists.

See #574
schauder added a commit that referenced this issue Oct 29, 2024
Non trivial aggregates work with single value wrapped PK.
Simple aggregates work with composite id for insert, update, delete and exists.

See #574
@KangoV
Copy link

KangoV commented Dec 2, 2024

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

schauder added a commit that referenced this issue Dec 11, 2024
It is now possible to use composite ids, by having an entity reference annotated with `@Id` and `@Embedded`.

Closes #574
schauder added a commit that referenced this issue Dec 12, 2024
Entities may be annotated with `@Id` and `@Embedded`, resulting in a composite id on the database side.
The full embedded entity is considered the id, and therefore the check for determining if an aggregate is considered a new aggregate requiring an insert or an existing one, asking for an update is based on that entity, not its elements.
Most use cases will require a custom `BeforeConvertCallback` to set the id for new aggregate.

For an entity with `@Embedded` id, the back reference used in tables for referenced entities consists of multiple columns, each named by a concatenation of <table-name> + `_` + <column-name>.
E.g. the back reference to a `Person` entity, with a composite id with the properties `firstName` and `lastName` will consist of the two columns `PERSON_FIRST_NAME` and `PERSON_LAST_NAME`.
This holds for directly referenced entities as well as `List`, `Set` and `Map`.

Closes #574
@schauder schauder linked a pull request Dec 12, 2024 that will close this issue
schauder added a commit that referenced this issue Dec 23, 2024
Entities may be annotated with `@Id` and `@Embedded`, resulting in a composite id on the database side.
The full embedded entity is considered the id, and therefore the check for determining if an aggregate is considered a new aggregate requiring an insert or an existing one, asking for an update is based on that entity, not its elements.
Most use cases will require a custom `BeforeConvertCallback` to set the id for new aggregate.

For an entity with `@Embedded` id, the back reference used in tables for referenced entities consists of multiple columns, each named by a concatenation of <table-name> + `_` + <column-name>.
E.g. the back reference to a `Person` entity, with a composite id with the properties `firstName` and `lastName` will consist of the two columns `PERSON_FIRST_NAME` and `PERSON_LAST_NAME`.
This holds for directly referenced entities as well as `List`, `Set` and `Map`.

Closes #574
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
has: votes-jira in: jdbc Spring Data JDBC type: enhancement A general enhancement
Projects
None yet
Development

Successfully merging a pull request may close this issue.