Use Preparedstatement to delete several rows from database

Hello everyone,

I am trying to delete multiple rows from database at one time using Preparedstatement.
It works well when I tried in SQL directly,the sql query is as follows:

delete from planners_offices where planner ='somename' and office in ( 'officeone', 'officetwo', 'officethree')

I want to delete those 3 rows at one time.

But when I am using preparedstatement to implement this function, it does not work. It did not throw any exception, but just does not work for me, the updated rows value always returns "0".

Here is my simplified code:


PreparedStatement ps = null;
sqlStr = " delete from PLANNERS_OFFICES where planner = ? and office in (?) "



Connection con = this.getConnection(dbname);

try
{
//set the sql statement into the preparedstatement
ps = con.prepareStatement(sqlStr);

ps.setString(1,"somename");
ps.setString(2,"'officeone','officetwo','officethree'");


int rowsUpdated =ps.executeUpdate();
System.out.println(rowsUpdated);


}
//catch exception
catch (SQLException e)
{
System.out.println("SQL Error: " + sqlStr);


e.printStackTrace();
}
catch (Exception e) {
e.printStackTrace();
} finally {
this.releaseConnection(dbname, con);
}


try{
ps.close();
}catch (SQLException e){
e.printStackTrace();
}



rowsUpdated always give me "0".

I tried only delete one record at one time, "ps.setString(2, "officeone");", it works fine.
I am guessing the second value I want to bind to the preparedstatement is not right, I tried several formats of that string, it does not work either.


Can anyone give me a clue?

Thanks in advance !

Rachel

I am not sure, but i think you have to seperate the three variables from each other. Have you tried this?

I think you don't have to use extra quotes ' in ps.setString(). The function does this for you.