Support Products Partners JBoss Labs

   Member Menu
· List Directory
· Document history
· Print page
· Edit
· WikiWord Link
 
 Monthly JBoss newsletter:
 
Hibernate Books
CaveatEmptor

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.
 
© Copyright 2006, Red Hat Middleware, LLC. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc. [Privacy Policy]