Hi All !

I mentioned the following to one of the java developer:
Use parameterized queries.
The key performance benefit of parameterized queries is that the optimizer can formulate an access path that can be reused over repeated executions of the statement. This can accrue a large performance gain for the program as compared to issuing a completely new SQL statement every time a new value is required in a WHERE clause. (quoted from the db2 manual)

The java developer replied that all java sql statements are parameterized queries. I am not a java person. Could someone validate this statement for me - that all java sql statements are parameterized queries.

I am trying to identify if I need to include the 'Use parameterized queries' statement in the best practices document (specifically sql best practices for java developers)

Thanks
Anil

No, all java sql statements are not parameterized. The java.sql.Statement does not take parameters. The java.sql.PreparedStatement and the CallableStatement do.

Andy

If you run a snapshot for dynamic SQL, then you will see the queries. If the predicates have question marks (?) instead of literals, then it is parameterized.

http://publib.boulder.ibm.com/infoce...plespjava1.htm
This is a very simple example of a parameterized SQL in Java program ..
Get an example of a program which the Developers claim to be using parameterised SQL ...
and check for keywords like setString (or any of the setxxx type constructs detailed in table 57 in http://publib.boulder.ibm.com/infoce...d/rjvjdapi.htm)
The general constuct of the program will be as follows :
a) Assign a statement text with a parameter marker to a string variable
b) Prepare the sql statement using the above string variable and assign it to a statement object
c) Use setString,setInt etc on the statement object and set the parameter values of the SQL
d) Execute the statement object using executeQuery (select stmts) or executeUpdate (IUD stmts)
e) Repeat c and d as many times as needed [ because the sql statement is already prepared ]

HTH

Sathyaram