Using try-with-resources with JDBC objects

There is a new language construct that is introduced in JDK7 that applies to JDBC.  It’s called try-with-resource.  There is a tutorial at http://docs.oracle.com/javase/tutorial/essential/exceptions/tryResourceClose.html.
It allows you to automatically close an object when it is no longer needed.  Theoretically, the object must extend java.lang.AutoCloseable.  In JDK7, that list includes java.sql.CallableStatement, Connection, PreparedStatement, Statement, ResultSet, and *RowSet.  The following code

Statement stmt = null;
try {
  stmt = con.createStatement();
} catch (Exception ignore) {
} finally {
  if (stmt != null) stmt.close()
}
becomes a shorter version using the new syntax
try (Statement stmt = con.createStatement()) {
} catch (Exception ignore) {
}

Here’s what a larger example looks like that I embedded in a WebLogic servlet for testing.  Note that there are two resources in the first try-with-resource, separated by a semi-colon.

private String doit() {
  String table2 = "test222";
  String dropsql = "drop table " + table2 + "";
  String createsql = "create table " + table2 + " ( col1 int, col2 int )";
  String insertsql = "insert into " + table2 + " values (1,2)";
  String selectsql = "select col1, col2 from " + table2 + "";
  try {
    ds = getDS();
  } catch(Exception e) {
    return("failed to get datasource");
  }
  try (Connection conn = ds.getConnection();
    Statement stmt = conn.createStatement()) {
    try {
       stmt.execute(dropsql);
   } catch (Exception ignore) {} // ignore if table not dropped
   stmt.execute(createsql);
   stmt.execute(insertsql);
   try (ResultSet rs = stmt.executeQuery(selectsql)) {
     rs.next();
   } catch (Exception e2) {
     e2.printStackTrace();
     return("failed");
   }
 } catch(Exception e) {
   e.printStackTrace();
   return("failed");
 }
 return "DONE";
}

I’m not sure whether or not I like the programming paradigm – you can decide that for yourself.

How do I know that the connection, statement, and result set get closed?  I can turn on JDBCSQL debugging in the server and look at the server log output (this is a good trick to use to see what is happening under the covers).  Here is a stripped down version of the output.

 Connection@1 CreateStatement() 
 Connection@1 CreateStatement returns StatementWrapper@2 
 StatementWrapper@2 execute(drop table test222) 
 StatementWrapper@2 execute(drop table test222) throws java.sql.SQLSyntaxErrorException: table or view does not exist
 StatementWrapper@2 execute(create table test222 ( col1 int, col2 int )) 
 StatementWrapper@2 execute returns false 
 StatementWrapper@2 execute(insert into test222 values (1,2)) 
 StatementWrapper@2 execute returns false 
 StatementWrapper@2 executeQuery(select col1, col2 from test222) 
 StatementWrapper@2 executeQuery returns ResultSetImpl@3 
 ResultSetImpl@3 next() 
 ResultSetImpl@3 next returns true 
 ResultSetImpl@3 close() 
 ResultSetImpl@3 close returns 
 StatementWrapper@2 close() 
 StatementWrapper@2 close returns 
 Connection@1 close() 
 Connection@1 close returns

You might be thinking that there are not a lot of JDBC 4.1 compliant drivers out there and that’s true (the Oracle thin driver will have an ojdbc7.jar in version 12c, when it is released).  However, an object like java.sql.Statement implements AutoClosable so when running on JDK7 the driver Statement also is assumed to implement AutoClosable.  The object just needs to have a close() method so all of these JDBC objects have met the criteria since the initial specification.  WebLogic Server started supporting JDK7 in version 10.3.6.  Testing with all drivers that are shipped with WebLogic server, plus some drivers from various vendors, show that this JDK7 language feature works just fine with pre-JDBC 4.1 drivers. 

Note that this trick doesn't work for new JDK7 methods - you will get an AbstractMethodError.

So if you like this programming paradigm, start using it now with WebLogic Server 10.3.6 or later.

Comments:

Post a Comment:
  • HTML Syntax: NOT allowed
About

The official blog for Oracle WebLogic Server fans and followers!

Stay Connected

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
5
6
7
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today