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

PreparedStatement!

Seetharaman Venkatasamy
Ranch Hand

Joined: Jan 28, 2008
Posts: 5575

Hi All,


please can anyone explain me the meaning of precompiled ...

thanks & regards,
seetharaman
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3704
    
    5

From the PreparedStatement API: "A SQL statement is precompiled and stored in a PreparedStatement object. This object can then be used to efficiently execute this statement multiple times."

The alternative is a Statement in which data is not known about the query until it is executed. The common example is if you need to do 100 inserts. With a PreparedStatement you can use the same statement 100 times, just changing some of the input values inbetween update calls. With a regular statement, this is compiled at runtime and cannot be changed.


My Blog: Down Home Country Coding with Scott Selikoff
chandra mohan
Greenhorn

Joined: May 05, 2008
Posts: 20
Originally posted by Scott Selikoff:
From the PreparedStatement API: "A SQL statement is precompiled and stored in a PreparedStatement object. This object can then be used to efficiently execute this statement multiple times."

The alternative is a Statement in which data is not known about the query until it is executed. The common example is if you need to do 100 inserts. With a PreparedStatement you can use the same statement 100 times, just changing some of the input values inbetween update calls. With a regular statement, this is compiled at runtime and cannot be changed.


Scott,
Lets have a small pseudo code of what you said
(If we are going to insert 100 records means usually we will use the loop)

String var = "a";
String query="Insert...";

for()
{
........
preparedstatement.setString(1,var);
executeInsert
}

In the above pseudo code instead of prepared statement if I am using statement then also I can change the value at runtime like this correct ?

String var = "a";
String query="Insert..." + var;

for()
{
........
var = ...
executestatement
}


So I can assign values at runtime in both the statements then what Pre compile exactly means ?

I thought that It is for performance improvement.
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30370
    
150

Chandra,
When the driver/database/connection sees a SQL statement, it figures out how to execute it. If the string matches exactly one executed recently, it re-uses the plan. This is related to pre-compiling. In your first example, the SQL is exactly the same since a question mark is used. In your second example, the query is different and the database treats it like a query it is never seen before.

Additionally, you can use batching for insert SQL statements. With prepared statements, you make this even more explicit because you set the SQL once and just set batches of parameters.


[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
chandra mohan
Greenhorn

Joined: May 05, 2008
Posts: 20
Thanks Jeanne,

I got your point about precomiplation but I have one more doubt. You said in my second example the query is different.I am just changing the value not the query so how it would be different ?
Herman Schelti
Ranch Hand

Joined: Jul 17, 2006
Posts: 387
hi Chandra,

YOU know that the query is the same (with just a different value of the parameter), but the database sees a different String than before, therefore thinks it's a new query.

Herman
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30370
    
150

Chandra,
Herman is correct. This is why I emphasized the word "exactly". The database isn't smart enough to know to things are the same unless they are identical.
chandra mohan
Greenhorn

Joined: May 05, 2008
Posts: 20
Thanks guys,
I got it now.
 
jQuery in Action, 2nd edition
 
subject: PreparedStatement!