Hibernate ProjectionList for getting count from table with column value restrictions

Question

class Books{
    private int bookId; //primary key
    private String bookName;
    Private String category;
}

Consider the above model class(Books) where it has category variable as A,B,C.

It is stored in table - 'books' with column names 'book_id' ,'book_name' , 'category'(category values will be either A or B or C).

Using Hibernate ProjectionList, I would like to have

1.count of book_id(from DB table) with category value as A 
2.count of book_id(from DB table) with category value as B 
3.count of book_id(from DB table) with category value as C

Assign it to a 3 separate int values countA,countB,countC

Possible duplicate of [How do we count rows using Hibernate?](http://stackoverflow.com/questions/1372317/how-do-we-count-rows-using-hibernate) — Tschallacka, Aug 10 '16 at 07:02
@MichaelDibbets Your link answers projection count of entire table not with restrictions — sundaresan.c, Aug 10 '16 at 07:08
check the second answer... or the third, or the fourth or the fifth... — Tschallacka, Aug 10 '16 at 07:10
@MichaelDibbets I dont find any restrictions involvement there. Don't find solution for my asked question. — sundaresan.c, Aug 10 '16 at 07:13
What if you use native query and get sum of all categories using `GROUP BY`? — java8.being, Aug 10 '16 at 07:43

score 0 · Answer 1 · edited May 23 '17 at 12:00

0

Just to show one of the methods to do it, als linked in How do we count rows using Hibernate? combined with Doing an "IN" query with Hibernate

Integer count = (Integer) session.CreateQuery("select count(*) from Books where category = ?1")
     .query.setParameter(1, "A")
     .uniqueResult();

edited May 23 '17 at 12:00

Community

1
1

answered Aug 10 '16 at 07:16

Tschallacka

27,901
14
88
133

your answer gives me total count. I want it as a separate count @MichaelDibbets – sundaresan.c Aug 10 '16 at 07:21
Are you really expecting to be spoon fed?? This is all the help i'm giving. learn how to build queries for christs sake. – Tschallacka Aug 10 '16 at 07:22
http://stackoverflow.com/questions/28829818/how-to-create-a-native-query-with-named-parameters – Tschallacka Aug 10 '16 at 07:25
I know it, look at my edited answer, but this is like the first thing you see if you'd read the documentation... Where queries, selecing counts, etc. – Tschallacka Aug 10 '16 at 07:26
Find my answer below @MichaelDibbets – sundaresan.c Aug 10 '16 at 08:56

score 0 · Answer 2 · answered Aug 10 '16 at 08:54

                Criteria cr = session.createCriteria(Book.class);
                ProjectionList projList = Projections.projectionList();        
                projList.add(Projections.groupProperty("category"));
                projList.add(Projections.count("category").as("count"));
                cr.setProjection(projList);
                List<Object[]> rows = cr.list();
                if(rows.size()>0){
                    for(Object[] itr : rows){
                        if("A".equalsIgnoreCase(((String) itr[0]).trim())){
                            int countA = (long)itr[1];
                        }
                        if("B".equalsIgnoreCase(((String) itr[0]).trim())){
                            int countB = (long)itr[1];
                        }
                        if("C".equalsIgnoreCase(((String) itr[0]).trim())){
                            int countC = (long)itr[1];
                        }
                    }
                }

Hibernate ProjectionList for getting count from table with column value restrictions

2 Answers2