Using Clobs with Oracle and HibernateHibernate 1.2.1 comes with support for Clobs (and Blobs). Just use the clob type in your mapping file and java.sql.Clob in your persistent class. However, due to problems with the Oracle JDBC driver, this support falls short when you try to store more than 4000 characters in a Clob. In order to properly store Clobs in Oracle 8 with Hibernate 1.2.x, you must do the following:
s = sf.openSession();
tx = s.beginTransaction();
foo = new Foo();
foo.setClob( Hibernate.createClob(" ") );
s.save(foo);
tx.commit();
s.close();
s = sf.openSession();
tx = s.beginTransaction();
foo = (Foo) s.load( Foo.class, foo.getId(), LockMode.UPGRADE );
oracle.sql.CLOB clob = (oracle.sql.CLOB) foo.getClob();
java.io.Writer pw = clob.getCharacterOutputStream();
pw.write(content);
pw.close();
tx.commit();
s.close();
You should be careful not to pass a zero-length string to Hibernate.createClob(), otherwise Oracle will set the column value to NULL and the subsequent getClob() call will return null. In Hibernate2, the following (much more elegant) solution exists:
s = sf.openSession();
tx = s.beginTransaction();
foo = new Foo();
foo.setClob( Hibernate.createClob(" ") );
s.save(foo);
s.flush();
s.refresh(foo, LockMode.UPGRADE); //grabs an Oracle CLOB
oracle.sql.CLOB clob = (oracle.sql.CLOB) foo.getClob();
java.io.Writer pw = clob.getCharacterOutputStream();
pw.write(content);
pw.close();
tx.commit();
s.close();
If you need a solution that is more transparent and you can rely on having the Oracle 9.x JDBC drivers then you can try using the newly introduced oracle.sql.CLOB.createTemporary method. Here is an example user type that uses this idea while converting Clobs to strings. Note that it uses reflection to avoid a compile-time dependency on the Oracle driver, however the methods can be used directly if you wish. Also it should be straightforward to convert this UserType to one that just maps to a clob in the data object.
package foobar;
import java.io.Reader;
import java.io.BufferedReader;
import java.io.StringReader;
import java.io.IOException;
import java.io.Writer;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import net.sf.hibernate.Hibernate;
import net.sf.hibernate.HibernateException;
import net.sf.hibernate.UserType;
import org.apache.commons.lang.ObjectUtils;
/**
* Based on community area design patterns on Hibernate site.
* Maps java.sql.Clob to a String special casing for Oracle drivers.
* @author Ali Ibrahim, Scott Miller
*/
public class StringClobType implements UserType
{
/** Name of the oracle driver -- used to support Oracle clobs as a special case */
private static final String ORACLE_DRIVER_NAME = "Oracle JDBC driver";
/** Version of the oracle driver being supported with clob. */
private static final int ORACLE_DRIVER_MAJOR_VERSION = 9;
private static final int ORACLE_DRIVER_MINOR_VERSION = 0;
public int[] sqlTypes()
{
return new int[] { Types.CLOB };
}
public Class returnedClass()
{
return String.class;
}
public boolean equals(Object x, Object y)
{
return ObjectUtils.equals(x, y);
}
public Object nullSafeGet(ResultSet rs, String[] names, Object owner)
throws HibernateException, SQLException
{
Reader clobReader = rs.getCharacterStream(names[0]);
if (clobReader == null)
{
return null;
}
String str = new String();
BufferedReader bufferedClobReader = new BufferedReader(clobReader);
try
{
String line = null;
while( (line = bufferedClobReader.readLine()) != null )
{
str += line;
}
bufferedClobReader.close();
}
catch (IOException e)
{
throw new SQLException( e.toString() );
}
return str;
}
public void nullSafeSet(PreparedStatement st, Object value, int index)
throws HibernateException, SQLException
{
DatabaseMetaData dbMetaData = st.getConnection().getMetaData();
if (value==null)
{
st.setNull(index, sqlTypes()[0]);
}
else if (ORACLE_DRIVER_NAME.equals( dbMetaData.getDriverName() ))
{
if ((dbMetaData.getDriverMajorVersion() >= ORACLE_DRIVER_MAJOR_VERSION) &&
(dbMetaData.getDriverMinorVersion() >= ORACLE_DRIVER_MINOR_VERSION))
{
try
{
// Code compliments of Scott Miller
// support oracle clobs without requiring oracle libraries
// at compile time
// Note this assumes that if you are using the Oracle Driver.
// then you have access to the oracle.sql.CLOB class
// First get the oracle clob class
Class oracleClobClass = Class.forName("oracle.sql.CLOB");
// Get the oracle connection class for checking
Class oracleConnectionClass = Class.forName("oracle.jdbc.OracleConnection");
// now get the static factory method
Class partypes[] = new Class[3];
partypes[0] = Connection.class;
partypes[1] = Boolean.TYPE;
partypes[2] = Integer.TYPE;
Method createTemporaryMethod = oracleClobClass.getDeclaredMethod( "createTemporary", partypes );
// now get ready to call the factory method
Field durationSessionField = oracleClobClass.getField( "DURATION_SESSION" );
Object arglist[] = new Object[3];
Connection conn = st.getConnection();
// Make sure connection object is right type
if (!oracleConnectionClass.isAssignableFrom(conn.getClass()))
{
throw new HibernateException("JDBC connection object must be a oracle.jdbc.OracleConnection. " +
"Connection class is " + conn.getClass().getName());
}
arglist[0] = conn;
arglist[1] = Boolean.TRUE;
arglist[2] = durationSessionField.get(null); //null is valid because of static field
// Create our CLOB
Object tempClob = createTemporaryMethod.invoke( null, arglist ); //null is valid because of static method
// get the open method
partypes = new Class[1];
partypes[0] = Integer.TYPE;
Method openMethod = oracleClobClass.getDeclaredMethod( "open", partypes );
// prepare to call the method
Field modeReadWriteField = oracleClobClass.getField( "MODE_READWRITE" );
arglist = new Object[1];
arglist[0] = modeReadWriteField.get(null); //null is valid because of static field
// call open(CLOB.MODE_READWRITE);
openMethod.invoke( tempClob, arglist );
// get the getCharacterOutputStream method
Method getCharacterOutputStreamMethod = oracleClobClass.getDeclaredMethod( "getCharacterOutputStream", null );
// call the getCharacterOutpitStream method
Writer tempClobWriter = (Writer) getCharacterOutputStreamMethod.invoke( tempClob, null );
// write the string to the clob
tempClobWriter.write((String)value);
tempClobWriter.flush();
tempClobWriter.close();
// get the close method
Method closeMethod = oracleClobClass.getDeclaredMethod( "close", null );
// call the close method
closeMethod.invoke( tempClob, null );
// add the clob to the statement
st.setClob( index, (Clob)tempClob );
}
catch( ClassNotFoundException e )
{
// could not find the class with reflection
throw new HibernateException("Unable to find a required class.\n" + e.getMessage());
}
catch( NoSuchMethodException e )
{
// could not find the metho with reflection
throw new HibernateException("Unable to find a required method.\n" + e.getMessage());
}
catch( NoSuchFieldException e )
{
// could not find the field with reflection
throw new HibernateException("Unable to find a required field.\n" + e.getMessage());
}
catch( IllegalAccessException e )
{
throw new HibernateException("Unable to access a required method or field.\n" + e.getMessage());
}
catch( InvocationTargetException e )
{
throw new HibernateException(e.getMessage());
}
catch( IOException e )
{
throw new HibernateException(e.getMessage());
}
}
else
{
throw new HibernateException("No CLOBS support. Use driver version " + ORACLE_DRIVER_MAJOR_VERSION +
", minor " + ORACLE_DRIVER_MINOR_VERSION);
}
}
else
{
String str = (String)value;
StringReader r = new StringReader(str);
st.setCharacterStream(index, r, str.length());
}
}
public Object deepCopy(Object value)
{
if (value == null) return null;
return new String((String) value);
}
public boolean isMutable()
{
return false;
}
}
Notes: 1. This approach is very fragile when not used directly with oracle jdbc connections. Somwhere in the createTemporary method the connection is cast to an oracle.jdbc.OracleConnection. Of course this means that the connection you give it must be assignable to that class. The code here checks for that and tries to throw a meaningful exception. The practical implication is that connection pooling mechanisms such as in web application servers or jdbc wrappers such as p6spy can break the code. The workaround is to somehow extract the underlying connection to give to the createTemporary method (this is usually straightforward as I have done this for p6spy and oc4j in my custom code). 2. Related to the first point, even though OC4J/Orion data source pooling class for Oracle actually is assignable to oracle.jdbc.OracleConnection, there were NullPointerExceptions being thrown. When I extracted the underlying connection through the getPhysicalConnection method, it worked, so I assume there is some wierdness with the behavior of the wrapper class (OrclCMTConnection). Enjoy!
|