String LOBs on PostgreSQL with Hibernate 3.6

Thursday, February 24, 2011 12:35:05 AM

For String properties that may contain more than 255 characters, it is advised to add a @Lob annotation to the appropriate property. For example, to keep a comment text in an entity, one would write:

@Lob
public String getComment() { return comment; }
public void setComment(String comment) { this.comment = comment; }

On PostgreSQL, a large string is usually kept in a TEXT column (instead of VARCHAR). However, after updating to Hibernate 3.6, an exception was suddenly thrown when accessing such a property (along with an SQLState: 22003 from PostgreSQL):

org.postgresql.util.PSQLException: Bad value for type long : This is some text...
	at org.postgresql.jdbc2.AbstractJdbc2ResultSet.toLong(AbstractJdbc2ResultSet.java:2796)
	at org.postgresql.jdbc2.AbstractJdbc2ResultSet.getLong(AbstractJdbc2ResultSet.java:2019)
	at org.postgresql.jdbc4.Jdbc4ResultSet.getClob(Jdbc4ResultSet.java:43)
	at org.postgresql.jdbc2.AbstractJdbc2ResultSet.getClob(AbstractJdbc2ResultSet.java:384)
        ... and more

Obviously, the PostgreSQL connector tried to interprete the textual content as a long integer now, which – of course – results in a failure. PostgreSQL knows two ways of storing binary large objects, either as BYTEA within the table space, or as OID in a separate place and referenced by a numerical identifier. It seems that Hibernate 3.6 suddenly treats TEXT columns like OID columns as well.

In the internet, I have found similar problems related to @Lob annotated byte[] properties. A common solution was to add a @Type annotation to the property.

An attempt to add @Type(type = “org.hibernate.type.MaterializedClobType”) did not help at all. Instead, @Type(type = “org.hibernate.type.TextType”) did the trick:

@Lob
@Type(type = "org.hibernate.type.TextType")
public String getComment() { return comment; }
public void setComment(String comment) { this.comment = comment; }

For PostgreSQL, it now works fine again, even without needing to alter the table column type. However I couldn’t check the impact of the annotation on other DBMS (like Oracle). Your feedback is welcome.

Written by Shred in Java7 comments

Tags: English, Hibernate, PostgreSQL

identicatwittergooglePlusdeliciousdigggooglegoogleReaderlinkarenamrWongredditwebnewsyigg

Comments

Gravatar

Hello,

maybe you should try this:

# STEP 1
@Lob
@Type(type = "org.hibernate.type.MaterializedClobType")
public String getComment() { return comment; }
public void setComment(String comment) { this.comment = comment; }

# STEP 2 - add to persistence.xml
<property name="hibernate.jdbc.use_streams_for_binary" value="true" />

Best Regards,
Krešimir

Krešimir Mar 16, 2011 9:50:27 PM
Gravatar

Hmm... No, does not seem to work for me. I still get the "Bad value for type long" error.

Shred Mar 17, 2011 1:12:46 AM
Gravatar

same problem here, seems like a bug in hibernate

axet Dec 7, 2011 11:15:00 PM
Gravatar

Adding hibernate.jdbc.use_streams_for_binary to persistence.xml is a NOP:
the hibernate.jdbc.use_streams_for_binary is a GLOBAL Hibernate property and as such it's only loaded from classpath:/hibernate.properties file (look at the Hibernate source code !)

and MaterializedClobType may work for byte[] Java type but will probably not for String (being TEXT in SQL), because the required conversion may not take place.
(And maybe org.hibernate.type.ByteArrayBlobType will better work for byte[) too...)

dontcare Jan 16, 2012 3:13:57 PM
Gravatar

Same problem. Your trick with @Type(type = "org.hibernate.type.TextType") works for me as well!

Thank you.

A. Feb 8, 2012 12:25:34 PM
Gravatar

We had a similar problem. Your hint solved the issue, thanks! :)

David Jun 15, 2012 9:09:32 AM
Gravatar

Hello,

I am investigating a similar problem. Your proposed solution does work with postgre. But in Oracle 11g this is converted to a Long Column, which is undesirable (see http://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm#i3056). I am not yet sure, what the perfect way to handle this would be. Using MaterializedClobType.INSTANCE.getAlternatives().getStreamBindingType() as a typeOverride in the dialect does get the code running (you can also use a configuration parameter to achieve this), but now hibernate stores the OID in a column of type Text. IMHO this is ugly and leads to some filtering code failing.

Carsten Hoffmann Nov 26, 2012 3:07:07 PM
For the Gravatar icon. Will not be shown.
No HTML. No spam. Please be polite.
  Please click on the 'X' for submission: