aspose file tools*
The moose likes JDBC and the fly likes What exactly we mean by Precompiled SQL Statement Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "What exactly we mean by Precompiled SQL Statement" Watch "What exactly we mean by Precompiled SQL Statement" New topic
Author

What exactly we mean by Precompiled SQL Statement

Raja Sagar Panamgipalli
Ranch Hand

Joined: Aug 13, 2003
Posts: 109
Hi Guys,
I came across an article stating the following and several books too...
PreparedStatement allows you to precompile your SQL and run it repeatedly.

Now can anyone explain me what does it really mean..."precompile your sql" i somehow don't get it.

Hey thanks for being patient..
Regards.
Sagar


SCJP 1.4<br />SCBCD 1.3
Dima Gutzeit
Greenhorn

Joined: Jan 21, 2004
Posts: 1
If a statement is used multiple times in a session, precompiling it provides better performance than sending it to the database and compiling it for each use. The more complex the statement, the greater the performance benefit.
If a statement is likely to be used only a few times, precompiling it may be inefficient because of the overhead involved in precompiling, saving, and later deallocating it in the database.
Precompiling a dynamic SQL statement for execution and saving it in memory uses time and resources. If a statement is not likely to be used multiple times during a session, the costs of doing a database prepare may outweigh its benefits. Another consideration is that once a dynamic SQL statement is prepared in the database, it is very similar to a stored procedure. In some cases, it may be preferable to create stored procedures and have them reside on the server, rather than defining prepared statements in the application.
Raja Sagar Panamgipalli
Ranch Hand

Joined: Aug 13, 2003
Posts: 109
Thanx Dima..

I some what get it but don't get it..
can u do a favor by expalianing the following piece of code..

PreparedStatement pstmt=con.prepareStatement("INSERT INTO EMP(NAME,PHONE)VALUES(? ,?)");
then i give
pstmt.setString(1,"Jim");
pstmt.setString(2,"274 7071");
pstmt.executeUpdate();

what exactly happens here...
In a preparedstatment we are specifying the arguments while creating an object of PreparedStatement from Connection Obj.when unlike in a createStatement method of the Connection we don't do that.
Have i messed up...sorry...plz elaborate..

God bless mankind..
regards
Sagar
Wayne L Johnson
Ranch Hand

Joined: Sep 03, 2003
Posts: 399
You are on the right track, Raja. The basic "createStatement()" method returns a "Statement" instance that can be used to send SQL to the database for execution. Every SQL you send will get validated, compiled, and executed.
With the "prepareStatement(...)" method call you pass in the SQL once (at creation time) where it can be validated and compiled. It will throw an exeception if the SQL is syntactically invalid, or references tables or columns that don't exist, or if there number of parameters (?) don't match up with the number of values specified. That's what happens when you do:

With this prepared statement you can do only one thing: insert records into the "EMP" table. You have now told the database what you want to do, but not what values to insert. That comes with the second set of statements:

This means that using the given prepared statement (pstmt), you want to use "Jim" as the first parameter and "274 7071" as the second parameter, and then execute the SQL against the database.
There is overhead in using prepared statements, and if you only insert a few records it will probably take a little more time. However if you insert many records you will see a performance gain.
There is another advantage to using prepared statements, and that is with handling String and Date/Time data. Some databases use single quotes as text delimiters, others use double quotes, and some recognize either. However if you want to insert character data containing single- and/or double-quotes, this can be tricky using a regular Statement. And if you move from one database to another this can force you to modify your SQL. The same can be said about inserting dates and times: each database has a different format that you have to follow.
But when you use a PreparedStatement, the values you specify with "setString(...)" can contain single quotes or double quotes and the underlying JDBC classes will take care of the conversion for you. This is a big advantage of using prepared statements.
Good luck!
Raja Sagar Panamgipalli
Ranch Hand

Joined: Aug 13, 2003
Posts: 109
Hey Thanx John,
That was elaborate..and can go into my notes..
Thanx buddy
shyam sunder prasad
Ranch Hand

Joined: Mar 23, 2011
Posts: 62
thanks john,i too understand very from reading your explanation prepared statement.
shyam sunder prasad
Ranch Hand

Joined: Mar 23, 2011
Posts: 62
Hi Raja ,

with my knowledge i can say the what does it really mean.

preparedStatement is compiled onlyyonce.
Ex:


above code compiled once, because java compiler checks for syntax validation.

and sql is validation will be done by database driver.

mainly we use it in loops for better performance.





if we go for Statement,this will be compiled ,validated and executed every time.
chris webster
Bartender

Joined: Mar 01, 2009
Posts: 1777
    
  16

One advantage here is that having pre-compiled and cached the SQL command using bind variables (?, ?, ...), you can call the same SQL multiple times with different values for the bind variables, but there is no need to re-compile the SQL as the DB can just re-use the cached statement. This can make a significant difference to performance when you are executing a given SQL command many times with different values.


No more Blub for me, thank you, Vicar.
Debashis Mandal
Greenhorn

Joined: Feb 04, 2013
Posts: 5
I have some doubts regarding preparedstatement.
1. Who will do the pre compilation for this kind of statement.
2. below is some code snippet:
PreparedStatement ps=conn.preparedStatement("insert into tableA values(?,?)");
ps.setString(1,"a");
ps.setString(2,"b");
ps.executeUpdate();
ps.close;
/*
some java code
*/
PreparedStatement ps1=conn.preparedStatement("insert into tableA values(?,?)");
ps1.setString(1,"a");
ps1.setString(2,"b");
ps1.executeUpdate();
ps1.close;

Now as both ps and ps1 contain the same SQL query so is there any compilation for ps1?
3. if we call close() method of preparedStatement then will that precompile statement also wipeout from Database.

Please clarify and also thanks in advance!!
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

All these things generally depend on the database and JDBC driver. You should consult your database and JDBC driver documentation if you want to go into such details.

In general:

1. Precompilation is done by the database. Some simpler databases don't precompile statements at all. Others might precompile it on the prepareStatement call, and yet others might do it when execute is first called on the statement, taking values of the parameters into account when compiling (creating a plan for) the statement.

2. Databases that do precompile statements usually cache them, so in all probability ps1 won't be compiled again. Some JDBC drivers (eg. Oracle's) even cache prepared statements, so they haven't actually closed it when ps.close() was called.

3. Databases generally cache statements until something evicts them from the cache. Remember that yours is very probably not the only application on the database, so your application can negatively impact other (if it hoards database resources) and similarly, other applications can negatively impact your.
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: What exactly we mean by Precompiled SQL Statement