Maximum size for query collection parameter in JPA 2.1 / EclipseLink 2.5.2

Is there a maximum size for a query collection parameter in JPA 2.1 / EclipseLink 2.5.2 (connecting to mysql 5.6, if that matters)?

e.g., what is the maximum allowable and/or recommend size for ids in the following code, to ensure performance and to avoid any possible bugs or issues in JPA / EclipseLink / mysql?:

@PersistenceContext
private EntityManager em;

{
    final Collection<Long> ids = /*obtain a Collection of IDs*/;

    em.createQuery("select e from Entity1 e where e.id in :ids")
        .setParameter("ids", ids);
}

Solution

There is no simple and clear answer to your question as it depends on multiple factors like JVM's process heap size, persistence provider proprietary features, the underlying database tuning options, etc. In a real-life all these factors should be tuned up rather individually.

MySQL 5.6 Reference Manual, chapter 12.3.2 Comparison Functions and Operators defines that:

The number of values in the IN list is only limited by the max_allowed_packet value.

As max_allowed_packet is equal to the maximum size of one packet or any generated/intermediate string (here: 1GB) you should be theoretically allowed to send a query string of maximum size of 1GB.

JPA 2.0 specification (JSR-317), chapter 4.6.9 In Experssions does not mention anything about the limitations, so at least we can assume that it's a matter of the "weakest" component in the tool-chain (application container, persistence provider, underlying database, JVM heap size).

As noticed by @Chris it is limited in Oracle to 1000 parameters per statement. The similar issue with EclipseLink has been described by James (a former architect of TopLink/EclipseLink) on his blog:

The first thing that I noticed in this run was that Oracle has a limit of 1,000 parameters per statement. Since the IN batch fetching binds a large array, and I'm reading 5,000 objects, this limit was exceeded and the run blew up with a database error. The BatchFetchPolicy in EclipseLink accounts for this and defines a size for the max number of ids to include the an IN. The default size limit in EclipseLink was suppose to be 500 (...)

and

EclipseLink defines a JPA Query hint "eclipselink.batch.size" that allows the size to be set. So I will set this to 500 for the test. This means that to read in all of the 5,000 objects, the IN batch fetch will need to execute 10 queries per batch fetched relationship.

I encourage to read the whole post anyway.