It's CLOBbering time

Sometimes in your database life, VARCHAR2(4000) just wont cut the mustard. And as I discovered recently, that's as big as Oracle VARCHARs will go. It's time to break out the CLOBs...

CLOBs stand for... well, Character LOBs. I'm afraid LOB doesn't mean anything (so says Jim Starkey, and he invented the term). But some people call them Large objects, others say Locator OBjects as they work like pointers. This also makes dropping a CLOB in for a VARCHAR less than straight forward, as they work from this pointer based access mechanism. Like SAX and DOM with XML parsing, it's two different philosophy's on large data access.

But I am a modern day Spring + Hibernate Java guy, so straight away I look to Hibernate. This offers support for mapping java.sql.Clob or java.sql.Blob objects into the database. But I'm not too keen to add them into my domain model and tie it to JDBC. It might also wreak havoc with my unit tests, as you can't access LOB properties without a database connection.

So onward up the stack to Spring and it's OracleLobHandler which is an Oracle specific version of the more generic DefaultLobHandler class, which is an:

Abstraction for handling large binary fields and large text fields in
specific databases, no matter if represented as simple types or Large OBjects (from the LobHandler javadoc).

Figuring out the best way to use this object came from Mark Allen's Weblog, which suggests simply adding the LobHandler to the Spring SessionFactory, then use a Spring support type for CLOBs, such as ClobStringType, in the Hibernate mapping file. Simple !

So firstly I add the following in my datasources Spring config file:

<bean id="sessionFactory" name="sessionFactory"
 class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
 ...
 <property name="lobHandler">
     <bean class="org.springframework.jdbc.support.lob.OracleLobHandler">
         <property name="nativeJdbcExtractor">
             <bean class="org.spring...CommonsDbcpNativeJdbcExtractor" />
         </property>
     </bean>
 </property>
</bean>

Then I tweak the property in my Hibernate mapping to use the Spring support class:

<property name="message" column="MESSAGE" lazy="true"
 type="org.springframework.orm.hibernate3.support.ClobStringType" />

And that pretty much does the magic. I change the column from VARCHAR2(4000) to CLOB in the database, and then the above code seemlessly turns the new CLOBs back into Java Strings as I pull them back, requiring no code changes at all. Hibernate can be a pain at times, but as always, adding Spring to the mix makes a change like this very smooth. Once you know how, of course ;)