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'.

Get query string from PreparedStatement

When performing an SQL query, If you would like to catch any SQLException and throw a RunTimeException containing the failed query, like:

try {
String YOUR_QUERY = "SELECT * FROM table WHERE id = ? ";
Preparedstatement stamnt = con.prepareStatement( YOUR_QUERY )
stamnt.setString(1, 100);

stamnt.executeUpdate( );
}
catch (SQLException exc) {
throw new RuntimeException( );
}

where query would be "SELECT * FROM TABLE WHERE id = 100". However, it it is not possible to retrieve this query from a prepared statement.

There are two ways to solve this problem:-

(1) the easiest way is encapsulate the parameters and query string in the exception. It will not solve your problem 100%, but at least it will be enough descriptive to handle the error. Code snippet as below-

String param = 100;
String YOUR_QUERY = "SELECT * FROM table WHERE id = ? ";
try {
Preparedstatement stamnt = con.prepareStatement( YOUR_QUERY );
stamnt.setString(1, param);
stamnt.executeUpdate( );
}
catch (SQLException exc) {
throw new RuntimeException(YOUR_QUERY + “ parameters are :- ” + param );
}

(2) You can create a utility method in which we will pass the query string and list of parameters. Simply replace all the ‘?’ with corresponding parameters. Code snippet as below-

String param = 100;
String YOUR_QUERY = "SELECT * FROM table WHERE id = ? ";
try {
stamnt.setString(1, param);
Preparedstatement stamnt = con.prepareStatement( YOUR_QUERY );
stamnt.executeUpdate( );
}
catch (SQLException exc) {
String query = getQueryString(QUERY_STRING, param);
throw new RuntimeException(query);
}

String getQueryString(String query, Object[] params) {
    int idx = query.indexOf("?");
    int count = 0;
    while (idx != -1) {
        Object value = params[count++];
        if (value instanceof String) {
            value = "'" + value.toString() + "'";
        }
        query = query.replaceFirst("\\?", value.toString());
        idx = query.indexOf("?");
    }
    return query;
}