aspose file tools*
The moose likes JDBC 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
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Execute sql in a loop" Watch "Execute sql in a loop" New topic
Author

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 : http://java.sun.com/docs/books/tutorial/jdbc/basics/prepared.html

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]);
updateSales.executeUpdate();
}


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>();
paramMap.put("PROD_TYPE",priData.get(i).getProdTypeDescTxt());
paramMap.put("CALL_DATE",String.valueOf(priData.get(i).getPurchDttm()));
}

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.

Thanks.
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Execute sql in a loop