Monday, May 20, 2013

hibernate/JPA annotation for byte[] + blob in oracle + bytea in postgres

After reading this http://stackoverflow.com/questions/3677380/proper-hibernate-annotation-for-byte and try everything that I found over Internet .
Problem : I want to have a JPA/Hibernate annotation that stores byte[] as blob in Oracle and as bytea in postgres.
Answer there is no such annotation ! So annoying to be real !
It is a similar problem with boolean storage http://vthanhvinh.blogspot.fi/2013/05/orgpostgresqlutilpsqlexception-error.html. However, there is no annotation that can help in this case.
Luckily, we can override annotation mapping with xml file, my great discovery. I have a entity like this
 package com.wapice.example;  
 import javax.persistence.Column;  
 import javax.persistence.Entity;  
 import javax.persistence.Table;  
 import javax.persistence.TableGenerator;  
 @Entity  
 @Table(name ="SMMBINRESOURCE")  
 @TableGenerator(name = "BINRESOURCEID", table = "SMMID", pkColumnName = "SERIES", valueColumnName = "NEXTVAL", pkColumnValue = "BINRESOURCEID", initialValue = 0, allocationSize = 1)  
 public class BinaryResource extends ProjectSpecificObject<Long> {  
      private String _name;  
      private String _mimetype;  
      private byte[] _data;  
      // logged object fields  
      /**  
       *   
       */  
      private static final long serialVersionUID = 8989926469766677944L;  
      @Column(name="DATA",columnDefinition="BLOB")  
      public byte[] getData() {  
           return _data;  
      }  
      public void setData(byte[] data) {  
           _data = data;  
      }  
      @Column(name="NAME", columnDefinition="nvarchar2")  
      public String getName() {  
           return _name;  
      }  
      public void setName(String name) {  
           _name = name;  
      }  
      @Column(name="MIMETYPE", columnDefinition="nvarchar2")  
      public String getMimetype() {  
           return _mimetype;  
      }  
      public void setMimetype(String mimetype) {  
           _mimetype = mimetype;  
      }  
 }  

By using @Column(name="DATA",columnDefinition="BLOB") , it will work with Oracle and correctly map byte[] to blob. But that would not work with Postgres.
So I have a xml mapping file for postgres as follow:
 <?xml version="1.0" encoding="UTF-8" ?>  
 <entity-mappings xmlns="http://java.sun.com/xml/ns/persistence/orm"  
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  
    xsi:schemaLocation="http://java.sun.com/xml/ns/persistence/orm    
 http://java.sun.com/xml/ns/persistence/orm_2_0.xsd"  
   version="2.0">  
   <description>SMM extended mapping</description>  
   <package>entity</package>   
   <entity class="com.wartsila.smm.model.BinaryResource" name="SMMBINRESOURCE">      
     <table name="SMMBINRESOURCE"/>  
     <attributes>  
       <basic name="name">  
         <column name="NAME" length="100"/>  
       </basic>  
       <basic name="mimetype">  
            <column name="MIMETYPE"/>  
       </basic>  
       <basic name="data">  
             <column name="DATA" column-definition="bytea"/>  
       </basic>  
     </attributes>  
   </entity>  
 </entity-mappings>  
then I define Hibernate sessionFactory for Postgres SQL as :
 <?xml version="1.0" encoding="UTF-8"?>  
 <beans xmlns="http://www.springframework.org/schema/beans"  
  xmlns:context="http://www.springframework.org/schema/context"  
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:jdbc="http://www.springframework.org/schema/jdbc"  
  xmlns:jee="http://www.springframework.org/schema/jee" xmlns:p="http://www.springframework.org/schema/p"  
  xsi:schemaLocation="  
  http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee-3.0.xsd  
    http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd  
  http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.0.xsd  
  http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-3.0.xsd  
  http://www.springframework.org/schema/data/jpa http://www.springframework.org/schema/data/jpa/spring-jpa-1.0.xsd  
  ">  
   <bean id="sessionFactory" class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">  
  <property name="dataSource">  
   <ref bean="smmDataSource" />  
  </property>  
  <!--   
  <property name="entityInterceptor">  
       <bean class="com.wartsila.smm.hibernate.interceptors.CutomEntityInterceptor"></bean>  
     </property>  
  -->  
  <property name="hibernateProperties">  
   <props>  
   <prop key="hibernate.connection.defaultNChar">true</prop>  
   <prop key="hibernate.dialect">org.hibernate.dialect.PostgreSQLDialect</prop>  
   <prop key="hibernate.show_sql">false</prop>  
   <prop key="hibernate.hbm2ddl.auto">validate</prop>  
   </props>  
  </property>  
  <property name="packagesToScan">  
       <list>  
         <value>com.wapice.example/value>  
       </list>  
     </property>  
     <property name="mappingResources">  
         <list>  
      <value>com/wapice/example/orm-postgres.xml</value>  
       </list>  
      </property>  
  </bean>  
 </beans>  

This will correctly map byte[] to bytea in Postgres. Moreover, as we can see BinaryResource extends from ProjectSpecificObject which is a @MappedSuperclass. But we do not need to re-define ProjectSpecificObject in xml mapping AGAIN. Therefore, in my opinion, this is quite nice solution.

No comments:

Post a Comment