How to map a PostgreSQL array with Hibernate
JavaArraysPostgresqlHibernateOrmJava Problem Overview
has anyone successfully mapped a numeric array in PostgreSQL to a numeric array in java via Hibernate?
sql:
CREATE TABLE sal_emp (name text, pay_by_quarter integer[]);
INSERT INTO sal_emp VALUES ('one', '{1,2,3}');
INSERT INTO sal_emp VALUES ('two', '{4,5,6}');
INSERT INTO sal_emp VALUES ('three', '{2,4,6}');
http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
mapping:
<hibernate-mapping>
<class name="SalEmp" table="sal_emp">
<id name="name" />
<property name="payByQuarter" column="pay_by_quarter" />
</class>
</hibernate-mapping>
class:
public class SalEmp implements Serializable{
private String name;
private Integer[] payByQuarter;
...// getters & setters
}
i get an exception when querying the table.
Java Solutions
Solution 1 - Java
Maven dependency
The first thing you need to do is to set up the following Hibernate Types Maven dependency in your project pom.xml
configuration file:
<dependency>
<groupId>com.vladmihalcea</groupId>
<artifactId>hibernate-types-52</artifactId>
<version>${hibernate-types.version}</version>
</dependency>
Assuming you have this table in your database:
create table event (
id int8 not null,
version int4,
sensor_names text[],
sensor_values integer[],
primary key (id)
)
And you want to map it like this:
@Entity(name = "Event")
@Table(name = "event")
@TypeDefs({
@TypeDef(
name = "string-array",
typeClass = StringArrayType.class
),
@TypeDef(
name = "int-array",
typeClass = IntArrayType.class
)
})
public static class Event extends BaseEntity {
@Type( type = "string-array" )
@Column(
name = "sensor_names",
columnDefinition = "text[]"
)
private String[] sensorNames;
@Type( type = "int-array" )
@Column(
name = "sensor_values",
columnDefinition = "integer[]"
)
private int[] sensorValues;
//Getters and setters omitted for brevity
}
The string-array
and int-array
are custom types which can be defined in the BaseEntity
superclass:
@TypeDefs({
@TypeDef(
name = "string-array",
typeClass = StringArrayType.class
),
@TypeDef(
name = "int-array",
typeClass = IntArrayType.class
)
})
@MappedSuperclass
public class BaseEntity {
@Id
private Long id;
@Version
private Integer version;
//Getters and setters omitted for brevity
}
The StringArrayType
and IntArrayType
are classes offered by the Hibernate Types project.
Testing time
Now, when you insert a couple of entities;
Event nullEvent = new Event();
nullEvent.setId(0L);
entityManager.persist(nullEvent);
Event event = new Event();
event.setId(1L);
event.setSensorNames(
new String[] {
"Temperature",
"Pressure"
}
);
event.setSensorValues(
new int[] {
12,
756
}
);
entityManager.persist(event);
Hibernate is going to generate the following SQL statements:
INSERT INTO event (
version,
sensor_names,
sensor_values,
id
)
VALUES (
0,
NULL(ARRAY),
NULL(ARRAY),
0
)
INSERT INTO event (
version,
sensor_names,
sensor_values,
id
)
VALUES (
0,
{"Temperature","Pressure"},
{"12","756"},
1
)
Solution 2 - Java
Hibernate does not support database arrays (e.g. ones mapped to java.sql.Array
) out of the box.
array
and primitive-array
types provided by Hibernate are for mapping Java arrays into backing table - they're basically a variation of one-to-many / collection-of-elements mappings, so that's not what you want.
Latest PostgreSQL JDBC driver (8.4.whatever) supports JDBC4 http://java.sun.com/javase/6/docs/api/java/sql/Connection.html#createArrayOf(java.lang.String,%20java.lang.Object%5B%5D)">`Connection.createArrayOf()`</a> method as well as http://java.sun.com/javase/6/docs/api/java/sql/ResultSet.html#getArray(java.lang.String)">`ResultSet.getArray()`</a> and http://java.sun.com/javase/6/docs/api/java/sql/PreparedStatement.html#setArray(int,%20java.sql.Array)">PreparedStatement.setArray()</a> methods, though, so you can write your own UserType
to provide array support.
Here is a UserType implementation dealing with Oracle array that provides a good starting point, it's reasonably straightforward to adapt it to handle java.sql.Array
instead.
Solution 3 - Java
Perhaps this is useful for someone else: I found that in my case it performs poorly and could not be used with c3p0. (Only explored these issues briefly, is they can be resolved please correct me!)
Hibernate 3.6:
import java.io.Serializable;
import java.sql.Array;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Arrays;
import org.apache.commons.lang.ArrayUtils;
import org.hibernate.HibernateException;
import org.hibernate.usertype.UserType;
public class IntArrayUserType implements UserType {
protected static final int SQLTYPE = java.sql.Types.ARRAY;
@Override
public Object nullSafeGet(final ResultSet rs, final String[] names, final Object owner) throws HibernateException, SQLException {
Array array = rs.getArray(names[0]);
Integer[] javaArray = (Integer[]) array.getArray();
return ArrayUtils.toPrimitive(javaArray);
}
@Override
public void nullSafeSet(final PreparedStatement statement, final Object object, final int i) throws HibernateException, SQLException {
Connection connection = statement.getConnection();
int[] castObject = (int[]) object;
Integer[] integers = ArrayUtils.toObject(castObject);
Array array = connection.createArrayOf("integer", integers);
statement.setArray(i, array);
}
@Override
public Object assemble(final Serializable cached, final Object owner) throws HibernateException {
return cached;
}
@Override
public Object deepCopy(final Object o) throws HibernateException {
return o == null ? null : ((int[]) o).clone();
}
@Override
public Serializable disassemble(final Object o) throws HibernateException {
return (Serializable) o;
}
@Override
public boolean equals(final Object x, final Object y) throws HibernateException {
return x == null ? y == null : x.equals(y);
}
@Override
public int hashCode(final Object o) throws HibernateException {
return o == null ? 0 : o.hashCode();
}
@Override
public boolean isMutable() {
return false;
}
@Override
public Object replace(final Object original, final Object target, final Object owner) throws HibernateException {
return original;
}
@Override
public Class<int[]> returnedClass() {
return int[].class;
}
@Override
public int[] sqlTypes() {
return new int[] { SQLTYPE };
}
}
Solution 4 - Java
This has been tested against string arrays. Maybe some modifications in the converter is required for numeric arrays. This works with Spring JPA.
-
add
PostgreSQLTextArray
to your projectimport java.sql.ResultSet; import java.sql.SQLException; import java.util.Arrays; import java.util.Map;
/**
- This is class provides {@link java.sql.Array} interface for PostgreSQL
text
array. - @author Valentine Gogichashvili
*/
public class PostgreSQLTextArray implements java.sql.Array {
private final String[] stringArray; private final String stringValue; /** * Initializing constructor * @param stringArray */ public PostgreSQLTextArray(String[] stringArray) { this.stringArray = stringArray; this.stringValue = stringArrayToPostgreSQLTextArray(this.stringArray); } @Override public String toString() { return stringValue; } private static final String NULL = "NULL"; /** * This static method can be used to convert an string array to string representation of PostgreSQL text array. * @param a source String array * @return string representation of a given text array */ public static String stringArrayToPostgreSQLTextArray(String[] stringArray) { final int arrayLength; if ( stringArray == null ) { return NULL; } else if ( ( arrayLength = stringArray.length ) == 0 ) { return "{}"; } // count the string length and if need to quote int neededBufferLentgh = 2; // count the beginning '{' and the ending '}' brackets boolean[] shouldQuoteArray = new boolean[stringArray.length]; for (int si = 0; si < arrayLength; si++) { // count the comma after the first element if ( si > 0 ) neededBufferLentgh++; boolean shouldQuote; final String s = stringArray[si]; if ( s == null ) { neededBufferLentgh += 4; shouldQuote = false; } else { final int l = s.length(); neededBufferLentgh += l; if ( l == 0 || s.equalsIgnoreCase(NULL) ) { shouldQuote = true; } else { shouldQuote = false; // scan for commas and quotes for (int i = 0; i < l; i++) { final char ch = s.charAt(i); switch(ch) { case '"': case '\\': shouldQuote = true; // we will escape these characters neededBufferLentgh++; break; case ',': case '\'': case '{': case '}': shouldQuote = true; break; default: if ( Character.isWhitespace(ch) ) { shouldQuote = true; } break; } } } // count the quotes if ( shouldQuote ) neededBufferLentgh += 2; } shouldQuoteArray[si] = shouldQuote; } // construct the String final StringBuilder sb = new StringBuilder(neededBufferLentgh); sb.append('{'); for (int si = 0; si < arrayLength; si++) { final String s = stringArray[si]; if ( si > 0 ) sb.append(','); if ( s == null ) { sb.append(NULL); } else { final boolean shouldQuote = shouldQuoteArray[si]; if ( shouldQuote ) sb.append('"'); for (int i = 0, l = s.length(); i < l; i++) { final char ch = s.charAt(i); if ( ch == '"' || ch == '\\' ) sb.append('\\'); sb.append(ch); } if ( shouldQuote ) sb.append('"'); } } sb.append('}'); assert sb.length() == neededBufferLentgh; return sb.toString(); } @Override public Object getArray() throws SQLException { return stringArray == null ? null : Arrays.copyOf(stringArray, stringArray.length); } @Override public Object getArray(Map<String, Class<?>> map) throws SQLException { return getArray(); } @Override public Object getArray(long index, int count) throws SQLException { return stringArray == null ? null : Arrays.copyOfRange(stringArray, (int) index, (int) index + count); } @Override public Object getArray(long index, int count, Map<String, Class<?>> map) throws SQLException { return getArray(index, count); } @Override public int getBaseType() throws SQLException { return java.sql.Types.VARCHAR; } @Override public String getBaseTypeName() throws SQLException { return "text"; } @Override public ResultSet getResultSet() throws SQLException { throw new UnsupportedOperationException(); } @Override public ResultSet getResultSet(Map<String, Class<?>> map) throws SQLException { throw new UnsupportedOperationException(); } @Override public ResultSet getResultSet(long index, int count) throws SQLException { throw new UnsupportedOperationException(); } @Override public ResultSet getResultSet(long index, int count, Map<String, Class<?>> map) throws SQLException { throw new UnsupportedOperationException(); } @Override public void free() throws SQLException { }
}
- This is class provides {@link java.sql.Array} interface for PostgreSQL
-
Add
ListToArrayConverter
to your codeimport org.postgresql.jdbc4.Jdbc4Array;
import javax.persistence.AttributeConverter; import javax.persistence.Converter; import java.sql.SQLException; import java.util.ArrayList; import java.util.List;
@Converter(autoApply = true) public class ListToArrayConveter implements AttributeConverter
- , Object> {
@Override
public PostgreSQLTextArray convertToDatabaseColumn(List
attribute) { if (attribute == null || attribute.isEmpty()) { return null; } String[] rst = new String[attribute.size()]; return new PostgreSQLTextArray(attribute.toArray(rst)); } @Override public List<String> convertToEntityAttribute(Object dbData) { List<String> rst = new ArrayList<>(); try { String[] elements = (String[]) ((Jdbc4Array) dbData).getArray(); for (String element : elements) { rst.add(element); } } catch (SQLException e) { e.printStackTrace(); } return rst; }
}
-
Use it!
@Entity @Table(name = "emails") public class Email {
[...] @SuppressWarnings("JpaAttributeTypeInspection") @Column(name = "subject", columnDefinition = "text[]") @Convert(converter = ListToArrayConveter.class) private List<String> subject; [...]
Solution 5 - Java
I was able to save a String[]
to PostgreSQL 9.4 and EclipseLink 2.6.2 via the JPA Converter approach posted here
which seems to be the source for the answer of
> Tk421 of 1st July 2016.
Loading an Array from DB also works well.
Additionally added to persistence.xml
my path to ListToArrayConverter:
<class> com.foo1.foo2.foo3.backend.jpa.convert.ListToArrayConverter </class>
Please mention that Jdbc4Array
is not present in Postgre JDBC driver anymore, please instead use:
org.postgresql.jdbc.PgArray
See here: Package org.postgresql.jdbc4 is missing since 9.4-1207
Solution 6 - Java
Here is the int[]
UserType I used to do what you're after which also includes the null checks for nullSafeGet()
and nullSafeSet()
:
import org.apache.commons.lang.ArrayUtils;
import org.hibernate.HibernateException;
import org.hibernate.engine.spi.SessionImplementor;
import org.hibernate.usertype.UserType;
import java.io.Serializable;
import java.sql.Array;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class IntegerArrayUserType implements UserType {
protected static final int SQLTYPE = java.sql.Types.ARRAY;
@Override
public Object nullSafeGet(ResultSet rs, String[] names, SessionImplementor session, Object owner) throws HibernateException, SQLException {
Array array = rs.getArray(names[0]);
if (array == null) {
return null;
}
Integer[] javaArray = (Integer[]) array.getArray();
return ArrayUtils.toPrimitive(javaArray);
}
@Override
public void nullSafeSet(PreparedStatement st, Object value, int index, SessionImplementor session) throws HibernateException, SQLException {
Connection connection = st.getConnection();
if (value == null) {
st.setNull( index, sqlTypes()[0] );
} else {
int[] castObject = (int[]) value;
Integer[] integers = ArrayUtils.toObject(castObject);
Array array = connection.createArrayOf("integer", integers);
st.setArray(index, array);
}
}
@Override
public Object assemble(final Serializable cached, final Object owner) throws HibernateException {
return cached;
}
@Override
public Object deepCopy(final Object o) throws HibernateException {
return o == null ? null : ((int[]) o).clone();
}
@Override
public Serializable disassemble(final Object o) throws HibernateException {
return (Serializable) o;
}
@Override
public boolean equals(final Object x, final Object y) throws HibernateException {
return x == null ? y == null : x.equals(y);
}
@Override
public int hashCode(final Object o) throws HibernateException {
return o == null ? 0 : o.hashCode();
}
@Override
public boolean isMutable() {
return false;
}
@Override
public Object replace(final Object original, final Object target, final Object owner) throws HibernateException {
return original;
}
@Override
public Class<int[]> returnedClass() {
return int[].class;
}
@Override
public int[] sqlTypes() {
return new int[] { SQLTYPE };
}
}