Hibernate Books

All times are UTC - 5 hours [ DST ]



Post new topic Reply to topic  [ 3 posts ] 
Author Message
 Post subject: Using Restrictions.in() with an empty collection
PostPosted: Thu May 22, 2008 12:50 pm 
Newbie

Joined: Thu May 22, 2008 12:19 pm
Posts: 2
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


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

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?


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

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...


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 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.