Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Bind variables multiple times in Prepared Statement

 
Dhruv Chandna
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Author and ninkuma
Marshal
Pie
Posts: 65335
97
IntelliJ IDE Java jQuery Mac Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
"FeatherHead", please check your private messages for an important administrative matter.
 
Sagar Rohankar
Ranch Hand
Posts: 2907
1
Java Spring Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes, you can clear out the set parameter once you are finished with using them , like here



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
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic