How to use EntityManager for SELECT COUNT(1)?

[tonylmai]

First I'd like to apologize for posting a persistence question on this board. I've tried other boards but no one's home. I am desperate :-(

I would like to perform a SELECT COUNT(1) FROM a table "TableA" but can't seem to get the SQL statement to work with EntityManager. The following code always return a 0 even I know the table contains hundreds of records.

int rowCnt= (Integer) em.createQuery("SELECT count(1) FROM TableA").getSingleResult();

Can anyone help?

I am using JBoss 4.2.1 with Seam 1.2.1. Thanks.
-tony

[fernando_jmt]

If you're trying to execute an SQL (not JPA QL), just try with this:

int rowCnt= (Integer) em.createNativeQuery("SELECT count(1) FROM TableA").getSingleResult();

HTH.

[fhh]

Or you could try

SELECT count(a) FROM TableA

(untested)

Regards

Felix

[christian.bauer]

count() returns a Long, not an Integer.

[tonylmai]

Thanks for all your help. You guys are the greatest. :-)