JDBC Best Practices

Added by Brad Johnson, last edited by Brad Johnson on Jun 16, 2005  (view change)

uPortal 2.x Database Code Standards

Please use this template when writing code to access the uPortal 2.x database.

This explicitly closes the result set, statement, and connection objects. There is no guarantee as to when the garbage collector will run, if at all, so until that happens there are resources being consumed that are no longer needed, including scarce resources like cursors. It is much better to release the resources when done with them rather than letting the JVM do it.

Connection con = RDBMServices.getConnection();
try {
    String query = "SELECT ...";
    RDBMServices.PreparedStatement pstmt = new RDBMServices.PreparedStatement(con, query);
    try {
        pstmt.clearParameters();
        pstmt.setXXXXX(1, someValue);
        pstmt.setXXXXX(2, someValue); // etc
        LogService.log(LogService.DEBUG, query);
        ResultSet rs = pstmt.executeQuery();
        try {
            if (rs.next()) {
               ...
            }
        } finally {
            rs.close();
        }
    } finally {
        pstmt.close();
    }
} finally {
    RDBMServices.releaseConnection(con);
}

Proposed Change to the above Template

This version will allow the original exception to be reported. In the above template an exception may happen in the rs.close or pstmt.close and would be reported instead of any exception that caused the problem in the first place:

Connection con = RDBMServices.getConnection();
try {
    String query = "SELECT ...";
    RDBMServices.PreparedStatement pstmt = new RDBMServices.PreparedStatement(con, query);
    try {
        pstmt.clearParameters();
        pstmt.setXXXXX(1, someValue);
        pstmt.setXXXXX(2, someValue); // etc
        LogService.log(LogService.DEBUG, query);
        ResultSet rs = pstmt.executeQuery();
        try {
            if (rs.next()) {
               ...
            }
        } finally {
            try{
                rs.close();
            }catch(Exception e){ /*ignore*/ }
        }
    } finally {
        try{
            pstmt.close();
        }catch(Exception e){ /*ignore*/ }
    }
} finally {
    RDBMServices.releaseConnection(con);
}
Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.