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 is to use @Column(columnDefinition = "text")
, which requires not to use @Lob
at String
s 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.