If you give a easy solution for this problem, then you are very good in JDBC. The following SQL query should be executed only through PreparedStatement. You should not use Statement. Query is select employees from employeemast where employee_id in (1,2,3,4,5) This you have to give in prepared statement as select employees from employeemast where employee_id in (?) The values (1,2,3,4,5) will change dynamically in a for loop. if you say preparedStatementOb.setString(1,stringValue) here stringValue = "1,2,3,4,5" This setSting will wrap a single quotes in the query ie, query will build by java like following. select employees from employeemast where employee_id in ('1,2,3,4,5') which will cause invalid number error. since employee_id is a number in database. You should not dynamically build ? in the query like following select employees from employeemast where employee_id in (?,?,?,?,?) There should be only one Question mark. If you find a solution then u are great.
unfortunately the PreparedStatement was not made to be used in this way. Even if you find a workaround to make it execute, you will most likely lose all advantages of using PreparedStatements( statement caching cannot be done as the number of parameters is different on each execution, type checking has to be done on every execution, etc. ) So even though you may accomplish fooling the db into using a PreparedStatement in this way, it accomplishes nothing. Your better choice for dynamic queries is a Statement which loops to append the IN values. and just because you happen to slip it by one driver, does not mean that it will work for another. Jamie
Joined: Sep 28, 2001
I guess it depends on the application. The benefit is that strings don't need to be escaped, which is a big prob with statements. I'm happy to take a performance hit.