Spring Boot JPA对具有复合主键的实体使用findAll()返回空列表。

huangapple
go评论108阅读模式
英文:

Spring Boot JPA returns empty list for findAll() of entity with composite key

问题

I'm having a hard time getting data from the Postgres DB schema which looks like:

  1. CREATE TABLE public.mm_enterprise (
  2.                                       systemcode varchar(64) NOT NULL,
  3.                                       lid varchar(255) NOT NULL,
  4.                                       euid int8 NOT NULL,
  5.                                       CONSTRAINT pk_enterprise PRIMARY KEY (systemcode, lid, euid)
  6. );
  7. CREATE INDEX mm_enterprise1 ON public.mm_enterprise USING btree (euid);

and entity for that:

  1. @Entity
  2. @Table(name = "mm_enterprise")
  3. public class EUID {
  5.     @Id
  6.     @GeneratedValue(strategy = GenerationType.IDENTITY)
  7.     @Column(name = "lid", nullable = false)
  8.     private String lid;
  10.     @Column(name = "systemcode", nullable = false)
  11.     private String systemcode;
  13.     @Column(name = "euid", nullable = false)
  14.     private Integer euid;
  16.     public EUID() {
  17.     }
  19.     public String getSystemcode() {
  20.         return systemcode;
  21.     }
  23.     public void setSystemcode(String systemcode) {
  24.         this.systemcode = systemcode;
  25.     }
  27.     public Integer getEuid() {
  28.         return euid;
  29.     }
  31.     public void setEuid(Integer euid) {
  32.         this.euid = euid;
  33.     }
  35.     public String getLid() {
  36.         return lid;
  37.     }
  39.     public void setLid(String lid) {
  40.         this.lid = lid;
  41.     }
  42. }

Repository for findAll() looks like

  1. @Repository
  2. public interface EUIDRepository extends JpaRepository<EUID, String> {
  4.     List<EUID> findAll();
  5. }

but when I will run it the list will come empty. Hibernate is set to validate as well output SQL, which looks like

  1. Hibernate: 
  2.     select
  3.         euid0_.lid as lid1_0_,
  4.         euid0_.euid as euid2_0_,
  5.         euid0_.systemcode as systemco3_0_ 
  6.     from
  7.         mm_enterprise euid0_

and that query works fine when I will execute it directly on the DB.

Any thoughts on what I have skipped? Postgres version is 13.8

PS.

It looks like that lid can't be annotated as @Id for some reason. DB is a 3rd-party DB so I'm not able to add id SERIAL to that table.

英文:

I'm having a hard time getting data from the Postgres DB schema which looks like:

  1. CREATE TABLE public.mm_enterprise (
  2.                                       systemcode varchar(64) NOT NULL,
  3.                                       lid varchar(255) NOT NULL,
  4.                                       euid int8 NOT NULL,
  5.                                       CONSTRAINT pk_enterprise PRIMARY KEY (systemcode, lid, euid)
  6. );
  7. CREATE INDEX mm_enterprise1 ON public.mm_enterprise USING btree (euid);

and entity for that:

  1. @Entity
  2. @Table(name = &quot;mm_enterprise&quot;)
  3. public class EUID {
  5.     @Id
  6.     @GeneratedValue(strategy = GenerationType.IDENTITY)
  7.     @Column(name = &quot;lid&quot;, nullable = false)
  8.     private String lid;
  10.     @Column(name = &quot;systemcode&quot;, nullable = false)
  11.     private String systemcode;
  13.     @Column(name = &quot;euid&quot;, nullable = false)
  14.     private Integer euid;
  16.     public EUID() {
  17.     }
  19.     public String getSystemcode() {
  20.         return systemcode;
  21.     }
  23.     public void setSystemcode(String systemcode) {
  24.         this.systemcode = systemcode;
  25.     }
  27.     public Integer getEuid() {
  28.         return euid;
  29.     }
  31.     public void setEuid(Integer euid) {
  32.         this.euid = euid;
  33.     }
  35.     public String getLid() {
  36.         return lid;
  37.     }
  39.     public void setLid(String lid) {
  40.         this.lid = lid;
  41.     }
  42. }

Repository for findAll() looks like

  1. @Repository
  2. public interface EUIDRepository extends JpaRepository&lt;EUID, String&gt; {
  4.     List&lt;EUID&gt; findAll();
  5. }

but when I will run it the list will come empty. Hibernate is set to validate as well output SQL, which looks like

  1. Hibernate: 
  2.     select
  3.         euid0_.lid as lid1_0_,
  4.         euid0_.euid as euid2_0_,
  5.         euid0_.systemcode as systemco3_0_ 
  6.     from
  7.         mm_enterprise euid0_

and that query works fine when I will execute it directly on the DB.

Any thoughts on what I have skipped? Postgres version is 13.8

PS.

It looks like that lid can't be annotated as @Id for some reason. DB is a 3rd-party DB so I'm not able to add id SERIAL to that table.

答案1

得分: 3

如@Mar-Z在评论中提到的，有两种方法可以正确获取它。

#1 使用@IdClass（首选）

创建ID类如下：

  1. @Data
  2. @NoArgsConstructor
  3. @EqualsAndHashCode
  4. public class SystemcodeLidEuidId implements Serializable {
  6.     private static final long serialVersionUID = -909206262878526790L;
  8.     private String lid;
  9.     private String systemcode;
  10.     private Long euid;
  11. }

然后创建实体类如下：

  1. @Entity
  2. @Table(name = "mm_enterprise")
  3. @AllArgsConstructor
  4. @NoArgsConstructor
  5. @IdClass(SystemcodeLidEuidId.class)
  6. public class EUID implements Serializable {
  8.     private static final long serialVersionUID = -909206262878526790L;
  10.     @Id
  11.     @Column(name = "lid")
  12.     private String lid;
  14.     @Id
  15.     @Column(name = "systemcode")
  16.     private String systemcode;
  18.     @Id
  19.     @Column(name = "euid")
  20.     private Long euid;
  22. }

#2 使用@Embeddable和@EmbeddedId

创建嵌入式ID类如下：

  1. @Data
  2. @Embeddable
  3. public class OrderEntryPK implements Serializable {
  5.     private String lid;
  6.     private String systemcode;
  8. }

然后创建实体类如下：

  1. @Data
  2. @Entity
  3. @Table(name = "mm_enterprise")
  4. public class EUID {
  6.     @EmbeddedId
  7.     private OrderEntryPK id;
  9.     @Column(name = "euid", nullable = false)
  10.     private Long euid;
  12. }
英文:

As @Mar-Z mentioned in the comments there are 2 ways to get it the right way.

#1 Using @IdClass (preferred)

Create ID class as

  1. @Data
  2. @NoArgsConstructor
  3. @EqualsAndHashCode
  4. public class SystemcodeLidEuidId implements Serializable {
  6.     private static final long serialVersionUID = -909206262878526790L;
  8.     private String lid;
  9.     private String systemcode;
  10.     private Long euid;
  11. }

and then entity as

  1. @Entity
  2. @Table(name = &quot;mm_enterprise&quot;)
  3. @AllArgsConstructor
  4. @NoArgsConstructor
  5. @IdClass(SystemcodeLidEuidId.class)
  6. public class EUID implements Serializable {
  8.     private static final long serialVersionUID = -909206262878526790L;
  10.     @Id
  11.     @Column(name = &quot;lid&quot;)
  12.     private String lid;
  14.     @Id
  15.     @Column(name = &quot;systemcode&quot;)
  16.     private String systemcode;
  18.     @Id
  19.     @Column(name = &quot;euid&quot;)
  20.     private Long euid;
  22. }

#2 Using @@Embeddable and @EmbeddedId

  1. @Data
  2. @Embeddable
  3. public class OrderEntryPK implements Serializable {
  5.     private String lid;
  6.     private String systemcode;
  8. }

and an entity

  1. @Data
  2. @Entity
  3. @Table(name = &quot;mm_enterprise&quot;)
  4. public class EUID {
  6.     @EmbeddedId
  7.     private OrderEntryPK id;
  9.     @Column(name = &quot;euid&quot;, nullable = false)
  10.     private Long euid;
  12. }

huangapple
  • 本文由 发表于 2023年5月25日 06:55:13
  • 转载请务必保留本文链接：https://go.coder-hub.com/76327880.html
  • java
  • postgresql
  • spring-boot
  • spring-data-jpa
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定