prepareStatement with IN
hi ,
i am facing some problem with prepare statement.
here is my query
String queryString = select Count(MESSAGE) from Services where ID in (?) And Message <> '' ;
here is the parameter
String str = "bank";
String str1 = String str = "bank, organization";
if i use
pstmt.setString(1, new String(str));
i get answer correctly but when
but if i use
pstmt.setString(1, new String(str1));
i am not getting any thing is these a known problem with prepare statement.
if yes what is work around.
i know by using statement and query
String queryString = select Count(MESSAGE) from Services where ID in (+str+) And Message <> '' ;
i faced lot of issues when i used it with LIKE also.
i have to use preparestatement.
please advice
Thanks in advance
-Arpit
# 2
The other posiibilty (and I know this will work) is to figure out how many items should appear in the "IN" list before creating the string to use to create the PreparedStatement and then format the String accordingly.
i.e. when you know there will be two arguments then use "IN (?, ?)" rather than "IN (?)" and call setString for each of the markers using the arguments individually.
# 4
At some point in the program you do, or you wouldn't be able to pass it in. Wait to build the PreparedStatement until the arguments are received, and then evaluate them and build the PreparedStatement then.
For example, say this PreparedStatement is used in a method with the arguments passed in as an array of Strings, then do the following:
public ResultSet method (String[] args, Connection conn) {
String query = "Select bogus from bogusTable where bogusId IN (";
for (int i = 0; i < args.length; i++) {
query += "?, ";
}
query = query.replaceFirst(", $", ")");
PreparedStatement ps = conn.prepareStatement(query);
for (int i = 0; i < args.length; i++) {
ps.setString(i + 1, args[i]);
}
return ps.executeQuery();
}
As you can see, it is possible. Also, if you haven't noticed, I left out the error/exception handling in the example.
# 6
Then you need to rethink the query, or find another way to do it. There is no reason that the common framework can't be added to, either. If you need to use an IN in a PReparedStatement, then that is, really, the only way it will work. Otherwise, you need to use a Statement in which you are allowed to change the text, or have some way to introduce replaceable text in your query (i.e. the query in the properties file contains the word REPLACETHIS which you can then be exchanged with text of your own, in order to introduce the "IN list" String and which would also be needed for the "Statement" solution).
If you are unwilling or unable (because your compnay is unwilling) to make any of these changes, then, like I said, you need to rethink your query so that you are not using an IN statement.
