Linked Questions

Popular Questions

Dashboard Implementation in Spring Boot using JPQL

Asked by At

I have a table named users in mysql database. I need to create some sort of dashboard and fetch the count of the user depending upon their status and type.

enter image description here

I have written the query as below. Now i need to convert this to JPQL so that i can project the result to some object directly.

    Select totalUserCount ,  totalActiveUserCount,  totalInActiveUserCount, totalRetailerUserCount, totalWholesellerUserCount
FROM (
     (SELECT COUNT(*) AS totalUserCount            FROM users u1) AS u1,
     (SELECT COUNT(*) AS totalActiveUserCount      FROM users u2 WHERE u2.status = ‘Activated’) AS u2,
     (SELECT COUNT(*) AS totalInActiveUserCount    FROM users u3 WHERE u3.status = ‘Not Activated’) AS u3,
     (SELECT COUNT(*) AS totalRetailerUserCount    FROM users u4 WHERE u4.type   = ‘Retailer’) AS u4,
     (SELECT COUNT(*) AS totalWholesellerUserCount FROM users u5 WHERE u5.type   = ‘Wholeseller’) AS u5
);

I dont want to use native query as the implementation looks ugly and the type conversion is really troublesome. Below is my implementation

@Query(VALUE="Select totalUserCount ,  totalActiveUserCount,
totalInActiveUserCount, totalRetailerUserCount, totalWholesellerUserCount" +
"FROM (" +
"(SELECT COUNT(*) AS totalUserCount FROM users u1) AS u1," +
"(SELECT COUNT(*) AS totalActiveUserCount      FROM users u2 WHERE u2.status = ‘Activated’) AS u2," +
"(SELECT COUNT(*) AS totalInActiveUserCount    FROM users u3 WHERE u3.status = ‘Not Activated’) AS u3," +
"(SELECT COUNT(*) AS totalRetailerUserCount    FROM users u4 WHERE u4.type   = ‘Retailer’) AS u4," +
"(SELECT COUNT(*) AS totalWholesellerUserCount FROM users u5 WHERE u5.type   = ‘Wholeseller’) AS u5" +
")", nativeQuery = TRUE)
LIST<Object[]> getDashboard();

Now i nees some help to convert it into JPQL so that i can directly project it to Data Transfer Object.

Related Questions