PreparedStatement
in [Java Programming]
|
Prev: Newbie Q. Accessing a variable in one method from another.
Next: Is there a 'Filter' or 'Predicate' interface anywhere in the JDK?
From: gk on 23 Jun 2010 03:56 Please see this .. http://java.sun.com/j2se/1.4.2/docs/api/java/sql/PreparedStatement.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 ?
From: Lew on 23 Jun 2010 06:33 gk wrote: > Please see this .. > > http://java.sun.com/j2se/1.4.2/docs/api/java/sql/PreparedStatement.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
From: Robert Klemme on 23 Jun 2010 12:24 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.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/
From: Arne Vajhøj on 23 Jun 2010 20:36 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.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
From: Arne Vajhøj on 23 Jun 2010 20:35
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 |