Win a copy of Mesos in Action this week in the Cloud/Virtualizaton forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

How prepared statements shall be used

 
vivekkumar sharma
Ranch Hand
Posts: 70
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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();
}
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34410
346
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
vivekkumar sharma
Ranch Hand
Posts: 70
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
 
stu derby
Ranch Hand
Posts: 333
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34410
346
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Vivek,
Your code looks good. The only thing to add is a finally block so the prepared statement gets closed.
 
vivekkumar sharma
Ranch Hand
Posts: 70
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
thanxs Everyone
 
mridul das
Greenhorn
Posts: 19
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
stu derby
Ranch Hand
Posts: 333
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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).
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic