• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Prepared Statement

 
Ranch Hand
Posts: 207
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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:
 
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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:
https://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
Posts: 207
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 207
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
please explain me with process flow and elaborately,if possible with examples
 
thomas davis
Ranch Hand
Posts: 207
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 207
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Ranch Hand
Posts: 163
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 ]
reply
    Bookmark Topic Watch Topic
  • New Topic