Search code examples
javaperformancehibernateormcriteria

How to select a huge list using Hibernate Criteria and pass the ids in the IN clause

Hi i want to find objects in my database with Restrictions.in

Criteria c = s.createCriteria(MyClass.class);
c.add(Restrictions.in("id", hugeidlist));
return c.list();

the list contains over 100.000 entries (most of the won't be available in the database) which lets hibernate die with 

http-bio-8080-exec-5 03/06/2014 16:05:59,346 | WARN | org.hibernate.engine.jdbc.spi.SqlExceptionHelper | logExceptions | SQL Error: 0, SQLState: 08006
http-bio-8080-exec-5 03/06/2014 16:05:59,351 | ERROR | org.hibernate.engine.jdbc.spi.SqlExceptionHelper | logExceptions | An I/O error occured while sending to the backend.
http-bio-8080-exec-5 03/06/2014 16:05:59,353 | WARN | com.mchange.v2.c3p0.impl.NewPooledConnection | handleThrowable | [c3p0] A PooledConnection that has already signalled a Connection error is still in use!
http-bio-8080-exec-5 03/06/2014 16:05:59,354 | WARN | com.mchange.v2.c3p0.impl.NewPooledConnection | handleThrowable | [c3p0] Another error has occurred [ org.postgresql.util.PSQLException: This connection has been closed. ] which will not be reported to listeners!
org.postgresql.util.PSQLException: This connection has been closed.

what can i do?

Solution

  • Sending a huge IN clause query is going to be very slow, and on Oracle you are limited to 1000 parameters in the IN query list anyway.

    Id the ids you are passing were fetched previously with a query, than you should use a a single query to combine those two using the EXISTS clause which takes the filtering criteria used by your first query.