• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

How prepared statements shall be used

 
Ranch Hand
Posts: 70
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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();
}
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 ]
 
Ranch Hand
Posts: 333
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
thanxs Everyone
 
Greenhorn
Posts: 19
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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).
reply
    Bookmark Topic Watch Topic
  • New Topic