JDBC 4 method java.sql.Clob.free () and backward compatibility

Question

I am studying an interesting performance issue where there are no calls to java.sql.Clob.free() on a frequently used resource. This method in Clob was introduced in Java 6 / JDBC 4, so it is possible that this is actually a regression that occurs when upgrading from JDBC 3 to JDBC 4.

Is this known in the Oracle JDBC driver? Could it be said that before, Clobs somehow magically released, whereas with JDBC 4 they MUST be released manually? Or is there a driver setting that can be used for compatibility with JDBC 3?

Note that this also applies to Blob , of course.

Answer 1

Our application should definitely call java.sql.Clob.free () explicitly on oracle.sql.CLOB (used for jdbc () to get java.sql.Clob) with Oracle 11g and 'ojdbc6.jar' (Specification version ' 4.0 'and implementation version '11 .2.0.3.0' in MANIFEST.MF). Otherwise, the application suffers from significant memory leaks.

Answer 2

I searched for the Oracle vendor JDBC driver (ojdbc6.jar) for Oracle Database here
I found the demo.zip file here
I unzipped it and greased the source for clob and blob.
There is a file TemporaryLobJDBC40.java
There is a sample where temporary clob and blob are created, then filled with some data, then inserted into the table through a prepared statement (parameterized INSERT).
Then the statement is executed, closed, temp clob and blob are released and the transaction is completed.

The author then iterates over the rows of the table, creating constant blob / clobs and assigning them the objects returned from getClob (), getBlob () and uploading the contents to the stream.

Permanent drops are never released. I assume that after each iteration, when objects go beyond the scope, the garbage collector automatically releases these objects.

After the last iteration, the last two Blob / Clob objects are not explicitly freed, but are implicitly cleared by the garbage collector (when it decides to start), their scope ends with the last iteration. (after})

Personally, I would do the cleaning explicitly, but to each his own. This demo shows that you can do it anyway.

Here's the code (TemporaryLobJDBC40.java):

 /* * This sample shows how to create * a temporary BLOB and CLOB, write * some data to them and then insert * them into a table. This makes a * permanent copy in the table. The * temp lobs are still available for * further use if desired until the * transaction is committed. * When fetched from the table, the * lobs are no longer temporary. * * This version uses the new * JDBC 4.0 factory methods in * java.sql.Connection and the * free methods in java.sql.Blob and Clob * * Testing for temporary status still * requires Oracle specfiic APIs in * oracle.sql.BLOB and oracle.sql.CLOB. * * It needs jdk6 or later version and ojdbc6.jar */ import java.sql.Connection; import java.sql.SQLException; import java.sql.Statement; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.Blob; import java.sql.Clob; class TemporaryLobJDBC40 { public static void main (String args []) throws Exception { Connection conn = DemoConnectionFactory.getHRConnection( args ); LobExample.createSchemaObjects( conn ); Blob tempBlob = conn.createBlob(); Clob tempClob = conn.createClob(); System.out.println ("tempBlob.isTemporary()="+ ((oracle.sql.BLOB)tempBlob).isTemporary()); System.out.println ("tempClob.isTemporary()="+ ((oracle.sql.CLOB)tempClob).isTemporary()); LobExample.fill(tempBlob, 100L); LobExample.fill(tempClob, 100L); String insertSql = "insert into jdbc_demo_lob_table values ( ?, ?, ? )"; PreparedStatement pstmt = conn.prepareStatement( insertSql ); pstmt.setString( 1, "one" ); pstmt.setBlob( 2, tempBlob ); pstmt.setClob( 3, tempClob ); pstmt.execute(); pstmt.close(); tempBlob.free(); tempClob.free(); conn.commit(); Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery( "select b, c from jdbc_demo_lob_table" ); while( rs.next() ) { Blob permanentBlob = rs.getBlob(1); Clob permanentClob = rs.getClob(2); System.out.println ("permanentBlob.isTemporary()="+ ((oracle.sql.BLOB)permanentBlob).isTemporary()); System.out.println ("permanentClob.isTemporary()="+ ((oracle.sql.CLOB)permanentClob).isTemporary()); LobExample.dump(permanentBlob); LobExample.dump(permanentClob); } rs.close(); stmt.close(); conn.close(); } }