Wednesday, August 28, 2013

How to set value/values(single or multiple) for IN clause in a preparedStatement in JDBC while executing a query.


Suppose we have a table states with following fields

state_id
state_name
country_name

If we want to find the states of india and canada then we will use the prepareStatement as follows

PreparedStatement pstmt = connection.prepareStatement("Select * from states where country_name in (?,?)");
pstmt.setString(1,"india","canada");
result = pstmt.executeQuery();

If we know the number of values which we want to use within in clause then it is very simple as above.
But when we dont know the values which we want to use within in clause then?
when we dont have any idea about the number of values with in IN clause then we will use  prepareStatement as follows.

String queryString = "Select * from states ";

Suppose we have a list of country names(countryNames) which is coming at run time.

StringBuffer sb = new StringBuffer();
for(int i = 0;i<countryNames.size();i++){
sb.append("?,");
}

queryString = queryString + " where country_name IN("+sb.deleteCharAt( sb.length() -1 ).toString()+") ";

pstmt = con.prepareStatement(queryString);
int index = 1;
for( String country : countryNames ) {
   pstmt.setString(  index++, country ); // or whatever it applies
}
result = pstmt.executeQuery();

No comments:

Post a Comment