使用构造函数表达式和JPQL使用DTO投影获取列表 [英] Fetch List Using DTO projections using a Constructor Expression and JPQL

问题描述

在DisabScreenRequest上执行搜索，并同时获取其子级详细信息.使用构造函数表达式和JPQL使用DTO投影.

Perform a search on DisabScreenRequest and fetch its child details also. Using DTO projections using a Constructor Expression and JPQL.

带有子表的父实体.

@Entity
@Table(name = "SCREEN_REQUEST")
public class DisabScreenRequest implements Serializable {
    private static final long serialVersionUID = 1L;

    @Id 
    private long requestId;

    @Column(name = "CIVILID")
    private Long civilId;

    @ManyToMany()
    @JoinTable(name = "_DISAB_SCREEN_REQ_DETAILS", joinColumns = {
            @JoinColumn(name = "REQUEST_ID") }, inverseJoinColumns = { @JoinColumn(name = "DISABILTY_TYPE_ID") })
    private Set<DisabMaster> disabilities = new HashSet<DisabMaster>();

    public DisabScreenRequest() {
    }

}

这是残疾表.

@Entity
@Table(name="DISAB_MASTER")
@Immutable 
public class DisabMaster implements Serializable {
    private static final long serialVersionUID = 1L;

    @Id
    @Column(name="DIS_TYPE_ID")
    private long disabilityTypeId;

    @Column(name="DIS_TYPE_DESC")
    private String disTypeDesc;


    public DisabMaster() {
        super();
    }   

}

必须提取所有请求以及每个请求的残障状态.

Had to fetch all the requests along with the disability for each request.

搜索DTO(使用此方法，除了这里提到的一个以外，我还有其他联接可以添加.

Search DTO(using this I had other joins to add other than one mentioned here).

public class RequestSearchDto {

    private long requestId;     

    private Long civilId;   

    private  Set<DisabMaster> disabilities;

    public RequestSearchDto() {
        super();
    }

    public RequestSearchDto(long requestId, Long civilId) {
        super();
        this.requestId = requestId;     
        this.civilId = civilId;     
    }

    public RequestSearchDto(long requestId, Long civilId, Set<DisabMaster>  disabilities) {
        super();
        this.requestId = requestId;     
        this.civilId = civilId;     
        this.disabilities = disabilities;
    }   


}

这是我的JPQL查询

public interface ReposJPQL {

    public String GET__REQUEST = "SELECT DISTINCT new org.test.RequestSearchDto "
            + "(dsr.requestId, dsr.civilId, dsr.disabilities)"
            + " FROM DisabScreenRequest dsr WHERE 1=1 ";


}

这将得到一个 org.hibernate.exception.SQLGrammarException:无法提取ResultSet.

我在这里做错了什么，如何获取子表数据? 让我知道您是否需要任何信息

What Iam I doing wrong here, how can I fetch the child table data ? Let me know if you need any info

堆栈跟踪:

Caused by: java.sql.SQLException: ORA-00936: missing expression

    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:113)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
    at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:754)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:219)
    at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:813)
    at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1051)
    at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:854)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1156)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3415)
    at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3460)
    at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:60)

推荐答案

如果您需要使用其嵌套子实体的集合来获取父实体，则可以通过@EntityGraph批注或JPQL和join fetch使用此简单方法:

If you need to fetch parent entity with a collection of its nested child entities you can use this simple approach using @EntityGraph annotation or JPQL with join fetch:

@Entity
public class Parent {
    //...
    @OneToMany
    private List<Child> children;
}

@Entity
public class Child {
    //...
}

interface ParentRepo extends JpaRepository<Parent, Integer> {

    // with @EntityGraph
    @EntityGraph(attributePaths = "children")
    @Override
    List<Parent> findAll(); 

    // or manually
    @Query("select distinct p from Parent p left join fetch p.children")
    List<Parent> findWithQuery(); 
}

请注意在查询中使用distinct以避免重复记录.

Note to use distinct in your query to avoid duplicate records.

示例:重复的父母实体

更多信息: DATAJPA-1299

这篇关于使用构造函数表达式和JPQL使用DTO投影获取列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！