How to map a 2-d matrix in Java to Hibernate/JPA?

Question

I have a legacy database I'm trying to redesign into the 21st century. One of the existing data structures involves a particular class which contains a 2-dimensional matrix of values. If I were to reverse-engineer this class from the database, I'd end up with a series of attributes like:

private BigDecimal NODE_1_MATRIX_POS_1_1;
private BigDecimal NODE_1_MATRIX_POS_1_2;

and so on. Since this is a 6x6 matrix, there are a lot of such columns.

I've been looking for a better way, but I'm not sure I'm there. What I'd like to do is something like this:

@Entity
public class TestClass {

    @Id
    private long id;

    @CollectionOfElements
    @JoinTable(
        name="MATRIX_DATA", 
        joinColumns=@JoinColumn(name="ENTRY_ID"))
    private List<List<BigDecimal>> matrix;

But this fails:

org.hibernate.MappingException: Could not determine type for: java.util.List, at table: MATRIX_DATA, for columns: [org.hibernate.mapping.Column(element)]

Rather than just trying to fix the error, I thought I'd ask around and try to find the right approach to solving this mapping challenge. Has anyone found success and satisfaction mapping multidimensional arrays via JPA?

Answer 1

Rather than just trying to fix the error, I thought I'd ask around and try to find the right approach to solving this mapping challenge. Has anyone found success and satisfaction mapping multidimensional arrays via JPA?

AFAIK, nested collections are not supported by standard JPA. The JPA wiki book has a good section on this topic (I'm quoting only a part of it):

Nested Collections, Maps and Matrices

It is somewhat common in an object model to have complex collection relationships such as a List of Lists (i.e. a matrix), or a Map of Maps, or a Map of Lists, and so on. Unfortunately these types of collections map very poorly to a relational database.

JPA does not support nested collection relationships, and normally it is best to change your object model to avoid them to make persistence and querying easier. One solution is to create an object that wraps the nested collection.

For example if an Employee had a Map of Projects keyed by a String project-type and the value a List or Projects. To map this a new ProjectType class could be created to store the project-type and a OneToMany to Project.

...

And that would be my suggestion. For example:

@Entity
public class TestClass {    
    @Id
    private long id;

    @OneToMany(mappedBy="testClass")
    private List<MatrixRow> matrix;
}

Where MatrixLine would be (omitting many details):

@Entity
public class MatrixRow {
    @Id
    private long id;

    @ManyToOne
    private TestClass testClass;

    @CollectionOfElements
    private List<BigDecimal> row;
}

Or maybe you could use a custom user type (I'm not too sure how this would work).

Or (after all, you're already using non portable annotations) have a look at this question to see how you could extend Hibernate:

• How do I map a nested collection, Map<Key,List<Values>>, with hibernate JPA annotations?

Answer 2

Hibernate Types project

You can map a PostgreSQL multidimensional array using the Hibernate Types project.

You can choose to use a Java array on the entity attribute side or use List.

Database table

For exmaple, assuming you have the following plane database table:

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

Where the seat_status is a PostgreSQL enum:

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

JPA entity

You can map the seatGrid column using the EnumArrayType:

@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;
 
    //Getters and setters omitted for brevity

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

So, you need to declare the appropriate Hibernate Type to use. For enums, you need to use the EnumArrayType:

@TypeDef(
    name = "seat_status_array",
    typeClass = EnumArrayType.class
)

The @Type annotation allows you to pass parameters to the Hibernate Type, like the SQL array class:

@Type(
    type = "seat_status_array",
    parameters = @org.hibernate.annotations.Parameter(
        name = "sql_array_type",
        value = "seat_status"
    )
)

Testing time

Now, when you persist the following Post 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
                }
            }
        )
);

Hibernate will issue 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"}
    },
    1
)

And, when fetching the entity, everything works as expected:

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'));