Bind variables multiple times in Prepared Statement

[Dhruv Chandna]

Hi,
I have a beginners question. I have the following piece of code that iterates through a list and then binds a variable to a prepared statement and executes the query. Please let me know if it is appropriate to use PreparedStatement like the way I have done.

List<Integer> list = new ArrayList<Integer>();
list.add(1);
list.add(2);

String query = "select name from emp where emp_no = ?";

Connection conn = <someconnection>;
PreparedStatement pstmt = conn.prepareStatement(query);
ResultSet rSet = null

for(Integer id: list){
pstmt.setInt(0, id);
rSet = pstmt.executeQuery();
while(rSet.next()){
//Do something.
}
}

Also, I do not want to use "IN" in the select query.

Thanks a lot.
FeatherHead

[Bear Bibeault]

"FeatherHead", please check your private messages for an important administrative matter.

[Sagar Rohankar]

Yes, you can clear out the set parameter once you are finished with using them , like here

  for(Integer id: list){
pstmt.setInt(0, id);
rSet = pstmt.executeQuery();
while(rSet.next()){
//Do something.
}  
pstmt.clearParameters(); // See docs fore details

Also, I do not want to use "IN" in the select query.

I dont get it, Whats your point?

And pl change your name as per JavaRanch naming policy !

[Dhruv Chandna]

Thanks a lot.

By not using "IN" in the query i meant that i did not want to modify my query in order to execute the prepared statement just once.

I've changed the display name. Sorry for not abiding by the ranch rules.

[Jeanne Boyarsky]

Dhruv,
Your approach is fine. Note that you will have a network trip for each query. If you have a large number of ids and the database is on another machine, this could be a performance problem.