Prepared Statement and Precompilation (What is precompilation?)

JDBC driver sends a special statement (i.e. PreparedStatement) to DB to get it executed.

 

Now, it’s up to DB level settings whether it will store the query in DB’s cache or not and it has nothing to do with prepared statement. Actually Driver sends the request to DBMS that I need you to give me the parsed/compiled query when I send it for the first time. This 'compiled' query object will be stored in PreparedStatement object (in Java). Mind here that if you use same query with different PreparedStatement object, it will again be parsed :) See the example below:

 

PreparedStatement ps1 = con.prepareStatement("Update Employee set name = ? where id = ?");  // In this statement the parsed or in fact compiled query is stored in ps1 regardless DB caches it nor not.

 

ps1.setString(1, "Bob");

ps1.setInt(2,5);

ResultSet rs = ps1.executeQuery();

 

ps1.setString(1, "Robert");

ps1.setInt(2,6);

rs = ps1.executeQuery();

 

In both above scenarios query is already compiled and just parameter is set which gives you good performance,

 

PreparedStatement ps1 = con.prepareStatement("Update Employee set name = ? where id = ?"); // In this statement the query is parsed again - mind the word – “again".  This compiled query object is stored in ps2.

 

And if you want to use normal Statement object

 

normalStatement.executeUpdate("Update Employee set name = ‘Bob’ where id = 6");

In above case query will be parsed/compiled on runtime only 'again'.

Different ways of getting database connection

There are two ways of getting JDBC connection in java. One is from Driver Manager and the other is from an object of javax.sql.DataSource. The data source can be obtained from JNDI look up or by any other means. The implementation of Data Source internally uses the DriverManager but can pool the connections and reuse them and can give wrappers on actual connection so that when you call the close method, the connection is returned to the pool instead of actually closing the underlying connection.