Count(*) in hibernate

pams · **Joined:** Fri Mar 31, 2006 7:46 am **Posts:** 17

Hi

I have a query.Whenever I execute the query written in my hbm.xml

SELECT count(*)
FROM table tablename

I get, org.hibernate.exception.SQLGrammarException: could not execute query as an Exception.

If, I directly write a query in my persistent Class, then also i get some other exception. Kindly tell me how to use this thing..

focus · **Posted:** Mon Mar 05, 2007 2:11 am

pams wrote:

SELECT count(*)
FROM table tablename

Try

Code:

SELECT COUNT(*) FROM tablename

Cheers
mc

pams · **Joined:** Fri Mar 31, 2006 7:46 am **Posts:** 17

Nope, it didnt work....

pams · **Joined:** Fri Mar 31, 2006 7:46 am **Posts:** 17

hey,

If I use "SELECT COUNT(*) FROM table" in hbm.xml, I am getting SQLgrammerException.

But, If I directly use it in PersistenceManager, then I am getting "undeclared Throwable Exception"

focus · **Posted:** Mon Mar 05, 2007 2:49 am

Can you include more of your hbm.xml - I just want to see the SQL in context.

Cheers
mc

pams · **Joined:** Fri Mar 31, 2006 7:46 am **Posts:** 17

<sql-query name="Querytablename_Keys_operation">
<return alias="tablename" class="path of java file same as tablename"/>
SELECT COUNT(*) FROM TABLE {tablename}
</sql-query>

In hibernate-mapping section, all the columns of that table are mapped to the same java file.

focus · **Posted:** Mon Mar 05, 2007 3:12 am

pams wrote:

<sql-query name="Querytablename_Keys_operation">
<return alias="tablename" class="path of java file same as tablename"/>
SELECT COUNT(*) FROM TABLE {tablename}
</sql-query>

I just realised the problem...

you have defined the return value as a Person table but the query is going to be returning a number only.

Cheers
mc

pams · **Joined:** Fri Mar 31, 2006 7:46 am **Posts:** 17

yeah.. that is true..

CAn you tell me in detail how should I do it, I mean what excatly should me the entire thing that has to be done in hbm and in java file..

focus · **Posted:** Mon Mar 05, 2007 3:50 am

Good question, haven't done this myself, not this way...try something like this.

Code:

<sql-query name="Querytablename_Keys_operation">
<return alias="tablename" class="path of java file same as tablename"/>
SELECT COUNT(*) AS {tablename.recordcount} FROM TABLE {tablename}
</sql-query>

And then in your Java file add:
public void setRecordCount(Integer i) {
this.recordcount = i;
}

Cheers
mc

pams · **Joined:** Fri Mar 31, 2006 7:46 am **Posts:** 17

Hey, I tried that but it didnt work
it gives

java.lang.reflect.UndeclaredThrowableException
at $Proxy0.createQuery(Unknown Source)

as an error.

I tried writing this code in my PersistenceManager class, but the result is the same...

focus · **Posted:** Wed Mar 07, 2007 1:26 am

Code:

<sql-query name="Querytablename_Keys_operation">
<return alias="mycount" class="Integer"/>
SELECT COUNT(*) AS {mycount} FROM TABLE Keys
</sql-query>

Okay, I read a bit more. I would try something like this. The return element defines what is being returned. We simply need to get the value being returned into an Integer object. Hence I have changed the return element to reflect this.

You can hardcode the name of the table where I put "Keys". This is no longer the class value.

Also, in your code where you call this query:

Code:

Integer result = (Integer) session.getNamedQuery("Querytablename_Keys_operation").uniqueResult();

Again, I haven't tested this but I'm sure it is along these lines.

Cheers
mc