Hi,
I am testing the Criteria API in both PostgreSQL and HSQL. One of the tests pretends to get all the companies having some addresses. My query looks like this:
Code:
public Set<Organization> getOrganizationsByPostalAddress(Set<PostalAddress> postalAddresses) {
final HashSet<Organization> lResult = new HashSet<Organization>();
if (!postalAddresses.isEmpty()) {
List<Organization> list = session.createCriteria(HibernateOrganization.class).add(Restrictions.in(HibernateOrganization.POSTALADDRESS, postalAddresses)).list();
lResult.addAll(list);
}
lResult.remove(null);
return lResult;
}
Hibernate generates the following SQL query:
Code:
select this_.name as name48_0_, this_.emailAddress as emailAdd2_48_0_, this_.street as street48_0_, this_.city as city48_0_, this_.zipcode as zipcode48_0_, this_.telephonenumber as telephon6_48_0_
from de_novatec_persistence_example_step1_hibernate_HibernateSimpleOrganizationTest.ORGANIZATION this_
where (this_.street, this_.city, this_.zipcode) in ((?, ?, ?), (?, ?, ?));
(Lets suppose Hibernate fills the values in the correct order.
http://forum.hibernate.org/viewtopic.php?t=948834&highlight=inexpressionBut today the issue is different)
Running this query in PostgreSQL works great. But with the HSQLDialect it doesn't. HSQL Does not supports:
Code:
SELECT *
FROM table t
WHERE (t.a, t.b, t.c) IN ((?,?,?),(?,?,?))
HSQL throws me the following exception:
Code:
09:32:27,578 ERROR JDBCExceptionReporter:78 - Unexpected token: , in statement [select this_.name as name48_0_, this_.emailAddress as emailAdd2_48_0_, this_.street as street48_0_, this_.city as city48_0_, this_.zipcode as zipcode48_0_, this_.telephonenumber as telephon6_48_0_ from de_novatec_persistence_example_step1_hibernate_HibernateSimpleOrganizationTest.ORGANIZATION this_ where (this_.street, this_.city, this_.zipcode) in ((?, ?, ?), (?, ?, ?))]
org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:2216)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
at org.hibernate.loader.Loader.list(Loader.java:2099)
at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:94)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1569)
at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:283)
at de.novatec.persistence.example.step1.hibernate.HibernateManager.getOrganizationsByPostalAddress(HibernateManager.java:354)
at de.novatec.persistence.example.step1.hibernate.HibernateSimpleOrganizationTest.getOrganizationsByPostalAddress(HibernateSimpleOrganizationTest.java:302)
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 org.junit.internal.runners.TestMethodRunner.executeMethodBody(TestMethodRunner.java:99)
at org.junit.internal.runners.TestMethodRunner.runUnprotected(TestMethodRunner.java:81)
at org.junit.internal.runners.BeforeAndAfterRunner.runProtected(BeforeAndAfterRunner.java:34)
at org.junit.internal.runners.TestMethodRunner.runMethod(TestMethodRunner.java:75)
at org.junit.internal.runners.TestMethodRunner.run(TestMethodRunner.java:45)
at org.junit.internal.runners.TestClassMethodsRunner.invokeTestMethod(TestClassMethodsRunner.java:66)
at org.junit.internal.runners.TestClassMethodsRunner.run(TestClassMethodsRunner.java:35)
at org.junit.internal.runners.TestClassRunner$1.runUnprotected(TestClassRunner.java:42)
at org.junit.internal.runners.BeforeAndAfterRunner.runProtected(BeforeAndAfterRunner.java:34)
at org.junit.internal.runners.TestClassRunner.run(TestClassRunner.java:52)
at junit.framework.JUnit4TestAdapter.run(JUnit4TestAdapter.java:32)
at junit.framework.TestSuite.runTest(TestSuite.java:232)
at junit.framework.TestSuite.run(TestSuite.java:227)
at org.junit.internal.runners.OldTestClassRunner.run(OldTestClassRunner.java:76)
at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:38)
at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:460)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:673)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:386)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)
Caused by: java.sql.SQLException: Unexpected token: , in statement [select this_.name as name48_0_, this_.emailAddress as emailAdd2_48_0_, this_.street as street48_0_, this_.city as city48_0_, this_.zipcode as zipcode48_0_, this_.telephonenumber as telephon6_48_0_ from de_novatec_persistence_example_step1_hibernate_HibernateSimpleOrganizationTest.ORGANIZATION this_ where (this_.street, this_.city, this_.zipcode) in ((?, ?, ?), (?, ?, ?))]
at org.hsqldb.jdbc.Util.throwError(Unknown Source)
at org.hsqldb.jdbc.jdbcPreparedStatement.<init>(Unknown Source)
at org.hsqldb.jdbc.jdbcConnection.prepareStatement(Unknown Source)
at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:505)
at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:423)
at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:139)
at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1547)
at org.hibernate.loader.Loader.doQuery(Loader.java:673)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
at org.hibernate.loader.Loader.doList(Loader.java:2213)
... 31 more
Is there a workaround? Am I doing something wrong?
Thanks!
Hibernate version:3.2
Mapping documents:
Code:
<hibernate-mapping>
<class name="de.novatec.persistence.example.step1.hibernate.HibernateOrganization" table="ORGANIZATION">
<id name="name" type="java.lang.String" access="field">
<column name="name" />
<generator class="assigned"></generator>
</id>
<component name="postalAddress" class="de.novatec.persistence.example.step1.hibernate.HibernatePostalAddress">
<property name="street" type="string" />
<property name="city" type="string" />
<property name="zipcode" type="string" />
</component>
<!-- More attributes -->
</class>
</hibernate-mapping>
Login
FAQ
Search