关闭

Using Clobs/Blobs with Oracle and Hibernate(Hibernate中使用Oracle的Clobs/Blobs类型)

标签: hibernateoracleimportobjectinterceptorstring
1719人阅读 评论(0) 收藏 举报
分类:
Using Clobs/Blobs with Oracle and Hibernate
Hibernate 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!

Updated Clobs handling for Oracle and Hibernate
- uses interceptor and avoids compile-time dependency.
[Lukasz's compilation (added on 14th Oct 2004)]
Just copy/paste it.
User type:
import net.sf.hibernate.HibernateException;
import net.sf.hibernate.UserType;
 
import org.apache.commons.lang.ObjectUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
 
import java.io.IOException;
import java.io.StringReader;
import java.io.Writer;
 
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
 
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
 
 
/**
 * Implementation of Oracle's CLOB handling
 */
public class StringClobType implements UserType {
    protected static Log log = LogFactory.getLog(StringClobType.class);
 
    /** 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;
 
    /**
     * @see net.sf.hibernate.UserType#nullSafeGet(java.sql.ResultSet, java.lang.String[], java.lang.Object)
     */
    public Object nullSafeGet(ResultSet rs, String[] names, Object owner)
        throws SQLException {
        //Get the clob field we are interested in from the result set
        Clob clob = rs.getClob(names[0]);
 
        return ((clob == null) ? null : clob.getSubString(1, (int) clob.length()));
    }
 
    /**
     * oracleClasses independent (at compile time); based on http://forum.hibernate.org/viewtopic.php?p=2173150,
     * changes: changed line: Connection conn = ps.getConnection(); to: Connection conn = dbMetaData.getConnection();
     *
     * @see net.sf.hibernate.UserType#nullSafeSet(java.sql.PreparedStatement, java.lang.Object, int)
     */
    public void nullSafeSet(PreparedStatement ps, Object value, int index)
        throws SQLException, HibernateException {
        DatabaseMetaData dbMetaData = ps.getConnection().getMetaData();
        log.debug(dbMetaData.getDriverName());
        log.debug(dbMetaData.getDriverMajorVersion() + " " + dbMetaData.getDriverMinorVersion());
        log.debug(dbMetaData.getConnection().getClass().getName());
 
        if (value == null) {
            ps.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];
 
                    //changed from: Connection conn = ps.getConnection();
                    Connection conn = dbMetaData.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
                    ps.setClob(index, (Clob) tempClob);
 
                    LobCleanUpInterceptor.registerTempLobs(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);
            ps.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;
    }
    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);
    }
}
Interceptor:
import net.sf.hibernate.Interceptor;
import net.sf.hibernate.type.Type;
 
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
 
import java.io.Serializable;
 
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
 
import java.util.HashSet;
import java.util.Iterator;
import java.util.Set;
 
 
public class LobCleanUpInterceptor implements Interceptor {
    protected static Log log = LogFactory.getLog(LOBEntityInterceptor.class);
 
    // a thread local set to store temperary LOBs
    private static final ThreadLocal threadTempLobs = new ThreadLocal();
 
    public boolean onLoad(Object arg0, Serializable arg1, Object[] arg2, String[] arg3, Type[] arg4) {
        return false;
    }
    public boolean onFlushDirty(Object arg0, Serializable arg1, Object[] arg2, Object[] arg3, String[] arg4, Type[] arg5) {
        return false;
    }
    public boolean onSave(Object arg0, Serializable arg1, Object[] arg2, String[] arg3, Type[] arg4) {
        return false;
    }
    public void onDelete(Object arg0, Serializable arg1, Object[] arg2, String[] arg3, Type[] arg4) {}
    public void preFlush(Iterator arg0) {}
    public Boolean isUnsaved(Object arg0) {
        return null;
    }
    public int[] findDirty(Object arg0, Serializable arg1, Object[] arg2, Object[] arg3, String[] arg4, Type[] arg5) {
        return null;
    }
    public Object instantiate(Class arg0, Serializable arg1) {
        return null;
    }
 
    public void postFlush(Iterator arg0) {
        Set tempLobs = (Set) threadTempLobs.get();
 
        if (tempLobs == null) {
            return;
        }
 
        try {
            for (Iterator iter = tempLobs.iterator(); iter.hasNext();) {
                Object lob = iter.next();
                Method freeTemporary = lob.getClass().getMethod("freeTemporary", new Class[0]);
                freeTemporary.invoke(lob, new Object[0]);
 
                log.info("lob cleaned");
            }
        } catch (SecurityException e) {
            log.error("clean LOB failed: " + e.getMessage(), e);
            throw new RuntimeException(e);
        } catch (NoSuchMethodException e) {
            log.error("clean LOB failed: " + e.getMessage(), e);
            throw new RuntimeException(e);
        } catch (IllegalArgumentException e) {
            log.error("clean LOB failed: " + e.getMessage(), e);
            throw new RuntimeException(e);
        } catch (IllegalAccessException e) {
            log.error("clean LOB failed: " + e.getMessage(), e);
            throw new RuntimeException(e);
        } catch (InvocationTargetException e) {
            log.error("clean LOB failed: " + e.getMessage(), e);
            throw new RuntimeException(e);
        } finally {
            threadTempLobs.set(null);
            tempLobs.clear();
        }
    }
 
    // register oracle temperary BLOB/CLOB into
    // a thread-local set, this should be called at
    // the end of nullSafeSet(...) in BinaryBlobType
    // or StringClobType
    public static void registerTempLobs(Object lob) {
        getTempLobs().add(lob);
    }
 
    // lazy create temperary lob storage
    public static Set getTempLobs() {
        Set tempLobs = (Set) threadTempLobs.get();
 
        if (tempLobs == null) {
            tempLobs = new HashSet();
            threadTempLobs.set(tempLobs);
        }
 
        return tempLobs;
    }
}
things that you need to do (beside copy/paste):
  • if using Oracle - use Oracle's 9 (or grater) drivers
  • obtain session with interceptor
sessionFactory.openSession(new LobCleanUpInterceptor());
  • use it:
<property name="lobField" column="whatever" type="StringClobType"/>
Tested on Oracle 8i and 9i (Oracle 9 drivers; ojdbc14 9.2.0.5), HSQLDB 1.7.2, MySql 4.0 (Connector 3.0.15-ga).
Note for MySql users: - CLOB becomes TEXT and it can hold only up to 65k, if you need more add length="16777215" to your column mapping for MEDIUMTEXT or add more for LONGTEXT.

Another solution for Oracle 8i
- does not need an Interceptor, works with 8i JDBC drivers
By Eli Levine [elilevine _AT_ yahoo] October 18, 2004
This implementation requires two additional DB objects, a sequence and a temporary table, created as such:
-- create table
CREATE GLOBAL TEMPORARY TABLE TEMP_CLOB_TABLE (
 ID         NUMBER,
 TEMP_CLOB CLOB) ON COMMIT DELETE ROWS;
 
-- create sequence
CREATE SEQUENCE SEQ_TEMP_CLOB_ID INCREMENT BY 1 START WITH 0 MINVALUE 0 MAXVALUE 99999999 CYCLE NOCACHE NOORDER;
UserType implementation:
import java.io.IOException;
import java.io.Writer;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
 
import net.sf.hibernate.HibernateException;
import net.sf.hibernate.UserType;
import oracle.sql.CLOB;
 
public class StringClobTypeUsingTempTable implements UserType {
 
    /**
     * @return java.sql.Types.CLOB
     */
    public int[] sqlTypes() {
        return new int[] { Types.CLOB };
    }
 
    /**
     * @return java.lang.String.class
     */
    public Class returnedClass() {
        return String.class;
    }
   
    public boolean equals(Object x, Object y) throws HibernateException {
        return (x == y) || (x != null && y != null && (x.equals(y)));
    }
 
   
    /**
     * @see net.sf.hibernate.UserType#nullSafeGet(java.sql.ResultSet, java.lang.String[], java.lang.Object)
     */
    public Object nullSafeGet(ResultSet rs, String[] names, Object owner) throws HibernateException, SQLException {
        Clob clob = rs.getClob(names[0]);
        return clob.getSubString(1, (int) clob.length());
    }
 
    /**
     * @see net.sf.hibernate.UserType#nullSafeSet(java.sql.PreparedStatement, java.lang.Object, int)
     */
    public void nullSafeSet(PreparedStatement st, Object value, int index) throws HibernateException, SQLException {
       
        int tempClobId;
       
        Connection con = st.getConnection();
        Statement sta;
        ResultSet rs;
       
        String sql = "select seq_temp_clob_id.nextval from dual";
       
        sta = con.createStatement();
        rs = sta.executeQuery(sql);
        rs.next();
        tempClobId = rs.getInt(1);
       
        rs.close();
        sta.close();
       
        sta = con.createStatement();
        sql = "insert into temp_clob_table (id, temp_clob) values(" + tempClobId + ", empty_clob())";
        sta.executeUpdate(sql);
       
        sta.close();
       
        sta = con.createStatement();
        sql = "select temp_clob from temp_clob_table where id=" + tempClobId+ " for update";
        sta = con.createStatement();
        rs = sta.executeQuery(sql);
       
        rs.next();
           
        CLOB tempClob = (CLOB)rs.getClob(1);
           
        Writer tempClobWriter = tempClob.getCharacterOutputStream();
        try {
            tempClobWriter.write((String)value);
            tempClobWriter.flush();
            tempClobWriter.close();
        } catch (IOException ioe) {
            throw new HibernateException(ioe);
        }
       
        rs.close();
        sta.close();
       
        st.setClob(index, tempClob);
    }
 
    /**
     * @see net.sf.hibernate.UserType#deepCopy(java.lang.Object)
     */
    public Object deepCopy(Object value) throws HibernateException {
        if (value == null) return null;
        return new String((String) value);
    }
 
    /**
     * @return false
     * @see net.sf.hibernate.UserType#isMutable()
     */
    public boolean isMutable() {
        return false;
    }
 
}
 
No Interceptor is needed to clean up the temporary CLOB because it is created in the global temporary table and is cleared by the database on commit.

Dealing with BLOBs (Interceptor method)
User custom type ByteArrayBlobType
-23/02/2005.[sebastien.fabbri#at#gmail]
Additional information concerning usage of BLOBs in the same manner for Weblogic 8.1 and Oracle 9.2 using Spring 1.1.3 and Hibernate 2.1.7.
note: see [ http://saloon.javaranch.com/cgi-bin/ubb/ultimatebb.cgi?ubb=get_topic&f=78&t=000548 ] which would be a really cool if I was able to make it working ;(
Based on the solution of the UserType + Interceptor already mentionned that works great, here is the implementation of the ByteArrayBlobType to map to attribute byte[] in the DAO Object:
import net.sf.hibernate.HibernateException;
import net.sf.hibernate.UserType;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import java.io.IOException;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import net.sf.hibernate.HibernateException;
import net.sf.hibernate.UserType;
import oracle.sql.BLOB;
import org.apache.commons.lang.ObjectUtils;
import org.apache.log4j.Logger;
 
import weblogic.jdbc.extensions.WLConnection;
 
/**
 */
 
public class ByteArrayBlobType implements UserType {
 
    private Logger log = Logger.getLogger(getClass());
 
    /**
     * Return the SQL type codes for the columns mapped by this type.
     */
    public int[] sqlTypes() {
        return new int[] { Types.BLOB};
    }
 
    /**
     * The class returned by <tt>nullSafeGet()</tt>.
     */
    public Class returnedClass() {
        return String.class;
    }
    public boolean equals(Object x, Object y) {
        return ObjectUtils.equals(x, y);
    }
 
    /**
     * Retrieve an instance of the mapped class from a JDBC resultset.
Implementors
     * should handle possibility of null values.
     */
    public Object nullSafeGet(ResultSet rs, String[] names, Object owner)
        throws HibernateException, SQLException {
        InputStream blobReader = rs.getBinaryStream(names[0]);
        if (blobReader == null)
            return null;
        byte[] b = new byte[1024];
 
        ByteArrayOutputStream os = new ByteArrayOutputStream();
 
        try {
            while ((blobReader.read(b)) != -1)
                os.write(b);
        } catch (IOException e) {
            throw new SQLException(e.toString());
        } finally {
            try {
                os.close();
            } catch (IOException e) {
            }
        }
        return os.toByteArray();
    }
    /**
     * Write an instance of the mapped class to a prepared statement.
Implementors
     * should handle possibility of null values. A multi-column type should
be written
     * to parameters starting from <tt>index</tt>.
     *
     */
    public void nullSafeSet(PreparedStatement st, Object value, int index)
            throws HibernateException, SQLException {
       
        if (value == null) {
            st.setNull(index, sqlTypes()[0]);
            return;
        }
       
        try {
            Connection conn =
st.getConnection().getMetaData().getConnection();
           
            if (conn instanceof WLConnection)
                conn = ((WLConnection)conn).getVendorConnection();
            log.info(conn.getClass().getName());
           
            OutputStream tempBlobOutputStream = null;
            BLOB tempBlob = BLOB.createTemporary(conn, true,
BLOB.DURATION_SESSION);
            try {
                tempBlob.open(BLOB.MODE_READWRITE);
                tempBlobOutputStream = tempBlob.getBinaryOutputStream();
                tempBlobOutputStream.write((byte[])value);
                tempBlobOutputStream.flush();
            } finally {
                if (tempBlobOutputStream != null)
                    tempBlobOutputStream.close();
                tempBlobOutputStream.close();
            }
            st.setBlob(index, (Blob) tempBlob);
        } catch (IOException e) {
           throw new HibernateException(e);
        }
    }
    /**
     * Return a deep copy of the persistent state, stopping at entities and at
     * collections.
     */
    public Object deepCopy(Object value) {
        return (byte[])value;
    }
    /**
     * Are objects of this type mutable?
     */
    public boolean isMutable() {
        return false;
    }
}

Another solution for Oracle 9i
- does not need any change to the mapping
Just use this jdbc-wrapper and any setString/getString will automatically deal with clob

CLOBs larger than 4K characters in Oracle 8
If you use the Oracle OCI driver in stead of the JDBC Thin driver, you can use CLOBs upto 4GB if you want. You can download the drivers from Oracle.com. Do note that you need to install a native Oracle library (in other words, you need extended admin privileges on your server), but it works like a charm.
The URL for connecting using OCI is: jdbc:oracle:oci8:@ORCL where ORCL is the TNS name for your database on the servers TNS list.
Hope this adds some value ;)
 
0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:132935次
    • 积分:1844
    • 等级:
    • 排名:千里之外
    • 原创:44篇
    • 转载:17篇
    • 译文:0篇
    • 评论:5条
    最新评论