How to map Java and SQL arrays with JPA and Hibernate

Introduction

Hibernate custom Types allow you to map all sorts of database specific column types, like IP address, JSON columns, bit sets or SQL arrays.

There are two ways to define a custom Hibernate Type:

The latter option is preferred since it allows you to better split the Java-to-JDBC and the JDBC-to-SQL type handling. In this article, we are going to see how you can map SQL arrays to their Java counterpart.

You don’t have to create all these types manually, you can simply get them via Maven Central using the following dependency:

<dependency>
    <groupId>com.vladmihalcea</groupId>
    <artifactId>hibernate-types-52</artifactId>
    <version>1.0.0</version>
</dependency>

For more info, check out the hibernate-types open-source project.

SQL to JPA mapping

Considering we have the following table in our PostgreSQL database schema:

create table event (
    id int8 not null, 
    version int4, 
    sensor_names text[], 
    sensor_values integer[], 
    primary key (id)
)

We want to map this table using JPA and Hibernate. However, neither JPA nor Hibernate support SQL arrays by default, and we want to map these arrays to a String and int Java arrays respectively.

The JPA mapping for this database table looks as follows:

@Entity(name = "Event")
@Table(name = "event")
public 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
}

StringArrayType

The StringArrayType extends AbstractSingleColumnStandardBasicType and defines the SQL and Java Type descriptors:

public class StringArrayType
        extends AbstractSingleColumnStandardBasicType<String[]>
        implements DynamicParameterizedType {

    public StringArrayType() {
        super( 
            ArraySqlTypeDescriptor.INSTANCE, 
            StringArrayTypeDescriptor.INSTANCE 
        );
    }

    public String getName() {
        return "string-array";
    }

    @Override
    protected boolean registerUnderJavaType() {
        return true;
    }

    @Override
    public void setParameterValues(Properties parameters) {
        ((StringArrayTypeDescriptor) 
            getJavaTypeDescriptor())
            .setParameterValues(parameters);
    }
}

IntArrayType

The IntArrayType also extends AbstractSingleColumnStandardBasicType and defines the SQL and Java Type descriptors:

public class IntArrayType
        extends AbstractSingleColumnStandardBasicType<int[]>
        implements DynamicParameterizedType {

    public IntArrayType() {
        super( 
            ArraySqlTypeDescriptor.INSTANCE, 
            IntArrayTypeDescriptor.INSTANCE 
        );
    }

    public String getName() {
        return "int-array";
    }

    @Override
    protected boolean registerUnderJavaType() {
        return true;
    }

    @Override
    public void setParameterValues(Properties parameters) {
        ((IntArrayTypeDescriptor) 
            getJavaTypeDescriptor())
            .setParameterValues(parameters);
    }
}

Array Types class diagram

Before going into details about how each component works internally, it’s worth to see the classes involved when mapping a custom Hibernate Type using Java and SQL descriptors:

ArraySqlTypeDescriptor

As you can see from the aforementioned Hibernate Types, both share the same SqlTypeDescriptor which is used for handling the JDBC-to-SQL type mapping:

public class ArraySqlTypeDescriptor 
    implements SqlTypeDescriptor {

    public static final ArraySqlTypeDescriptor INSTANCE = 
        new ArraySqlTypeDescriptor();

    @Override
    public int getSqlType() {
        return Types.ARRAY;
    }

    @Override
    public boolean canBeRemapped() {
        return true;
    }

    @Override
    public <X> ValueBinder<X> getBinder(
        JavaTypeDescriptor<X> javaTypeDescriptor) {
        return new BasicBinder<X>( javaTypeDescriptor, this) {
            @Override
            protected void doBind(
                    PreparedStatement st, 
                    X value, 
                    int index, 
                    WrapperOptions options
                ) throws SQLException {
                
                AbstractArrayTypeDescriptor<Object> abstractArrayTypeDescriptor = 
                    (AbstractArrayTypeDescriptor<Object>) 
                        javaTypeDescriptor;
                    
                st.setArray( 
                    index, 
                    st.getConnection().createArrayOf(
                        abstractArrayTypeDescriptor.getSqlArrayType(),
                        abstractArrayTypeDescriptor.unwrap( 
                            value, 
                            Object[].class, 
                            options 
                        )
                    )
                );
            }

            @Override
            protected void doBind(
                    CallableStatement st, 
                    X value, 
                    String name, 
                    WrapperOptions options
                ) throws SQLException {
                throw new UnsupportedOperationException( 
                    "Binding by name is not supported!" 
                );
            }
        };
    }

    @Override
    public <X> ValueExtractor<X> getExtractor(
        final JavaTypeDescriptor<X> javaTypeDescriptor) {
        return new BasicExtractor<X>(javaTypeDescriptor, this) {
            @Override
            protected X doExtract(
                    ResultSet rs, 
                    String name, 
                    WrapperOptions options
                ) throws SQLException {
                return javaTypeDescriptor.wrap(
                    rs.getArray(name), 
                    options
                );
            }

            @Override
            protected X doExtract(
                    CallableStatement statement, 
                    int index, 
                    WrapperOptions options
                ) throws SQLException {
                return javaTypeDescriptor.wrap(
                    statement.getArray(index), 
                    options
                );
            }

            @Override
            protected X doExtract(
                    CallableStatement statement, 
                    String name, 
                    WrapperOptions options
                ) throws SQLException {
                return javaTypeDescriptor.wrap(
                    statement.getArray(name), 
                    options
                );
            }
        };
    }
}

Basically, the ArraySqlTypeDescriptor instructs Hibernate how to handle SQL arrays using the JDBC Statement.setArray and ResultSet.

StringArrayTypeDescriptor

StringArrayTypeDescriptor is rather straightforward since the bulk of logic is encapsulated in AbstractArrayTypeDescriptor. StringArrayTypeDescriptor defines the expected Java type (e.g. String[]) and dthe underlying database array type (e.g. text).

public class StringArrayTypeDescriptor
        extends AbstractArrayTypeDescriptor<String[]> {

    public static final StringArrayTypeDescriptor INSTANCE = 
        new StringArrayTypeDescriptor();

    public StringArrayTypeDescriptor() {
        super( String[].class );
    }

    @Override
    protected String getSqlArrayType() {
        return "text";
    }
}

IntArrayTypeDescriptor

The same goes for IntArrayTypeDescriptor since it defines the expected Java type (e.g. int[]) and dthe underlying database array type (e.g. integer).

public class IntArrayTypeDescriptor
        extends AbstractArrayTypeDescriptor<int[]> {

    public static final IntArrayTypeDescriptor INSTANCE = 
        new IntArrayTypeDescriptor();

    public IntArrayTypeDescriptor() {
        super( int[].class );
    }

    @Override
    protected String getSqlArrayType() {
        return "integer";
    }
}

AbstractArrayTypeDescriptor

The bulk of Java-to-JDBC type handling is included in the AbstractArrayTypeDescriptor base class:

public abstract class AbstractArrayTypeDescriptor<T>
        extends AbstractTypeDescriptor<T> 
        implements DynamicParameterizedType {

    private Class<T> arrayObjectClass;

    @Override
    public void setParameterValues(Properties parameters) {
        arrayObjectClass = ( (ParameterType) parameters
            .get( PARAMETER_TYPE ) )
            .getReturnedClass();

    }

    public AbstractArrayTypeDescriptor(Class<T> arrayObjectClass) {
        super( 
            arrayObjectClass, 
            (MutabilityPlan<T>) new MutableMutabilityPlan<Object>() {
                @Override
                protected T deepCopyNotNull(Object value) {
                    return ArrayUtil.deepCopy( value );
                }
            } 
        );
        this.arrayObjectClass = arrayObjectClass;
    }

    @Override
    public boolean areEqual(Object one, Object another) {
        if ( one == another ) {
            return true;
        }
        if ( one == null || another == null ) {
            return false;
        }
        return ArrayUtil.isEquals( one, another );
    }

    @Override
    public String toString(Object value) {
        return Arrays.deepToString((Object[]) value);
    }

    @Override
    public T fromString(String string) {
        return ArrayUtil.fromString(
            string, 
            arrayObjectClass
        );
    }

    @SuppressWarnings({ "unchecked" })
    @Override
    public <X> X unwrap(
            T value, 
            Class<X> type, 
            WrapperOptions options
        ) {
        return (X) ArrayUtil.wrapArray( value );
    }

    @Override
    public <X> T wrap(
            X value, 
            WrapperOptions options
        ) {
        if( value instanceof Array ) {
            Array array = (Array) value;
            try {
                return ArrayUtil.unwrapArray( 
                    (Object[]) array.getArray(), 
                    arrayObjectClass 
                );
            }
            catch (SQLException e) {
                throw new IllegalArgumentException( e );
            }
        }
        return (T) value;
    }

    protected abstract String getSqlArrayType();
}

AbstractArrayTypeDescriptor relies on ArrayUtil to handle the Java array deep copying, wrapping and unwrapping logic.

Testing time

When persisting these two Event 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 statement:

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
)

Great! Now, we can also update the Event entities too:

Event event = entityManager.find(Event.class, 1L);

assertArrayEquals( 
    new String[] {
        "Temperature", 
        "Pressure"
    }, 
    event.getSensorNames() 
);
assertArrayEquals( 
    new int[] {
        12, 
        756
    }, 
    event.getSensorValues() 
);

event.setSensorNames(
    new String[] {
        "Temperature",
        "Pressure",
        "Volume"
    } );
event.setSensorValues(
    new int[] {
        12,
        756,
        76
    }
);

And Hibernate is going to generate the following SQL UPDATE statement:

UPDATE 
    event 
SET 
    version = 1, 
    sensor_names = {"Temperature","Pressure","Volume"}, 
    sensor_values = {"12","756","76"} 
WHERE 
    id = 1 
AND 
    version = 0

Since the Hibernate array types are mutable, we can even update them without overriding the array object:

Event event = entityManager.find(Event.class, 1L);

event.getSensorValues()[2] = 84;

Hibernate generating the appropriate UPDATE SQL statement:

UPDATE 
    event 
SET 
    version = 2, 
    sensor_names = {"Temperature","Pressure","Volume"}, 
    sensor_values = {"12","756","84"} 
WHERE 
    id = 1 
AND 
    version = 1

That’s it!

If you enjoyed this article, I bet you are going to love my book as well.

Conclusion

While JPA defines the AttributeConverter interface, the standard converters are way too limited when it comes to mapping arrays or JSON types.

Because Hibernate types allow you to control the underlying JDBC type handling, it’s possible to map SQL array types to Java arrays.

Code available on GitHub.

Enter your email address to follow this blog and receive notifications of new posts by email.

Advertisements

4 thoughts on “How to map Java and SQL arrays with JPA and Hibernate

  1. I am not sure, but may be the text of the excpetion “Binding by index is not supported!” should be corrected to “Binding by name is not supported!”

  2. Could you elaborate a bit on the following statement?

    “The latter option is preferred since it allows you to better split the Java-to-JDBC and the JDBC-to-SQL type handling.”

    Is there something one can do with the latter approach that I can’t do with the former approach? Is there some performance aspect one should be aware of?

    A year ago I used the UserType approach for an integer array mapping, and even though I feel it was a bit simpler than the approach showed here, it still contained a fair amount of code that I didn’t understand (specifically implementing the UserType interface).

    Do you know if there are any plans to simplify this process or perhaps to bundle mappings for more popular types? As it is both approaches require a fairly deep knowledge of the inner workings of Hibernate.

    1. Just try to implement the String and Binary JSON types with UserType and you’ll see what I’m talking about.

      There’s no performance impact of one or the other approaches, it’s just a matter of design.

      Implementing the UserType API is less intuitive than the pproach with Type descriptors. Just because it involves more code, it does not mean it’s more difficult.

      There’s already a simplified approach, like the JPA Attribute Converter. But simplicity has its price, and you won’t be able to do complex mappings (e.g. JSON, arrays) with it.

      The reason why Hibernate Types are superior is because you can control the JDBC part entirely. Coming up with a new Type is not a day-to-day activity, and you’ll need to know the API to make it work effectively. But that’s the case for both UserType (which you already said that you had problems knowing how to implement all those methods) or the Type descriptors.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s