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, the class name should start with a capital letter. Also the comment is misleading since count is not an object. — Boris
– Boris, Commented Sep 20, 2018 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
– Boris, Commented Sep 20, 2018 at 17:10

Stephen · Accepted Answer · 2018-09-21 03:18:01Z

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related