Menu
I would like to know if there is a limit on the size of a collection when using the IN clause.
select a from A where a.b IN (:c)
and c is a list(for example).
446
Limiting query results in JPA is slightly different to SQL; we don't include the limit keyword directly into our JPQL. Instead, we just make a single method call to Query#maxResults, or include the keyword first or top in our Spring Data JPA method name.
In Oracle we can only put up to 1000 values into an IN clause.
Spring Data JPA supports keywords 'first' or 'top' to limit the query results (e.g. findTopBy....). An optional numeric value can be appended after 'top' or 'first' to limit the maximum number of results to be returned (e.g. findTop3By....). If this number is not used then only one entity is returned.
It depends on the specific DBMS, ie. sqlite and postgres have widely different limits (not surprisingly).
EDIT:
For Oracle: How to put more than 1000 values into an Oracle IN clause
For mysql: http://explainextended.com/2009/08/18/passing-parameters-in-mysql-in-list-vs-temporary-table/
57
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
