Mapping Arrays with Hibernate
Take your skills to the next level!
The Persistence Hub is the place to be for every Java developer. It gives you access to all my premium video courses, monthly Java Persistence News, monthly coding problems, and regular expert sessions.
Arrays are a common data type in Java and part of the SQL-99 standard. So, it should be easy and quite common to use them with Hibernate, right?
No, they aren’t. Most developers try to avoid using arrays in their persistence layers. There are several reasons for that:
- Not all DBMS support arrays. Because of that, Hibernate doesn’t support native database array types.
- Mappings as a @ElementCollection are not very efficient. Most developers prefer to model a many-to-many or one-to-many association.
- Hibernate can’t offer lazy-loading for the elements of an array. That makes it a bad data type for any associations.
Avoid it for @ElementCollection
I explained @ElementCollection in a previous article, so I keep this part short. The elements of an @ElementCollection get mapped to a separate database table, but they don’t have a separate lifecycle and identity. They are part of the entity. That makes them easy to use, but it also forces Hibernate to always read and write the @ElementCollection together with all other attributes. This often creates performance issues.
Let’s take a look at an example of a Book entity. Each Book belongs to 1 or more topics. The names of these topics are modeled as an @ElementCollection of type String[]. In the table model, the topic names get persisted in the book_topics table.
@Entity
public class Book {
@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE)
private Long id;
private String title;
@ElementCollection
@OrderColumn(name = "pos")
private String[] topics;
...
}
Whenever you fetch a Book entity from the database, Hibernate will need to execute an additional query to get the elements of the String[] topics attributes. And if you change any attribute of a Book entity, Hibernate will remove all of its records from the book_topics table and insert a new record for each element in the topics attribute.
Avoid it for Associations
Another mapping you should avoid is the mapping of a to-many association as an array. The main reason for that is that Hibernate doesn’t support lazy loading for it. Because an array has a fixed size, Hibernate has to initialize the association when it instantiates the entity object. That often causes lots of unnecessary database queries and slows down your application.
When mapping the association as a java.util.List or java.util.Set, you can use FetchType.LAZY to tell Hibernate not to fetch the association until you use it in your business code. This almost always provides the best performance and avoids n+1 select issues.
Map it as a BINARY type
OK, let’s take a look at one of the 2 ways you can efficiently use an array in your mapping. By default, Hibernate maps a Java array of any type to a BINARY type in the database.
@Entity
public class Book {
@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE)
private Long id;
private String title;
private String[] topics;
...
}
This mapping works well if you only store and read the attribute in your business code. But it provides only minimal support for queries. Because the attribute is mapped as a BINARY type, you can compare the full array that’s stored in the database field with the value of a bind parameter.
In the example of this article, a book on the topics “Java Persistence” and “Hibernate” will only get returned by the query if I search for books with exactly these 2 topics.
Book b = new Book();
b.setTitle("Hibernate Tips");
b.setTopics(new String[]{"Java Persistence", "Hibernate"});
em.persist(b);
TypedQuery<Book> q = em.createQuery("SELECT b FROM Book b WHERE b.topics = :topics", Book.class);
q.setParameter("topics", new String[]{"Java Persistence", "Hibernate"});
Assert.assertEquals(q.getResultList().size(), 1);
But if I try to select all books on the topic “Hibernate”, the query doesn’t return any books on multiple topics, even though one of them is “Hibernate”.
Book b = new Book();
b.setTitle("Hibernate Tips");
b.setTopics(new String[]{"Java Persistence", "Hibernate"});
em.persist(b);
TypedQuery<Book> q = em.createQuery("SELECT b FROM Book b WHERE b.topics = :topics", Book.class);
q.setParameter("topics", new String[]{"Hibernate"});
Assert.assertEquals(q.getResultList().size(), 0);
That makes the mapping as a binary type a bad fit for most applications.
Map it as a Native Database Array
I wrote at the beginning of this article that Hibernate can’t map a Java array as a native database array. But you might want to use it anyways because, in contrast to the mapping as a binary type, you can search within a database array. You then need to provide a custom UserType implementation and reference this type in your entity mapping.
Creating and Using a UserType
The UserType interface might look intimidating because it requires you to implement several methods. But for the typical array types, this isn’t too complex.
public class PostgreSqlStringArrayType implements UserType {
@Override
public int[] sqlTypes() {
return new int[]{Types.ARRAY};
}
@Override
public Class returnedClass() {
return String[].class;
}
@Override
public boolean equals(Object x, Object y) throws HibernateException {
if (x instanceof String[] && y instanceof String[]) {
return Arrays.deepEquals((String[])x, (String[])y);
} else {
return false;
}
}
@Override
public int hashCode(Object x) throws HibernateException {
return Arrays.hashCode((String[])x);
}
@Override
public Object nullSafeGet(ResultSet rs, String[] names, SharedSessionContractImplementor session, Object owner)
throws HibernateException, SQLException {
Array array = rs.getArray(names[0]);
return array != null ? array.getArray() : null;
}
@Override
public void nullSafeSet(PreparedStatement st, Object value, int index, SharedSessionContractImplementor session)
throws HibernateException, SQLException {
if (value != null && st != null) {
Array array = session.connection().createArrayOf("text", (String[])value);
st.setArray(index, array);
} else {
st.setNull(index, sqlTypes()[0]);
}
}
@Override
public Object deepCopy(Object value) throws HibernateException {
String[] a = (String[])value;
return Arrays.copyOf(a, a.length);
}
@Override
public boolean isMutable() {
return false;
}
@Override
public Serializable disassemble(Object value) throws HibernateException {
return (Serializable) value;
}
@Override
public Object assemble(Serializable cached, Object owner) throws HibernateException {
return cached;
}
@Override
public Object replace(Object original, Object target, Object owner) throws HibernateException {
return original;
}
}
The most important methods are sqlTypes(), returnedClass(), nullSafeGet(ResultSet rs, String[] names, SharedSessionContractImplementor session, Object owner) and nullSafeSet(PreparedStatement st, Object value, int index, SharedSessionContractImplementor session).
The first 2 methods return the SQL and the Java type supported by this UserType implementation.
The nullSafeSet method sets your Java array as a native database array on the PreparedStatement. When you implement this method, you have to call the setNull value on your PreparedStatement if the Java array is null.
The nullSafeGet method gets a java.sql.Array object from the ResultSet and retrieves its values as a Java array. Please keep in mind that the database column might contain no value. In that case, the java.sql.Array is null, and you need to handle it accordingly.
After you implemented your UserType, you need to reference it in your entity mapping. You can do that by annotating your entity attribute with Hibernate’s @Type annotation and setting the fully qualified class name as the value of its type attribute.
@Entity
public class Book {
@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE)
private Long id;
private String title;
@Column(columnDefinition = "text[]")
@Type(type = "com.thorben.janssen.PostgreSqlStringArrayType")
private String[] topics;
...
}
Searching in a Database Array
The main benefit of mapping your attribute to a native database array is its additional query capabilities.
Using PostgreSQL and a native SQL query, you can use the ANY function in your WHERE clause to retrieve all records containing a specific value as one of the array elements. So, the following query returns all Book entities which have the value “Hibernate” as one of the values in the String[] topics attribute.
Book b = new Book();
b.setTitle("Hibernate Tips");
b.setTopics(new String[]{"Java Persistence", "Hibernate"});
em.persist(b);
Query q = em.createNativeQuery("SELECT * FROM Book b WHERE :topics = ANY(b.topics)", Book.class);
q.setParameter("topics", "Hibernate");
Assert.assertEquals(q.getResultList().size(), 1);
Conclusion
If you want to use a Java array in your domain model, you can choose between the mapping as a binary type and a native database array.
I prefer mapping it as a native database array because it allows you to search within the array. Hibernate doesn’t support this mapping out of the box. You need to provide a custom UserType implementation that tells Hibernate how to handle the Java and the native database array.
After you implemented your UserType, you can use native SQL queries to search for specific values within your array
