java - Stackoverflow 和 Hibernate 使用 sql IN (id, id, id, id..id)

Question

我收到下面的错误，说有堆栈溢出。发生这种情况是因为带有 IN (id, id, id...id) 的 SQL 语句有大量参数。有没有什么办法解决这一问题？这是在我使用 Eclipse 的本地环境中发生的。

JPA

@Query(value="SELECT p FROM PendingCourseRegistration p WHERE p.sisId IN ?1 AND p.testId = ?2")
List<PendingCourseRegistration> findPendingCourseRegistrationInSisIdsAndTestId(List<String> sisIds, Long testID);

错误

java.lang.StackOverflowError: null
    at java.lang.Abstract witingBuilder.append(AbstractStringBuilder.java:416) ~[na:1.7.0_17]
    at java.lang.StringBuffer.append(StringBuffer.java:237) ~[na:1.7.0_17]
    at antlr.BaseAST.toStringList(BaseAST.java:341) ~[antlr-2.7.7.jar:na]
    at antlr.BaseAST.toStringList(BaseAST.java:347) ~[antlr-2.7.7.jar:na]
    at antlr.BaseAST.toStringList(BaseAST.java:347) ~[antlr-2.7.7.jar:na]
    at antlr.BaseAST.toStringList(BaseAST.java:347) ~[antlr-2.7.7.jar:na]

hibernate 查询

2:26.763 [ocPifScheduler-1] DEBUG o.s.o.j.SharedEntityManagerCreator$SharedEntityManagerInvocationHandler - Creating new EntityManager for shared EntityManager invocation
09:52:26.788 [Scheduler-1] DEBUG org.hibernate.hql.internal.ast.QueryTranslatorImpl - parse() - HQL: SELECT p FROM com.test.PendingCourseRegistration p WHERE p.sisId IN (:x10_, :x11_, :x12_, :x13_, :x14_, :x15_, :x16_, :x17_, :x18_, :x19_, :x110_, :x111_, :x112_, :x113_, :x114_, :x115_, :x116_, :x117_, :x118_, :x119_, ...:xN) AND p.id = ?2
09:52:26.891 [Scheduler-1] DEBUG org.hibernate.hql.internal.ast.QueryTranslatorImpl - --- HQL AST ---
 \-[QUERY] Node: 'query'
    +-[SELECT_FROM] Node: 'SELECT_FROM'
    |  +-[FROM] Node: 'FROM'
    |  |  \-[RANGE] Node: 'RANGE'
    |  |     +-[DOT] Node: '.'
    |  |     |  +-[DOT] Node: '.'
    |  |     |  |  +-[DOT] Node: '.'
    |  |     |  |  |  +-[DOT] Node: '.'
    |  |     |  |  |  |  +-[DOT] Node: '.'
    |  |     |  |  |  |  |  +-[DOT] Node: '.'
    |  |     |  |  |  |  |  |  +-[IDENT] Node: 'com'
    |  |     |  |  \-[IDENT] Node: 'model'
    |  |     |  \-[IDENT] Node: 'PendingCourseRegistration'
    |  |     \-[ALIAS] Node: 'p'
    |  \-[SELECT] Node: 'SELECT'
    |     \-[IDENT] Node: 'p'
    \-[WHERE] Node: 'WHERE'
       \-[AND] Node: 'AND'
          +-[IN] Node: 'in'
          |  +-[DOT] Node: '.'
          |  |  +-[IDENT] Node: 'p'
          |  |  \-[IDENT] Node: 'sisId'
          |  \-[IN_LIST] Node: 'inList'
          |     +-[COLON] Node: ':'
          |     |  \-[IDENT] Node: 'x10_'
          |     +-[COLON] Node: ':'
          |     |  \-[IDENT] Node: 'x11_'
          |     +-[COLON] Node: ':'
          |     |  \-[IDENT] Node: 'x12_'
          |     +-[COLON] Node: ':'
          |     |  \-[IDENT] Node: 'x13_'
          |     +-[COLON] Node: ':'
          |     |  \-[IDENT] Node: 'x14_'
          |     +-[COLON] Node: ':'
          |     |  \-[IDENT] Node: 'x15_'
          |     +-[COLON] Node: ':'
          |     |  \-[IDENT] Node: 'x16_'
          |     +-[COLON] Node: ':'
          |     |  \-[IDENT] Node: 'x17_'

Answer 1

在我们使用 Hibernate 4.3.1.Final 的 Grails 项目 (2.3.6) 中，我们从未遇到过该错误，但由于查询缓冲区大小 限制，我们遇到了另一个错误:

由于您正在使用 in (?, ..., ?) 您编写的 ?, 与列表中的项目一样多，这意味着对于大list(说 50000)你可能写了一个 100000 个字符的查询，你可能有这个异常(这里有 pgSQL 驱动程序):

Foobar.executeQuery("select f from Foobar f where f.id in (:ids)", 
                 [ids: 1L..100000L]); // Groovy way of creating a list of 100000 items.

错误:

SqlExceptionHelper:146 - An I/O error occured while sending to the backend. 
SqlExceptionHelper:146 - This connection has been closed.

这就是我认为你可能需要的原因

1. 要么将您的 id 列表拆分为更小的列表(例如 500 项左右)并手动合并结果，这比您使用的默认 spring-data 要做更多的工作。

   <

2. 要么将 id 列表存储到一个临时表中(使用 JPA 可能会证明这是一项痛苦的任务)。临时表将是 (key, sids) 的元组。您将生成一个临时 key (用于 session )，使用该 key 将 ID 批量插入到该表中，刷新以便 hibernate 将更改推送到数据库中，使用该表使用子查询(p.sidIds in(选择sisIds from IdTable where key = ?1)), 从该表中删除数据。虽然这是一项痛苦的任务，但它可能会提高性能。