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

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

is there maximum size query collection parameter in jpa 2.1 / eclipselink 2.5.2 (connecting mysql 5.6, if matters)?

e.g., maximum allowable and/or recommend size ids in next code, ensure performance , avoid possible bugs or issues in jpa / eclipselink / mysql?:

@persistencecontext private entitymanager em; { final collection<long> ids = /*obtain collection of ids*/; em.createquery("select e entity1 e e.id in :ids") .setparameter("ids", ids); }

there no simple , clear reply question depends on multiple factors jvm's process heap size, persistence provider proprietary features, underlying database tuning options, etc. in real-life these factors should tuned rather individually.

mysql 5.6 reference manual, chapter 12.3.2 comparing functions , operators defines that:

the number of values in in list limited max_allowed_packet value.

as max_allowed_packet equal maximum size of 1 packet or generated/intermediate string (here: 1gb) should theoretically allowed send query string of maximum size of 1gb.

jpa 2.0 specification (jsr-317), chapter 4.6.9 in experssions not mention limitations, @ to the lowest degree can assume it's matter of "weakest" component in tool-chain (application container, persistence provider, underlying database, jvm heap size).

as noticed @chris limited in oracle 1000 parameters per statement. similar issue eclipselink has been described james (a former architect of toplink/eclipselink) on blog:

the first thing noticed in run oracle has limit of 1,000 parameters per statement. since in batch fetching binds big array, , i'm reading 5,000 objects, limit exceeded , run blew database error. the batchfetchpolicy in eclipselink accounts , defines size max number of ids include in. default size limit in eclipselink suppose 500 (...)

and

eclipselink defines jpa query hint "eclipselink.batch.size" allows size set. set 500 test. means read in of 5,000 objects, in batch fetch need execute 10 queries per batch fetched relationship.

i encourage read whole post anyway.

mysql jpa eclipselink jpa-2.0 jpa-2.1