Set array of values to SQL query

Question

I'm using JDBC, I have to set array of values to a single column, I know it works in Hibernate and Ibatis but it seems to be hard to get it working Pure JDBC sql.

I have an array of String values

names[] = new String[]{"A","B","C"};

and a Query like

select * from emp where name in(?)

I tried pstmt.setObject(1,names), it is not working..

You might get there if you create your query string dynamically and add ?s for each item in your array, and then bind each one of them to your query. — Ricardo Appleton
– Ricardo Appleton, Commented Jan 9, 2014 at 12:30

JB Nizet · Accepted Answer · 2012-01-26 17:38:11Z

1

This is not supported in pure JDBC. You have to generate a query so that the in clause contains one placeholder for each element of the array.

Spring's JDBC helper classes support named parameters and what you want to do.

answered Jan 26, 2012 at 17:38

JB Nizet

694k94 gold badges1.3k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Falco Over a year ago

I think it is supported in JDBC now - see my answer!

Falco · Accepted Answer · 2014-04-14 11:31:50Z

0

This will work with the following Syntax:

"SELECT * FROM emp WHERE name IN ( SELECT column_value FROM TABLE( ? ) )"

pmst.setArray( 1, conn.createArrayOf( "VARCHAR", names ) );

answered Apr 14, 2014 at 11:31

Falco

3,46626 silver badges29 bronze badges

2 Comments

Dave Jarvis Over a year ago

Note that createArrayOf might not work across all major relational databases. See: stackoverflow.com/q/28400518/59087

Falco Over a year ago

There are several tricks to get it to work with other Vendors, But it is a hassle you need a separate case for each DBMS...

Collectives™ on Stack Overflow

Set array of values to SQL query

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related