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

PreparedStatement!

 
Seetharaman Venkatasamy
Ranch Hand
Posts: 5575
Eclipse IDE Java Windows XP
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi All,


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

thanks & regards,
seetharaman
 
Scott Selikoff
author
Saloon Keeper
Posts: 4014
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
chandra mohan
Greenhorn
Posts: 20
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author & internet detective
Marshal
Posts: 34401
346
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
chandra mohan
Greenhorn
Posts: 20
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 387
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author & internet detective
Marshal
Posts: 34401
346
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 20
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks guys,
I got it now.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic