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

Question

Say I have a postgres table like so:

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

Would I even be able to use Spring Data to insert into the columns pay_by_quarter or schedule ? If possible, how would this look as a Repository and Entity ? I haven't been able to find any documentation or examples addressing this, possibly because of how it overlaps with the more common use-case, inserting into multiple tables as one-to-many relations. Speaking of which, I fully intend to use the Postgresql array datatype and no relational tables.

Yes, you could achieve this with Spring Data. However, I suggest this is a very bad practice, violating the 1NF (en.wikipedia.org/wiki/First_normal_form). If possible, consider a model revision.. — crm86
– crm86, Commented Aug 24, 2016 at 9:28
@crm86 I prefer to not have FNF for the particular data I am saving (mass streams of tweets from twitter). I want to store the one-to-many relations in a single table (to avoid so many joins for such poorly valued data). I also want to avoid NoSQL document databases as they are unruly to maintain for me. I suppose another alternative to array data type would be JSONB and store everything as a document in postgresql. But I think working with columns (and a few array datatypes) is better than working with JSON (in Postgresql). — Zombies
– Zombies, Commented Aug 24, 2016 at 9:37

Community · Accepted Answer · 2017-05-23 12:24:18Z

You need to create your own type and implement the UserType interface. Based in next response I've written a Generic UserType to use in all arrays and it works but you must use non primitive data types (Integer, Long, String,...). Otherwise see the above update with Boolean type.

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 array properties would be same type of data base with same dimension:

integer[] -> Integer[]
text[][]-> String[][]

And in this special cases put the GenericType class above the properties

@Type(type = "packageofclass.GenericArrayUserType")

Then your entity would 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 don't want to use this Generic UserType the Integer[] type and write the String[][] type. You need to write your own types, in your case there would be as next:

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 has 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 seems It doesn't works with GenericArrayUserType, so the solutions could be create in your CREATE DDL declare booleanof 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 parses very good without any Typeor Converter. Output: wowBoolean=[[[true, false], [true, false]], [[true, true], [true, true]]])

Update With @Converter of JPA 2.1

I've tried an option with @Converterof JPA 2.1 with EclipseLinkand Hibernate. I've just tried integer[] (not text[][]) Converterlike this (*I've changed the property to a List<Integer> but it 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 the Entity:

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

So the solution based on the JPA specification doesn't works. Why? Hibernate does not support database arrays (java.sql.Array)....

Then I've tried with EclipseLink (see how to configure here) and it works, but not always ...It seems there's a bug, It works the first time well but then next times it's not possible to update or query this row. Then just I've success add new rows but It's not possible to update or query after....

Conclusion

At the moment, It seems there is not supported by JPA vendors properly... Only the solution with Hibernate UserType works well but it's just for Hibernate.

Thank you for this. Is there any way to do this without depending on Hibernate?? For example, would it be possible to not use org.hibernate.usertype.UserType ?? Or is depending on Hibernate the only solution for this use-case?
This solution depends on hibernate I know, but it think is easier. If you want don't depend on 'Hibernate' you could use @Converter of Jpa 2.1. See this post: stackoverflow.com/a/24194996/4751165
In the example dealing with JPA 2.1 you use the Array type, that is the java.sql.Array right?
You have never initialized typeParameterClass in your first code block. Don't you get a null pointer exception?
I get the following error using GenericArrayUserType: "No Dialect mapping for JDBC type: 2003"

abhinav kumar · Accepted Answer · 2020-08-04 07:56:00Z

0

Simple Approach will be

try to convert string[] to string and then in Entity class make

@Column(name = "nice_work" columnDefinition="text")

function for converting string[] to string and vice-versa

private static String stringArrayTOString(String[] input) {
        StringBuffer sb =new StringBuffer("");
        int i=0;
        for(String value:input) {
            
            if(i!=0) {
                sb.append(",");
            }
            sb.append(value);
            i++;
        }
        return sb.toString();
    }
    
    private static String[] stringToStringArray(String input) {
        String[] output = input.split(",");
        return output;
    }

answered Aug 4, 2020 at 7:56

abhinav kumar

1,9731 gold badge16 silver badges22 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Update Hibernate UserType

Conclusion

8 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Update Hibernate UserType

Conclusion

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related