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

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

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:

CREATE TABLE public.mm_enterprise (
                                      systemcode varchar(64) NOT NULL,
                                      lid varchar(255) NOT NULL,
                                      euid int8 NOT NULL,
                                      CONSTRAINT pk_enterprise PRIMARY KEY (systemcode, lid, euid)
);
CREATE INDEX mm_enterprise1 ON public.mm_enterprise USING btree (euid);

and entity for that:

@Entity
@Table(name = "mm_enterprise")
public class EUID {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "lid", nullable = false)
    private String lid;

    @Column(name = "systemcode", nullable = false)
    private String systemcode;

    @Column(name = "euid", nullable = false)
    private Integer euid;

    public EUID() {
    }

    public String getSystemcode() {
        return systemcode;
    }

    public void setSystemcode(String systemcode) {
        this.systemcode = systemcode;
    }

    public Integer getEuid() {
        return euid;
    }

    public void setEuid(Integer euid) {
        this.euid = euid;
    }

    public String getLid() {
        return lid;
    }

    public void setLid(String lid) {
        this.lid = lid;
    }
}

Repository for findAll() looks like

@Repository
public interface EUIDRepository extends JpaRepository<EUID, String> {

    List<EUID> findAll();
}

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

Hibernate: 
    select
        euid0_.lid as lid1_0_,
        euid0_.euid as euid2_0_,
        euid0_.systemcode as systemco3_0_ 
    from
        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:

CREATE TABLE public.mm_enterprise (
                                      systemcode varchar(64) NOT NULL,
                                      lid varchar(255) NOT NULL,
                                      euid int8 NOT NULL,
                                      CONSTRAINT pk_enterprise PRIMARY KEY (systemcode, lid, euid)
);
CREATE INDEX mm_enterprise1 ON public.mm_enterprise USING btree (euid);

and entity for that:

@Entity
@Table(name = &quot;mm_enterprise&quot;)
public class EUID {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = &quot;lid&quot;, nullable = false)
    private String lid;

    @Column(name = &quot;systemcode&quot;, nullable = false)
    private String systemcode;

    @Column(name = &quot;euid&quot;, nullable = false)
    private Integer euid;

    public EUID() {
    }

    public String getSystemcode() {
        return systemcode;
    }

    public void setSystemcode(String systemcode) {
        this.systemcode = systemcode;
    }

    public Integer getEuid() {
        return euid;
    }

    public void setEuid(Integer euid) {
        this.euid = euid;
    }

    public String getLid() {
        return lid;
    }

    public void setLid(String lid) {
        this.lid = lid;
    }
}

Repository for findAll() looks like

@Repository
public interface EUIDRepository extends JpaRepository&lt;EUID, String&gt; {

    List&lt;EUID&gt; findAll();
}

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

Hibernate: 
    select
        euid0_.lid as lid1_0_,
        euid0_.euid as euid2_0_,
        euid0_.systemcode as systemco3_0_ 
    from
        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类如下:

@Data
@NoArgsConstructor
@EqualsAndHashCode
public class SystemcodeLidEuidId implements Serializable {

    private static final long serialVersionUID = -909206262878526790L;

    private String lid;
    private String systemcode;
    private Long euid;
}

然后创建实体类如下:

@Entity
@Table(name = "mm_enterprise")
@AllArgsConstructor
@NoArgsConstructor
@IdClass(SystemcodeLidEuidId.class)
public class EUID implements Serializable {

    private static final long serialVersionUID = -909206262878526790L;

    @Id
    @Column(name = "lid")
    private String lid;

    @Id
    @Column(name = "systemcode")
    private String systemcode;

    @Id
    @Column(name = "euid")
    private Long euid;

}

#2 使用@Embeddable@EmbeddedId

创建嵌入式ID类如下:

@Data
@Embeddable
public class OrderEntryPK implements Serializable {

    private String lid;
    private String systemcode;

}

然后创建实体类如下:

@Data
@Entity
@Table(name = "mm_enterprise")
public class EUID {

    @EmbeddedId
    private OrderEntryPK id;

    @Column(name = "euid", nullable = false)
    private Long euid;

}
英文:

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

@Data
@NoArgsConstructor
@EqualsAndHashCode
public class SystemcodeLidEuidId implements Serializable {

    private static final long serialVersionUID = -909206262878526790L;

    private String lid;
    private String systemcode;
    private Long euid;
}

and then entity as

@Entity
@Table(name = &quot;mm_enterprise&quot;)
@AllArgsConstructor
@NoArgsConstructor
@IdClass(SystemcodeLidEuidId.class)
public class EUID implements Serializable {

    private static final long serialVersionUID = -909206262878526790L;

    @Id
    @Column(name = &quot;lid&quot;)
    private String lid;

    @Id
    @Column(name = &quot;systemcode&quot;)
    private String systemcode;

    @Id
    @Column(name = &quot;euid&quot;)
    private Long euid;

}

#2 Using @@Embeddable and @EmbeddedId

@Data
@Embeddable
public class OrderEntryPK implements Serializable {

    private String lid;
    private String systemcode;

}

and an entity

@Data
@Entity
@Table(name = &quot;mm_enterprise&quot;)
public class EUID {

    @EmbeddedId
    private OrderEntryPK id;

    @Column(name = &quot;euid&quot;, nullable = false)
    private Long euid;

}

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

发表评论

匿名网友

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

确定