How to pass multiple search values in where condition

Question

I connected to database in my java program. I have a situation where I need to pass filter values dynamically, filter values depends of others parts of java code

Example query: select * from table1 where id in (dynamic and multiple)?

How to pass these dynamic and multiple values using Java connection.

I have a list of a values that I need to pass to my where condition, how can pass those values. Pls help me resolve — Sharath N, Oct 18 '16 at 18:51
you could create in clause as string and append to the query. — lsiva, Oct 18 '16 at 18:51
Does the above query works,? Is there any limit for number of parameters? — Sharath N, Oct 18 '16 at 18:56
if its a int, it will work, if this is string you have to enclose it with single quotes — lsiva, Oct 18 '16 at 18:56
If you have a large in clause put that in a table and use it as subquery like select * from table1 where id in (select col from INCLAUSE_TAB) — lsiva, Oct 18 '16 at 19:16
If you are using PreparedStatement, there are lots of duplicate/similar queries which exist check http://stackoverflow.com/questions/178479/preparedstatement-in-clause-alternatives http://stackoverflow.com/questions/1305240/how-to-set-list-of-parameters-on-prepared-statement http://stackoverflow.com/questions/3107044/preparedstatement-with-list-of-parameters-in-a-in-clause — Kamal Kunjapur, Oct 18 '16 at 19:20

score 0 · Accepted Answer · answered Oct 18 '16 at 19:00

0

Try this

String query =  "select * from emp where id in(##)";

create the in clause like this

String inClause = "'abcd', 'cedf', '1234'";

String finalQuery = query.replace("##", inClause );

answered Oct 18 '16 at 19:00

lsiva

473
1
7
16

score 0 · Answer 2 · answered Oct 20 '16 at 11:34

If you are using iBatis, you can try with below sql query:-

<select id="table1Result" resultMap="table1Map">
select * from table1 where id in <foreach item="item" index="index" collection="list" open="(" separator=","        close=")"</select>

While calling it from java, pass a List of ids.

How to pass multiple search values in where condition

2 Answers2