Re: PreparedStatement
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