-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 8 posts ] 
  Previous topic | Next topic 
Author Message
w0mbat
 Post subject: Is it possible to get a rowCount from a Query?
PostPosted: Wed May 25, 2005 4:44 pm 
Newbie

Joined: Wed May 25, 2005 2:28 pm
Posts: 5
My production application has many hql and sql queries which are presented to the user as search result screens. I am paginating the search screens, so of course I only retrieve partial query results thus:
Code:
query.setFirstResult(pageNumber * resultSize);
query.setMaxResults(resultSize);
query.list();

What I want to know is: is it possible to obtain a row count for a Query object?

What I want is:
Code:
String countQueryStr = "select count(*) from (" + query.getQueryString() + ")";
Query countQuery = session.createQuery(countQueryStr);
int count = (Integer) countQuery.uniqueResult();

However, getQueryString() returns an hql query with named parameters, and I have not discovered a way to copy the named parameter values from the orignal query into the new countQuery. The ideal would of course be query.count(), but from the following bugs I gather that this feature has either been overlooked or rejected:
http://opensource.atlassian.com/projects/hibernate/browse/HB-143
http://opensource.atlassian.com/projects/hibernate/browse/HB-474

Any thoughts?

(I am well aware of Projections and the Criteria interface, and am using them wherever possible, however my app has hundreds of large and nasty hql queries - some named, some not - and Criteria are simply not an option. If I have missed documentation or forum topics that would otherwise answer this question, it is not for lack of looking.)
Top
 Profile  
 
david
 Post subject:
PostPosted: Wed May 25, 2005 10:25 pm 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 7:19 pm
Posts: 2364
Location: Brisbane, Australia
For Hibernate 2.1.x project(s) I coded a HQL builder class that helps build the approrpiate HQL to be used in this sort of situation.
Top
 Profile  
 
w0mbat
 Post subject:
PostPosted: Wed May 25, 2005 11:41 pm 
Newbie

Joined: Wed May 25, 2005 2:28 pm
Posts: 5
Our project was indeed originally architected using Hibernate 2.x, but we have just recently switched to 3.x. And while we do indeed have our own custom HqlBuilder to generate many queries, there yet exist many (~200) named queries stored in the mapping files. These queries come from various people on the other side of the building, and as a result we don't mess with them much, and they are not dynamically generated.

I could, as you seem to be suggesting, pull these out of the mappings and store them as static strings (tedious though that would be), but I was hoping there would be a more elegant solution that would allow me to continue using session.getNamedQuery(). Also, if I use my custom builder, that would mean I would have to generate two distinct Query objects, and populate them both with the named parameter values. I would much rather re-use a single Query, as performance is a big issue in our app.

Thanks for your help.
Top
 Profile  
 
w0mbat
 Post subject:
PostPosted: Thu Jun 02, 2005 6:03 pm 
Newbie

Joined: Wed May 25, 2005 2:28 pm
Posts: 5
I have attempted to wrap an hql query of the form:
Code:
select obj from Object obj

within a count, thus:
Code:
select count(*) from (select obj from Object obj)

Unfortunately, the classic query parser does not support this notation. Does the new parser handle this? If not, what can I do?

Thanks.


Code:
Caused by: org.hibernate.QueryException: in expected: select [select count(*) from (select new foo.bar.MyObject from foo.bar.MyObject where ...)]
        at org.hibernate.hql.classic.FromParser.token(FromParser.java:106)
        at org.hibernate.hql.classic.ClauseParser.token(ClauseParser.java:86)
        at org.hibernate.hql.classic.PreprocessingParser.token(PreprocessingParser.java:108)
        at org.hibernate.hql.classic.ParserHelper.parse(ParserHelper.java:29)
        at org.hibernate.hql.classic.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:176)
        at org.hibernate.hql.classic.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:152)
        at org.hibernate.impl.SessionFactoryImpl.getQuery(SessionFactoryImpl.java:425)
        at org.hibernate.impl.SessionImpl.getQueries(SessionImpl.java:880)
        at org.hibernate.impl.SessionImpl.list(SessionImpl.java:830)
        at org.hibernate.impl.QueryImpl.list(QueryImpl.java:74)
        at org.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:603)
        at fivesquare.common.data.dao.impl.PageImpl.<init>(PageImpl.java:74)
        at fivesquare.common.data.dao.impl.BaseHibernateDAO$9.doInHibernate(BaseHibernateDAO.java:766)
        at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:311)
Top
 Profile  
 
grim_toaster
 Post subject:
PostPosted: Fri Jun 03, 2005 6:54 am 
Newbie

Joined: Tue May 17, 2005 8:09 am
Posts: 5
It's a bit of a nasty workaround, but you can do it like this:

Code:
       Query query = session.getNamedQuery("test.external.query");
       String originalSql = query.getQueryString().trim();
       String lowerSql    = originalSql.toLowerCase();

       if (lowerSql.startsWith("select")) {
           // remove everything up to the " from "...
           int fromIndex = lowerSql.indexOf(" from ");
           originalSql = originalSql.substring(fromIndex + 1);
       }

       Query newQuery = session.createQuery("SELECT count(*) " + originalSql);

       newQuery.setString("variable", "value");
       Integer count = (Integer) newQuery.uniqueResult();
       System.out.println("count=" + count);


And if you want to improve things further, you can remove any potential order by's. Although not the ideal solution, it beats having to write your 200-odd additional queries!

Andy
Top
 Profile  
 
w0mbat
 Post subject:
PostPosted: Mon Jun 13, 2005 10:24 pm 
Newbie

Joined: Wed May 25, 2005 2:28 pm
Posts: 5
Actually, that won't work, since some of my queries have aggregate functions in the select, and these obviously cause the query to generate a different number of results.

I have, however, discovered an alternate approach. By using ScrollableResults I can (inefficiently) hack the row count thus:
Code:
ScrollableResults scrollableResults = query.scroll();
scrollableResults.last();
count = scrollableResults.getRowNumber() + 1;


This seems to work in most cases, although I find that it still fails in queries with inner joins, throwing the following exception:
Code:
org.hibernate.HibernateException: Cannot scroll queries which initialize collections
        at org.hibernate.loader.Loader.scroll(Loader.java:1592)
        at org.hibernate.hql.classic.QueryTranslatorImpl.scroll(QueryTranslatorImpl.java:1069)
        at org.hibernate.impl.SessionImpl.scroll(SessionImpl.java:956)
        at org.hibernate.impl.QueryImpl.scroll(QueryImpl.java:62)
        at org.hibernate.impl.QueryImpl.scroll(QueryImpl.java:52)

For now I just ignore the exception, since so far this approach seems to work for my purposes. I'm sure that somewhere down the line I will discover a case where this will not work, since it is quite obviously the wrong way to go about doing things, but for now it's the best I can do. Hopefully someone on the Hibernate team will temporarily shed their veil of arrogance long enough to address this problem, or at least explain why they have no intention of doing so.
Top
 Profile  
 
david
 Post subject:
PostPosted: Tue Jun 14, 2005 2:22 am 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 7:19 pm
Posts: 2364
Location: Brisbane, Australia
Quote:
Hibernate team will temporarily shed their veil of arrogance long enough to address this problem


I must say you have a nice way to ask for help. Given that I cannot see how my previous answer has waranted this comment then I don't understand how this helps either your situation or my motivation.
I do this as a vocation, eg, for fun. If its not fun (and my time appreciated [I don't ask for thanks at all]) then how can you honestly expect an answer from me (or any other member of the team).

I know the guys. Travelled OS with Gavin. They are truly a great bunch. Maybe this issue has frustrated you and you have typed something that has come across poorly. Sometimes we can get frustrated as well. Anyway, I need a break.
Top
 Profile  
 
christian
 Post subject:
PostPosted: Tue Jun 14, 2005 5:00 am 
Hibernate Team
Hibernate Team

Joined: Mon Aug 25, 2003 9:11 pm
Posts: 4592
Location: Switzerland
Banned
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 8 posts ] 

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.