Mapping array with Hibernate

Question

Can you please help me to map this class using Hibernate?

public class MyClass{
    private Long id;
    private String name;
    private int[] values;
    ...
}

I'm using PostgreSQL and the column type in the table is integer[] How my array should be mapped?

Goodell

Answer 1

I have never mapped arrays to hibernate. I always use collections. So, I have slightly changed you class:

public class MyClass{
    private Long id;
    private String name;
    private List<Integer> values;

    @Id
    // this is only if your id is really auto generated
    @GeneratedValue(strategy=GenerationType.AUTO) 
    public Long getId() {
        return id;
    }

    @OneToMany(cascade=CascadeType.ALL, fetch=FetchType.LAZY)
    public List<Integer> getValues() {
        return values;
    }   
    ...

Laverne

It doesn't work. Caused by: org.hibernate.AnnotationException: Use of @OneToMany or @ManyToMany targeting an unmapped class – Sarpedon

how come this answer has been accepted ? You have to use @ElementCollection or create a custom type. – Postdiluvian

When using OneToMany you can only use list with other entities. So primatives wont work. As @AleksBenMaza said use ElementCollection with non mapped elements – Cystotomy

@Laverne I need to map my multidimensional double array? what to do please suggest. – Hudibrastic

Answer 2

Hibernate (and JPA) can't directly map the PostgreSQL array type. See this question for how to proceed if you really need to retain your database structure as it is. This thread has an example of the required custom type.

If you can change your schema, you can let hibernate create an additional table to handle the collection - List<Integer>. Then, depending on the version of hibernate you are using:

• JPA 2.0 compliant - use @ElementCollection
• JPA 1.0 compliant - use @CollectionOfElements

Hosier

Will those annotations create a one-to-many association automatically? – Carriole

Answer 3

Maven dependency

The first thing you need to do is to set up the following Hypersistence Utils Maven dependency in your project pom.xml configuration file:

<dependency>
    <groupId>io.hypersistence</groupId>
    <artifactId>hypersistence-utils-hibernate-62</artifactId>
    <version>${hypersistence-utils.version}</version>
</dependency>

PostgreSQl ARRAY columns

Assuming you have this table in your PostgreSQL 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")
public class Event {

    @Id
    private Long id;
 
    @Type(StringArrayType.class)
    @Column(
        name = "sensor_names", 
        columnDefinition = "text[]"
    )
    private String[] sensorNames;
 
    @Type(IntArrayType.class)
    @Column(
        name = "sensor_values", 
        columnDefinition = "integer[]"
    )
    private int[] sensorValues;
 
    //Getters and setters omitted for brevity
}

The StringArrayType and IntArrayType are classes offered by the Hypersistence Utils 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
)

Horvath

Your examples work perfectly fine for Insert/Update. Can you give an example to fetch two types of arrays in single query? – Scolecite

I tried, but I am getting only those values whose properties I set. And I am unable to transform the resultset using sqlresultsetmapping – Scolecite

Does your library supports Spring-Boot 2.2.4 Release? When I upgrade it, I am getting this exception Caused by: java.util.ServiceConfigurationError: com.fasterxml.jackson.databind.Module: Provider com.fasterxml.jackson.module.jaxb.JaxbAnnotationModule not found – Scolecite

Answer 4

Hibernate can map only the primitive types. Check under the org.hibernate.type folder of hibernate jar package. int array is not one of them. So you would have to write a custom type that can implement the UserType interface.

public class MyClass{
     private Long id;
     private String name;
     private Integer[] values;

     @Type(type = "com.usertype.IntArrayUserType")
     public Integer[] getValues(){
         return values;
     }

     public void setValues(Integer[] values){
         this.values = values;
     }
 }

IntArrayUserType.class

package com.usertype.IntArrayUserType;

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 };
}

When you query for the MyClass entity you can add something like this:

Type intArrayType = new TypeLocatorImpl(new TypeResolver()).custom(IntArrayUserType.class);
Query query = getSession().createSQLQuery("select values from MyClass")
   .addScalar("values", intArrayType);
List<Integer[]> results = (List<Integer[]>) query.list();

Messick

Do you use this in production? Does this work for you? – Casual

Yes it is working in production. I have copied this snippet from my working code – Messick

Answer 5

Update to the given answers:

With Hibernate 6.x mapping an array is as simple as:

    import org.hibernate.annotations.Array;


    @Array(length = 12)
    public double[] monthlyPayments = new double[12];

And if you're using Liquibase, then you can define this column as follows:

<column name="monthlyPayments" type="float[12]" />

This will map into a float[12] array in PostgreSql.

Spunky

Answer 6

From Hibernate 6.1 Final, basic arrays and collections may now be mapped to database ARRAY types if possible, or alternatively JSON/XML types.

https://in.relation.to/2022/06/14/orm-61-final/

Descriptive