Spring data jpa projection could not extract ResultSet

user_15129850 0
Viewed 565

I'm trying to map my entity to projection using the below query but i'm getting error as

Exception : could not extract ResultSet SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet

here is the query

@Query("select rf.rfqID as rfqID,rf.creationDate as creationDate," +
   "rf.deadLineDate as deadLineDate,rf.details as details," +
   "rf.message as message, rf.rfqDoc as rfqDoc," +
   "CASE WHEN (rf.creationDate > CURRENT_DATE) THEN 'open' ELSE 'closed' END as status," +
   "rf.rfqMembers as rfqMembers " +
   "from RFQ rf where rf.createdBy = ?1")
Page<RfqDto> loadAllRfq(String creator, Pageable pageable);

In my Dto I have an extra status column which I don't want to persist in db and would like to get the status via query

here is my projection interface

public interface RfqDto {
    String rfqID();
    Date creationDate();
    Date deadLineDate();
    String details();
    String message();
    String rfqDoc();
    String status();
    List<RfqMember> rfqMembers();
}
spring-data-jpa spring-boot hibernate
1 Answers

The root cause of your problem is here:

In my Dto I have an extra status column which I don't want to persist in db and would like to get the status via query

As it's explained in the documentation:

The important bit here is that the properties defined here exactly match properties in the aggregate root. ... The query execution engine creates proxy instances of that interface at runtime for each element returned and forwards calls to the exposed methods to the target object.

So, you can not use spring data jpa projection for your case. You can not use hibernate/jpa projection as well, because it dose not support collections in row results.

You can try to use Blaze-Persistence Entity Views. See for example this answer.

user_62771040
Related