postgresql 如何使用 Spring Data / JPA 插入 Postgres Array 类型的列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/39119164/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to use Spring Data / JPA to insert into a Postgres Array type column?
提问by Zombies
Say I have a postgres table like so:
假设我有一个像这样的 postgres 表：
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_quarteror 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 arraydatatype and no relational tables.
我甚至可以使用Spring数据插入到列pay_by_quarter或schedule？如果可能，这将如何作为 Repository 和 Entity ？我无法找到任何解决此问题的文档或示例，可能是因为它与更常见的用例重叠，将作为一对多关系插入到多个表中。说到这里，我完全打算使用 Postgresqlarray数据类型，而不是关系表。
回答by Pau
You need to create your own type and implement the UserType interface. Based in next responseI've written a Generic UserTypeto use in all arrays and it works but you must use non primitive data types(Integer, Long, String,...). Otherwise see the above update with Booleantype.
您需要创建自己的类型并实现UserType interface. 根据下一个响应，我编写了一个通用型UserType以用于所有数组，它可以工作，但您必须使用非原始数据类型（Integer、Long、String...）。否则请参阅上面的更新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 array properties would be same type of data base with same dimension:
那么数组属性将是具有相同维度的相同类型的数据库：
integer[]->Integer[]text[][]->String[][]
integer[]->Integer[]text[][]->String[][]
And in this special cases put the GenericTypeclass above the properties
在这种特殊情况下，将GenericType类放在属性之上
@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 UserTypethe Integer[]type and write the String[][]type. You need to write your own types, in your case there would be as next:
如果你不想使用这种通用UserType的Integer[]类型，写String[][]类型。您需要编写自己的类型，在您的情况下，如下所示：
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 }; } }
整数[]
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 }; } }文本[][]
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 DDLdeclare booleanof type bytea:
使用 Boolean 或 boolean 似乎它不适用于GenericArrayUserType，因此可以在您的类型CREATE DDL声明boolean中创建解决方案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;
private boolean[][][] wowBoolean;
It parses very good without any Typeor Converter. Output: wowBoolean=[[[true, false], [true, false]], [[true, true], [true, true]]])
它在没有任何Type或的情况下解析得很好Converter。输出：wowBoolean=[[[true, false], [true, false]], [[true, true], [true, true]]])
Update With @Converterof JPA 2.1
更新@Converter时间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):
我已经尝试了@ConverterJPA 2.1EclipseLink和的选项Hibernate。我刚刚尝试过integer[]（不是text[][]）Converter这样的（*我已将属性更改为 aList<Integer>但没关系）：
@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 specificationdoesn't works. Why? Hibernate does not support database arrays (java.sql.Array)....
所以基于 的解决方案JPA specification不起作用。为什么？Hibernate 不支持数据库数组 ( 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....
然后我尝试了 EclipseLink（请参阅如何在此处配置）并且它可以工作，但并非总是如此......似乎有一个错误，它第一次运行良好，但下次无法更新或查询该行。然后我成功添加了新行，但之后无法更新或查询....
Conclusion
结论
At the moment, It seems there is not supported by JPAvendors properly... Only the solution with HibernateUserTypeworks well but it's just for Hibernate.
目前，似乎没有得到JPA供应商的正确支持......只有 的解决方案HibernateUserType运行良好，但它仅适用于Hibernate.
