|
I think the problem with Hibernate and Oracle CLOBs is well documented and has caused problems for many people including me. I have read all the postings and other advice in a search for a solution that is elegant and wel encapsulated. The nearest effort is to be found on the Design Patterns page and involves the construction of a CustomType for mapping strings to CLOBS and involves the creation of an Oracle temp CLOB for populating the clob field. This solution was almost perfect except for the fact that it is not possible to free the temp Clob in any convenient way and will cause Oracle to leak resources. In order to address this issue I wrote a class called a TempClobReleaser that has a registerClob static method on it that you must call from your custom type whenever a new temp Clob is created. A thread runs in this class and periodically frees all registered temp clobs at certain time intervals and after the clob has lived in the registry for a certain period of time. Its kind if like a garbage collector I guess. The registry is implemented as a Set and the class has only static methods. This solution clearly is not perfect but does a half decent job of addressing a nasty problem (Oracle's fault not Hibernate's I hasten to add). Here is the code in case it is of any help to anyone. I have hardly tested this so I am not offering any guarantees I just thought it would be better to share an idea like this just in case it helps:
NOTES
PLease ignore the logging its internal proprietary stuff
Sorry for bad formatting I just pasted this in. I am sure you can deal with it
/**
* $RCSfile: $
*
*
* Created : 21-May-2004
* Company :
* Title : Temporary CLOB releaser
*
* -------------------------------------------------------------------------
* Modification History
* -------------------------------------------------------------------------
*
* $Revision: $
* $Author: $
* $Date: $
* $History: $
* $Log: $
*
* --------------------------------------------------------------------------
*/
package ixarc.domain.hibernate.oracle;
import ixp.utils.*;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.Date;
import java.util.HashSet;
import java.util.Set;
import oracle.sql.CLOB;
/**
* This class is used by the oracle based clob to string converter for hibernate ORM framework provided in the
* ix toolkit for reading and writing story body data. The type converter is used to get around the shortfalls in the
* Oracle JDBC driver that does not correctly implement JDBC streaming semantics for clob types for data over 4k.
* To get around the issue the type converter (StringClobType) must create a temparory oracle CLOB and stream the
* string data into it before assigning it to the prepared statement. Unfortunately hibernate in its current version
* does not provide an event hook that can execute a procedure when the statement completes and so the tempoary
* clob is never released. For any given Connection (Oracle session) these temporary CLOBS will accululate and use up
* TEMP tablespace. This class is a registry of temp clobs. Each temp clob created in eh converter is registered in this
* class and this class will clean them up at certain intervals. This solution is not a pretty one and not failsafe but
* until eitehr Oracle sort their act out or hibernate supports an event model this is the only acceptable way fo now.
* @author Paul Branscombe
* @version $Revision: $
*/
public class TempClobReleaser
{
/**
* This class represents a CLOB registry entry
* @author Paul Branscombe
* @version $Revision: $
*/
private static class ClobEntry
{
private CLOB m_clob;
private Date m_timestamp;
public void setCLOB(CLOB clob)
{
this.m_clob = clob;
this.m_timestamp = new Date();
}
public CLOB getCLOB()
{
return this.m_clob;
}
public Date getTimeStamp()
{
return this.m_timestamp;
}
}
/**
* This inner class is a thread that checks CLOB entries to see if they have expired. If they have the CLOB
* is freed and the entry removed from the registry.
* @author Paul Branscombe
* @version $Revision: $
*/
private static class ReleaserThread extends Thread
{
private boolean m_stopped = false;
public void Stop()
{
this.m_stopped = true;
}
public void run()
{
while (!this.m_stopped)
{
// Add the enqueued additions to the main registry
TempClobReleaser.m_clobs.addAll(TempClobReleaser.m_enqueuedClobs);
synchronized(TempClobReleaser.m_enqueuedClobs)
{
// Clear the enqueued items now that they have been added to the main registry
TempClobReleaser.m_enqueuedClobs.clear();
}
for (Iterator ii = TempClobReleaser.m_clobs.iterator(); ii.hasNext();)
{
// Get the next entry
ClobEntry entry = (ClobEntry)ii.next();
// Is the entry older than the time to live lifespan. If so then free the clob
// and remove it from the registry
long timeNow = new Date().getTime();
long entryTime = entry.getTimeStamp().getTime();
long differenceInSeconds = (timeNow - entryTime)/1000;
if (differenceInSeconds >= TempClobReleaser.m_lifespan)
{
CLOB clob = entry.getCLOB();
try
{
CLOB.freeTemporary(clob);
TempClobReleaser.m_clobs.remove(entry);
TempClobReleaser.m_logger.LogEvent(0, "Clob Releaser", ILogger.LOG_ERROR, "Clob freed");
}
catch (SQLException e)
{
TempClobReleaser.m_logger.LogEvent(0, "Clob Releaser", ILogger.LOG_ERROR, "Clob free failed: " + e.toString());
}
}
}
try
{
// Wait a bit before starting again
Thread.sleep(TempClobReleaser.m_checkInterval * 1000);
}
catch (InterruptedException e)
{
TempClobReleaser.m_logger.LogEvent(0, "Clob Releaser", ILogger.LOG_ERROR, "Releaser thread interrupted: " + e.toString());
}
}
}
}
/** Releaser checking thread */
private static ReleaserThread m_releaserThread;
/** Application logger instance */
private static ILogger m_logger = new NullLogger();
/** Set of temp clobs current awaiting release */
private static Set m_clobs = new HashSet();
/** Collection for enqueing added clobs whilst release operations are taking place */
private static Set m_enqueuedClobs = new HashSet();
/** CLOB time to live in registry */
private static int m_lifespan = 30;
/** The interval in seconds between checking for expired CLOBs */
private static long m_checkInterval = 30;
/**
* Sets the amount of time that the clob can be in the registry before it is freed
* @param seconds - The time span in seconds
*/
private static void setClobLifeSpan(int seconds)
{
TempClobReleaser.m_lifespan = seconds;
}
/**
* Sets the amount of time that the clob can be in the registry before it is freed
* @param seconds - The time span in seconds
*/
private static void setCheckInterval(int seconds)
{
TempClobReleaser.m_checkInterval = seconds;
}
/**
* Starts the releaser thread running that frees the registered clobs
*/
public static void Start()
{
m_releaserThread = new ReleaserThread();
m_releaserThread.start();
}
/**
* Stops the releaser thread
*/
public static void Stop()
{
m_releaserThread.Stop();
try
{
m_releaserThread.join();
m_releaserThread = null;
}
catch (InterruptedException e)
{
TempClobReleaser.m_logger.LogEvent(0, "Clob Releaser", ILogger.LOG_ERROR, "Thread join error: " + e.toString());
}
}
/**
* registers a temp CLOB in use as awaiting release
* @param clob - The temp CLOB
*/
public static void registerTempCLOB(CLOB clob)
{
// Create a new entry instance to represent the clob being added
ClobEntry entry = new ClobEntry();
entry.setCLOB(clob);
synchronized(TempClobReleaser.m_enqueuedClobs)
{
TempClobReleaser.m_enqueuedClobs.add(entry);
}
}
}
Code that uses the class:
This is my implementation of the String to CLOB custom type that works with connection pooling that produces proxies that would normally prevent access to the underlying Oracle connection object. As I have posted before, the metadata seems to allow access in these cases and I have been able to continues using C3P0 connection pooling. The is modified from a sample taken from this site so most credit goes to the original author. I have removed the refection stuff to make the code more readable and maintainable which is fine where an Oracle build time dependancy is not a problem.
/**
* $RCSfile: StringClobType.java $
*
* All Rights Reserved.
*
* Created : 30-April-2004
* Company :
* Title : Clob to String Type Hibernate Converter
*
* -------------------------------------------------------------------------
* Modification History
* -------------------------------------------------------------------------
*
* $Revision: $
* $Author: $
* $Date: $
* $History: $
* $Log: $
*
* --------------------------------------------------------------------------
*/
package ixarc.domain.hibernate.oracle;
import oracle.sql.CLOB;
import java.io.*;
import java.lang.reflect.*;
import java.sql.*;
import org.apache.commons.lang.*;
import net.sf.hibernate.*;
/**
* Based on community area design patterns on Hibernate site.
* Maps java.sql.Clob to a String special casing for Oracle drivers.
*
* This class is used in teh Hibernate ORM environment to map entity based String type properties
* to Oracle CLOB type columns and to hide the implementation from the domain entity classes. This
* implementation works specifically with Oracle 9i and can be used in a hibernate mapping only when
* this is the target database. For other target databases a different UserType implementation should
* be specified. This implementation differs from the one from the community site in that I have decided
* not to used reflection to get instances of the Oracle classes and invoke methods on them just to
* prevent needed the Oracle driver at build time. This class should be built in an implementation
* specific project away from the main data access framework. This make the code much easier to read.
* Also have added comments javadocs and refactored code to be more readable and intuitive
* @author Ali Ibrahim, Scott Miller, Paul Branscombe
* @version $Revision: $
*/
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;
/**
* Return the SQL type of the column that this instance is converting to a java String
* @return - The CLOB type as the only array element
*/
public int[] sqlTypes()
{
return new int[] {Types.CLOB};
}
/**
* The class of object that we this class will return. The class of object we are converting to in other
* words.
* @return The String class
*/
public Class returnedClass()
{
return String.class;
}
/**
* Compare two instances of the class mapped by this type for persistence "equality".
* @param x
* @param y
* @return - true if both object values are the same
*/
public boolean equals(Object x, Object y)
{
return ObjectUtils.equals(x, y);
}
/**
* Gets the string value of the Clob column. For argument details see Hibernate Javadocs
* @param rs - The ResultSet containing the row that contains the column to be converted
* @param names - The name of the column to be converted
* @param owner - See Hibernate documentation
* @return - String containing the CLOB contents
* @throws HibernateException
* @throws SQLException
*/
public Object nullSafeGet(ResultSet rs, String[] names, Object owner) throws HibernateException, SQLException
{
String columnName = names[0];
// Get the clob field we are interested in from the result set
Clob textClob = rs.getClob(columnName);
String clobText = textClob.getSubString(1, (int)textClob.length());
return clobText;
}
/**
* Sets the contents of the specified CLOB column using the value provided. This should be a java String
* @param st - The JDBC PreparedStatement performing the update or insert
* @param value - The String value to set in the CLOB column
* @param index - The index of the value param in the prepared statement
* @throws HibernateException
* @throws SQLException
*/
public void nullSafeSet(PreparedStatement st, Object value, int index) throws HibernateException, SQLException
{
// Here we just want to make a check to make sure that we have the right version of the JDBC driver for
// Oracle. Versions older the 9.x will not work this way
DatabaseMetaData dbMetaData = st.getConnection().getMetaData();
// If the inserted value is null juct set the paramater to null and we are done.
if (value == null)
{
st.setNull(index, sqlTypes()[0]);
}
// If the driver reported by the JDBC driver is the one we need then we can continue
else if (ORACLE_DRIVER_NAME.equals(dbMetaData.getDriverName()))
{
// If we have a driver that has teh same or later version than the one required then we can
// continue
if ( (dbMetaData.getDriverMajorVersion() >= ORACLE_DRIVER_MAJOR_VERSION) && (dbMetaData.getDriverMinorVersion() >= ORACLE_DRIVER_MINOR_VERSION))
{
try
{
// Get the connection in use and make sure it is an OracleConnection
Connection conn = dbMetaData.getConnection();
if (!(conn instanceof oracle.jdbc.driver.OracleConnection))
{
throw new HibernateException("JDBC connection object must be a oracle.jdbc.driver.OracleConnection. " +
"Connection class is " + conn.getClass().getName());
}
// Create a temporary CLOB using the Oracle CLOB class
CLOB tempClob = CLOB.createTemporary(conn, true, CLOB.DURATION_SESSION);
// Add the temp clob to the clob releaser registry so that it will be freed
TempClobReleaser.registerTempCLOB(tempClob);
// Open the CLOB for read write
tempClob.open(CLOB.MODE_READWRITE);
// Get the character output stream so tha we can write our string to it
Writer tempClobWriter = tempClob.getCharacterOutputStream();
// write the string to the clob
tempClobWriter.write( (String) value);
tempClobWriter.flush();
tempClobWriter.close();
// get the close method
tempClob.close();
// add the clob to the statement
st.setClob(index, (Clob)tempClob);
}
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;
}
}
Hope this helps until we get a hibernate event model and/or Oracle sort their lives out.
Cheers
Paul Branscombe
|
Login
FAQ
Search
