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;
}