Tuesday, April 16, 2013

Hibernate & Postgresql

If you are using Hibernate 3.5 or above to talk to Postgresql database, have you ever tried to store a byte array?

Let's take an example.

Here is the mapping which will store and read byte[] from the database.
@Lob
@Column(name = "image")
private byte[] image;

Here is the JPA mapping file configuration.
<persistence version="2.0" 
    xmlns="http://java.sun.com/xml/ns/persistence" 
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
    xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">

  <persistence-unit name="testPU" transaction-type="JTA">
    <provider>org.hibernate.ejb.HibernatePersistence</provider>
    <jta-data-source>test</jta-data-source>
    <properties>
    </properties>
  </persistence-unit>

</persistence>

When you try to save your entity you will get the following exception.
org.postgresql.util.PSQLException: ERROR: column "image" is of type bytea but expression is of type oid
  Hint: You will need to rewrite or cast the expression.

This is because Postgresql handles large objects differently for performance reasons. You can find more details on how Postgresql handles large objects at here and here.

If you dont have time to read all these, here is solution.

Step-1. Add the following to the persistence.xml file
<properties>
    <property name="hibernate.jdbc.use_streams_for_binary" value="true"/>

</properties>
Step-2. Remove the @Lob annotation from you Entity class.
This is important as not removing this will give you the same exception as before.

Software used:

  • hibernate-release-4.1.9.Final
  • Postgreql 9.1

You can find more solutions and details at the following links:
  1. http://stackoverflow.com/questions/4488693/hibernate-postgresql-column-x-is-of-type-oid-but-expression-is-of-type-byte
  2. http://virgo47.wordpress.com/2008/06/13/jpa-postgresql-and-bytea-vs-oid-type/
  3. http://in.relation.to/15492.lace
  4. https://hibernate.atlassian.net/browse/HHH-4876