HOME
Galerie
Softwaretechnik
Linux
Sonstiges
Contact
LibreOffice braucht Starthilfe
Weitere Galerien sind da!String LOBs on PostgreSQL with Hibernate 3.6
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.
Comments
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
Hmm... No, does not seem to work for me. I still get the "Bad value for type long" error.
same problem here, seems like a bug in hibernate
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...)
Same problem. Your trick with @Type(type = "org.hibernate.type.TextType") works for me as well!
Thank you.
We had a similar problem. Your hint solved the issue, thanks! :)
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.
ATOM 1.0