Get query string from PreparedStatement

[Former Member]

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

try {

String QUERY_STRING = "SELECT * FROM table WHERE id = ? ";

stmt.setString(1, 100);

Preparedstatement stmt = con.prepareStatement( QUERY_STRING )

stmt.executeUpdate( );

}

catch (SQLException exc) {

throw new RuntimeException( <query> );

}

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

[Former Member · Accepted solution]

Hi,

the reason why this doesn't work is that a prepared statement is actually sent to the DB with a ? in the parameter to allow the DB server to precompile the query only once and use variable substitution (look up Oracle Shared Pool to understand better for why this is important for performance) instead of full re-compilation with each new query that uses different parameters.

So the short answer is that you can't get the full sql query string from the prepared statement (without writing / finding a proxy class that works for primitives and Strings).

The longer answer is that you can do this through wrapping the Prepared Statement with a debugging proxy, but even that might not allow you to get all the right output.

The easiest way for you to deal with the situation so that you have some readable result every time is to simply create a map which matches the setters and then do a simple replacement of the ? in the query String which you then add to the exception you throw as a message.

[Former Member]

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 QUERY_STRING = "SELECT * FROM table WHERE id = ? ";

try {

stmt.setString(1, param);

Preparedstatement stmt = con.prepareStatement( QUERY_STRING )

stmt.executeUpdate( );

}

catch (SQLException exc) {

throw new RuntimeException(QUERY_STRING + “ parameters are :- ” + param );

}

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

String param = 100;

String QUERY_STRING = "SELECT * FROM table WHERE id = ? ";

try {

stmt.setString(1, param);

Preparedstatement stmt = con.prepareStatement( QUERY_STRING )

stmt.executeUpdate( );

}

catch (SQLException exc) {

String query = getQueryString(QUERY_STRING, param);

throw new RuntimeException(query);

}

Note:- for a generalized method pass the second argument of type List, so that any arbitrary length of parameters can be put in query.

Hope it will solve your problem.