Fetch List Using DTO projections using a Constructor Expression and JPQL

Question

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

The parent entity with a child table.

@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() {
    }

}

This is the disability table.

@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.

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


}

This is my JPQL query

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


}

This will get an org.hibernate.exception.SQLGrammarException: could not extract ResultSet.

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

Stack trace :

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)

Answer 1

This is a perfect use case for Blaze-Persistence Entity Views.

I created the library to allow easy mapping between JPA models and custom interface defined models, something like Spring Data Projections on steroids. The idea is that you define your target structure the way you like and map attributes(getters) via JPQL expressions to the entity model. Since the attribute name is used as default mapping, you mostly don't need explicit mappings as 80% of the use cases is to have DTOs that are a subset of the entity model.

A mapping for your model could look as simple as the following

@EntityView(DisabScreenRequest.class)
interface RequestSearchDto extends Serializable {
  @IdMapping
  long getRequestId();
  Long getCivilId();
  Set<DisabMaster> getDisabilities();
}

Querying is a matter of applying the entity view to a query, the simplest being just a query by id.

RequestSearchDtodto = entityViewManager.find(entityManager, RequestSearchDto.class, id);

But the Spring Data integration allows you to use it almost like Spring Data Projections: https://persistence.blazebit.com/documentation/1.4/entity-view/manual/en_US/#spring-data-features

Answer 2

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

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

Example: duplicate-parent-entities

More info: DATAJPA-1299

Answer 3

AFAIK, you can't use constructor expression which take a Collection.

See the JPA 2.2 Spec, section 4.14 BNF, read about the constructor expression:

constructor_expression ::=
    NEW constructor_name ( constructor_item {, constructor_item}* )
constructor_item ::=
    single_valued_path_expression |
    scalar_expression |
    aggregate_expression |
    identification_variable