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

Question

我正在重新创建一些旧的 Java 代码并使用 Spring Boot 对其进行改造。我正在尝试使用 JPA 接口命令重新创建此查询，但由于“加入”，我感到很困惑。

原始查询是：

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;
    }

部分内容实体

@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 接口来实现这个查询。

先感谢您。

标签： javaspringhibernatespring-bootjpa

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;
    }

部分内容实体

@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);
}