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.
In this article, you'll learn how to configure Spring Boot, Spring Data JPA to support a PostgreSQL database. Get source code of this tutorial on my GitHub Repository. Check out these two links to download and install a PostgreSQL database on your machine.
JPA cannot deal with reactive repositories such as provided by Spring Data R2DBC. This means you will have to do more things manually when using R2DBC. There are other reactive drivers around such as for example Quarkus Reactive Postgres client (which uses Vert.
As a consequence, findById() returns the actual object and getById returns a reference of the entity.
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;
With Boolean or boolean seems It doesn't works with GenericArrayUserType
, so the solutions could be create in your CREATE DDL
declare boolean
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 parses very good without any Type
or Converter
. Output: wowBoolean=[[[true, false], [true, false]], [[true, true], [true, true]]])
Update With @Converter
of JPA 2.1
I've tried an option with @Converter
of JPA 2.1 with EclipseLink
and Hibernate
. I've just tried integer[]
(not text[][]
) Converter
like 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....
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
.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With