Re: Passing a list to a Java prepared statement

From: Chuck Simpson (chuckls_at_cox-internet.com)
Date: 10/31/04 

Date: Sun, 31 Oct 2004 13:48:32 -0600

On Wed, 06 Oct 2004 07:30:31 -0700, GMan wrote:

> I'm using JDBC with Oracle 8i and I have the following prepared statement
> code:
>
> ...
>
> String sql = "Select ID from Users where level in (?)";
>
> PreparedStatement statement = connection.prepareStatement(sql);
>
> String levels = "1, 2, 3";
>
> statement.setString(1, levels);
>
> rs = (ResultSet) statement.executeQuery();
>
> ...
>
> - The executeQuery call fails with: "ORA-01722: invalid number". - Level
> is an integer field in the users table
>
> So, I'm guessing I've got to do something different here to handle these
> kinds of "list" parameters. Any thoughts?

Probably the easiest solution in Java is to create a statement
String in a StringBuffer and append the same number of placeholders
(?) as list elements, convert to a String, create a PreparedStatement
and fill in the placeholders. Eg:

StringBuffer sb = new StringBuffer(
  "Select ID from Users where level in (?");
int n = list.size();
for(int i = 1; i < n; i++) {
  sb.append(",?");
}
PreparedStatement ps = con.prepareStatement(sb.toString());
for(int i = 0; i < n; i++) {
  ps.setObject(i, list.get(i));
}
ResultSet rs = ps.executeQuery();

Hope this helps.

Chuck

Relevant Pages