Get objects form database using hibernate with two list as parameters

Question

I'm trying to get results from database iterating over two collection as shown below

for (SomeType element1 in List1) {
    for (SomeType2 element2 in List2) {
        Query query = session.createQuery("from Table where column1 =: element1 and column2 =: element2");
        //Operations on list of results
    }
}

How can I do something like this with best performance? In my app list1 has 200 elements and list2 around 30 and it takes too much time.

This is horrible for performance. You're executing 200 * 30 queries given your lists size. Use HQL [IN](http://stackoverflow.com/questions/4828049/in-clause-in-hql-or-java-persistence-query-language) clause instead. — isah, Feb 25 '16 at 12:10
Can I use two HQL 'ins' in one query? For example: `from Table where column1 in (:list1) and column2 in (:list2)` — Kamil Rafałko, Feb 25 '16 at 12:12

Dherik · Accepted Answer · 2016-02-25T14:49:43.497

1

You can try:

Query query = session.createQuery("from Table where column1 IN (:List1) and column2 IN (:List2)");

edited Feb 25 '16 at 14:49

answered Feb 25 '16 at 12:44

Dherik

13,091
10
86
132

score 0 · Answer 2 · answered Feb 25 '16 at 12:12

Not perfect but it can do the job:

StringBuilder sb = new StringBuilder("(");
for (SomeType element1 in List1) {
    for (SomeType2 element2 in List2) {
        sb.append(element1).append(',').append(element2).append("),");
    }
}
// delete the last ','
sb.setLength(sb.length() - 1);
Query query = session.createQuery(
  "from Table where (column1, column2) in ("+sb.toString+")"
);

Get objects form database using hibernate with two list as parameters

2 Answers2