PreparedStatement problem

[gk]

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

"...An object that represents a precompiled SQL statement.

A SQL statement is precompiled and stored in a PreparedStatement
object. This object can then be used to efficiently execute this
statement multiple times....."




what these means ?

what does it mean by "precompiled SQL statement" ? who compiles ? JDK
or DBMS ?

ok, say i am writing ..

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


Now, what does "precompiled SQL statement" means here ?

could you please explain the meaning of this word "precompiled" ?

we know java code is compiled by JDK and SQL query is compiled by DBMS
..

I dont understand whats the meaning of "precompiled" here and how it is
related in this context ?

 

[Robert Klemme]

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

"...An object that represents a precompiled SQL statement.

A SQL statement is precompiled and stored in a PreparedStatement
object. This object can then be used to efficiently execute this
statement multiple times....."




what these means ?

what does it mean by "precompiled SQL statement" ? who compiles ? JDK
or DBMS ?

ok, say i am writing ..

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


Now, what does "precompiled SQL statement" means here ?

could you please explain the meaning of this word "precompiled" ?

we know java code is compiled by JDK and SQL query is compiled by DBMS
.

I dont understand whats the meaning of "precompiled" here and how it is
related in this context ?

Somewhere below you application layer the SQL statement is parsed and
stored for later use. This can happen in the driver or in the DB - more
likely in the DB though. But you don't have to worry where exactly that
happens. All you need to know is that it's usually more efficient to
reuse a PreparedStatement with different parameter sets than to use
plain SQL every time because parsing is not just about syntax; every DB
will do a lot of other things like figuring an execution plan etc.

The most notable exception to this rule is probably when the compilation
leads to a sub optimal execution plan for other sets of parameters.

If you want to read up on what DB's typically do, you can search for
"bind parameters", "parameter sniffing", "SQL prepare".

Regards

robert

 

[Mark Jeffcoat]

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

"...An object that represents a precompiled SQL statement.

A SQL statement is precompiled and stored in a PreparedStatement
object. This object can then be used to efficiently execute this
statement multiple times....."




what these means ?

what does it mean by "precompiled SQL statement" ? who compiles ? JDK
or DBMS ?

With proper JDBC support, the DBMS. The DBMS can work out
one query plan for a PreparedStatment, and reuse the same
plan for every set of parameters you give it. This can
be a big win if you're going to be making the same query
many times.

 

[gk]

Somewhere below you application layer the SQL statement is parsed and
stored for later use. This can happen in the driver or in the DB - more
likely in the DB though. But you don't have to worry where exactly that
happens.

I am worried about it really and hence i posted this question.
I want to know , how its working.

could you please explain more.

ok, the above code is first compiled by java code ...right ? then we
run this code....right ?

so, what happens now ? what is the precompilation means here ? who is
precompiling and what is precompiled ?

please explain this stuff.

i want to know this concept.

Thanks for the response.



All you need to know is that it's usually more efficient to

 

[gk]

With proper JDBC support, the DBMS. The DBMS can work out
one query plan for a PreparedStatment, and reuse the same
plan for every set of parameters you give it. This can
be a big win if you're going to be making the same query
many times.

but how its working ?

do you mean, when we run the java code ....the SQL query go to the DB
....then DB makes a query plan for it via its DB compiler and keep
remeber this query plan permanently as it has come from a
preparedstatement .......so next times , the DB dont make any new query
plan .


But see , they are telling "precompiled" ...still , i dont know what it
means ? and also how DB will know whther the query is coming from a
preparedstatement or simple statement .....DB is seperate layer.

 

[Mark Jeffcoat]

but how its working ?

I'm tempted to say, "It works very well". Databases
are very good abstractions, and the vast majority
of time, a basic understanding of relational algebra
and SQL is all you need to deal with them successfully.

But you've got a good question below:

do you mean, when we run the java code ....the SQL query go to the DB
...then DB makes a query plan for it via its DB compiler and keep
remeber this query plan permanently as it has come from a
preparedstatement .......so next times , the DB dont make any new query
plan .

That's right.

But see , they are telling "precompiled" ...still , i dont know what it
means ? and also how DB will know whther the query is coming from a
preparedstatement or simple statement .....DB is seperate layer.

That's also right: the database is quite separate from the
JVM.

The key here is JDBC. JDBC is not something that lives
entirely in the Java world, it knows about the internals
of the database you're connecting to, and knows exactly
how to tell the database what kind of statement it's
dealing with. That's why you need a new JDBC implementation
every time you switch databases.

(For instance, I have in one current project's lib directory
jconn3.jar and postgresql-8.0-313.jdbc3.jar; one's for a Sybase
database, and the other's for Postgres. Both implement the same
JDBC.)

Just how much work can be done in advance ("precompilation")
probably varies wildly with what type of database you're
connecting to. That's okay; while you're writing client code,
it just doesn't matter.

 

[gk]

I'm tempted to say, "It works very well". Databases
are very good abstractions, and the vast majority
of time, a basic understanding of relational algebra
and SQL is all you need to deal with them successfully.

But you've got a good question below:



That's right.



That's also right: the database is quite separate from the
JVM.

The key here is JDBC. JDBC is not something that lives
entirely in the Java world, it knows about the internals
of the database you're connecting to, and knows exactly
how to tell the database what kind of statement it's
dealing with. That's why you need a new JDBC implementation
every time you switch databases.

(For instance, I have in one current project's lib directory
jconn3.jar and postgresql-8.0-313.jdbc3.jar; one's for a Sybase
database, and the other's for Postgres. Both implement the same
JDBC.)

Just how much work can be done in advance ("precompilation")
probably varies wildly with what type of database you're
connecting to. That's okay; while you're writing client code,
it just doesn't matter.

yea, thats what i wanted know . nice explanation.