java - 在 Spring Boot 中重新创建查询

Question

我正在重新创建一些旧的 Java 代码并使用 Spring Boot 重新制作它。我正在尝试使用 JPA 接口(interface)命令重新创建此查询，但由于“加入”而被难住了。

原始查询是:

select pc.procedureCodeId, PC.AMOUNTTYPE from Section s join SectionContents sc 
    on sc.sectionTitle = s.sectionTitle and sc.cdtVersionId = s.cdtVersionId
   join ProcedureCode pc on pc.procedureCodeId = sc.procedureCodeId and
    pc.cdtVersionId = sc.cdtVersionId and pc.benefitId = ? where
    s.sectionTitle = ? and s.cdtVersionId = ?

在重制中我创建了 3 个实体:

实体部分

@Entity
@Table(name = "SECTION", schema = "BPDMOWNER", catalog = "")
@IdClass(SectionEntityPK.class)
public class SectionEntity {

    private String sectiontitle;
    private long cdtversionid;
    private String filingid;
    private String sectiondescription;
    private String defaultplanname;
    private Timestamp lastupdate;
    private String lastupdatedby;
    private String sectionheading;
    private String schedule;
    private String eocfilingid;

    @Id
    @Column(name = "SECTIONTITLE", nullable = false, length = 30)
    public String getSectiontitle() {
        return sectiontitle;
    }

SectioncontentEntity

@Entity
@Table(name = "SECTIONCONTENTS", schema = "BPDMOWNER", catalog = "")
@IdClass(SectioncontentsEntityPK.class)
public class SectioncontentsEntity {

    private String sectiontitle;
    private long cdtversionid;
    private long sequence;
    private String amounttext;
    private String amounttextspanish;
    private String amounttype;
    private Byte leaderline;
    private Timestamp lastupdate;
    private String lastupdatedby;
    private Long intoc;
    private Byte eocleaderline;
    private SectionEntity section;

    @Id
    @Column(name = "SECTIONTITLE", nullable = false, length = 30)
    public String getSectiontitle() {
        return sectiontitle;
    }

程序代码实体

@Entity
@Table(name = "PROCEDURECODE", schema = "BPDMOWNER", catalog = "")
public class ProcedurecodeEntity {
    private long procedurecodeid;
    private String procedurecode;
    private String proceduredescription;
    private String proceduredescriptionspanish;
    private String proceduredescriptiondbb;
    private String amounttext;
    private String amounttextspanish;
    private String amounttype;
    private String procedurecodecomment;
    private String procedurecoderemark;
    private Timestamp lastupdate;
    private String lastupdatedby;
    private Long benefitid;

    @Id
    @Column(name = "PROCEDURECODEID", nullable = false, precision = 0)
    public long getProcedurecodeid() {
        return procedurecodeid;
    }

如果有人能帮我弄清楚如何使用@Entity 类和@Repository 接口(interface)实现这个查询。

提前谢谢你。

Answer 1

首先，您必须在实体之间创建关联才能使 Jpa 存储库查询正常工作。

实体部分

@Entity
@Table(name = "SECTION", schema = "BPDMOWNER", catalog = "")
@IdClass(SectionEntityPK.class)
public class SectionEntity {

    private String sectiontitle;
    private long cdtversionid;
    private String filingid;
    private String sectiondescription;
    private String defaultplanname;
    private Timestamp lastupdate;
    private String lastupdatedby;
    private String sectionheading;
    private String schedule;
    private String eocfilingid;

    @OneToOne
    @JoinColumn(name="section_contents_cdtversionid") // just your column name for the association
    private SectionContentsEntity sectionContent;

    @Id
    @Column(name = "SECTIONTITLE", nullable = false, length = 30)
    public String getSectiontitle() {
        return sectiontitle;
    }

SectionContentsEntity

@Entity
@Table(name = "SECTIONCONTENTS", schema = "BPDMOWNER", catalog = "")
@IdClass(SectioncontentsEntityPK.class)
public class SectioncontentsEntity {

    private String sectiontitle;
    private long cdtversionid;
    private long sequence;
    private String amounttext;
    private String amounttextspanish;
    private String amounttype;
    private Byte leaderline;
    private Timestamp lastupdate;
    private String lastupdatedby;
    private Long intoc;
    private Byte eocleaderline;

    @OneToOne(mappedBy="sectionContent")
    private SectionEntity section;

    @OneToOne
    @JoinColumn(name="procedure_code_id") // just your column name for the association
    private ProcedurecodeEntity procedureCode;

    @Id
    @Column(name = "SECTIONTITLE", nullable = false, length = 30)
    public String getSectiontitle() {
        return sectiontitle;
    }

程序代码实体

@Entity
@Table(name = "PROCEDURECODE", schema = "BPDMOWNER", catalog = "")
public class ProcedurecodeEntity {
    private long procedurecodeid;
    private String procedurecode;
    private String proceduredescription;
    private String proceduredescriptionspanish;
    private String proceduredescriptiondbb;
    private String amounttext;
    private String amounttextspanish;
    private String amounttype;
    private String procedurecodecomment;
    private String procedurecoderemark;
    private Timestamp lastupdate;
    private String lastupdatedby;
    private Long benefitid;

    @OneToOne(mappedBy="procedureCode")
    private SectionContent sectionContent;

    @Id
    @Column(name = "PROCEDURECODEID", nullable = false, precision = 0)
    public long getProcedurecodeid() {
        return procedurecodeid;
    }

如果您的关联不是 OneToOne 或双向关联，只需根据您的需要进行更改即可。

然后在你的 jpa 查询中你应该像这样使用它:

public interface ProcedurecodeEntityRepository extends JpaRepository<ProcedurecodeEntity, Integer> {
    @Query("select pc.procedurecodeid, pc.amounttype from ProcedurecodeEntity pc " + 
          "join pc.sectionContent sc " +
          "join pc.sectionContent.section s " + 
          "where pc.benefitid = ?1 " +
          "and s.sectiontitle = ?2 " +
          "and s.cdtversionid = ?3")
   ProcedurecodeEntity findByBenefitIdAndSectionTitleAndCdtVersionId(long benefitid, String sectiontitle, long cdtversionid);
}