java - 如何使用 Hibernate Criteria 选择一个巨大的列表并在 IN 子句中传递 ids

Question

您好，我想使用 Restrictions.in 在我的数据库中查找对象

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

该列表包含超过 100.000 个条目(其中大部分在数据库中不可用)，这使得 hibernate 终止

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.

我能做什么？

Answer 1

发送一个巨大的 IN 子句查询将会非常慢，而且在 Oracle 上，IN 查询列表中的参数无论如何都被限制为 1000 个。

Id 您传递的 ID 是之前通过查询获取的，因此您应该使用单个查询使用 EXISTS 子句将这两个查询组合起来，该子句采用第一个查询使用的过滤条件。