JPA and Hibernate NamedQuery Error: maximum number of expressions in a list is 1000

Question

I have the below Hibernate NamedQuery that runs into issues when one of the "in" expressions has 1000 or more items. When I have 1000 or more items in the ma.deviceId in (:devices), I get java.sql.SQLException: ORA-01795: maximum number of expressions in a list is 1000

The only "in" expression that I need to take care of is the "and ma.deviceId in (:devices)". Anyone have any ideas on how to rewrite this NamedQuery? I'm using JPA and Hibernate.

@NamedQuery(name = "Messages.findMessages", query = " SELECT ma from Messages ma JOIN FETCH ma.messagePayLoadXml mx LEFT OUTER JOIN FETCH ma.messageProperties  mp " +
                  " WHERE ma.customerId = :customerId and ma.time >= :startTime and ma.time <= :endTime " +
                  " and ma.deviceId in (:devices) and  mx.messageType = 'XML' and mx.alerts in " +
                  " ( select mtfm.messageType from MessageTypeFeatureMap mtfm where mtfm.feature in (:featureType) ) " +
                  " and ma.messageKey = mx.messageKey and ( mp.deleted = 0 or mp.deleted is null ) " +
                  " order by ma.time desc " )

https://forums.oracle.com/forums/thread.jspa?threadID=233143 — Matt Ball, Aug 21 '12 at 17:33
possible duplicate of [Java Oracle exception - "maximum number of expressions in a list is 1000"](http://stackoverflow.com/questions/9767920/java-oracle-exception-maximum-number-of-expressions-in-a-list-is-1000) — Matt Ball, Aug 21 '12 at 17:33

score 2 · Accepted Answer · answered Aug 21 '12 at 17:37

2

There are 2 ways.
1) Store your list in intermediary table and do
... IN (SELECT ... FROM intermediaryTable)
2) Break your list into sublists each upto 1000 elements and write your query as
(... IN (subList1) OR ... IN (subList2) ...)

answered Aug 21 '12 at 17:37

Germann Arlington

3,315
2
17
19

Is there any way to increase the list allocation size in JPA ? – Gunjan Shah Dec 03 '12 at 10:24
No, because it is NOT JPA limitation. The limit is imposed be SQL databases. – Germann Arlington Dec 03 '12 at 14:55

score 1 · Answer 2 · answered Aug 21 '12 at 17:45

For our application we have done a simple fix for this situation...

 If number of values are greater than 999
      QUERY = SELECT + FROM + WHERE + COND1 +
              ( FIELD IN ( 999 values ) OR FIELD IN ( 999 values )...)  + ORDER
 Else
      QUERY = SELECT + FROM + WHERE + COND1 + IN (...) + ORDER

JPA and Hibernate NamedQuery Error: maximum number of expressions in a list is 1000

2 Answers2