PreparedStatement

Discussion in 'Java' started by gk, Jun 23, 2010.

  1. gk

    gk Guest

    Please see this ..

    http://java.sun.com/j2se/1.4.2/docs/api/java/sql/PreparedStatement.html

    PreparedStatement : An object that represents a precompiled SQL
    statement.

    "precompiled SQL statement" ... who compiled this ?

    Is it working like this way ...when I first execute the code below
    DBMS compiles when it encounter for the first time and then next time
    DBMS does not compile . So, We call it precompiled.

    java code:

    PreparedStatement pstmt = con.prepareStatement("UPDATE EMPLOYEES
    SET SALARY = ? WHERE ID = ?");
    pstmt.setBigDecimal(1, 153833.00)
    pstmt.setInt(2, 110592)



    If I used Statement instead of PreparedStatement does that mean
    everytime DBMS will compile the SQL query ?
     
    gk, Jun 23, 2010
    #1
    1. Advertising

  2. gk

    Lew Guest

    gk wrote:
    > Please see this ..
    >
    > http://java.sun.com/j2se/1.4.2/docs/api/java/sql/PreparedStatement.html
    >
    > PreparedStatement : An object that represents a precompiled SQL
    > statement.
    >
    > "precompiled SQL statement" ... who compiled this ?


    There are a couple of layers of compilation, one at the JDBC layer
    (potentially) and the other at the DBMS server.

    Precompilation is not the only benefit of prepared statements.

    > Is it working like this way ...when I first execute the code below
    > DBMS compiles when it encounter for the first time and then next time
    > DBMS does not compile . So, We call it precompiled.


    Roughly speaking, yes, although the full truth is somewhat more complicated.

    > java [sic] code:
    >
    > PreparedStatement pstmt = con.prepareStatement("UPDATE EMPLOYEES
    > SET SALARY = ? WHERE ID = ?");
    > pstmt.setBigDecimal(1, 153833.00)
    > pstmt.setInt(2, 110592)
    >
    >
    >
    > If I used Statement instead of PreparedStatement does that mean
    > everytime DBMS will compile the SQL query ?


    Roughly speaking, yes, although the full truth is somewhat more complicated.

    --
    Lew
     
    Lew, Jun 23, 2010
    #2
    1. Advertising

  3. On 23.06.2010 12:33, Lew wrote:
    > gk wrote:
    >> Please see this ..
    >>
    >> http://java.sun.com/j2se/1.4.2/docs/api/java/sql/PreparedStatement.html
    >>
    >> PreparedStatement : An object that represents a precompiled SQL
    >> statement.
    >>
    >> "precompiled SQL statement" ... who compiled this ?

    >
    > There are a couple of layers of compilation, one at the JDBC layer
    > (potentially) and the other at the DBMS server.
    >
    > Precompilation is not the only benefit of prepared statements.
    >
    >> Is it working like this way ...when I first execute the code below
    >> DBMS compiles when it encounter for the first time and then next time
    >> DBMS does not compile . So, We call it precompiled.

    >
    > Roughly speaking, yes, although the full truth is somewhat more
    > complicated.


    It is important to mention that for PS to work efficiently the statement
    must be kept in user code. Invoking prepareStatement() with the same
    string argument twice makes no guarantees about saving compilation in
    the DB. To make the code efficient user must prepare the statement and
    keep it around for recurring use.

    That is, if you want to benefit from compilation savings - if it is only
    for avoidance of SQL injection / proper conversion of arguments and
    performance does not matter you can recreate PS over and over again-

    >> java [sic] code:
    >>
    >> PreparedStatement pstmt = con.prepareStatement("UPDATE EMPLOYEES
    >> SET SALARY = ? WHERE ID = ?");
    >> pstmt.setBigDecimal(1, 153833.00)
    >> pstmt.setInt(2, 110592)
    >>
    >>
    >>
    >> If I used Statement instead of PreparedStatement does that mean
    >> everytime DBMS will compile the SQL query ?

    >
    > Roughly speaking, yes, although the full truth is somewhat more
    > complicated.


    Somehow that sentence sounds familiar. ;-)

    Cheers

    robert

    --
    remember.guy do |as, often| as.you_can - without end
    http://blog.rubybestpractices.com/
     
    Robert Klemme, Jun 23, 2010
    #3
  4. gk

    Arne Vajhøj Guest

    On 23-06-2010 03:56, gk wrote:
    > Please see this ..
    >
    > http://java.sun.com/j2se/1.4.2/docs/api/java/sql/PreparedStatement.html


    In general you should use the latest documentation (1.6) unless
    you specifically develop for an old version.

    > PreparedStatement : An object that represents a precompiled SQL
    > statement.
    >
    > "precompiled SQL statement" ... who compiled this ?
    >
    > Is it working like this way ...when I first execute the code below
    > DBMS compiles when it encounter for the first time and then next time
    > DBMS does not compile . So, We call it precompiled.
    >
    > java code:
    >
    > PreparedStatement pstmt = con.prepareStatement("UPDATE EMPLOYEES
    > SET SALARY = ? WHERE ID = ?");
    > pstmt.setBigDecimal(1, 153833.00)
    > pstmt.setInt(2, 110592)
    >
    > If I used Statement instead of PreparedStatement does that mean
    > everytime DBMS will compile the SQL query ?


    I believe that the actual implementation is database and/or
    JDBC driver specific.

    Some primitive databases and JDBC drivers will work like:

    PreparedStatement pstmt = con.prepareStatement("UPDATE EMPLOYEES SET
    SALARY = ? WHERE ID = ?"); // store SQL in memory in pstmt object
    pstmt.setBigDecimal(1, new BigDecimal("153833.00")); // replace value in
    memory
    pstmt.setInt(2, 110592); // replace value in memory
    pstmt.executeUpdate(); // send SQL to database which compiles & execute
    pstmt.setBigDecimal(1, new BigDecimal("153834.00")); // replace value in
    memory
    pstmt.setInt(2, 110593); // replace value in memory
    pstmt.executeUpdate(); // send SQL to database which compiles & execute

    Better databases and JDBC drivers will work like:

    PreparedStatement pstmt = con.prepareStatement("UPDATE EMPLOYEES SET
    SALARY = ? WHERE ID = ?"); // send SQL to database for compilation
    pstmt.setBigDecimal(1, new BigDecimal("153833.00")); // send value to
    database
    pstmt.setInt(2, 110592); // send value to database
    pstmt.executeUpdate(); // tell database to execute
    pstmt.setBigDecimal(1, new BigDecimal("153834.00")); // send value to
    database
    pstmt.setInt(2, 110593); // send value to database
    pstmt.executeUpdate(); // tell database to execute

    The first just handles proper handling of input with
    single quotes (incl. malicious SQL injection) and date
    formats.

    The second also does that but will typical also provide
    a performance improvement, because the SQL is
    reused in compiled form in the database tier and
    less data is send over the wire.

    You should practically always use PreparedStatement!

    Arne
     
    Arne Vajhøj, Jun 24, 2010
    #4
  5. gk

    Arne Vajhøj Guest

    On 23-06-2010 12:24, Robert Klemme wrote:
    > On 23.06.2010 12:33, Lew wrote:
    >> gk wrote:
    >>> Please see this ..
    >>>
    >>> http://java.sun.com/j2se/1.4.2/docs/api/java/sql/PreparedStatement.html
    >>>
    >>> PreparedStatement : An object that represents a precompiled SQL
    >>> statement.
    >>>
    >>> "precompiled SQL statement" ... who compiled this ?

    >>
    >> There are a couple of layers of compilation, one at the JDBC layer
    >> (potentially) and the other at the DBMS server.
    >>
    >> Precompilation is not the only benefit of prepared statements.
    >>
    >>> Is it working like this way ...when I first execute the code below
    >>> DBMS compiles when it encounter for the first time and then next time
    >>> DBMS does not compile . So, We call it precompiled.

    >>
    >> Roughly speaking, yes, although the full truth is somewhat more
    >> complicated.

    >
    > It is important to mention that for PS to work efficiently the statement
    > must be kept in user code. Invoking prepareStatement() with the same
    > string argument twice makes no guarantees about saving compilation in
    > the DB. To make the code efficient user must prepare the statement and
    > keep it around for recurring use.
    >
    > That is, if you want to benefit from compilation savings - if it is only
    > for avoidance of SQL injection / proper conversion of arguments and
    > performance does not matter you can recreate PS over and over again-


    Note that good database connection pools are able to reuse
    real driver prepared statement even if the pool driver
    prepared statement is not reused.

    Arne
     
    Arne Vajhøj, Jun 24, 2010
    #5
  6. On 24 Jun., 02:36, Arne Vajhøj <> wrote:
    > On 23-06-2010 12:24, Robert Klemme wrote:
    >
    >
    >
    > > On 23.06.2010 12:33, Lew wrote:
    > >> gk wrote:
    > >>> Please see this ..

    >
    > >>>http://java.sun.com/j2se/1.4.2/docs/api/java/sql/PreparedStatement.html

    >
    > >>> PreparedStatement : An object that represents a precompiled SQL
    > >>> statement.

    >
    > >>> "precompiled SQL statement" ... who compiled this ?

    >
    > >> There are a couple of layers of compilation, one at the JDBC layer
    > >> (potentially) and the other at the DBMS server.

    >
    > >> Precompilation is not the only benefit of prepared statements.

    >
    > >>> Is it working like this way ...when I first execute the code below
    > >>> DBMS compiles when it encounter for the first time and then next time
    > >>> DBMS does not compile . So, We call it precompiled.

    >
    > >> Roughly speaking, yes, although the full truth is somewhat more
    > >> complicated.

    >
    > > It is important to mention that for PS to work efficiently the statement
    > > must be kept in user code. Invoking prepareStatement() with the same
    > > string argument twice makes no guarantees about saving compilation in
    > > the DB. To make the code efficient user must prepare the statement and
    > > keep it around for recurring use.

    >
    > > That is, if you want to benefit from compilation savings - if it is only
    > > for avoidance of SQL injection / proper conversion of arguments and
    > > performance does not matter you can recreate PS over and over again-

    >
    > Note that good database connection pools are able to reuse
    > real driver prepared statement even if the pool driver
    > prepared statement is not reused.


    Good point! I have to say I'm wary to use those features as long as
    there is no guarantee that the environment of an application is
    stable. If it has to run with a pool with and without PS caching you
    need to to the caching yourself. Otherwise you might see dramatic
    performance differences. If you know the app is only ever going to be
    used in an environment relying on this feature is of course perfectly
    OK.

    Kind regards

    robert
     
    Robert Klemme, Jun 24, 2010
    #6
  7. gk

    Lew Guest

    Arne Vajhøj wrote:
    > > Note that good database connection pools are able to reuse
    > > real driver prepared statement even if the pool driver
    > > prepared statement is not reused.

    >


    Robert Klemme <> wrote:
    > Good point!  I have to say I'm wary to use those features as long as
    >


    You're wary to use which features? PreparedStatement? Database
    connection pooling?

    > there is no guarantee that the environment of an application is
    > stable.  If it has to run with a pool with and without PS caching you
    > need to to the caching yourself.  Otherwise you might see dramatic
    > performance differences.  If you know the app is only ever going to be
    > used in an environment relying on this feature is of course perfectly
    > OK.
    >


    If the environment is not stable, then performance variations are
    normal and expected, and optimization is premature. This does not
    militate against using PreparedStatement nor against connection
    pools. It's beneficial to use both, and should be required to use the
    former.

    Unless you meant some other features, in which case I don't understand
    your comment.

    --
    Lew
     
    Lew, Jun 24, 2010
    #7
  8. On 24.06.2010 16:51, Lew wrote:
    > Arne Vajhøj wrote:
    >>> Note that good database connection pools are able to reuse
    >>> real driver prepared statement even if the pool driver
    >>> prepared statement is not reused.

    >>

    >
    > Robert Klemme<> wrote:
    >> Good point! I have to say I'm wary to use those features as long as
    >>

    >
    > You're wary to use which features? PreparedStatement? Database
    > connection pooling?


    I meant to say I am wary to rely on a pool caching prepared statements
    if either the environment of the JDBC application is undefined or may
    change. All I was trying to say is that it should not be taken for
    granted that the overhead of Connection.prepareStatement() is always low
    just because there are situations (the mentioned pools which cache PS)
    where this is the case.

    >> there is no guarantee that the environment of an application is
    >> stable. If it has to run with a pool with and without PS caching you
    >> need to to the caching yourself. Otherwise you might see dramatic
    >> performance differences. If you know the app is only ever going to be
    >> used in an environment relying on this feature is of course perfectly
    >> OK.

    >
    > If the environment is not stable, then performance variations are
    > normal and expected, and optimization is premature. This does not
    > militate against using PreparedStatement nor against connection
    > pools. It's beneficial to use both, and should be required to use the
    > former.


    One definitively should use PS - just not mindlessly recreating a PS for
    the same SQL via the Connection. That decision should be taken
    consciously and if it is not known what the source of the Connection
    does then IMHO it's better to keep PS around for the time that they are
    repeatedly used with the same Connection.

    > Unless you meant some other features, in which case I don't understand
    > your comment.


    I was probably too unclear. Sorry for that.

    Cheers

    robert

    --
    remember.guy do |as, often| as.you_can - without end
    http://blog.rubybestpractices.com/
     
    Robert Klemme, Jun 24, 2010
    #8
  9. gk

    Lew Guest

    Lew wrote:
    >> If the environment is not stable, then performance variations are
    >> normal and expected, and optimization is premature.  This does not
    >> militate against using PreparedStatement nor against connection
    >> pools.  It's beneficial to use both, and should be required to use the
    >> former.

    >


    Robert Klemme wrote:
    > One definitively should use PS - just not mindlessly recreating a PS for
    > the same SQL via the Connection.  That decision should be taken
    > consciously and if it is not known what the source of the Connection
    > does then IMHO it's better to keep PS around for the time that they are
    > repeatedly used with the same Connection.
    >


    I see your point and agree wholeheartedly.

    I go a step further and suggest that one keep the PreparedStatement
    around for use within the same use of a Connection regardless of the
    promise you think the connection pool makes. From a logical
    standpoint, an application acquires a new Connection each time; that
    the Connection may be pooled and may be the same as one from an
    earlier use is hidden from the application. Ergo, whether the
    PreparedStatement obtained from the Connection is reused from an
    earlier invocation is also hidden. Ergo, it is foolish to rely on
    putative promises that depend on such reuse.

    The point of connection pools is to let an application pretend that
    it's getting a new Connection (and thus a virgin PreparedStatement)
    each time even though under the hood the pooler is reusing them. You
    can't simultaneously pretend that the Connection is new and rely on it
    being reused.

    --
    Lew
     
    Lew, Jun 24, 2010
    #9
  10. On 24.06.2010 19:56, Lew wrote:
    > Lew wrote:
    >>> If the environment is not stable, then performance variations are
    >>> normal and expected, and optimization is premature. This does not
    >>> militate against using PreparedStatement nor against connection
    >>> pools. It's beneficial to use both, and should be required to use the
    >>> former.

    >>

    >
    > Robert Klemme wrote:
    >> One definitively should use PS - just not mindlessly recreating a PS for
    >> the same SQL via the Connection. That decision should be taken
    >> consciously and if it is not known what the source of the Connection
    >> does then IMHO it's better to keep PS around for the time that they are
    >> repeatedly used with the same Connection.
    >>

    >
    > I see your point and agree wholeheartedly.
    >
    > I go a step further and suggest that one keep the PreparedStatement
    > around for use within the same use of a Connection regardless of the
    > promise you think the connection pool makes. From a logical
    > standpoint, an application acquires a new Connection each time; that
    > the Connection may be pooled and may be the same as one from an
    > earlier use is hidden from the application. Ergo, whether the
    > PreparedStatement obtained from the Connection is reused from an
    > earlier invocation is also hidden. Ergo, it is foolish to rely on
    > putative promises that depend on such reuse.
    >
    > The point of connection pools is to let an application pretend that
    > it's getting a new Connection (and thus a virgin PreparedStatement)
    > each time even though under the hood the pooler is reusing them. You
    > can't simultaneously pretend that the Connection is new and rely on it
    > being reused.


    That's a great way to express it - much better than my wariness.

    Kind regards

    robert

    --
    remember.guy do |as, often| as.you_can - without end
    http://blog.rubybestpractices.com/
     
    Robert Klemme, Jun 24, 2010
    #10
  11. gk

    Arne Vajhøj Guest

    On 24-06-2010 08:40, Robert Klemme wrote:
    > On 24 Jun., 02:36, Arne Vajhøj<> wrote:
    >> On 23-06-2010 12:24, Robert Klemme wrote:
    >>> On 23.06.2010 12:33, Lew wrote:
    >>>> gk wrote:
    >>>>> Please see this ..

    >>
    >>>>> http://java.sun.com/j2se/1.4.2/docs/api/java/sql/PreparedStatement.html

    >>
    >>>>> PreparedStatement : An object that represents a precompiled SQL
    >>>>> statement.

    >>
    >>>>> "precompiled SQL statement" ... who compiled this ?

    >>
    >>>> There are a couple of layers of compilation, one at the JDBC layer
    >>>> (potentially) and the other at the DBMS server.

    >>
    >>>> Precompilation is not the only benefit of prepared statements.

    >>
    >>>>> Is it working like this way ...when I first execute the code below
    >>>>> DBMS compiles when it encounter for the first time and then next time
    >>>>> DBMS does not compile . So, We call it precompiled.

    >>
    >>>> Roughly speaking, yes, although the full truth is somewhat more
    >>>> complicated.

    >>
    >>> It is important to mention that for PS to work efficiently the statement
    >>> must be kept in user code. Invoking prepareStatement() with the same
    >>> string argument twice makes no guarantees about saving compilation in
    >>> the DB. To make the code efficient user must prepare the statement and
    >>> keep it around for recurring use.

    >>
    >>> That is, if you want to benefit from compilation savings - if it is only
    >>> for avoidance of SQL injection / proper conversion of arguments and
    >>> performance does not matter you can recreate PS over and over again-

    >>
    >> Note that good database connection pools are able to reuse
    >> real driver prepared statement even if the pool driver
    >> prepared statement is not reused.

    >
    > Good point! I have to say I'm wary to use those features as long as
    > there is no guarantee that the environment of an application is
    > stable. If it has to run with a pool with and without PS caching you
    > need to to the caching yourself. Otherwise you might see dramatic
    > performance differences. If you know the app is only ever going to be
    > used in an environment relying on this feature is of course perfectly
    > OK.


    Of:

    A)
    - writing simple easy to read code
    - tell the operations guys to setup a good connection pool to
    get good performance
    B)
    - drop the app server connection pool
    - embed a good connection pool with the app
    C)
    - write code that keeps connections and prepared
    statements open for longer time

    then I would prefer #A and #B over #C.

    Arne
     
    Arne Vajhøj, Jun 29, 2010
    #11
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Manoj S. P.
    Replies:
    2
    Views:
    4,416
    Manoj S. P.
    Jul 2, 2003
  2. Raj
    Replies:
    1
    Views:
    419
    Adam Maass
    Aug 25, 2003
  3. uNConVeNtiOnAL

    print out PreparedStatement

    uNConVeNtiOnAL, Sep 3, 2003, in forum: Java
    Replies:
    8
    Views:
    33,245
    Brian A Palmer
    Sep 10, 2003
  4. Max
    Replies:
    6
    Views:
    15,833
    Roedy Green
    Sep 11, 2003
  5. Timo Nentwig
    Replies:
    0
    Views:
    423
    Timo Nentwig
    Feb 2, 2004
Loading...

Share This Page