Map sql query result to java object(in Non-Entity class) using spring jpa

Question

I want to assign SQL query result to Java object which is in non-entity class. My query is counting the number of records in Table A mapped to another Table B.

@Query(value="select count(a.id) from table1 a join table2 b on a.id=b.id group by a.id", nativeQuery=true)

Non-Entity class

   public class Sample {

    //assign query result to count variable
    private long count;
   // getters and setters


    }

A and B are Entity class, I'm selecting specified columns of Entity A and B and including that columns in Sample.class and sending data as JSON on REST call.

Now my question is to assign count result to count variable.

Thanks in advance

Answer 1

How to do a JPQL query using a "group by" into a projection (Non-Entity-Class)?

Scenario you have two tables: User and User_Role and you want to know how many users in your system has the "public" role and how many have the "admin" role (Any other roles too if present).

For example: I want a query that will let me know there are two users that have "public" role and one user has the "admin" role.

Simplest Example:

@Query("SELECT ur.roleName, count(u.id) from User u left join u.userRole ur group by ur.roleName")
List<Object[]> getCounts();

In this case dealing with the result is more complicated then you typically would want. You would have to iterate over both the list and array of Objects.

Query into a projection Example:

@Query("SELECT new com.skjenco.hibernateSandbox.bean.GroupResultBean(ur.roleName, count(u.id)) from User u left join u.userRole ur group by ur.roleName")
List<GroupResultBean> getCountsToBean();

This would give you a List that is much better to work with.

Code Example: https://github.com/skjenco/hibernateSandbox/blob/master/src/test/java/com/skjenco/hibernateSandbox/repository/UserProjectionExampleTest.java