SQL in clause with items > 1000

Question

0I have this method:

public List<Product> getProductsByListIds(List<Long> ids) {
    String query = "from Product pr where pr.id in(:ids)";
    List<Product> products= (List<Product>) getSession().createQuery(query)
        .setParameterList("ids", ids).list();
    return products;
  }

This method is OK, my only problem is when the ids.size() >1000 I'm trying to find a convincing solution to this problem.

Which DBMS are you using? Some DBMS do indeed have a limitation on the number of elements for an `IN` list. But the only one I know is Oracle and the limitation is 1000 there, not 100 — , Jul 19 '16 at 10:48
For this you can use Criteria Interface. Please have a look https://docs.jboss.org/hibernate/orm/3.2/api/org/hibernate/Criteria.html — Janny, Jul 19 '16 at 10:50
In Oracle the limit is 1000 not 100. I don't know about MySQL — , Jul 19 '16 at 10:56

score 0 · Answer 1 · answered Jul 19 '16 at 10:55

My advice would be to take a step back and look at the design and what you're trying to achieve, passing hundreds of parameters into an SQL statement is not going to be very efficient and I'd be surprised if it's the most elegant solution to your requirement.

Without knowing more about how this method is called and where this list of ids comes from it's difficult to give concrete advice, however I would recommend that you look into using joins if possible.

SQL in clause with items > 1000

1 Answers1