How prepared statements shall be used

Hi Ranchers,

I have a doubt understanding how to use prepared statements.

When we call function below, will the "updateSales" object created each time or it will be created only for the first call and then will be resued .
If this is not the correct way to use prepared statements, then where shall be prepared statements be created .

TA
Vivek

public void updateSales(String CoffeName,float sales)
{

PreparedStatement updateSales;
String updateString = "update COFFEES " +
"set SALES = ? where COF_NAME like ?";
updateSales = con.prepareStatement(updateString);
updateSales.setInt(1, sales);
updateSales.setString(2, CoffeName);
updateSales.executeUpdate();
}

Vivek,
The actual java object (updateSales) is different each time. What is reused is the database execution plan. Compared to the cost of re-creating the execution plan, the object creation time is minimal.

HI ,
Thankx for reply.
is this right and efficent way to code SQL queries in an application.

Who maintains compiled prepared statements? is it JDBC driver ?

regards
Vivek

public void updateSales(String CoffeName,float sales)
{

PreparedStatement updateSales;
String updateString = "update COFFEES " +
"set SALES = ? where COF_NAME like ?";
updateSales = con.prepareStatement(updateString);
updateSales.setInt(1, sales);
updateSales.setString(2, CoffeName);
updateSales.executeUpdate();
}
[ May 29, 2006: Message edited by: vivekkumar sharma ]

Originally posted by vivekkumar sharma:

Who maintains compiled prepared statements? is it JDBC driver ?

Many databases maintain a cache of parsed SQL statements, within the database. This is particularly useful when multiple clients are executing similar SQL. Such caches are often LRU (least recently used) caches; thus the DB will keep as many execution plans as it can for as long as it can, but won't keep them forever.

Vivek,
Your code looks good. The only thing to add is a finally block so the prepared statement gets closed.

thanxs Everyone

method(String xyz){
PreparedStatement pStmt;
String query= "select * from users where " +
"user_role not in (?)";
pStmt= con.prepareStatement(query);
pStmt.setString(1, xyz);
pStmt.execute();
}

Now the xyz coulb be something like "'guest','admin'"
This doesn't work as xyz is passed as a parameter to the compiled statement.So this doesnot return the correct results.How would I get round this. Is it possible using a PreparedStatement.
Need a quick reply.

Originally posted by mridul das:
method(String xyz){
PreparedStatement pStmt;
String query= "select * from users where " +
"user_role not in (?)";
pStmt= con.prepareStatement(query);
pStmt.setString(1, xyz);
pStmt.execute();
}

Now the xyz coulb be something like "'guest','admin'"
This doesn't work as xyz is passed as a parameter to the compiled statement.So this doesnot return the correct results.How would I get round this. Is it possible using a PreparedStatement.
Need a quick reply.

You can't bind an expression list (even a list of values) to a SQL placeholder (the ?), you can only bind a single value.

In other words, you can't do that. If your list is fixed length, then your SQL could be written as "... in (?, ?)" (or whatever number of values are in your list).