These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]

Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 3 posts ] 
Author Message
 Post subject: Using Restrictions.in() with an empty collection
PostPosted: Thu May 22, 2008 12:50 pm 

Joined: Thu May 22, 2008 12:19 pm
Posts: 2

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:

List<EntityA> listEntityA = session.findByExample(...);
Set<String> entityNames = new HashSet<String>();

for (EntityA entityA : listEntityA) {

List<EntityB> listEntityB = session
   .add(Restrictions.in("name", listEntityB)

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:

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

 Post subject:
PostPosted: Fri May 30, 2008 9:44 am 

Joined: Thu May 22, 2008 12:19 pm
Posts: 2
As I have not received any response for this thus far, I am thinking about submitting a bug, unless anyone has any insight?

 Post subject: Re: Using Restrictions.in() with an empty collection
PostPosted: Fri Nov 17, 2017 9:42 am 

Joined: Fri Nov 17, 2017 9:35 am
Posts: 1
First of all, you have not completed the 'add()' method. Second, what is that you are trying to check with in 'Restrictions.in' query?

.add(Restrictions.in("name", listEntityB)

Instead, add 'entityNames' in place of 'listEntityB'. What you do in this query is that you check whether the table contains your given list or not, like -

select * from <table_name> where <id> in (entityNames)

Hope this helps...

Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 3 posts ] 

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.