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:

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(
	at org.postgresql.jdbc2.AbstractJdbc2ResultSet.getLong(
	at org.postgresql.jdbc4.Jdbc4ResultSet.getClob(
	at org.postgresql.jdbc2.AbstractJdbc2ResultSet.getClob(
        ... 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:

@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 Java8 comments

Tags: English, Hibernate, PostgreSQL





maybe you should try this:

# STEP 1
@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 Mar 16, 2011 9:50:27 PM

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

same problem here, seems like a bug in hibernate

axet Dec 7, 2011 11:15:00 PM

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:/ 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

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

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

David Jun 15, 2012 9:09:32 AM


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 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


valsaraj Jan 20, 2015 5:43:18 AM
For the Gravatar icon. Will not be shown.
No HTML. No spam. Please be polite.
  Please click on the 'X' for submission: