Java passing values into IN in sql WHERE clause

Question

I have a sql query like

(sqlQuery)
SELECT *
FROM table1
WHERE to_char(id) in (?)

Then in my prepared statement

preparedStatement ps = new preparedStatemend(sqlQuery)
ps.setString(1,param);

where my param is a string chain looking like param = '12','34', '444'

Now, I thought that when I execute it then everything will be ok, but it's not and I get and error like ORA 01460 uninmplemented or unreasonable conversion.

So, how can I pass my parameter consists of a few valuse into the IN clause in sql query?

Simple pass List into the IN clause i.e. convert params into List rather than a single String with commas. — gohil90, Jan 13 '18 at 12:36
This is more of an SQL question than a Java question. But the issue is that you need as many question marks `?` as you have parameters. So you'll need to create your `PreparedStatement` for each time you use it. — SeverityOne, Jan 13 '18 at 12:37
*where my param is a string chain looking like `param = '12','34', '444'`* I trust you are checking for things like SQL Injection when doing things like that? — Joe C, Jan 13 '18 at 12:38
Not related to your question, but you will get better performance with `where id in (?)` than what you have now. — Dan Bracuk, Jan 13 '18 at 12:48
How many IDs are you passing to the string? The IN clause can't exceed 4000 characters (SQL limit). — APC, Jan 13 '18 at 13:28
Actually, since this is oracle, the limiting factor is 1000 list items, max. — Dan Bracuk, Jan 13 '18 at 14:41
@DanBracuk Use an Oracle collection: `SELECT * FROM table_name WHERE id MEMBER OF ?` and then [pass in a collection as the bind parameter](https://stackoverflow.com/a/43179008/1509264) which can [have more than 1000 items](https://stackoverflow.com/a/34699771/1509264). — MT0, Jan 14 '18 at 13:19

davidxxx · Accepted Answer · 2018-01-13T12:40:20.480

4

You will have to specify a ? for each possible values of the IN clause.

For example if you have 4 options possible in the IN clause, this :

to_char(id) in (?)

should so be :

to_char(id) in (?, ?, ?, ?)

And you should also set each parameter by incrementing successively the index of the param to set :

So this :

ps.setString(1,param);

should be :

int index = 1;
for( String s : inValues ) {
   ps.setString(index++, s); 
}

edited Jan 13 '18 at 12:40

answered Jan 13 '18 at 12:38

davidxxx

125,838
23
214
215

1

`to_char(id) in ('?', '?', '?', '?') ` Surely without the single quotes? – Joe C Jan 13 '18 at 12:38
Normally it's without quotes at all (single or double), other than the quotes that surround the entire query. – Joe C Jan 13 '18 at 12:40
@Joe C In terms of SQL sure. – davidxxx Jan 13 '18 at 12:42

score 1 · Answer 2 · edited Jun 20 '20 at 09:12

First of all you should know that there is limit for the data can be in the IN clause. So if you have a large number of comma separated values to pass as the parameter (bind variable) of the IN clause you should manage something else.

Now if you have a limited number of ids (for example less than 500 or so, depends on the size of ids which totally can not be more than 4000 characters) you can use the regexp_substr along with connect by to tokenize the comma separated values of your single parameter ? in the where clause: to_char(id) in (?) :

 SELECT REGEXP_SUBSTR(? ,'[^,]+', 1, LEVEL) FROM dual
CONNECT BY REGEXP_SUBSTR(? , '[^,]+', 1, LEVEL) IS NOT NULL

when you pass the string "'12','34', '444'" as the parameter, it would tokenize them as rows:

'12'

'34'

'444'

So theoretically you can change your query in this way:

SELECT *
  FROM table1
 WHERE to_char(id) in 
 (
       SELECT REGEXP_SUBSTR(? ,'[^,]+', 1, LEVEL) FROM dual
      CONNECT BY REGEXP_SUBSTR(? , '[^,]+', 1, LEVEL) IS NOT NULL
 )

But in this way if the size of the table1 is big the performance would be so bad because of the conversion of the id for every record.

It's better to convert your limited number of parameters of the IN clause to enhance the performance:

SELECT *
  FROM table1
 WHERE id in 
 (
       SELECT REGEXP_SUBSTR(? ,'[^,]+', 1, LEVEL) FROM dual
      CONNECT BY REGEXP_SUBSTR(? , '[^,]+', 1, LEVEL) IS NOT NULL
 )

and in your java code you should pass the string "12,34, 444" as the parameter (without single single quotes around each id).

You should know that this solution is not optimized for a large number of ids. However, it makes the number of parameters of the IN clause dynamic.

Hope this helps.

The big size of the table is not a problem here. The performance can be bad if the number of comma separated ids grows too big. In every situation using IN clause can harm the performance. — STaefi, Jan 13 '18 at 15:48

Java passing values into IN in sql WHERE clause

2 Answers2