如何使用Spring Data / JPA插入Postgres Array类型列中？ [英] How to use Spring Data / JPA to insert into a Postgres Array type column?

查看：108
本文介绍了如何使用Spring Data / JPA插入Postgres Array类型列中？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

说我有一个像这样的postgres表：

Say I have a postgres table like so:

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

我什至可以使用Spring Data插入 pay_by_quarter列中或时间表？如果可能的话，它看起来如何作为存储库和实体？我找不到解决此问题的任何文档或示例，可能是因为它与更常见的用例重叠，并以一对多关系插入到多个表中。说到这，我完全打算使用Postgresql array 数据类型，并且没有关系表。

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.

推荐答案

您需要创建自己的类型并实现 UserType接口。基于下一个响应，我编写了一个通用的 UserType 来使用在所有数组中均可使用，但必须使用非原始数据类型（整数，长整数，字符串等）。否则，请参见上面的 Boolean 类型更新。

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 [] []

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

在这种特殊情况下，将 GenericType 类上方的属性

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

@Type(type = "packageofclass.GenericArrayUserType")

那么您的实体将是：

@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

}

如果您不想使用此通用 UserType Integer [] 并输入 String [] [] 类型。您需要编写自己的类型，以您为例，如下所示：

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 []

  • 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 [] []

  • 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;



    更新Hibernate用户类型



    带有布尔值或布尔值似乎不适用于 GenericArrayUserType ，因此可以在 CREATE DDL 声明类型为 bytea 的 boolean ：

    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 [] [] [] wowBool​​ean;

    它在没有任何类型或转换器。输出： wowBool​​ean = [[[[true，false]，[true，false]]，[[true，true]，[true，true]]]））

    It parses very good without any Typeor Converter. Output: wowBoolean=[[[true, false], [true, false]], [[true, true], [true, true]]])

    使用 JPA 2.1 的 @Converter 更新strong>

    Update With @Converter of JPA 2.1

    我已经尝试了带有 EclipseLink的JPA 2.1的 @Converter 的选项和 Hibernate 。我刚刚尝试过 integer [] （不是 text [] [] ）转换器这样（*我已将该属性更改为 List< Integer> ，但这没关系）：

    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;

    }

}

    然后，添加

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

    因此，基于 JPA规范的解决方案不起作用。为什么？ Hibernate不支持数据库数组（ java.sql.Array ）...。

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

    然后我尝试了使用EclipseLink（请参见此处）并且它可以工作，但并不总是...似乎有一个错误，它第一次可以很好地工作，但是下次它就不可能更新或查询该行。然后，只要我成功添加新行，但是之后就无法更新或查询。

    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....

    目前，似乎没有 JPA 供应商正确支持...仅包含的解决方案Hibernate UserType 效果很好，但仅适用于 Hibernate 。

    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.

    这篇关于如何使用Spring Data / JPA插入Postgres Array类型列中？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

    查看全文
    相关文章
    Java开发最新文章
    热门教程
    热门工具
    登录 关闭
    扫码关注1秒登录
    发送“验证码”获取 | 15天全站免登陆