1

I am constructing a hibernate query and I pass a list of String values into the IN clause. That list sometimes happens to be more than 1000 values so I receive an error. I have looked up some solutions like breaking that clause into smaller ones or making temporary table, but none of them showed how actually it is better to work with variable list. So if I had something like:

 SELECT * FROM MY_TABLE WHERE NAME IN (list).

What would be the best way to handle this?

Sergey Kalinichenko
  • 714,442
  • 84
  • 1,110
  • 1,523
Marty
  • 117
  • 9
  • Are you using Oracle? – Sergey Kalinichenko Jan 25 '18 at 08:18
  • If you have that many values in an `IN` clause you should load those values to a temporary table and then join with the main table to do your select: `select * from my_table a left join temp_in_data b on a.name=b.name;` – Jim Garrison Jan 25 '18 at 08:20
  • @dasblinkenlight, yes, oracle. – Marty Jan 25 '18 at 08:21
  • @JimGarrison, but isn't it bad idea to make temp table from java list? I can't select those values from db, because they are coming from client selection. – Marty Jan 25 '18 at 08:22
  • You create the temp table; insert the values; then perform the join. With counts in the thousands this is the only sensible way to do it. – Jim Garrison Jan 25 '18 at 08:24

0 Answers0