Multidimensional array mapping with JPA and Hibernate

Imagine having a tool that can automatically detect if you are using JPA and Hibernate properly. Hypersistence Optimizer is that tool!

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 Hibernate Types open-source project, starting with the 2.9 release, you can now persist multidimensional array entity attributes with JPA and Hibernate.

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:

@Entity(name = "Plane")
@Table(name = "plane")
@TypeDef(
    name = "seat_status_array", 
    typeClass = EnumArrayType.class
)
public static 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 Hibernate Types 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 Hibernate Types 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?

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 Hibernate Types project are very complex and allow storing both one-dimensional and multidimensional PostgreSQL ARRAY column types.

FREE EBOOK

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.