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

Update: An alternative proposed by valsaraj in the comments is to use @Column(columnDefinition = “text”), which requires not to use @Lob at Strings at all:

@Column(columnDefinition = "text")
public String getComment() { return comment; }
public void setComment(String comment) { this.comment = comment; }

I think this is the better solution, however it requires the underlying database to support the text column type.

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

Tags: Hibernate, PostgreSQL

Show 10 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
Gravatar

@Column(columnDefinition="TEXT")

valsaraj Jan 20, 2015 5:43:18 AM
Gravatar

I think this solution is even better. However along with the @Column annotation, the @Lob annotation needs to be removed.

Shred Feb 25, 2015 11:53:50 AM
Gravatar

I am facing similar problem, And tried same soluation @Column(columnDefinition="TEXT") now it started working PostgreSQL and it is failing for Oracle.

Can some one help me on this....

Padmanabhan Aug 4, 2015 7:42:08 AM
For the Gravatar icon. Will not be shown.
No HTML. No spam. Please be polite.
  Please click on the 'X' for submission: