Mapping PostgreSQL Array With Hibernate

1. Overview

PostgreSQL supports arrays of any type (built-in or user-defined) to be defined as types of columns of a table. In this tutorial, we’ll explore a few ways to map the PostgreSQL array with Hibernate.

2. Basic Setup

As a pre-requisite to connect with a PostgreSQL database, we should add the latest postgresql Maven dependency to our pom.xml along with the Hibernate configurations. Also, let’s create an entity class called User with the String array roles:

@Entity
public class User {
    @Id
    private Long id;
    private String name;

    private String[] roles;

    //getters and setters 
}

3. Custom Hibernate Types

Hibernate supports custom types to map a user-defined type into SQL queries. Therefore, we can create custom types to map a PostgreSQL array with Hibernate for storing/fetching data. First, let’s create the CustomStringArrayType class implementing Hibernate’s UserType class to provide a custom type to map the String array:

public class CustomStringArrayType implements UserType<String[]> {
    @Override
    public int sqlType() {
        return Types.ARRAY;
    }

    @Override
    public Class returnedClass() {
        return String[].class;
    }

    @Override
    public String[] nullSafeGet(ResultSet rs, int position, SharedSessionContractImplementor session,
      Object owner) throws SQLException {
        Array array = rs.getArray(position);
        return array != null ? (String[]) array.getArray() : null;
    }

    @Override
    public void nullSafeSet(PreparedStatement st, String[] value, int index, 
      SharedSessionContractImplementor session) throws SQLException {
       if (st != null) {
           if (value != null) {
               Array array = session.getJdbcConnectionAccess().obtainConnection()
                 .createArrayOf("text", value);
               st.setArray(index, array);
           } else {
              st.setNull(index, Types.ARRAY);
           }
        }
     }
    //implement equals, hashCode, and other methods 
}

Here, we should note that the return type of the returnedClass method is the String array. Also, the nullSafeSet method creates an array of PostgreSQL type text.

4. Mapping Array With Custom Hibernate Types

4.1. User Entity

Then, we’ll use the CustomStringArrayType class to map the String array roles to the PostgreSQL text array:

@Entity
public class User {
    //...

    @Column(columnDefinition = "text[]")
    @Type(value = com.baeldung.hibernate.arraymapping.CustomStringArrayType.class)
    private String[] roles;
  
   //getters and setters 
}

That’s it! We’re ready with our custom type implementation and array mapping to perform CRUD operations on the User entity.

4.2. Unit Test

To test our custom type, let’s first insert a User object along with the String array roles:

@Test
public void givenArrayMapping_whenArraysAreInserted_thenPersistInDB() 
  throws HibernateException, IOException {
    transaction = session.beginTransaction();

    User user = new User();
    user.setId(2L);
    user.setName("smith");

    String[] roles = {"admin", "employee"};
    user.setRoles(roles);

    session.persist(user);
    session.flush();
    session.clear();

    transaction.commit();

    User userDBObj = session.find(User.class, 2L);

    assertEquals("smith", userDBObj.getName());
    assertEquals("admin", userDBObj.getRoles()[0]);
    assertEquals(578, userDBObj.getLocations()[1]);
}

Also, we can fetch the User record that contains roles in the form of the PostgreSQL text array:

@Test
public void givenArrayMapping_whenQueried_thenReturnArraysFromDB() 
  throws HibernateException, IOException {
    User user = session.find(User.class, 2L);

    assertEquals("john", user.getName());
    assertEquals("superuser", user.getRoles()[0]);
    assertEquals("employee", user.getRoles()[1]);
    assertEquals("admin", user.getRoles()[1]);
    assertEquals(100, user.getLocations()[0]);
    assertEquals(389, user.getLocations()[1]);
    assertEquals("7000000000", user.getPhoneNumbers()[0]);
    assertEquals("8000000000", user.getPhoneNumbers()[1]);
}

4.3. CustomIntegerArrayType

Similarly, we can create a custom type for various array types supported by PostgreSQL. For instance, let’s create the CustomIntegerArrayType to map the PostgreSQL int array:

public class CustomIntegerArrayType implements UserType<Integer[]> {
    @Override
    public int sqlType() {
        return Types.ARRAY;
    }

    @Override
    public Class returnedClass() {
        return Integer[].class;
    }

    @Override
    public Integer[] nullSafeGet(ResultSet rs, int position, SharedSessionContractImplementor session, 
      Object owner) throws SQLException {
        Array array = rs.getArray(position);
        return array != null ? (Integer[]) array.getArray() : null;
    }

    @Override
    public void nullSafeSet(PreparedStatement st, Integer[] value, int index, 
      SharedSessionContractImplementor session) throws SQLException {
        if (st != null) {
          if (value != null) {
            Array array = session.getJdbcConnectionAccess().obtainConnection().createArrayOf("int", value);
            st.setArray(index, array);
          } else {
            st.setNull(index, Types.ARRAY);
          }
        }
    }

    //implement equals, hashCode, and other methods 
}

Similar to what we noticed in the CustomStringArrayType class, the return type of the returnedClass method is the Integer array. Also, the implementation of the nullSafeSet method creates an array of PostgreSQL type int. Last, we can use the CustomIntegerArrayType class to map Integer array locations to the PostgreSQL int array:

@Entity
public class User {
    //...
    
    @Column(columnDefinition = "int[]")
    @Type(value = com.baeldung.hibernate.arraymapping.CustomIntegerArrayType.class)
    private Integer[] locations;

    //getters and setters
}

5. Mapping Array With hibernate-types

On the other hand, instead of implementing a custom type for each type like String, Integer, and Long, we can use the hibernate-types library developed by a renowned Hibernate expert, Vlad Mihalcea.

5.1. Setup

First, we’ll add the latest hibernate-types-60 Maven dependency to our pom.xml:

<dependency>
    <groupId>com.vladmihalcea</groupId>
    <artifactId>hibernate-types-60</artifactId>
    <version>2.21.1</version>
</dependency>

5.2. User Entity

Next, we’ll add the integration code in the User entity to map the String array phoneNumbers:

@Entity
public class User {
    //...
    @Type(StringArrayType.class)
    @Column(
        name = "phone_numbers",
        columnDefinition = "text[]"
    )
    private String[] phoneNumbers;

    //getters and setters
}

Here, similar to the custom type CustomStringArrayType, we’ve used the StringArrayType class, provided by the hibernate-types library, as a mapper for the String array. Similarly, we can find a few other handy mappers like DateArrayType, EnumArrayType, and DoubleArrayType in the library.

5.3. Unit Test

That’s it! We’re ready with the array mapping using the hibernate-types library. Let’s update the already discussed unit test to verify the insert operation:

@Test
public void givenArrayMapping_whenArraysAreInserted_thenPersistInDB() 
  throws HibernateException, IOException {
    transaction = session.beginTransaction();
    
    User user = new User();
    user.setId(2L);
    user.setName("smith");
    
    String[] roles = {"admin", "employee"};
    user.setRoles(roles);
    
    String[] phoneNumbers = {"7000000000", "8000000000"};
    user.setPhoneNumbers(phoneNumbers);
    
    session.persist(user);
    session.flush();
    session.clear();
    
    transaction.commit();
}

Similarly, we can verify the read operation:

@Test
public void givenArrayMapping_whenQueried_thenReturnArraysFromDB() 
  throws HibernateException, IOException {
    User user = session.find(User.class, 2L);

    assertEquals("john", user.getName());
    assertEquals("superuser", user.getRoles()[0]);
    assertEquals("admin", user.getRoles()[1]);
    assertEquals(100, user.getLocations()[0]);
    assertEquals(389, user.getLocations()[1]);
    assertEquals("7000000000", user.getPhoneNumbers()[0]);
    assertEquals("8000000000", user.getPhoneNumbers()[1]);
}

6. Conclusion

In this article, we explored mapping the PostgreSQL array with Hibernate. First, we created a custom type to map the String array using Hibernate’s UserType class. Then, we used the custom type to map the PostgreSQL text array with Hibernate. Last, we used the hibernate-types library to map the PostgreSQL array.