Prepared Statements multiple parameters

[thebarslider]

Hello,

I am writing a Java program to purge some data from a database. I already have a list of the id's that I wish to delete.

Is it possible to write a statement like:

delete from table where autoid in ...

Where the three dots would be an array I passed to the statement rather than having to create a single statement for each id as although this is easy to do using a loop it is inefficient.

Hope someone can help.

Many Thanks.

 

[sedj]

You can execute a statement such as :

        PreparedStatement ps = c.prepareStatement("delete from ben_test where id in (?,?)");
        ps.setInt(1, 23);
        ps.setInt(2, 24);
        ps.execute();
        ps.close();
        c.close();

So what you would do is build up the PreparedStatement string (ie the number of '?') according to the number of id's you want to delete - and then loop on the 'ps.setInt()' methods, incrementing the parameter position as you go.

This would then achieve what you want to do.

--------------------------------------------------
Free Java/J2EE Database Connection Pooling Software

http://www.primrose.org.uk

 

[sedj]

BTW - It would have been nice for you to have posted your solution to the problem posed here by you : thread269-1171413

--------------------------------------------------
Free Java/J2EE Database Connection Pooling Software

http://www.primrose.org.uk