REVERT TO CONSOLE

The easiest way to map a CLOB type of Oracle to a pojo in Hibernate, is to setup a user defined type in Hibernate, which converts Oracle’s ClobType to a String and vice versa. So you’re pojo’s property is going to be a “String” type. There are only two steps to setting this up:

1) Write the Clobtype User defined type

import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.Serializable;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;

public class ClobType implements UserType {

	public int[] sqlTypes() {
		return new int[] { Types.CLOB };
	}

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

	public boolean equals(Object arg0, Object arg1) throws HibernateException {
		boolean ret = false;
		if (arg0 == null || arg1 == null) {
			ret = false;
		} else if (!(arg0 instanceof String) || !(arg1 instanceof String)) {
			ret = false;
		} else {
			ret = ((String) arg0).equals((String) arg1);
		}
		return ret;
	}

	public int hashCode(Object arg0) throws HibernateException {
		return arg0.hashCode();
	}

	public Object nullSafeGet(ResultSet arg0, String[] arg1, Object arg2)
			throws HibernateException, SQLException {

		String ret = null;
		StringBuffer buffer = new StringBuffer();
		try {
			// First we get the stream
			InputStream is = arg0.getAsciiStream(arg1[0]);
			byte[] buf = new byte[1024];
			int read = -1;

			while (is != null && (read = is.read(buf)) > 0) {
				buffer.append(new String(buf, 0, read));
			}
			if (is != null) {
				is.close();
			}
		} catch (IOException ioe) {
			ioe.printStackTrace();
			throw new HibernateException("Unable to read from resultset", ioe);
		}
		ret = buffer.toString();
		return ret;
	}

	public void nullSafeSet(PreparedStatement pst, Object data, int index)
			throws HibernateException, SQLException {
		data = data == null ? new String() : data;
		String in = (String) data;

		byte[] buf = in.getBytes();
		int len = buf.length;

		ByteArrayInputStream bais = new ByteArrayInputStream(buf);

		pst.setAsciiStream(index, bais, len);

	}

	public Object deepCopy(Object arg0) throws HibernateException {
		String ret = null;
		arg0 = arg0 == null ? new String() : arg0;
		String in = (String) arg0;
		int len = in.length();
		char[] buf = new char[len];

		for (int i = 0; i < len; i++) {
			buf[i] = in.charAt(i);
		}
		ret = new String(buf);
		return ret;
	}

	public boolean isMutable() {
		return false;
	}

	public Serializable disassemble(Object arg0) throws HibernateException {
		return (String) arg0;
	}

	public Object assemble(Serializable arg0, Object arg1)
			throws HibernateException {
		return this.deepCopy(arg0);
	}

	public Object replace(Object arg0, Object arg1, Object arg2)
			throws HibernateException {
		return this.deepCopy(arg0);
	}

}

2) Hibernate mapping: Update the type in your hbm file or annotations accordingly. The example here maps a property “label” to oracle column “label”

<property name="label" type="com.cramer.ate.utility.ClobType"
     update="true" insert="true" access="property" column="Label" />

That’s it. Since there is no dependency to anything else, it should work in a Spring environment too.

Note to Oracle 9i users: You might get the following exception:
java.sql.SQLException: operation not allowed: streams type cannot be used in batching

Solution? Use use 10.1.x drivers. Oracle 9i drivers have a known issue regarding the same, which they only fixed in 10.x. It’s believed that Oracle’s 10.x drivers are downward compatible with Oracle 9i (unlike 11.x drivers).