Re: PreparedStatement

From:

Robert Klemme <shortcutter@googlemail.com>

Newsgroups:

comp.lang.java.programmer

Date:

Thu, 24 Jun 2010 05:40:29 -0700 (PDT)

Message-ID:

<0ed2755f-9694-4eb9-804d-d33a194b1463@r27g2000yqb.googlegroups.com>

On 24 Jun., 02:36, Arne Vajh=F8j <a...@vajhoej.dk> 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.htm=

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 statemen=

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 onl=

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