Mapping a Clob to a String

Hibernate 1.2.3 has built-in support for clobs. Hibernate natively maps clob columns to java.sql.Clob. However, it's sometimes useful to read the whole clob into memory and deal with it as a String.

One approach for doing this to create a new UserType as follows.

I don't think this is the best implementation on some platforms - better to get/set character streams rather than get / set Clobs - GK

package mypackage;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.sql.Clob;

import cirrus.hibernate.Hibernate;
import cirrus.hibernate.HibernateException;
import cirrus.hibernate.UserType;

public class StringClobType implements UserType
{
    public int[] sqlTypes()
    {
        return new int[] { Types.CLOB };
    }

    public Class returnedClass()
    {
        return String.class;
    }

    public boolean equals(Object x, Object y)
    {
        return (x == y)
            || (x != null
                && y != null
                && (x.equals(y)));
    }

    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());
    }

    public void nullSafeSet(PreparedStatement st, Object value, int index)
        throws HibernateException, SQLException
    {
        st.setClob(index, Hibernate.createClob((String) value));
    }

    public Object deepCopy(Object value)
    {
        if (value == null) return null;
        return new String((String) value);
    }

    public boolean isMutable()
    {
        return false;
    }
}

The StringClobType will convert a clob into a String and back again.

Here's how to use it. First, define an entity that contains a String property:

public class TextValue
{
  private long id;
  private String byte;

  public long getId() { return id; }
  public void setId(long id) { this.id = id; }

  public String getText() { return text; }
  public void setText(String text) { this.text = text; }
}

Then map a clob column onto the String property:

<class name="TextValue" table="TEXT_VALUE">
  <id name="id/>
  <property name="text" column="TEXT" type="mypackage.StringClobType"/>
</class>

Notes:

1) Clobs aren't cachable. By converting the clob into a String, you can now cache the entity.

2) This approach reads the whole clob into memory at once.

3) The above type is known to work for reading clobs out of the db. Other usage patterns might also work.

4) See also Mapping a Blob to a byte[].

*****************************************************************************

Method 2

I could not get the above method to work.

This is based on the cirrus.hibernate.type.Clob source.

import java.io.BufferedReader;
import java.io.IOException;
import java.io.StringReader;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import org.apache.commons.lang.ObjectUtils;
import cirrus.hibernate.type.ImmutableType;

public class TextType extends ImmutableType {

  public Object get(ResultSet rs, String name) throws SQLException {
    String line;
    String str = "";
    BufferedReader b = new BufferedReader( rs.getCharacterStream( name ) );
      try {
        while( (line = b.readLine()) != null ) {
      str += line;
    }
      } catch (IOException e) {
    throw new SQLException( e.toString() );
    }
    return str;
  }

  public Class returnedClass() {
    return String.class;
  }

  public void set(PreparedStatement st, Object value, int index) 
    throws SQLException {
    StringReader r = new StringReader( (String)value );
    st.setCharacterStream( index, r, ((String)value).length() );
  }

  public int sqlType() {
    return Types.CLOB;
  }
    
  public String getName() { return "string"; }

  public boolean hasNiceEquals() {
    return false;
  }

  public boolean equals(Object x, Object y) {
    return ObjectUtils.equals(x, y);
  }

  public String toXML(Object value) {
    return (String) value;
  }
}

********************************

Method 2 does not account for a null clob nor does it append newline charaters.

I would suggest replacing the get method with this.

public Object get(ResultSet rs, String name) throws HibernateException, SQLException
    {
        Reader reader = rs.getCharacterStream(name);
        if (reader == null)
        {
            return null;
        }
        StringBuffer sb = new StringBuffer();
        try
        {
            char[] charbuf = new char[4096];
            for (int i = reader.read(charbuf); i > 0; i = reader.read(charbuf))
            {
                sb.append(charbuf, 0, i);
            }
        }
        catch (IOException e)
        {
            throw new SQLException( e.getMessage() );
        }
        return sb.toString();
    }

NEW COMMENT

Is that worked with strings>4k.	08 Jan 2004, 07:23	udoo
I'm using oracle 9.0.1, and ojdbc14/thin, but I can't insert strings > 4k, with such exception: java.sql.SQLException: No more data to read from socket at oracle.jdbc.dbaccess.DBError.throwSqlException (DBError.java:134) at oracle.jdbc.dbaccess.DBError.throwSqlException (DBError.java:179) at oracle.jdbc.dbaccess.DBError.check_error(DBError.java:1160) at oracle.jdbc.ttc7.MAREngine.unmarshalUB1(MAREngine.java:963) at oracle.jdbc.ttc7.MAREngine.unmarshalSB1(MAREngine.java:893) at oracle.jdbc.ttc7.Oclose.receive(Oclose.java:101) at oracle.jdbc.ttc7.TTC7Protocol.close(TTC7Protocol.java:683) at oracle.jdbc.driver.OracleStatement.close (OracleStatement.java:644) at oracle.jdbc.driver.OraclePreparedStatement.privateClose (OraclePreparedStatement.java:485) at oracle.jdbc.driver.OraclePreparedStatement.close (OraclePreparedStatement.java:393) at net.sf.hibernate.impl.BatcherImpl.closePreparedStatement (BatcherImpl.java:246) at net.sf.hibernate.impl.BatcherImpl.closeStatement (BatcherImpl.java:132) at net.sf.hibernate.impl.BatcherImpl.abortBatch (BatcherImpl.java:74) at net.sf.hibernate.persister.EntityPersister.insert (EntityPersister.java:519) at net.sf.hibernate.persister.EntityPersister.insert (EntityPersister.java:489) at net.sf.hibernate.impl.ScheduledInsertion.execute (ScheduledInsertion.java:28) at net.sf.hibernate.impl.SessionImpl.executeAll (SessionImpl.java:2303) at net.sf.hibernate.impl.SessionImpl.execute (SessionImpl.java:2256) at net.sf.hibernate.impl.SessionImpl.flush (SessionImpl.java:2182) any ideas?

Current Implementations of This?	30 Jun 2004, 20:47	diathesis
I had been hoping that these methods would get around my ahving to use the Clob directly, and to keep me away from using Oracle-specific code. Method 1 didn't get me past the 4k character limit. Something like 'too much data for this datatype (4000)' (I didn't record the error). Method 2 needed extra implementation to work with the current version of Hibernate (fromStringValue(), toString(), getReturnedClass()), which I implemented in the way that seemed best -- there's not a lot of documentation on the nature of these methods. That got me as far as getting 'No more data to read from socket.' Disabling JDBC batches got me to 'No more data to read from socket.' All the references to that error point me to the more complicated Oracle/Clob handling routines, so I'm going to have to go that way for the time being, and then circle back into figuring out what's necessary to support a direct string. If anyone has additional pointers, I'd be happy to hear 'em.

Re: Current Implementations of This?	01 Jul 2004, 11:00	130g
It seems that with the current UserType framework of Hibernate it is not possible to "correctly" implement the clob handling algorithms from Oracle. Based on http://download- west.oracle.com/docs/cd/B10501_01/java.920/a96654/oralob.htm#1043220 it seems that to write a clob you need to select the clob handler first, and then modify the clob handler. As far as I can see this is not possible to do in the set() method of af user type, as the inputs are a prepared statement to which we should bind the value to be inserted. Or is it just me being daft? :)

Null value should be checked in the returned clob	27 Aug 2004, 22:38	barryku
I was getting NullPointer exception with null clob, and the following is my fix, <code> public Object nullSafeGet(ResultSet rs, String[] names, Object owner) throws HibernateException, SQLException { Clob clob = rs.getClob(names[0]); return (clob==null? null :clob.getSubString(1, (int) clob.length ())); } </code>

How can member variable in class automatically mapped to String	06 Oct 2004, 07:45	garpinc
How can member variable in class automatically mapped to String with hbm2class even though type is user type. (whatever it's called, I use hibernate synchronzer)

FYI: Found answer about mapping	06 Oct 2004, 07:52	garpinc
With hibernate synchronizer I use <meta attribute="property-type">String</meta>

Has anyone found a UserType solution that works with 8i	11 Oct 2004, 18:38	garpinc
Has anyone found a UserType solution that works with 8i? I'm getting a ClassCastException when using the recomended solution.

re: UserType solution that works with 8i	14 Oct 2004, 21:53	Lukasz (Qr)
"UserType solution that works with 8i" - see the "Updated Clobs handling for Oracle and Hibernate": http://www.hibernate.org/56.html

method 1 and mysql..	02 Dec 2004, 09:38	hooverphonique
I can confirm that the first method (using Clob#getSubString) does NOT work for MySQL, since the Hibernate ClobImpl class does not allow extraction of data using getSubString, which is exactly the method MySQL uses to get to the character data (which actually causes the 2nd method to bomb as well, since the stream methods are not used by the MySQL JDBC driver (v 3.0.14) in this case).

Re: method 1 and mysql..	02 Dec 2004, 09:57	hooverphonique
On 02 Dec 2004 09:38, hooverphonique wrote: >I can confirm that the first method (using Clob#getSubString) does NOT >work for MySQL, since the Hibernate ClobImpl class does not allow >extraction of data using getSubString, which is exactly the method >MySQL uses to get to the character data Using method 1 (UserType implementation) with the following code in place of nullSafeSet and nullSafeGet works for me for both reading and writing CLOBs.. public Object nullSafeGet(ResultSet rs, String[] names, Object owner) throws HibernateException, SQLException { Reader reader = rs.getCharacterStream(names[0]); if (reader == null) return null; StringBuffer sb = new StringBuffer(); try { char[] charbuf = new char[4096]; for (int i = reader.read(charbuf); i > 0; i = reader.read(charbuf)) { sb.append(charbuf, 0, i); } } catch (IOException e) { throw new SQLException( e.getMessage() ); } return sb.toString(); } public void nullSafeSet(PreparedStatement st, Object value, int index) throws HibernateException, SQLException { if (value != null) { StringReader r = new StringReader( (String) value ); st.setCharacterStream( index, r, ((String) value).length() ); } else { st.setNull(index, sqlTypes()[0]); } }

Re: method 1 and mysql..	10 Aug 2005, 22:41	monoxxx
POST QUESTIONS ON THE FORUM! COMMENTS HERE SHOULD ADD VALUE TO THE PAGE!On 02 Dec 2004 09:57, hooverphonique wrote: >On 02 Dec 2004 09:38, hooverphonique wrote: >>I can confirm that the first method (using Clob#getSubString) does >NOT >>work for MySQL, since the Hibernate ClobImpl class does not allow >>extraction of data using getSubString, which is exactly the method >>MySQL uses to get to the character data >Using method 1 (UserType implementation) with the following code in >place of nullSafeSet and nullSafeGet works for me for both reading and >writing CLOBs.. > public Object nullSafeGet(ResultSet rs, String[] names, >Object owner) throws HibernateException, SQLException { > Reader reader = rs.getCharacterStream(names[0]); > if (reader == null) return null; > StringBuffer sb = new StringBuffer(); > try { > char[] charbuf = new char[4096]; > for (int i = reader.read(charbuf); i > 0; i >= reader.read(charbuf)) { > sb.append(charbuf, 0, i); > } > } > catch (IOException e) { > throw new SQLException( e.getMessage () ); > } > return sb.toString(); > } > public void nullSafeSet(PreparedStatement st, Object value, >int index) throws HibernateException, SQLException { > if (value != null) { > StringReader r = new StringReader( (String) >value ); > st.setCharacterStream( index, r, ((String) >value).length() ); > } else { > st.setNull(index, sqlTypes()[0]); > } > } 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()); } public void nullSafeSet(PreparedStatement st, Object value, int index) throws HibernateException, SQLException { st.setClob(index, Hibernate.createClob((String) value)); } Above two method I can't insert the data into Oracle 9i database; But When I change below this two method(Use characterStream to char[]) It worked !! That's amazing !! Great Thank You hooverphonique public Object nullSafeGet(ResultSet rs, String[] names, Object owner) throws HibernateException, SQLException { Reader reader = rs.getCharacterStream(names[0]); if (reader == null) return null; StringBuffer sb = new StringBuffer(); try { char[] charbuf = new char[4096]; for (int i = reader.read(charbuf); i > 0; i = reader.read(charbuf)) { sb.append(charbuf, 0, i); } } catch (IOException e) { throw new SQLException( e.getMessage () ); } return sb.toString(); } public void nullSafeSet(PreparedStatement st, Object value, int index) throws HibernateException, SQLException { if (value != null) { StringReader r = new StringReader( (String) value ); st.setCharacterStream( index, r, ((String) value).length() ); } else { st.setNull(index, sqlTypes()[0]); } }

Shouldn't this get removed?	13 Apr 2006, 12:39	koehn
Now that Hibernate supports the "text" type natively, shouldn't this be noted at the very top of this article?

This works both for reading or writing with Informix 10:	16 May 2008, 04:48	ivaylodd
========================================================================= public class StringClobType implements UserType { private static final int READ_BUFFER_SIZE = 4096; public Object assemble(Serializable cached, Object owner) { return null; } public Object deepCopy(Object value) { if (value == null) { return null; } return new String((String) value); } public Serializable disassemble(Object value) { return null; } public boolean equals(Object x, Object y) { return (x == y) \|\| ((x != null) && (y != null) && (x.equals(y))); } public int hashCode(Object x) { return x.hashCode(); } public boolean isMutable() { return false; } public Object nullSafeGet(ResultSet rs, String[] names, Object owner) throws SQLException { Clob clob = rs.getClob(names[0]); Reader reader = clob.getCharacterStream(); if (reader == null) { return null; } StringBuffer sb = new StringBuffer(); try { char[] charbuf = new char[READ_BUFFER_SIZE]; for (int i = reader.read(charbuf); i > 0; i = reader.read(charbuf)) { sb.append(charbuf, 0, i); } } catch (IOException e) { throw new SQLException(e.getMessage()); } return sb.toString(); } public void nullSafeSet(PreparedStatement st, Object value, int index) throws SQLException { if (value != null) { StringReader r = new StringReader((String) value); st.setCharacterStream(index, r, ((String) value).length()); } else { st.setNull(index, sqlTypes()[0]); } } public Object replace(Object original, Object target, Object owner) { return null; } public Class returnedClass() { return String.class; } public int[] sqlTypes() { return new int[] { Types.CLOB }; } ========================================================================= Note that in the nullSafeGet() method, the following code: Reader reader = rs.getCharacterStream(names[0]); doesn't work with Informix and throws an SQLException: java.sql.SQLException: Can't convert tonull Instead, I changed it to: Clob clob = rs.getClob(names[0]); Reader reader = clob.getCharacterStream(); and now it works.