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

Since `count` is a private variable you will have to create a setter for it and use it to assign the value. Why don't you follow [naming conventions](https://www.oracle.com/technetwork/java/javase/overview/codeconventions-135099.html), the class name should start with a capital letter. Also the comment is misleading since `count` is not an object. — Boris, Sep 20 '18 at 16:55
Would it work: `Sample sample = new Sample(); sample.setCount(countResult);` where `countResult` has a value from the query. Of course if you have a reference of the `Sample` class you don't have to initialise it. — Boris, Sep 20 '18 at 17:10

score 0 · Answer 1 · answered Sep 21 '18 at 03:18

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

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

1 Answers1