clob to string mapping support in Hibernate

stanlevin · **Joined:** Tue Sep 20, 2005 5:14 pm **Posts:** 8

Hi,

We are using Hibernate v3.0.1 with Oracale 9.2.06.
We have a User Permissions table with a Clob field :PERMISSIONS_CLOB" to hold user permissions data. We are reading the table into a class with "permissionsClobString" field defined as a String.

The reading fails with a NullPointerException,
however we can persist the class back to the database!!!

It seems that Hibernate does not do automatic type conversion from Clob to String when loading database data, but does it when persisting an instance back to the database. The problem is solved when using the Custom Value type: StringToClobe as the type for the "permissionsClobString" column.

We thought that Hibernate v.3 should map basic value types automatically.
Is there anything mssing from our configuration or is the StringToClob mapping class really required to do the mapping?

Thank you,
Stan.

Hibernate version:

We are using Hibernate-Version: 3.0.1

Mapping documents:

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping>
<class
name="com.csfb.fao.clr.security.test.UserSecurityNetPermissions"
table="Clr_User_Permissions"
lazy="false" >

<id
name="userId"
column="USER_ID"
type="java.lang.String">

<generator class="assigned">
</generator>
</id>

<property
name="permissionsClobString"
type="string"
update="true"
insert="true" >

<column name="PERMISSIONS_CLOB" sql-type="clob" />
</property>
</class>
</hibernate-mapping>
Code between sessionFactory.openSession() and session.close():
The sessions is managed by the Spring framework.

Full stack trace of any exception that occurs:

Caused by: java.lang.NullPointerException
at com.csfb.fao.clr.security.test.UserSecurityNetPermissions.setPermissionsClobString(UserSecurityNetPermissions.java:44)
... 30 more
Name and version of the database you are using:

"Oracle JDBC Driver version - 9.0.2.0.0"

The generated SQL (show_sql=true):

Hibernate: select USER_ID as USER1_0_,
usersecuri0_.PERMISSIONS_CLOB as PERMISSI2_0_0_
from Clr_User_Permissions usersecuri0_
where usersecuri0_.USER_ID=?

Debug level Hibernate log excerpt:

grafpoo · **Joined:** Fri Oct 15, 2004 4:54 pm **Posts:** 32

i am not having any issues persisting or reading clobs (oracle 9). my mapping is very basic for the clob field, i don't even specify the sql-type:

Quote:

and the DDL:

Quote:

create table INSTRUCTION (
INSTRUCTION_GUID char(32) not null,
NAME varchar2(32) not null,
TITLE varchar2(250) not null,
INSTRUCTION clob not null,
...

stanlevin · **Joined:** Tue Sep 20, 2005 5:14 pm **Posts:** 8

Hi,

Thank you for reply. Tried your basic mapping. Still getting null.
What verison of Hibernate do you use?

Could it be that the usage of Spring makes a difference?

Our new mapping looks like this:

Code:

        <property name="permissions_clob"
            type="java.lang.String"
            column="PERMISSIONS_CLOB" not-null="true"/> 

table is:

Code:

CREATE TABLE CLR.CLR_USER_PERMISSIONS 
(
    USER_ID            VARCHAR2(16) NOT NULL,
    PERMISSIONS_CLOB   CLOB         NOT NULL,
    USER_ADD_ID        VARCHAR2(16) NOT NULL,
    STAMP_ADD_DTIME    DATE         NOT NULL,
    USER_UPDATE_ID     VARCHAR2(16) NOT NULL,
    STAMP_UPDATE_DTIME DATE         NOT NULL
)

Thank you,
Stan.

CWitherow · **Posted:** Wed Jan 18, 2006 12:49 pm

I've haven't done this on Oracle but with DB2. My mapping uses: <property column="args" name="args" type="text"> for the clob object. The entire clob is read and placed into the "args" property as a String -- don't try to access it as a clob object.

Curtis ...

stanlevin · **Joined:** Tue Sep 20, 2005 5:14 pm **Posts:** 8

Thanks a lot.

The mapping type for CLOB really should be text.
How did we miss it?

The following works fine:

Code:

    <property
            name="permissionsClobString"
            [b]type="text"[/b]
            update="true"
            insert="true"
        >
            <column
                name="PERMISSIONS_CLOB" 
                sql-type="clob"
            />
        </property>

Regards,
Stan.

grafpoo · **Joined:** Fri Oct 15, 2004 4:54 pm **Posts:** 32

i am eating crow now because my simple solution doesn't work. to wit, i just got the following exception:

Quote:

20:20:57,095 WARN [JDBCExceptionReporter] SQL Error: 17157, SQLState: null
20:20:57,095 ERROR [JDBCExceptionReporter] setString can only process strings of less than 32766 chararacters
20:20:57,095 ERROR [AbstractFlushingEventListener] Could not synchronize database state with session

so i will be trying yours. thanks