JDBC Caution

Closing statements

During chemical operations, JChem sessions are opened, closed and assigned to PostgreSQL sessions. In order to release JChem sessions properly, PostgreSQL statements must be closed when using JDBC.

This can be done either by setting up autocommit in JDBC session ( queryWithAutoCommitTrue ) or by calling commit or rollback after every statement execution (even query statements) ( queryWithCommit ). Setting autocommit false without calling commit or rollback ( queryWithAutoCommitFalseWithoutCommit ) could cause sessions getting stuck in and the new queries waiting forever for the queries stuck.

private void queryWithAutoCommitTrue(String stringQuery) throws SQLException {
    connection.setAutoCommit(true);
    try (PreparedStatement statement = connection.prepareStatement(stringQuery, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);) {
        ResultSet resultSet = statement.executeQuery();
        // ResultSet processing here ...
    }
}

private void queryWithCommit(String stringQuery) throws SQLException {
    connection.setAutoCommit(false);
    try (PreparedStatement statement = connection.prepareStatement(stringQuery, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);){
        ResultSet resultSet = statement.executeQuery();
        // ResultSet processing here ...
        connection.commit();
    }
}

private void queryWithAutoCommitFalseWithoutCommit(String stringQuery) throws SQLException {
    //don't copy, this method is an example how NOT to write your code
    connection.setAutoCommit(false);
    try (PreparedStatement statement = connection.prepareStatement(stringQuery, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);) {
        ResultSet resultSet = statement.executeQuery();
        // ResultSet processing here ...
        throw new RuntimeException("don't copy, this method is an example how NOT to write your code");
    }
}

Cleaning SQL log

Setting SQL log level to debug may produce high amount of log entries. JDBC keeps these log messages connected to Statements, Connections, and ResultSets which may fill the JVM heap causing memory or GC problems.

To avoid this problem use the clearWarnings() methods of the classes above.