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