File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Execute sql in a loop Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of REST with Spring (video course) this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Execute sql in a loop" Watch "Execute sql in a loop" New topic

Execute sql in a loop

Meghna Bhardwaj
Ranch Hand

Joined: Jun 08, 2007
Posts: 109
Hi All,

We are using Spring frmaework and NamedParameterJdbcDaoSupport for all data access operations. I need to execute a select statement in a loop multiple times supplying it different values each time.

I am aware of how to do this with plain jdbc, taken from :

PreparedStatement updateSales;
String updateString = "update COFFEES " +
"set SALES = ? where COF_NAME like ?";
updateSales = con.prepareStatement(updateString);
int [] salesForWeek = {175, 150, 60, 155, 90};
String [] coffees = {"Colombian", "French_Roast", "Espresso",
"Colombian_Decaf", "French_Roast_Decaf"};
int len = coffees.length;
for(int i = 0; i < len; i++) {
updateSales.setInt(1, salesForWeek[i]);
updateSales.setString(2, coffees[i]);

However with the Spring framework all the connection etc is taken care of. So I am leaning towards using the method below in a for loop?

public Object execute(String sql,Map paramMap, PreparedStatementCallback action)

I am not too sure how this will work:

sql - is my select statement
parmap - i have:

for(int i=0; i<= priData.size(); i ++){
Map<String,String> paramMap = new HashMap<String,String>();

these are my variable values which i am setting in a loop.

I am not too sure about PreparedStatementCallback action. Has anyone used NamedParamter in a loop? If so please let me know how.

I agree. Here's the link:
subject: Execute sql in a loop
jQuery in Action, 3rd edition