If I use setMaxResult() in a Query, I get:
org.hibernate.exception.SQLGrammarException: could not execute query
I've testet serveral Versions:
HQL with setMaxResult() -> Exception
Code:
public void testWithMaxResult() throws Exception {
Long _transactionId = HibernateUtil.getRandomNr();
HibernateUtil.beginTransaction(this, _transactionId);
Session _session = HibernateUtil.getSession();
List<Test> _result = null;
try {
_result = _session.createQuery("from Test as t")
[b].setMaxResults(20)[/b]
.list();
assertTrue("Should throw Exception", false);
} catch (HibernateException e) {
e.printStackTrace();
throw e;
}
if (_result != null) {
for (Test _tst: _result) {
System.out.println("Id: " + _tst.getId().toString() + ", name : " + _tst.getName());
}
}
HibernateUtil.commitTransaction(this, _transactionId);
}
With SQLQuery and setMaxResult() -> Exception
Code:
public void testWithSQLMaxResult() throws Exception {
Long _transactionId = HibernateUtil.getRandomNr();
HibernateUtil.beginTransaction(this, _transactionId);
Session _session = HibernateUtil.getSession();
List<Test> _result = null;
try {
_result = _session.createSQLQuery("select {test.*} from tst_test as test")
.addEntity("test", Test.class)
[b].setMaxResults(10)[/b]
.list();
assertTrue("Should throw Exception", false);
} catch (HibernateException e) {
e.printStackTrace();
throw e;
}
if (_result != null) {
for (Test _tst: _result) {
System.out.println("Id: " + _tst.getId().toString() + ", name : " + _tst.getName());
}
}
HibernateUtil.commitTransaction(this, _transactionId);
}
With SQLQuery, but I coded the limit-Claus in the sql-String -> It works
Code:
public void testWithSQLLimitClause() throws Exception {
Long _transactionId = HibernateUtil.getRandomNr();
HibernateUtil.beginTransaction(this, _transactionId);
Session _session = HibernateUtil.getSession();
List<Test> _result = null;
try {
_result = _session.createSQLQuery("select {test.*} from tst_test as test limit [b]10[/b]")
.addEntity("test", Test.class)
.list();
} catch (HibernateException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
for (Test _tst: _result) {
System.out.println("Id: " + _tst.getId().toString() + ", name : " + _tst.getName());
}
HibernateUtil.commitTransaction(this, _transactionId);
}
If I take the logged SQL, that is built from the HQL-Query and replace the ? with a Number I can execute it on mysql without an error. It seems, that the generated SQL works.
Code:
select test.id as id0_, test.name as name0_0_, test.version as version0_0_ from tst_test as test limit 10
Here my configuration
Code:
<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
<session-factory>
<!-- <property name="connection.datasource">java:comp/env/jdbc/hibalfluda</property> -->
<property name="connection.username">user</property>
<property name="connection.url">jdbc:mysql:///server</property>
<property name="connection.password">password</property>
<property name="show_sql">true</property>
<property name="connection.driver_class">org.gjt.mm.mysql.Driver</property>
<property name="dialect">org.hibernate.dialect.MySQLInnoDBDialect</property>
<!-- Mapping files -->
<mapping package="ch.dabo.test"/>
<mapping class="ch.dabo.test.Test"/>
</session-factory>
</hibernate-configuration>
And full Stacktrace:
Code:
Hibernate: select test.id as id0_, test.name as name0_0_, test.version as version0_0_ from tst_test as test limit ?
11:05:50,640 WARN JDBCExceptionReporter:org.hibernate.util.JDBCExceptionReporter.logExceptions(JDBCExceptionReporter.java:57) - SQL Error: 1064, SQLState: 42000
11:05:50,640 ERROR JDBCExceptionReporter:org.hibernate.util.JDBCExceptionReporter.logExceptions(JDBCExceptionReporter.java:58) - 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
11:05:50,640 WARN JDBCExceptionReporter:org.hibernate.util.JDBCExceptionReporter.logExceptions(JDBCExceptionReporter.java:57) - SQL Error: 1064, SQLState: 42000
org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.java:70)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:1518)
at org.hibernate.loader.Loader.list(Loader.java:1498)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:103)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1340)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:151)
at ch.dabo.test.TestTest.testWithSQLMaxResult(TestTest.java:88)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
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:474)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:342)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:194)
Caused by: java.sql.SQLException: 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:2851)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1534)
at com.mysql.jdbc.ServerPreparedStatement.serverPrepare(ServerPreparedStatement.java:1485)
at com.mysql.jdbc.ServerPreparedStatement.<init>(ServerPreparedStatement.java:151)
at com.mysql.jdbc.Connection.prepareStatement(Connection.java:1309)
at com.mysql.jdbc.Connection.prepareStatement(Connection.java:1281)
at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:379)
at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:320)
at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:86)
at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1089)
at org.hibernate.loader.Loader.doQuery(Loader.java:365)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:206)
at org.hibernate.loader.Loader.doList(Loader.java:1515)
... 20 more
11:05:50,656 ERROR JDBCExceptionReporter:org.hibernate.util.JDBCExceptionReporter.logExceptions(JDBCExceptionReporter.java:58) - 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
System:
Hibernate 3.0 final
hibernate-annotations-3.0beta1
mysql 4.1.9
mysql-connector-java-3.1.6
Have I vorgotten something or is this a bug? Could it be, that the Limit ist not set and the Sql is executed with the ? ?
Login
FAQ
Search
