Multidimensional array mapping with JPA and Hibernate

Last modified:

Imagine having a tool that can automatically detect JPA and Hibernate performance issues. Wouldn’t that be just awesome?

Well, Hypersistence Optimizer is that tool! And it works with Spring Boot, Spring Framework, Jakarta EE, Java EE, Quarkus, or Play Framework.

So, enjoy spending your time on the things you love rather than fixing performance issues in your production system on a Saturday night!

Introduction

In this article, I’m going to present the best way to map a Java multidimensional array when using JPA and Hibernate.

While one-dimensional array types have long been supported by the Hypersistence Utils open-source project, starting with the 2.9 release, you can now persist multidimensional array entity attributes with JPA and Hibernate.

Multidimensional array mapping with JPA and Hibernate. #Java https://t.co/UNotbeqHTq pic.twitter.com/q2b4BrrG2q
— Java (@java) January 21, 2020

Domain Model

Let’s assume we have a plane seat reservation application, and each plane is represented in the database using the following table:

CREATE TABLE plane (
    id INT8 NOT NULL,
    name VARCHAR(255),
    seat_grid seat_status[][], 
    PRIMARY KEY (id)
)

Notice that the seat_grid column is of the seat_status[] type. The seat_status is a PostgreSQL enum that was created like this:

CREATE TYPE seat_status 
AS ENUM (
    'UNRESERVED', 
    'RESERVED', 
    'BLOCKED'
);

And, our application uses the following Plane entity class:

Multidimensional Array JPA Plane Entity

The Plane entity mapping is done as follows.

For Hibernate 6, the mapping will look as follows:

@Entity(name = "Plane")
@Table(name = "plane")
public class Plane {
 
    @Id
    private Long id;
 
    private String name;
 
    @Type(
        value = EnumArrayType.class,
        parameters = @org.hibernate.annotations.Parameter(
            name = "sql_array_type",
            value = "seat_status"
        )
    )
    @Column(
        name = "seat_grid",
        columnDefinition = "seat_status[][]"
    )
    private SeatStatus[][] seatGrid;
 
    public Long getId() {
        return id;
    }
 
    public Plane setId(Long id) {
        this.id = id;
        return this;
    }
 
    public String getName() {
        return name;
    }
 
    public Plane setName(String name) {
        this.name = name;
        return this;
    }
 
    public SeatStatus[][] getSeatGrid() {
        return seatGrid;
    }
 
    public Plane setSeatGrid(SeatStatus[][] seatGrid) {
        this.seatGrid = seatGrid;
        return this;
    }
 
    public SeatStatus getSeatStatus(int row, char letter) {
        return seatGrid[row - 1][letter - 65];
    }
}

And for Hibernate 5, like this:

@Entity(name = "Plane")
@Table(name = "plane")
@TypeDef(name = "seat_status_array", typeClass = EnumArrayType.class)
public class Plane {

    @Id
    private Long id;

    private String name;

    @Type(
        type = "seat_status_array",
        parameters = @org.hibernate.annotations.Parameter(
            name = "sql_array_type",
            value = "seat_status"
        )
    )
    @Column(
        name = "seat_grid", 
        columnDefinition = "seat_status[][]"
    )
    private SeatStatus[][] seatGrid;

    public Long getId() {
        return id;
    }

    public Plane setId(Long id) {
        this.id = id;
        return this;
    }

    public String getName() {
        return name;
    }

    public Plane setName(String name) {
        this.name = name;
        return this;
    }

    public SeatStatus[][] getSeatGrid() {
        return seatGrid;
    }

    public Plane setSeatGrid(SeatStatus[][] seatGrid) {
        this.seatGrid = seatGrid;
        return this;
    }

    public SeatStatus getSeatStatus(int row, char letter) {
        return seatGrid[row - 1][letter - 65];
    }
}

The EnumArrayType is provided by the Hypersistence Utils project, so you don’t need to implement it.

What’s important to notice is that the seat_grid entity attribute mapping provides the SQL column type associated used by the database array. This is important since Hypersistence Utils needs to know the database type when building the PostgreSQL array that sets the seat_grid column value.

The Plane entity setters use the Fluent style API to simplify the entity-building process.

Persisting a multidimensional array with JPA and Hibernate

The current flight operates an ATR-42 twin-turboprop plane, which has 12 rows of 4 seats on each row. When persisting the associated Plane entity:

entityManager.persist(
    new Plane()
        .setId(1L)
        .setName("ATR-42")
        .setSeatGrid(
            new SeatStatus[][] {
                {
                    SeatStatus.BLOCKED, SeatStatus.BLOCKED, 
                    SeatStatus.BLOCKED, SeatStatus.BLOCKED
                },
                {
                    SeatStatus.UNRESERVED, SeatStatus.UNRESERVED, 
                    SeatStatus.RESERVED, SeatStatus.UNRESERVED
                },
                {
                    SeatStatus.RESERVED, SeatStatus.RESERVED, 
                    SeatStatus.RESERVED, SeatStatus.RESERVED
                },
                {
                    SeatStatus.RESERVED, SeatStatus.RESERVED, 
                    SeatStatus.RESERVED, SeatStatus.RESERVED
                },
                {
                    SeatStatus.RESERVED, SeatStatus.RESERVED, 
                    SeatStatus.RESERVED, SeatStatus.RESERVED
                },
                {
                    SeatStatus.RESERVED, SeatStatus.RESERVED, 
                    SeatStatus.RESERVED, SeatStatus.RESERVED
                },
                {
                    SeatStatus.RESERVED, SeatStatus.RESERVED, 
                    SeatStatus.RESERVED, SeatStatus.RESERVED
                },
                {
                    SeatStatus.RESERVED, SeatStatus.RESERVED, 
                    SeatStatus.RESERVED, SeatStatus.RESERVED
                },
                {
                    SeatStatus.RESERVED, SeatStatus.RESERVED, 
                    SeatStatus.RESERVED, SeatStatus.RESERVED
                },
                {
                    SeatStatus.RESERVED, SeatStatus.RESERVED, 
                    SeatStatus.RESERVED, SeatStatus.RESERVED
                },
                {
                    SeatStatus.RESERVED, SeatStatus.RESERVED, 
                    SeatStatus.RESERVED, SeatStatus.RESERVED
                },
                {
                    SeatStatus.BLOCKED, SeatStatus.BLOCKED, 
                    SeatStatus.BLOCKED, SeatStatus.BLOCKED
                }
            }
        )
);

Hibernate will generate the proper SQL INSERT statement:

INSERT INTO plane (
    name, 
    seat_grid, 
    id
) 
VALUES (
    'ATR-42', 
    {
        {"BLOCKED", "BLOCKED", "BLOCKED", "BLOCKED"},
        {"UNRESERVED", "UNRESERVED", "RESERVED", "UNRESERVED"},
        {"RESERVED", "RESERVED", "RESERVED", "RESERVED"},
        {"RESERVED", "RESERVED", "RESERVED", "RESERVED"},
        {"RESERVED", "RESERVED", "RESERVED", "RESERVED"},
        {"RESERVED", "RESERVED", "RESERVED", "RESERVED"},
        {"RESERVED", "RESERVED", "RESERVED", "RESERVED"},
        {"RESERVED", "RESERVED", "RESERVED", "RESERVED"},
        {"RESERVED", "RESERVED", "RESERVED", "RESERVED"},
        {"RESERVED", "RESERVED", "RESERVED", "RESERVED"},
        {"RESERVED", "RESERVED", "RESERVED", "RESERVED"},
        {"BLOCKED", "BLOCKED", "BLOCKED", "BLOCKED"}
    }, 
    1
)

Fetching a multidimensional array with JPA and Hibernate

When fetching the Plane entity, we can see that the seatGrid entity attribute is fetched properly:

Plane plane = entityManager.find(Plane.class, 1L);

assertEquals(
    "ATR-42", 
    plane.getName()
);

assertEquals(
    SeatStatus.BLOCKED, 
    plane.getSeatStatus(1, 'A')
);

assertEquals(
    SeatStatus.BLOCKED, 
    plane.getSeatStatus(1, 'B')
);

assertEquals(
    SeatStatus.BLOCKED, 
    plane.getSeatStatus(1, 'C')
);

assertEquals(
    SeatStatus.BLOCKED, 
    plane.getSeatStatus(1, 'D')
);

assertEquals(
    SeatStatus.UNRESERVED, 
    plane.getSeatStatus(2, 'A')
);

assertEquals(
    SeatStatus.UNRESERVED, 
    plane.getSeatStatus(2, 'B')
);

assertEquals(
    SeatStatus.RESERVED, 
    plane.getSeatStatus(2, 'C')
);

assertEquals(
    SeatStatus.UNRESERVED, 
    plane.getSeatStatus(2, 'D')
);

Cool, right?

I'm running an online workshop on the 20-21 and 23-24 of November about High-Performance Java Persistence.

If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.

Conclusion

While you could implement your own Hibernate Type to support arrays, the ones offered by the Hypersistence Utils project are very complex and allow storing both one-dimensional and multidimensional PostgreSQL ARRAY column types.

Follow @vlad_mihalcea