Hi there,
Im having a problem with getting native SQL to work with a count query. I get the following error message:

net.sf.hibernate.MappingException: No persister for: java.lang.Integer

The method causing the problem is this ultimately this one:
createSQLQuery(getCountQuery(), "a", Integer.class);

I then have some more code which is supposed to make use of the number (returned as an Integer) returned from the query.

The SQL query to run is this one:
SELECT DISTINCT COUNT(*) FROM cks_group a, cks_group_fti f1 WHERE f1.string ~'^stuff' AND a.oid=f1.id;

The stuff after the WHERE is postgreSQL specific Free Text Searching stuff, which is why we have to use native SQL, as Hibernate doesnt currently support using underlying free text searching facilities.

One thing to note is that the return alias never actually appears in the query and isnt needed really (?), however leaving this as "null" simply results in the same error message anyway.

Does anyone know why this exception is being thrown?

Jon

createSQLQuery currently can only return Hibernate mapped classes, not primitive values. Use session.connection() to get the JDBC Connection and use a normal JDBC SQL query.

This hasnt worked either...same error message

SELECT DISTINCT COUNT(*) {a.*} FROM cks_group a, cks_group_fti f1 WHERE f1.string ~'^null' AND a.oid=f1.id

used in..

createSQLQuery(getCountQuery(), "a", Integer.class);

createSQLQuery cant return an Integer! Only mapped classes. As I said, use the JDBC connection from session.connection()

Hmmm. I dont think youre right there, you know.

I already have HQL queries which do exactly this and utilize the createQuery() method.

Im also pretty sure that I wrote one for createSQLQuery() when we were using mySQL and it worked well. Unfortunately when we changed databases I wiped the rewrote the code and now it doesnt work... and here I am.

But even if you are partially correct and it works for createQuery(), but not for createSQLQuery() .... why?

Jon

I am right. HQL is not the same as a createSqlQuery(). It does not work. If you want to know why - read the source. If you want to have it working - submit a patch.