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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s