HQL-Delete: potential limitations due to underlying SQL?

jscheffler · **Joined:** Tue Nov 15, 2005 1:54 pm **Posts:** 6

Hello,

Take a look at the following code snipped (executed
in Hibernate 3.1):

Code:

        Query queryObject = session.createQuery("delete from FOO where id in (:list)");
        final Long[] idObjects = new Long[ids.length];
        for(int i = 0; i < ids.length; i++)
        {
          idObjects[i] = new Long(ids[i]);
        }
        queryObject.setParameterList("list",idObjects);
        queryObject.executeUpdate();

I am basically deleting a list of objects by their primary key.
Sounds easy enough, and it works quite well. Unless...

Picture having a couple of thousand elements in that list.
In my case, it were 18500. In that case, the resulting SQL
statement will get quite big. Too big for some Query Parsers.
The following exception was created with SQL Server 2000
(although I also remember similar problems from past
projects with Oracle):

Code:

2005-11-16 17:53:20,916 WARN [org.springframework.remoting.support.RemoteInvocationTraceInterceptor] - Processing of RmiServiceExporter remote call resulted in fatal exception: com.initusa.miniavl.services.recording.RecordingService.deleteRecords
org.springframework.jdbc.UncategorizedSQLException: Hibernate operation: could not execute update query; uncategorized SQLException for SQL [delete from MINIAVL_RECORDS where id in (? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , 

[...] // shortened for readability

? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?)]; SQL state [HY000]; error code [0]; [Microsoft][SQLServer 2000 Driver for JDBC]The DBMS returned an unspecified error.; nested exception is java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]The DBMS returned an unspecified error.
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]The DBMS returned an unspecified error.
   at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source)
   at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source)
   at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReplyToken(Unknown Source)
   at com.microsoft.jdbc.sqlserver.tds.TDSRPCRequest.processReplyToken(Unknown Source)
   at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReply(Unknown Source)
   at com.microsoft.jdbc.sqlserver.SQLServerImplStatement.getNextResultType(Unknown Source)
   at com.microsoft.jdbc.base.BaseStatement.commonTransitionToState(Unknown Source)
   at com.microsoft.jdbc.base.BaseStatement.postImplExecute(Unknown Source)
   at com.microsoft.jdbc.base.BasePreparedStatement.postImplExecute(Unknown Source)
   at com.microsoft.jdbc.base.BaseStatement.commonExecute(Unknown Source)
   at com.microsoft.jdbc.base.BaseStatement.executeUpdateInternal(Unknown Source)
   at com.microsoft.jdbc.base.BasePreparedStatement.executeUpdate(Unknown Source)
   at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:101)
   at org.hibernate.hql.ast.exec.BasicExecutor.execute(BasicExecutor.java:71)
   at org.hibernate.hql.ast.QueryTranslatorImpl.executeUpdate(QueryTranslatorImpl.java:322)
   at org.hibernate.impl.SessionImpl.executeUpdate(SessionImpl.java:1019)
   at org.hibernate.impl.QueryImpl.executeUpdate(QueryImpl.java:89)
   at com.initusa.miniavl.common.dataobjects.base.HibernateServiceTemplate$2.doInHibernate(HibernateServiceTemplate.java:150)
   at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:358)
   at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:331)
   at com.initusa.miniavl.common.dataobjects.base.HibernateServiceTemplate.deleteHelper(HibernateServiceTemplate.java:140)
   at 
[...]

Now, don't get me wrong: this is not inherently an HQL problem! It is not the first time that I have seen databases choke on statements with big
IN-lists. What you can usually do is to break up the list in smaller
pieces ("WHERE id IN (...) OR id IN (...) OR id IN (...)"). So, I can
work around this issue quite easily. I was just wondering whether there
is a more generic way in Hibernate to do that. In other words: any
way that the framework can handle this instead of my application code?

Best regards,

Jens