Hi,
I have been using Hibernate for a few months now and I have to say it is by far the best ORM solution I have used, so first of all I would like to say thanks to all the Hibernate developers for making my life a lot easier.
On to my question: this regards the use of Criteria Queries, and particularly using Restrictions.in(), when using Hibernate with the Apache Derby database.
Here is my scenario:
1) I query one table in the database to lookup a set of values, which are placed in a Set.
2) I use this set as the base of a Restrictions.in() query.
The following should illustrate:
Code:
List<EntityA> listEntityA = session.findByExample(...);
Set<String> entityNames = new HashSet<String>();
for (EntityA entityA : listEntityA) {
entityNames.add(entityA.getName);
}
List<EntityB> listEntityB = session
.createCriteria()
.add(Restrictions.in("name", listEntityB)
.list();
The problem is, if the collection does not contain any elements, i.e. is empty, running the Restrictions.in() query results in a low level SQL syntax error with Apache Derby:
Code:
Caused by: java.sql.SQLException: Syntax error: Encountered ")" at line 1, column 1106.
at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(Unknown Source)
at org.apache.derby.jdbc.Driver30.newEmbedPreparedStatement(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
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 com.mchange.v2.c3p0.stmt.GooGooStatementCache$1StmtAcquireTask.run(GooGooStatementCache.java:525)
at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)
Caused by: ERROR 42X01: Syntax error: Encountered ")" at line 1, column 1106.
at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
at org.apache.derby.impl.sql.compile.ParserImpl.parseStatement(Unknown Source)
at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)
at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown Source)
... 12 more
I am aware that this sort of query is not usually necessary as Foreign Keys can be used instead. However, I recently discovered that Hibernate does not support ON DELETE SET NULL cascading and so removed the FK dependency.
A simple workaround is to simply check if the Set from 1) is empty - if it is then don't execute the query. However, I thought I would raise this in case it is a bug.
My mapping relationships are fairly complicated, so forgive me for not posting them here. I hope the explanation is detailed enough.
Hibernate version: 3.2.5
Name and version of the database you are using: Apache Derby version 10.3.1.4
Login
FAQ
Search