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.

its unclear: in the title there is "(query returning an object not Select count(*))" you mean you can't use a count query? — Zeromus
– Zeromus, Commented May 10, 2017 at 7: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
– Jerry, Commented May 10, 2017 at 7: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
– Zeromus, Commented May 10, 2017 at 7: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
– Jerry, Commented May 10, 2017 at 7:55

Youcef LAIDANI · Accepted Answer · 2017-05-10 07:36:36Z

1

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();
}

answered May 10, 2017 at 7:36

Youcef LAIDANI

60.3k21 gold badges111 silver badges178 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jerry Over a year ago

I am doing a q.list().size() which is very slow when my data is more

Mikita Herasiutsin · Accepted Answer · 2017-05-10 07:31:21Z

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, 2017 at 7:31

Mikita Herasiutsin

1181 gold badge2 silver badges8 bronze badges

Comments

Andrew Jon Dodds · Accepted Answer · 2017-05-10 07:32:57Z

0

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.

answered May 10, 2017 at 7:32

Andrew Jon Dodds

1844 bronze badges

Comments

We are Borg · Accepted Answer · 2017-05-10 07:42:03Z

0

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;
        }

answered May 10, 2017 at 7:42

We are Borg

5,31321 gold badges118 silver badges244 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related