java PreparedStatement

Question

How to set value for in clause in a preparedStatement in JDBC while executing a query.

Example.

mPreparedStatement = connection.prepareStatement("Select * from test where field in (?)");

If this inclause can hold multiple values how can I do it.Sometimes I know the list of parameters beforehand or sometimes I don't know beforehand.How to handle this case?

Answer 1

You might want to check this link:

http://www.javaranch.com/journal/200510/Journal200510.jsp#a2

It explains the pros and cons of different methods of creating PreparedStatement with in clause.

EDIT:

An obvious approach is to dynamically generate the '?' part at runtime, but I don't want to merely suggest just this approach because depending on the way you use it, it might be inefficient (since the PreparedStatement will need to be 'compiled' every time it gets used)

Answer 2

What I do is to add a "?" for each possible value.

For instace:

List possibleValues = ... 
String builder = new StringBuilder();

for( int i = 0 ; i < possibleValue.size(); i++ ) {
    builder.append("?,");
}

String stmt = "select * from test where field in " 
               + builder.deleteCharAt( builder.length() -1 ).toString();
PreparedStatement pstmt = ... 

And then happily set the params

int index = 1;
for( Object o : possibleValue ) {
   pstmt.setObject(  index++, o ); // or whatever it applies 
}

Answer 3

You can't replace ? in your query with an arbitrary number of values. Each ? is a placeholder for a single value only. To support an arbitrary number of values, you'll have to dynamically build a string containing ?, ?, ?, ... , ? with the number of question marks being the same as the number of values you want in your in clause.

Answer 4

What you can do is dynamically build the select string (the 'IN (?)' part) by a simple for loop as soon as you know how many values you need to put inside the IN clause. You can then instantiate the PreparedStatement.