Mapping Array Column of postgres in Hibernate

Representing Array Column in HQL:

Hibernate does not support database arrays (e.g. ones mapped to java.sql.Array) out of the box.

To Support Array Column of postgres we need to implement custom UserType.

For suppose following is the table syntax which users varchar[] array column

CREATE TABLE posts (title varchar, tags varchar[]);
INSERT INTO posts VALUES ('apple', '{fruit,seasonal}');
INSERT INTO posts VALUES ('badam', '{dry fruit,healthy,tasty}');
INSERT INTO posts VALUES ('coriander', '{leafy vegetables,tasty}');

Following custom UserType (StringArrayType), supports varchar[] column in Postgres.

package com.varam.blog.hql;
import java.io.Serializable;
import java.sql.Array;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;

import org.hibernate.HibernateException;
import org.hibernate.engine.spi.SessionImplementor;
import org.hibernate.usertype.UserType;

public class StringArrayType implements UserType {

 private final int[] arrayTypes = new int[] { Types.ARRAY };

 @Override
 public int[] sqlTypes() {
 return arrayTypes;
 }

 @Override
 public Class<String[]> returnedClass() {
 return String[].class;
 }

 @Override
 public boolean equals(Object x, Object y) throws HibernateException {
 return x == null ? y == null : x.equals(y);
 }

 @Override
 public int hashCode(Object x) throws HibernateException {
 return x == null ? 0 : x.hashCode();
 }

 @Override
 public Object nullSafeGet(ResultSet rs, String[] names, SessionImplementor session, Object owner) throws HibernateException, SQLException {
 // get the first column names
 if (names != null && names.length > 0 && rs != null && rs.getArray(names[0]) != null) {
 String[] results = (String[]) rs.getArray(names[0]).getArray();
 return results;
 }
 return null;
 }

 @Override
 public void nullSafeSet(PreparedStatement st, Object value, int index, SessionImplementor session) throws HibernateException,
 SQLException {
 // setting the column with string array
 if (value != null && st != null) {
 String[] castObject = (String[]) value;
 Array array = session.connection().createArrayOf("text", castObject);
 st.setArray(index, array);
 } else {
 st.setNull(index, arrayTypes[0]);
 }
 }

 @Override
 public Object deepCopy(Object value) throws HibernateException {
 return value == null ? null : ((String[]) value).clone();
 }

 @Override
 public boolean isMutable() {
 return false;
 }

 @Override
 public Serializable disassemble(Object value) throws HibernateException {
 return (Serializable) value;
 }

 @Override
 public Object assemble(Serializable cached, Object owner) throws HibernateException {
 return cached;
 }

 @Override
 public Object replace(Object original, Object target, Object owner) throws HibernateException {
 return original;
 }
}

usage of custom array type in annotation based hibernate model

@Entity
@Table(name = "posts", schema = "public")

public class Posts extends IEntity {
@Column(unique=true, name = "title") private String title;
@Column(name = "accountid") 
@Type(type = "com.varam.blog.hql.StringArrayType")
private String[] tags; 

//Setters and getter methods   
}

This will also help the hbm2ddl to automatically generate the sql array columns in postgres.

Advertisements