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)
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With