Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Java > PreparedStatement

Reply
Thread Tools

PreparedStatement

 
 
gk
Guest
Posts: n/a
 
      06-23-2010
Please see this ..

http://java.sun.com/j2se/1.4.2/docs/...Statement.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 ?

 
Reply With Quote
 
 
 
 
Lew
Guest
Posts: n/a
 
      06-23-2010
gk wrote:
> Please see this ..
>
> http://java.sun.com/j2se/1.4.2/docs/...Statement.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
 
Reply With Quote
 
 
 
 
Robert Klemme
Guest
Posts: n/a
 
      06-23-2010
On 23.06.2010 12:33, Lew wrote:
> gk wrote:
>> Please see this ..
>>
>> http://java.sun.com/j2se/1.4.2/docs/...Statement.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/
 
Reply With Quote
 
Arne Vajh°j
Guest
Posts: n/a
 
      06-24-2010
On 23-06-2010 03:56, gk wrote:
> Please see this ..
>
> http://java.sun.com/j2se/1.4.2/docs/...Statement.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
 
Reply With Quote
 
Arne Vajh°j
Guest
Posts: n/a
 
      06-24-2010
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/...Statement.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

 
Reply With Quote
 
Robert Klemme
Guest
Posts: n/a
 
      06-24-2010
On 24 Jun., 02:36, Arne Vajh°j <(E-Mail Removed)> 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/...Statement.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
 
Reply With Quote
 
Lew
Guest
Posts: n/a
 
      06-24-2010
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 <(E-Mail Removed)> 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
 
Reply With Quote
 
Robert Klemme
Guest
Posts: n/a
 
      06-24-2010
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<(E-Mail Removed)> 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/

 
Reply With Quote
 
Lew
Guest
Posts: n/a
 
      06-24-2010
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
 
Reply With Quote
 
Robert Klemme
Guest
Posts: n/a
 
      06-24-2010
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/
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
PreparedStatement: variable amount of placeholders Timo Nentwig Java 0 02-02-2004 01:56 PM
Display a PreparedStatement content Max Java 6 09-11-2003 09:09 PM
print out PreparedStatement uNConVeNtiOnAL Java 8 09-10-2003 04:49 PM
Discuss: PreparedStatement and Connection Pooling Raj Java 1 08-25-2003 10:34 PM
Execution time problem with Insert Query using PreparedStatement Manoj S. P. Java 2 07-02-2003 03:52 PM



Advertisments