Discuss New Concept,New Technic,New Tools, Including EAI,BPM,SOA,Tibco,IBM MQ,Tuxedo, Cloud,Hadoop,NoSQL,J2EE,Ruby,Scala,Python, Performance,Scalability,Distributed,HA, Social Network,Machine Learning.

October 17, 2012  Tagged with: , , ,

original:http://www.shredzone.de/cilla/page/299/string-lobs-on-postgresql-with-hibernate-36.html

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.