How to get the number of rows in the result set

xqi · **Joined:** Mon Jul 12, 2004 4:36 pm **Posts:** 6

Hi
I'm trying to get the number of rows in the result set without actually getting the result set. An example query looks like this:

select count(*) from (select ... from ... where ...);

sql allows this. when I tried to run it in hiberate, I got an net.sf.hibernate.QueryException. Is the syntax wrong or this is not allowed in hql? Any help is greatly appreciated. Thanks.

emmanuel · **Posted:** Mon Jul 12, 2004 5:35 pm

Why do you need that?
isn't

Code:

select count(*) from MyClass

enough?

xqi · **Joined:** Mon Jul 12, 2004 4:36 pm **Posts:** 6

emmanuel wrote:

Why do you need that?
isn't

Code:

select count(*) from MyClass

enough?

I'm not counting the rows in one table, but the rows returned by a complex query. Here's the actual query I was trying to run:

select count(*) from (select f.forumId, f.forumName, t.topicId, t.topicTitle, p from Forum f, Topic t, Post p, PostText pt where f.forumId = t.forumId and t.topicId = p.topicId and p.postId = pt.postId and p.postId in (select pt1.postId from PostText pt1 where pt1.postText like '%aa%' or pt1.postText like '%bb%'))

Hope this helps clarify my question. Thanks.

emmanuel · **Posted:** Mon Jul 12, 2004 5:50 pm

Code:

select count(f.forumId) from Forum f, Topic t, Post p, PostText pt where f.forumId = t.forumId and t.topicId = p.topicId and p.postId = pt.postId and p.postId in (select pt1.postId from PostText pt1 where pt1.postText like '%aa%' or pt1.postText like '%bb%') 

xqi · **Joined:** Mon Jul 12, 2004 4:36 pm **Posts:** 6

emmanuel wrote:

Code:

select count(f.forumId) from Forum f, Topic t, Post p, PostText pt where f.forumId = t.forumId and t.topicId = p.topicId and p.postId = pt.postId and p.postId in (select pt1.postId from PostText pt1 where pt1.postText like '%aa%' or pt1.postText like '%bb%') 

It didn't work. Thanks much anyway.

emmanuel · **Posted:** Mon Jul 12, 2004 5:57 pm

How does it fail?
Does replacing count(f.forumId) by count(*) solve the problem?

xqi · **Joined:** Mon Jul 12, 2004 4:36 pm **Posts:** 6

emmanuel wrote:

How does it fail?
Does replacing count(f.forumId) by count(*) solve the problem?

both failed with an net.sf.hibernate.QueryException. not much explanation is given.

thanks.

emmanuel · **Posted:** Fri Jul 16, 2004 9:42 am

Sure Hibernate give some information inside the QueryException (message of the exception). Check the log too.

It should work, are you sure that

Code:

select f.forumId from Forum f, Topic t, Post p, PostText pt where f.forumId = t.forumId and t.topicId = p.topicId and p.postId = pt.postId and p.postId in (select pt1.postId from PostText pt1 where pt1.postText like '%aa%' or pt1.postText like '%bb%')

work ?

xqi · **Joined:** Mon Jul 12, 2004 4:36 pm **Posts:** 6

emmanuel wrote:

Sure Hibernate give some information inside the QueryException (message of the exception). Check the log too.

It should work, are you sure that

Code:

select f.forumId from Forum f, Topic t, Post p, PostText pt where f.forumId = t.forumId and t.topicId = p.topicId and p.postId = pt.postId and p.postId in (select pt1.postId from PostText pt1 where pt1.postText like '%aa%' or pt1.postText like '%bb%')

work ?

the above query works, but if you add select count(*) in the front, it doesn't

emmanuel · **Posted:** Wed Jul 21, 2004 7:40 am

Then, I'm pretty sure that

Code:

select count(f.forumId) from Forum f, Topic t, Post p, PostText pt where f.forumId = t.forumId and t.topicId = p.topicId and p.postId = pt.postId and p.postId in (select pt1.postId from PostText pt1 where pt1.postText like '%aa%' or pt1.postText like '%bb%')

works.

if not try

Code:

select count(f) from Forum f, Topic t, Post p, PostText pt where f.forumId = t.forumId and t.topicId = p.topicId and p.postId = pt.postId and p.postId in (select pt1.postId from PostText pt1 where pt1.postText like '%aa%' or pt1.postText like '%bb%')

I'm interested.

xqi · **Joined:** Mon Jul 12, 2004 4:36 pm **Posts:** 6

emmanuel wrote:

Then, I'm pretty sure that

Code:

select count(f.forumId) from Forum f, Topic t, Post p, PostText pt where f.forumId = t.forumId and t.topicId = p.topicId and p.postId = pt.postId and p.postId in (select pt1.postId from PostText pt1 where pt1.postText like '%aa%' or pt1.postText like '%bb%')

works.

if not try

Code:

select count(f) from Forum f, Topic t, Post p, PostText pt where f.forumId = t.forumId and t.topicId = p.topicId and p.postId = pt.postId and p.postId in (select pt1.postId from PostText pt1 where pt1.postText like '%aa%' or pt1.postText like '%bb%')

I'm interested.

tried all. none worked.

emmanuel · **Posted:** Tue Jul 27, 2004 5:40 am

Please post a simple working test case to JIRA, I'll check that.

gavin · **Posted:** Tue Jul 27, 2004 9:59 am

Emmanuel, we shouldn't clog up JIRA with things like this, which are sure to be due to simple user error. He can post his mappings and stack trace here, and I'm sure we will immediately see the problem.