How to map a PostgreSQL array with Hibernate
Maven dependency
The first thing you need to do is to set up the following Hibernate Types Maven dependency in your project pom.xml
configuration file:
<dependency> <groupId>com.vladmihalcea</groupId> <artifactId>hibernate-types-52</artifactId> <version>${hibernate-types.version}</version></dependency>
Assuming you have this table in your database:
create table event ( id int8 not null, version int4, sensor_names text[], sensor_values integer[], primary key (id))
And you want to map it like this:
@Entity(name = "Event")@Table(name = "event")@TypeDefs({ @TypeDef( name = "string-array", typeClass = StringArrayType.class ), @TypeDef( name = "int-array", typeClass = IntArrayType.class )})public static class Event extends BaseEntity { @Type( type = "string-array" ) @Column( name = "sensor_names", columnDefinition = "text[]" ) private String[] sensorNames; @Type( type = "int-array" ) @Column( name = "sensor_values", columnDefinition = "integer[]" ) private int[] sensorValues; //Getters and setters omitted for brevity}
The string-array
and int-array
are custom types which can be defined in the BaseEntity
superclass:
@TypeDefs({ @TypeDef( name = "string-array", typeClass = StringArrayType.class ), @TypeDef( name = "int-array", typeClass = IntArrayType.class )})@MappedSuperclasspublic class BaseEntity { @Id private Long id; @Version private Integer version; //Getters and setters omitted for brevity}
The StringArrayType
and IntArrayType
are classes offered by the Hibernate Types project.
Testing time
Now, when you insert a couple of entities;
Event nullEvent = new Event();nullEvent.setId(0L);entityManager.persist(nullEvent); Event event = new Event();event.setId(1L);event.setSensorNames( new String[] { "Temperature", "Pressure" });event.setSensorValues( new int[] { 12, 756 } );entityManager.persist(event);
Hibernate is going to generate the following SQL statements:
INSERT INTO event ( version, sensor_names, sensor_values, id) VALUES ( 0, NULL(ARRAY), NULL(ARRAY), 0) INSERT INTO event ( version, sensor_names, sensor_values, id) VALUES ( 0, {"Temperature","Pressure"}, {"12","756"}, 1)
Hibernate does not support database arrays (e.g. ones mapped to java.sql.Array
) out of the box.
array
and primitive-array
types provided by Hibernate are for mapping Java arrays into backing table - they're basically a variation of one-to-many / collection-of-elements mappings, so that's not what you want.
Latest PostgreSQL JDBC driver (8.4.whatever) supports JDBC4 Connection.createArrayOf()
method as well as ResultSet.getArray()
and PreparedStatement.setArray() methods, though, so you can write your own UserType
to provide array support.
Here is a UserType implementation dealing with Oracle array that provides a good starting point, it's reasonably straightforward to adapt it to handle java.sql.Array
instead.
Perhaps this is useful for someone else: I found that in my case it performs poorly and could not be used with c3p0. (Only explored these issues briefly, is they can be resolved please correct me!)
Hibernate 3.6:
import java.io.Serializable;import java.sql.Array;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.Arrays;import org.apache.commons.lang.ArrayUtils;import org.hibernate.HibernateException;import org.hibernate.usertype.UserType;public class IntArrayUserType implements UserType {protected static final int SQLTYPE = java.sql.Types.ARRAY;@Overridepublic Object nullSafeGet(final ResultSet rs, final String[] names, final Object owner) throws HibernateException, SQLException { Array array = rs.getArray(names[0]); Integer[] javaArray = (Integer[]) array.getArray(); return ArrayUtils.toPrimitive(javaArray);}@Overridepublic void nullSafeSet(final PreparedStatement statement, final Object object, final int i) throws HibernateException, SQLException { Connection connection = statement.getConnection(); int[] castObject = (int[]) object; Integer[] integers = ArrayUtils.toObject(castObject); Array array = connection.createArrayOf("integer", integers); statement.setArray(i, array);}@Overridepublic Object assemble(final Serializable cached, final Object owner) throws HibernateException { return cached;}@Overridepublic Object deepCopy(final Object o) throws HibernateException { return o == null ? null : ((int[]) o).clone();}@Overridepublic Serializable disassemble(final Object o) throws HibernateException { return (Serializable) o;}@Overridepublic boolean equals(final Object x, final Object y) throws HibernateException { return x == null ? y == null : x.equals(y);}@Overridepublic int hashCode(final Object o) throws HibernateException { return o == null ? 0 : o.hashCode();}@Overridepublic boolean isMutable() { return false;}@Overridepublic Object replace(final Object original, final Object target, final Object owner) throws HibernateException { return original;}@Overridepublic Class<int[]> returnedClass() { return int[].class;}@Overridepublic int[] sqlTypes() { return new int[] { SQLTYPE };}}