File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Prepared Statement Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Prepared Statement " Watch "Prepared Statement " New topic
Author

Prepared Statement

thomas davis
Ranch Hand

Joined: Feb 01, 2003
Posts: 207
How does the databases identify the request is for prepared statement?
What is the process flow of handling prepared statement (starting from webserver to database server and back to webserver)?
I read from an article that prepared statements are using precompiled statement and after syntax check, compilation and optimization, handle is returned to the JDBC driver.
At the end of the program the statement handle is discarded.
I have not understood the complete process of handling prepared statement and the concept of handle. Can someone explain to me about this?
It would also be great if someone helps me to understand process going on at database when it receives the request for prepared statement, if possible with examples?
:roll:
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

There is a sample article here:
http://www.theserverside.com/resources/articles/Prepared-Statments/article.html
The short answer is that JDBC is based on a framework designed to hide the specifics of a database from the developer in an attempt to stop code from being vendor specific.
It does this by forcing all interaction to happen through interfaces, which hides the specific implementor, and using the DriverManager to kick things off for you.
I'm going to ignore the webserver since it has nothing to do with the question, the actions are almost the same regardless of where you execute the code.
The full steps (with basic steps in bold) are:
* register the Driver
* get a Connection
* get a PreparedStatement
* bind the PreparedStatement
* execute the PreparedStatement

it usually looks like this:

Hope this gets you started
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

I used to have a link to an IBM DevveloperWorks article that explained PreparedStatements nicely, but I lost it
The best I could find was this thread:
http://www.coderanch.com/t/296889/JDBC/java/Difference-between-Statement-PreparedStatement
I'm still looking, but haven't found a better thread yet...
Dave
thomas davis
Ranch Hand

Joined: Feb 01, 2003
Posts: 207
I heard that statements are interfaces and the implementation is provided by the FACTORY METHODS/CLASSES and this implementation can provide any vendors.I can conclude that these vendors are driver provider.Every driver-vendor got thier own implementation for these interfaces(Connection,Statement..).
This my assumption,if I am wrong,please correct me..with examples..
And still I do not understand the handle concept used for prepared statement....Sorry for disturbing you..
thomas davis
Ranch Hand

Joined: Feb 01, 2003
Posts: 207
please explain me with process flow and elaborately,if possible with examples
thomas davis
Ranch Hand

Joined: Feb 01, 2003
Posts: 207
Hi David,
used to have a link to an IBM DevveloperWorks article that explained PreparedStatements nicely, but I lost it

Could you please find out the URL ?
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

No, I haven't been able to find the link to the article on the IBM DeveloperWorks site. I'd appreciate if anyone else could.
I'll start with PreparedStatements from first principles, then we can see where we get from there. (This is off the top of my head but hopefully I won't get things too wrong)
The first concept to understand is that the SQL you write is not the same as the code that gets executed on the database. In the same way we write Java code that gets compiled into byte code, and it is the byte code that gets executed, SQL statements get compiled into DB code and it is this code that gets run by the database.
There is a lot of performance tweaks and database specific stuff that goes into this SQL compilation, but an important point is that it isn't free. If you execute 10 SQL queries, the database must compile and tweak 10 statements and this before it even begins the search.
It is also possible for a database to detect that it has already run a particular query and that it does not need to compile it again. If it still has the compiled version it can reuse it with the same query.
If you run the SQL query "SELECT * from myTable where id=27", this will get compiled into a statement that will only match this exact query. If you then run "SELECT * from myTable where id=28", the database won't match the two together. It will recompile the new query even though it is almost exactly the same as the previous one.
Some databases also support variables in SQL statements, so that it can compile the SQL without knowing what the value is. Now if we compile the SQL statement "SELECT * from myTable where id=?", the statement will only be compiled once even if we run it with ?=27 and ?=28 or any other value. This is good if we run the same query often.
Now on to how this relates to JDBC:
When you use a Statement, it works like the first case where it assumes it is a new query every time you run it and is unlikely to be able to rely on cached queries in the database.
If you use a PreparedStatement (as in my first post), it hooks into the compiled statement caching on the database invisibly. You don't have to help it detect cached statements. If there is one there, it will be used. It this sense PreparedStatements are no worse, since they may need to be compiled every time, but a Statement would have to do this anyway.
If I understand your concept of 'handle', you don't need to worry about it. It's managed for you invisibly by the driver.
The last point I'd like to make is that not all databases support PreparedStatements, even if the driver you have does. Since you have no idea what happens on the database (you only talk to the JDBC Driver), it is possible for the PreparedStatement to fake this support, even though what is really happeneing is the PreparedStatement is executing a new query every time.
I'll give it afew more minutes ooking for a better article, but this might be all you'll get...
thomas davis
Ranch Hand

Joined: Feb 01, 2003
Posts: 207
Yes I am okay with your explanation and examples provided along with it.I understood thoroughly.And it helped me also.
If you do not mind,please clear my following doubts :
1)How does FactoryMethods/Classes help JDBC API/Driver(Different vendors)?
2)Do all driver_vendor has to implement the connection and statement interfaces?How do they implement it?What methods are they implementing?
3)Prepared Statement
�Provide the ability to precompile an SQL statement that will be issued quite often.
�Has performance advantages, because the statement is compiled and optimized only once.
�After syntax check, compilation and optimization, handle is returned to the JDBC driver.
�At the end of the program the statement handle is discarded.
These are the features and advantages of using prepared statement.Can someone explain it elaborately one by one? What does it mean that handle is returned to the JDBC driver and at the end of the program the statement handle is discarded?
Please provide a real good explanation with conceptual clarity ?
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

1)How does FactoryMethods/Classes help JDBC API/Driver(Different vendors)?
A sample of this behaviour can be seen in this thread. The DriverManager acts as a Connection Factory so that you can get Connections without knowing where the Connection comes from, how it is created, or anything else.
2)Do all driver_vendor has to implement the connection and statement interfaces?How do they implement it?What methods are they implementing?
The short answer is that they have to implement the methods required bythe relevent interface. the longer answer is that writing Drivers is not trivial and you'd be better off checking the JDBC infor at the Sun Java site
http://java.sun.com/products/jdbc/
3)Prepared Statement
�Has performance advantages, because the statement is compiled and optimized only once.

Possibly. There are indications that while PeparedStatements are better in general, there are situations where better performance can be seen by re-compiling the statement every time. The reason for this is that a PreparedStaement can only be optimised as far as it knows the values, but since some of them are variable it cannot optimise this data. If the query is static the optimised version is the optimal solution you can get for this database, and might be much better depending on the DBMS and the query.
�At the end of the program the statement handle is discarded.
Not necessarily. pre-compiled statements are supported by the database, not the JDBC Driver. Therefore the Database is free to share this compiled statement with other Connections too.
I don't beieve the Driver gets a 'handle' to the commpiled statement. This implies the JDBC driver has an association to the compiled to the code on the database, which is not required. All that is required is that the database is able recognise the same query being executed and re-use the same compiled statement.
Amer Khan
Ranch Hand

Joined: Apr 05, 2003
Posts: 163
Prepared Statement
�Has performance advantages, because the statement is compiled and optimized only once.
====================================

When u send a SQL statement to the database,the db software reads the SQL statement and verifies that it is correct,(parsing the SQL statement)The database software then builds a plan,known as execution plan,to actually run the statement.A 'PS'allows u to perform the same basic SQL statement but supply different values for actual execution of that statement.This is more efficient because the same execution plan is used by the database when the SQL statement is run.
[ July 09, 2003: Message edited by: Amer Khan ]

<i>Dare to dream - everything that exists today,was once a figment of someone's imagination, nobody says tomorrow can't be a figment of your today.</i>
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Prepared Statement
 
Similar Threads
handling quotes in sql queries
How to upload a file from applet to the webserver ?
prepared statement
Prepared Statement
Inserted value too large for column