*
The moose likes JDBC and the fly likes How prepared statements shall be used Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "How prepared statements shall be used" Watch "How prepared statements shall be used" New topic
Author

How prepared statements shall be used

vivekkumar sharma
Ranch Hand

Joined: Dec 21, 2005
Posts: 70
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
internet detective
Marshal

Joined: May 26, 2003
Posts: 30123
    
150

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.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
vivekkumar sharma
Ranch Hand

Joined: Dec 21, 2005
Posts: 70
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

Joined: Dec 15, 2005
Posts: 333
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
internet detective
Marshal

Joined: May 26, 2003
Posts: 30123
    
150

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

Joined: Dec 21, 2005
Posts: 70
thanxs Everyone
mridul das
Greenhorn

Joined: Jul 15, 2005
Posts: 19
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

Joined: Dec 15, 2005
Posts: 333
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).
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: How prepared statements shall be used
 
Similar Threads
Prepared statements with % wildcards
sql INSERT using variables
Problem Pooling prepared statements
Problem with the "where" query.
Execute sql in a loop