Get count of results from hibernate select query (query returning an object not Select count(*))

Question

I am having an HQL query

String q = "From TMainTable where a= ? and b= ? and c=?"
Query q = session.createQuery(q).setParameter(0,1).setParameter(1,2).setParameter(2,3);
int count = getCount(q);
List<TMainTable> list = q.setFirstResult(pageNo).setMaxResults(maxLimit).list()

public int getCount(Query q){
   return q.list().size();
}

But the getCount method is taking so much of time. Say my table has 10000 rows and I am getting only 20 records at a time with the actual count. What is the fastest method to get count using the Query object.

I have tried this in the getCount function

public Long getCount(Query q){
String queryString = q.getQueryString();
            String countQueryString = "Select count(*) From "+queryString.substring(0, q.getQueryString().indexOf(" From"));
            System.out.println(countQueryString);
            Long c= (Long)ctx.getSession().createQuery(countQueryString).uniqueResult();
return c;
}

But how can I set the parameters in the new query ? Is there a way I can alter only the query string in Query Object.

check this post http://stackoverflow.com/questions/17383697/how-to-write-a-query-in-hibernate-for-count — Afridi, May 10 '17 at 07:34
its unclear: in the title there is "(query returning an object not Select count(*))" you mean you can't use a count query? — Zeromus, May 10 '17 at 07:45
yeah , All I have in getCount function is the query object. I can get the query string and alter it to make a count query, but again I am not getting how to build the new query — Jerry, May 10 '17 at 07:49
so... if you can actually take the query and replace it... just make your own query and be done with it? It's like taking a hammer to cut something and try to sharpen it when you can just grab a pair of scissor — Zeromus, May 10 '17 at 07:53
Yeah. I just edited the question and added what I was trying to do. In the Query object I need to edit the query string alone — Jerry, May 10 '17 at 07:55
my point still stand.in your example you take the old query, do some substring, create a new query and use that... is it that the actual query you take as input is variable? — Zeromus, May 10 '17 at 07:57
Actually I am looking for scissors only, just that I am not finding that ;). So trying to adjust with hammer — Jerry, May 10 '17 at 07:59

score 1 · Answer 1 · answered May 10 '17 at 07:36

You have to set parameters starting with 1 not with 0 :

Query q = session.createQuery(q).setParameter(0,1).setParameter(1,2).setParameter(2,3);
//--------------------------------------------^-----------------^-----------------^

Instead you have to use :

Query q = session.createQuery(q).setParameter(1,1).setParameter(2,2).setParameter(3,3);
//--------------------------------------------^-----------------^-----------------^

Second to get size of your list, you can use getResultList().size like this :

public int getCount(Query q){
   return q.getResultList().size();
}

I am doing a q.list().size() which is very slow when my data is more — Jerry, May 10 '17 at 07:44

score 0 · Answer 2 · answered May 10 '17 at 07:31

0

You should see what kind of sql is generated by hibernate. It can be very inefficient. If so you should write native sql query.

answered May 10 '17 at 07:31

Mikita Herasiutsin

118
1
2
7

score 0 · Answer 3 · answered May 10 '17 at 07:32

Conventionally, you would have two queries, one to do a count(*) formally, and a second for the actual query. The only way that the main query can get the number of results is by fully executing and then doing a count on the result list, which will be very slow.

score 0 · Answer 4 · answered May 10 '17 at 07:42

Try this out :

  Query query = session.createQuery("select count(*) from Class_NAME as a where YOUR_CONDITION");
        try {
            return Integer.valueOf(String.valueOf(query.uniqueResult()));
        } catch (Exception e) {
            //Print stacktrace
            return 0;
        }

Get count of results from hibernate select query (query returning an object not Select count(*))

4 Answers4