I'm getting SQL result sets in Java from my Oracle DB and I want to know if and how I can record/catch SQL errors in Java. So when I try to execute some SQL:
ResultSet resultSet = connection.executeQuery(query);
I get an error like ORA-00942: table or view does not exist. How can I record that?
Use the catch statement to store the proper SQL exception as you want/need:
try (ResultSet rs = statement.executeQuery(query)) {
/* retrieve the data */
} catch (SQLException e) {
/* handle the exception properly */
storeExceptionSomewhereElse(e);
}
//...
public void storeExceptionSomewhereElse(SQLException e) {
/*
Here you can store the exception in database
or external data source.
*/
}
You can use try-catch for getting the error. Then you can set up a Logger to record the the exception.
For Example:
catch(SqlException e){
Logger.LogDB("Record1: "+ e);
}
If you want a solution that is a little more robust and will log every exception from executeQuery, create wrapper classes for Connection and Statement by implenting said interfaces, and then proxying the calls to real conneciton objects. Change the logic that you use to acquire the connection, and pass in the original connection as a construtor arguement. Like so:
public class WrapperConnection implements java.sql.Connection {
public Connection _realConn;
public WrapperConnection(Connection realConnection) {
_realConn = realConnection;
}
@Override
public Statement createStatement() throws SQLException {
return new WrapperStatement(_realConn.createStatement());
}
...lots of other implmented proxy methods...
}
And the Statement object:
public class WrapperStatement implements Statement {
public Statement _realStmt;
public WrapperStatement(Statement realStmt) {
_realStmt = realStmt;
}
@Override
public ResultSet executeQuery(String sql) throws SQLException {
try {
return _realStmt.executeQuery(sql);
} catch(SQLException sqlEx) {
logSQLException(sqlEx);
throw sqlEx;
}
}
...lots of other implemented proxy calls...
}
The cumbersome piece is that you'll have to implement all other calls in the interface, but they are pretty simple, basically proxy each call down to the "real" object that was passed into the constructor; then again, this will give you the chance to log for every type of call as well (acquiring Prepared calls, etc.) You can also use this to log the SQL that is executed through executeQuery(), and pair it with the exception that is thrown.
