Hibernate version: 2.1.7


Hi.

I am using MySQL 4.1.7 and want to count the number of query results without actually returning them. Here is the query:

countsOfQueryResults = ((Integer)session.iterate("select count(*) from PERSON").next() ).intValue();


Unfortunately this results in

Hibernate: select count(*) as x0_0_ from
(util.JDBCExceptionReporter 57 ) SQL Error: 1064, SQLState: 42000
(util.JDBCExceptionReporter 58 ) Syntax error or access violation message from server: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1"
(util.JDBCExceptionReporter 57 ) SQL Error: 1064, SQLState: 42000
(util.JDBCExceptionReporter 58 ) Syntax error or access violation message from server: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1"

The error is the missing table name which was explicitly set in the sql query. Why is it omitted?

Any help is appreciated.

Thanks,
Sven

For a starter, use session.find() and if that does not work, supply more information.

does not work either. Here is the code, now using session.find:

public int getCountOfQueryResults(Class persistedClass) throws InfrastructureException{
int countsOfQueryResults = 0;

String tableName = HibernateUtil.getConfiguration()
.getClassMapping(persistedClass)
.getTable().getName();

Session session = HibernateUtil.getSession();

try {
countsOfQueryResults = session.find("select count(*) from :tableName").size();
} catch (HibernateException e) {
throw new InfrastructureException(e);
}

return countsOfQueryResults;
}

I get the same exception. The tableName PERSON is resolved from Person.class which is the persistedClass parameter.

Where do you fill in your :tableName?

Jos

ups, my mistake due to copy & paste.. forget about :tableName. Even if I replace :tableName with the actual table name PERSON as string I still get the exception. Here is the logging output:


(cfg.Environment 478 ) Hibernate 2.1.7
(cfg.Environment 507 ) hibernate.properties not found
(cfg.Environment 538 ) using CGLIB reflection optimizer
(cfg.Environment 567 ) using JDK 1.4 java.sql.Timestamp handling
(cfg.Configuration 900 ) configuring from resource: /hibernate.cfg.xml
(cfg.Configuration 872 ) Configuration resource: /hibernate.cfg.xml
(cfg.Configuration 331 ) Mapping resource: de/soccermanager/person/entity/Person.hbm.xml
(cfg.Binder 230 ) Mapping class: de.soccermanager.person.entity.Person -> PERSON
(cfg.Binder 201 ) Mapping joined-subclass: de.soccermanager.person.entity.Player -> PLAYER
(cfg.Binder 201 ) Mapping joined-subclass: de.soccermanager.person.entity.TeamManager -> TEAM_MANAGER
(cfg.Configuration 331 ) Mapping resource: de/soccermanager/person/entity/Address.hbm.xml
(cfg.Binder 230 ) Mapping class: de.soccermanager.person.entity.Address -> ADDRESS
(cfg.Configuration 331 ) Mapping resource: de/soccermanager/team/entity/Team.hbm.xml
(cfg.Binder 230 ) Mapping class: de.soccermanager.team.entity.Team -> TEAM
(cfg.Configuration 1058) Configured SessionFactory: null
(cfg.Configuration 632 ) processing one-to-many association mappings
(cfg.Binder 1182) Mapping collection: de.soccermanager.person.entity.TeamManager.listManagedTeams -> TEAM
(cfg.Binder 1182) Mapping collection: de.soccermanager.team.entity.Team.players -> PLAYER
(cfg.Configuration 641 ) processing one-to-one association property references
(cfg.Configuration 666 ) processing foreign key constraints
(dialect.Dialect 86 ) Using dialect: net.sf.hibernate.dialect.MySQLDialect
(cfg.SettingsFactory 70 ) Maximim outer join fetch depth: 2
(cfg.SettingsFactory 74 ) Use outer join fetching: true
(connection.DriverManagerConnectionProvider 42 ) Using Hibernate built-in connection pool (not for production use!)
(connection.DriverManagerConnectionProvider 43 ) Hibernate connection pool size: 1
(connection.DriverManagerConnectionProvider 47 ) JDBC isolation level: READ_COMMITTED
(connection.DriverManagerConnectionProvider 77 ) using driver: com.mysql.jdbc.Driver at URL: jdbc:mysql://localhost:3306/mytestdbhibi
(connection.DriverManagerConnectionProvider 78 ) connection properties: {user=xxx, password=xxx}
(transaction.TransactionManagerLookupFactory 33 ) No TransactionManagerLookup configured (in JTA environment, use of process level read-write cache is not recommended)
(cfg.SettingsFactory 114 ) Use scrollable result sets: true
(cfg.SettingsFactory 117 ) Use JDBC3 getGeneratedKeys(): true
(cfg.SettingsFactory 120 ) Optimize cache for minimal puts: false
(cfg.SettingsFactory 126 ) echoing all SQL to stdout
(cfg.SettingsFactory 129 ) Query language substitutions: {}
(cfg.SettingsFactory 140 ) cache provider: net.sf.ehcache.hibernate.Provider
(cfg.Configuration 1121) instantiating and configuring caches
(impl.SessionFactoryImpl 119 ) building session factory
(impl.SessionFactoryObjectFactory 82 ) Not binding factory to JNDI, no JNDI name configured
tablename = PERSON //Stdout for testing purposes

Hibernate: select count(*) as x0_0_ from
(util.JDBCExceptionReporter 57 ) SQL Error: 1064, SQLState: 42000
(util.JDBCExceptionReporter 58 ) Syntax error or access violation message from server: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1"
(util.JDBCExceptionReporter 57 ) SQL Error: 1064, SQLState: 42000
(util.JDBCExceptionReporter 58 ) Syntax error or access violation message from server: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1"


And here is the full stack trace:

de.persistencemanager.util.InfrastructureException: net.sf.hibernate.exception.SQLGrammarException: Could not execute query
at de.test.TestDeleteData.testDeleteSinglePlayer(TestDeleteData.java:80)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at junit.framework.TestCase.runTest(TestCase.java:154)
at junit.framework.TestCase.runBare(TestCase.java:127)
at junit.framework.TestResult$1.protect(TestResult.java:106)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.framework.TestResult.run(TestResult.java:109)
at junit.framework.TestCase.run(TestCase.java:118)
at junit.framework.TestSuite.runTest(TestSuite.java:208)
at junit.framework.TestSuite.run(TestSuite.java:203)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:421)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:305)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:186)
Caused by: de.persistencemanager.util.InfrastructureException: net.sf.hibernate.exception.SQLGrammarException: Could not execute query
at de.persistencemanager.dao.AbstractDAO.getCountOfQueryResults(AbstractDAO.java:58)
at de.test.TestDeleteData.testDeleteSinglePlayer(TestDeleteData.java:63)
... 15 more
Caused by: net.sf.hibernate.exception.SQLGrammarException: Could not execute query
at net.sf.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.java:69)
at net.sf.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:30)
at net.sf.hibernate.impl.SessionImpl.convert(SessionImpl.java:4110)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1556)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1530)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1522)
at de.persistencemanager.dao.AbstractDAO.getCountOfQueryResults(AbstractDAO.java:55)
... 16 more
Caused by: java.sql.SQLException: Syntax error or access violation message from server: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1"
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1997)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1167)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1278)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2251)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1586)
at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:88)
at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:875)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:269)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:133)
at net.sf.hibernate.loader.Loader.doList(Loader.java:1033)
at net.sf.hibernate.loader.Loader.list(Loader.java:1024)
at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:854)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1553)
... 19 more

you could do it like this:

Integer count =
(Integer) session.createQuery("select count(*) from PERSON")
.uniqueResult();

Regards,

Jos

Use the class name, not the table name.

Yupp, this did the trick!

What happens behind the scenes here? Does Hibernate investigate the string and if it finds a non-sql command it tries to find the appropriate class/properties and therefore replaces the classname with the mapped tablename?! I thought the sql string is just passed through...

Thx!
Sven[/b]

just take the sources and run in debug, you'll learn a lot