Problem mapping Oracle CLOB to ClobStringType with OracleLobHandler, Hibernate JPA

[pdotsenko]

Hi,

I am using Spring 2.5.5, Hibernate3 as the JPA provider. I need to map a domain object field to a CLOB Oracle field. The way I've done this before when using native Hibernate (not JPA) was with Spring's ClobStringType custom Hibernate type which worked great. Trying to replicate this with Hibernate JPA, I run into the problem that ClobStringType needs an OracleLobHandler from HibernateSessionFactory to do its work which I can't configure using EntityManagerFactory.

To get around the problem and still use the custom Hibernate user type, I subclassed the ClobStringType and added a static reference to OracleLobHandler, and it works in testing. I am worried however that this may lead to problems with thread safety. I also feel like there is a better way to do this, if anybody sees a problem with this approach or has any suggestions I would appreciate it. I realize I could use OracleLobHandler directly in a DAO but I would really like to use a custom Hibernate user type.

Thanks,
Paul Dotsenko

[mvadlamudi]

Dont use the "ClobStringType" just use following sample mapping

<hibernate-mapping>
<class name="com.kerio.ci.emailalert.dto.IncidentDTO"
table="CI_CUST_TICKETS">

<property name="ticketDetails1" type="java.sql.Clob">
<column name="TICKET_DETAIL" sql-type="CLOB"/>
</property>
</class>
</hibernate-mapping>

and also update the classes12.jar file.
try this and let me know

[mvadlamudi]

if you need more detils ,check the following thread.
http://forum.springsource.org/showthread.php?t=73737

[pdotsenko]

Hi mvadlamudi, thanks for the suggestion. I did end up going with a customized type that maps the clob to a string and it has worked fine so far. I am using Hibernate JPA and Oracle 11g jars though, not straight Hibernate.

[couzteau]

pdotsenko, can you share more details about your solution? I have the same problem, even though I use Oracle 10g. my hibernate configuration is done using annotations.

My questions:
-What annotation should I use in my persistent Java class.
-Which extra classes do I need to implement?

Thanks, help is much appreciated.

Jacques

[xiaofong]

Hello pdotsenko,

I saw your post here. I have the same scenario. I use JPA LocalContainerEntityManagerFactoryBean and not using HibernateSessionFactory directly either. I need to use OracleLobHandler to configure NativeJdbcExtractor for Geometry type of data. Could you please share with me how you handle it in your spring app context configuration?

Hi mvadlamudi, thanks for the suggestion. I did end up going with a customized type that maps the clob to a string and it has worked fine so far. I am using Hibernate JPA and Oracle 11g jars though, not straight Hibernate.

Thank you so much,

xiaofong

[pdotsenko]

Hi xiaofong,

I am not sure this will help you to map Geometry type fields, but to map Oracle CLOB() type fields to String, here is the user type class:

public class OracleClobStringType extends ClobStringType
{
    //Oracle clob handler
    private static OracleLobHandler oracleLobHandler = new OracleLobHandler();
    
    public static final String WEBSPHERE_CLASS_NAME="com.ibm.websphere.management.AdminServiceFactory";
    
    static
    {
        try
        {
            ClassUtils.forName(WEBSPHERE_CLASS_NAME);
            //we are running in Websphere
            oracleLobHandler.setNativeJdbcExtractor(new WebSphereNativeJdbcExtractor());
        }
        catch (ClassNotFoundException e)
        {
            //assume we are in Tomcat
            oracleLobHandler.setNativeJdbcExtractor(new CommonsDbcpNativeJdbcExtractor());
        }
    }

    public OracleClobStringType()
    {
        super(oracleLobHandler, LocalSessionFactoryBean.getConfigTimeTransactionManager());
    }
}

In the static block I initialize the app server-specific NativeJdbcExtractor, in this case the app server could be either Websphere or Tomcat. To map Java String field to Oracle clob column use this annotation:

    @org.hibernate.annotations.Type(type="org.xyz.OracleClobStringType")
    @Column(name = "COMMENT", nullable = true)
    public String getComment()
    {
        return this.comment;
    }

In order to support a Geometry type field you may need to implement org.hibernate.usertype.UserType from scratch and use Oracle-supplied jars to convert Geometry types to/from String.
Hope this helps.