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.
