Home > Net >  no converter found capable of converting from AbstractJPAQuery to DTO
no converter found capable of converting from AbstractJPAQuery to DTO

Time:10-19

Can anyone please tell how to use below native query in JPA? Now I am getting error message as converter not found.

Sql query giving proper response in DB(MYSQL)

SELECT SUM(CASE WHEN Status = 'PENDING' THEN 1 ELSE 0 END) AS `pendingRequest`,
       SUM(CASE WHEN Status = 'ACTIVE' or Status ='READY' THEN 1 ELSE 0 END) AS `approvedRequest`,
       SUM(CASE WHEN Status = 'FAILED' THEN 1 ELSE 0 END) AS `rejectedRequest`,
      SUM(CASE WHEN Status = 'ACTIVE' or Status ='READY' or Status ='PENDING' or Status ='FAILED' THEN 1 ELSE 0 END) as totalRequest FROM fiu_consent_db.consent_request;

Query which I have used in JPA Repository

@Query(value = "SELECT SUM(CASE WHEN Status = 'PENDING' THEN 1 ELSE 0 END) AS `pendingRequest`,\n"  
        "       SUM(CASE WHEN Status = 'ACTIVE' or Status ='READY' THEN 1 ELSE 0 END) AS `approvedRequest`,\n"  
        "       SUM(CASE WHEN Status = 'FAILED' THEN 1 ELSE 0 END) AS `rejectedRequest`,\n"  
        "       SUM(CASE WHEN Status = 'ACTIVE' or Status ='READY' or Status ='PENDING' or Status ='FAILED' THEN 1 ELSE 0 END) AS `totalRequest` FROM fiu_consent_db.consent_request",nativeQuery = true)
ConsentRequestCount getAllConsentRequestCount();

DTO that I created

@Data
@NoArgsConstructor
@AllArgsConstructor
public class ConsentRequestCount {

    private long pendingRequest;
    private long approvedRequest;
    private long rejectedRequest;
    private long totalRequest;


}

I have created a separate DTO for these 4 fileds and taken data type as long(tried with int also) but its failing to map.

Can anyone please suggest me on this?

CodePudding user response:

According to this, class-based DTO projections do not work with native queries. You can switch over to interface-based DTO projections by changing your DTO class to interface

So this DTO:

@Data
@NoArgsConstructor
@AllArgsConstructor
public class ConsentRequestCount {

    private long pendingRequest;
    private long approvedRequest;
    private long rejectedRequest;
    private long totalRequest;

}

change it to an interface:

public interface ConsentRequestCount {

    long getPendingRequest();
    long getApprovedRequest();
    long getRejectedRequest();
    long getTotalRequest();
}
  • Related