CODEWITHSUNDEEP
sqljdbcdb2prepared-statement
fedvasu
fedvasu

Reputation: 1252

making use of where in clause in jdbc

I am getting a bunch of numbers (only integer values) from UI and passing it to back-end. These numbers are coming in a string of format "num1,num2,..,numN" for example

"145,78,934"

the corresponding field in the database table is Decimal(8), what I would like to do is

update myawesomeTable set moreAwesomeField = 'YES' where criteriaField in (145,78,938)

what I am currently doing is pretty lame and slow.(something like below)

pstm.preparedStatement("update myawesomeTable set moreAwesomeField='YES' where criteriaField = ?")

going through a for loop I am updating criteriaField by taking one by one substring of criteriaField values and doing 

pstm.setBigDecimal(new BigDecimal(criStr[i]));

So what options do I have now? I am using Db2 9.5+,(and java 1.4-) I can't get the criteria field in anyother format, I tried "where in" preparedStatement without any luck. I would want this query to be executed oneshot.

Upvotes: 0

Views: 1147

Answers (1)

mustaccio
mustaccio

Reputation: 18945

You have two options.

1) Compose the statement string dynamically with literal values:

pstm.preparedStatement(
 "update myawesomeTable set moreAwesomeField='YES' where criteriaField in ("
 + yourVariableContainingTheListOfNumbers 
 + ")"
)

It's simple but exposes you to SQL injection

2) Compose the statement dynamically with parameter markers: calculate how many values are in the list, e.g. 3; compose the statement with the appropriate number of parameter markers so it looks like update myawesomeTable set moreAwesomeField='YES' where criteriaField in (?, ?, ?), then bind individual values to the parameter markers.

A variant of the latter option, if you can guarantee that the list length has an upper bound, e.g. no more than 10 values, would be to use a parameterized statement string with the maximum number of parameter markers and bind nulls to the markers for which there are no parameters.

Upvotes: 1

Related Questions