Geek Answers Handbook

How to use Spring Data / JPA to insert into a column of type Postgres Array?

Say I have a postgres table like this:

CREATE TABLE sal_emp ( name text, pay_by_quarter integer[], schedule text[][] );

Can I use Spring Data to insert into pay_by_quarter or schedule columns? If possible, how does it look like a repository and entity? I could not find any documentation or examples regarding this, possibly due to the way it overlaps with a more common use case, inserting into one table as a one-to-many relationship. Speaking of which, I am fully committed to using the Postgresql array data type and non-relational tables.

+5
source share
1 answer

You need to create your own type and implement the UserType interface . Based on the following answer , I wrote a Generic UserType for use in all arrays and it works, but you should use non-primitive data types (Integer, Long, String, ...). Otherwise, see the above update with type Boolean .

 public class GenericArrayUserType<T extends Serializable> implements UserType { protected static final int[] SQL_TYPES = { Types.ARRAY }; private Class<T> typeParameterClass; @Override public Object assemble(Serializable cached, Object owner) throws HibernateException { return this.deepCopy(cached); } @Override public Object deepCopy(Object value) throws HibernateException { return value; } @SuppressWarnings("unchecked") @Override public Serializable disassemble(Object value) throws HibernateException { return (T) this.deepCopy(value); } @Override public boolean equals(Object x, Object y) throws HibernateException { if (x == null) { return y == null; } return x.equals(y); } @Override public int hashCode(Object x) throws HibernateException { return x.hashCode(); } @Override public boolean isMutable() { return true; } @Override public Object nullSafeGet(ResultSet resultSet, String[] names, SessionImplementor session, Object owner) throws HibernateException, SQLException { if (resultSet.wasNull()) { return null; } if (resultSet.getArray(names[0]) == null) { return new Integer[0]; } Array array = resultSet.getArray(names[0]); @SuppressWarnings("unchecked") T javaArray = (T) array.getArray(); return javaArray; } @Override public void nullSafeSet(PreparedStatement statement, Object value, int index, SessionImplementor session) throws HibernateException, SQLException { Connection connection = statement.getConnection(); if (value == null) { statement.setNull(index, SQL_TYPES[0]); } else { @SuppressWarnings("unchecked") T castObject = (T) value; Array array = connection.createArrayOf("integer", (Object[]) castObject); statement.setArray(index, array); } } @Override public Object replace(Object original, Object target, Object owner) throws HibernateException { return original; } @Override public Class<T> returnedClass() { return typeParameterClass; } @Override public int[] sqlTypes() { return new int[] { Types.ARRAY }; } }

Then the properties of the array will have the same database type with the same dimension:

  • integer[] → integer[]
  • text[][] → String[][]

And in these special cases, put the GenericType class over the properties

 @Type(type = "packageofclass.GenericArrayUserType")

Then your essence will be:

 @Entity @Table(name="sal_emp") public class SalEmp { @Id private String name; @Column(name="pay_by_quarter") @Type(type = "packageofclass.GenericArrayUserType") private Integer[] payByQuarter; @Column(name="schedule") @Type(type = "packageofclass.GenericArrayUserType") private String[][] schedule; //Getters, Setters, ToString, equals, and so on }

If you do not want to use this generic type UserType integer[] and enter the type String[][] . You need to write your own types, in your case there will be the following:

  • integer []

     public class IntArrayUserType implements UserType { protected static final int[] SQL_TYPES = { Types.ARRAY }; @Override public Object assemble(Serializable cached, Object owner) throws HibernateException { return this.deepCopy(cached); } @Override public Object deepCopy(Object value) throws HibernateException { return value; } @Override public Serializable disassemble(Object value) throws HibernateException { return (Integer[]) this.deepCopy(value); } @Override public boolean equals(Object x, Object y) throws HibernateException { if (x == null) { return y == null; } return x.equals(y); } @Override public int hashCode(Object x) throws HibernateException { return x.hashCode(); } @Override public boolean isMutable() { return true; } @Override public Object nullSafeGet(ResultSet resultSet, String[] names, SessionImplementor session, Object owner) throws HibernateException, SQLException { if (resultSet.wasNull()) { return null; } if (resultSet.getArray(names[0]) == null) { return new Integer[0]; } Array array = resultSet.getArray(names[0]); Integer[] javaArray = (Integer[]) array.getArray(); return javaArray; } @Override public void nullSafeSet(PreparedStatement statement, Object value, int index, SessionImplementor session) throws HibernateException, SQLException { Connection connection = statement.getConnection(); if (value == null) { statement.setNull(index, SQL_TYPES[0]); } else { Integer[] castObject = (Integer[]) value; Array array = connection.createArrayOf("integer", castObject); statement.setArray(index, array); } } @Override public Object replace(Object original, Object target, Object owner) throws HibernateException { return original; } @Override public Class<Integer[]> returnedClass() { return Integer[].class; } @Override public int[] sqlTypes() { return new int[] { Types.ARRAY }; } }

  • text [] []

     public class StringMultidimensionalArrayType implements UserType { protected static final int[] SQL_TYPES = { Types.ARRAY }; @Override public Object assemble(Serializable cached, Object owner) throws HibernateException { return this.deepCopy(cached); } @Override public Object deepCopy(Object value) throws HibernateException { return value; } @Override public Serializable disassemble(Object value) throws HibernateException { return (String[][]) this.deepCopy(value); } @Override public boolean equals(Object x, Object y) throws HibernateException { if (x == null) { return y == null; } return x.equals(y); } @Override public int hashCode(Object x) throws HibernateException { return x.hashCode(); } @Override public boolean isMutable() { return true; } @Override public Object nullSafeGet(ResultSet resultSet, String[] names, SessionImplementor session, Object owner) throws HibernateException, SQLException { if (resultSet.wasNull()) { return null; } if (resultSet.getArray(names[0]) == null) { return new String[0][]; } Array array = resultSet.getArray(names[0]); String[][] javaArray = (String[][]) array.getArray(); return javaArray; } @Override public void nullSafeSet(PreparedStatement statement, Object value, int index, SessionImplementor session) throws HibernateException, SQLException { Connection connection = statement.getConnection(); if (value == null) { statement.setNull(index, SQL_TYPES[0]); } else { String[][] castObject = (String[][]) value; Array array = connection.createArrayOf("integer", castObject); statement.setArray(index, array); } } @Override public Object replace(Object original, Object target, Object owner) throws HibernateException { return original; } @Override public Class<String[][]> returnedClass() { return String[][].class; } @Override public int[] sqlTypes() { return new int[] { Types.ARRAY }; } }

In this case, your properties have different types:

 @Column(name="pay_by_quarter") @Type(type = "packageofclass.IntArrayUserType") private Integer[] payByQuarter; @Column(name="schedule") @Type(type = "packageofclass.StringMultidimensionalArrayType") private String[][] schedule;

Update Hibernate UserType

With boolean or boolean, it seems that it does not work with GenericArrayUserType , so solutions can be created in a CREATE DDL Boolean declaration of type bytea :

 CREATE TABLE sal_emp ( name text, pay_by_quarter integer[], schedule text[][], wow_boolean bytea );

And your property without any type:

private boolean[][][] wowBoolean;

It understands perfectly without any Type or Converter . Output: wowBoolean=[[[true, false], [true, false]], [[true, true], [true, true]]])

Update @Converter from JPA 2.1

I tried the option with @Converter JPA 2.1 with EclipseLink and Hibernate . I just tried integer[] (not text[][] ) Converter like this (* I changed the property to List<Integer> , but that doesn't matter):

 @Converter public class ConverterListInteger implements AttributeConverter<List<Integer>, Array>{ @Override public Array convertToDatabaseColumn(List<Integer> attribute) { DataSource source = ApplicationContextHolder.getContext().getBean(DataSource.class); try { Connection conn = source.getConnection(); Array array = conn.createArrayOf("integer", attribute.toArray()); return array; } catch (SQLException e) { e.printStackTrace(); } return null; } @Override public List<Integer> convertToEntityAttribute(Array dbData) { List<Integer> list = new ArrayList<>(); try { for(Object object : (Object[]) dbData.getArray()){ list.add((Integer) object); } } catch (SQLException e) { e.printStackTrace(); } return list; } }

Then add the converter to the property in Entity:

 @Convert(converter=ConverterListInteger.class) private List<Integer> pay_by_quarter;

So, the solution based on the JPA specification does not work. What for? Hibernate does not support database arrays ( java.sql.Array ) ....

Then I tried with EclipseLink (see how to configure here ), and it works, but not always ... It seems to be an error, it works the first time well, but then the next time it is impossible to update or request this line. Then only I have success, add new lines, but it is impossible to update or request after ....

Conclusion

At the moment, it seems that JPA support is not supported properly ... Only a solution with Hibernate UserType works well, but it's easy for Hibernate .

+8
source

More articles:


All Articles