Difference between Statement and preparedStatement (for SQL databases) ?

Discussion in 'Java' started by Ken Philips, Oct 16, 2004.

  1. Ken Philips

    Ken Philips Guest

    Sometimes I can see pure Statement declaration for accessing SQL databases
    others use preparedStatements.

    What is the difference?

    Ken
     
    Ken Philips, Oct 16, 2004
    #1
    1. Advertisements

  2. exerpt from http://www.oreilly.com/catalog/jorajdbc/chapter/ch19.html

    "Statement Versus PreparedStatement

    There's a popular belief that using a PreparedStatement object is
    faster than using a Statement object. After all, a prepared statement
    has to verify its metadata against the database only once, while a
    statement has to do it every time. So how could it be any other way?
    Well, the truth of the matter is that it takes about 65 iterations of
    a prepared statement before its total time for execution catches up
    with a statement. This has performance implications for your
    application, and exploring these issues is what this section is all
    about.

    When it comes to which SQL statement object performs better under
    typical use, a Statement or a PreparedStatement, the truth is that the
    Statement object yields the best performance. When you consider how
    SQL statements are typically used in an application--1 or 2 here,
    maybe 10-20 (rarely more) per transaction--you realize that a
    Statement object will perform them in less time than a
    PreparedStatement object."
     
    Michael Fortin, Oct 18, 2004
    #2
    1. Advertisements

  3. Ken Philips

    Luke Webber Guest

    While I find O'Reilly a very good publisher, I take some issue with the
    above. The performance of a PreparedStatement is very definitely
    implementation-dependent, and not to be dismissed so lightly.

    Luke
     
    Luke Webber, Oct 18, 2004
    #3
  4. And it ignores the other advantages of PreparedStatement, such as
    escaping quotes in setString() plus other data conversions, and the
    security implications in just throwing together a Statement with
    String concatenation, which can let a user bypass constraints by
    "commenting them out" - or even execute malicious SQL.
     
    Tor Iver Wilhelmsen, Oct 18, 2004
    #4
  5. Ken Philips

    venkat.sadasivam

    Joined:
    May 11, 2008
    Messages:
    7
    Likes Received:
    0
    The prepared statement concept is not specific to Java, it is a database concept. Statement precompiling means: when you execute a SQL query, database server will prepare a execution plan before executing the actual query, this execution plan will be cached at database server for further execution.

    The advantages of Prepared Statements are:

    1. As the execution plan get cached, performance will be better.
    2. It is a good way to code against SQL Injection as escapes the input values.
    3. When it comes to a Statement with no unbound variables, the database is free to optimize to its full extent. The individual query will be faster, but the down side is that you need to do the database compilation all the time, and this is worse than the benefit of the faster query.

    Other than training purpose it is better to use PreparedStatement to get full benefits and close all loopholes.
     
    venkat.sadasivam, Nov 5, 2008
    #5
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.