Introduction

Recently, I got a very good question on Twitter, and I decided that the best way to answer it is with a new article.

@vlad_mihalcea I have seen a few articles on the matter but curious of your thoughts on hibernate mapping of postgres enum data type.

— Chris Whited (@cmwhited) September 7, 2017

In this article, we are going to see how various ways to map an Enum using JPA and Hibernate.

Domain Model

Considering we have the following entity:

The Post entity has a status attribute which is an Enum, represented by the PostStatus type.

public enum PostStatus {
    PENDING,
    APPROVED,
    SPAM
}

In JPA, Enum types must be marked with the @Enumerated annotation, which can take an EnumType defining if the Java Enumeration is mapped to either a String or an Integer column type.

Mapping Enum to a String column type

To map the Enum to a String database column type, you need to specify the EnumType.STRING value when using the @Enumerated annotation.

@Entity(name = "Post")
@Table(name = "post")
public class Post {

    @Id
    private Long id;

    private String title;

    @Enumerated(EnumType.STRING)
    @Column(length = 8)
    private PostStatus status;

    //Getters and setters omitted for brevity
}

Assuming we are using PostgreSQL, on the database side, it is expected to have the following table:

CREATE TABLE post (
    id INT8 NOT NULL, 
    title VARCHAR(255), 
    status VARCHAR(8), 
    PRIMARY KEY (id)
)

Now, when inserting a Post entity:

Post post = new Post();
post.setId( 1L );
post.setTitle( "High-Performance Java Persistence" );
post.setStatus( PostStatus.PENDING );

entityManager.persist( post );

Hibernate generates the following SQL INSERT statement:

INSERT INTO post (
    status, 
    title, 
    id
) 
VALUES (
    'PENDING', 
    'High-Performance Java Persistence', 
    1
)

As expected, the String representation of the Java Enum was used to populate the associated database column value.

However, as readable as this might be for the developer, this column takes way more space than necessary. In this case, the status column takes 8 bytes. If we were to store 100 million post records, the status column alone will occupy 800 MB.

Mapping Enum to an Integer column type

As previously explained, you can also use an Integer column type. The Post entity mapping becomes as follows:

@Entity(name = "Post")
@Table(name = "post")
public class Post {

    @Id
    private Long id;

    private String title;

    @Enumerated
    @Column(columnDefinition = "smallint")
    private PostStatus status;

    //Getters and setters omitted for brevity
}

Notice that the @Enumerated column does not need to take the ORDINAL EnumType value since that’s used by default. We are also using the smallint integer column type since it is improbable that we will ever need more than 2 bytes to store all values for the Post Enum type.

As expected, on the database side, the following table will be used:

CREATE TABLE post (
    id INT8 NOT NULL, 
    title VARCHAR(255), 
    status SMALLINT, 
    PRIMARY KEY (id)
)

Now, when inserting the same Post entity, Hibernate generates the following SQL INSERT statement:

INSERT INTO post (
    status, 
    title, 
    id
) 
VALUES (
    0, 
    'High-Performance Java Persistence', 
    1
)

The Enum ordinal value, which is 0 for PostStatus.PENDING, is used to represent the Java Enum type.

Now, this is much more efficient, but it less expressive. So, how can we have both performance and readability?

Actually, it’s pretty simple. Just create a post_status_info as follows:

CREATE TABLE post_status_info (
    id SMALLINT NOT NULL, 
    description VARCHAR(255), 
    name VARCHAR(255), 
    PRIMARY KEY (id)
)

Now, you can populate to describe all PostStatu Enum values:

INSERT INTO post_status_info (
    description, 
    name, 
    id
) 
VALUES (
    'Posts waiting to be approved by the admin', 
    'PENDING', 
    0
)

INSERT INTO post_status_info (
    description, 
    name, 
    id
) 
VALUES (
    'Posts approved by the admin', 
    'APPROVED', 
    1
)

INSERT INTO post_status_info (
    description, 
    name, 
    id
) 
VALUES (
    'Posts rejected as spam', 
    'SPAM', 
    2
)

Now you can get the description by joining the post_status_info table, as illustrated by the following example:

Tuple tuple = (Tuple) entityManager
.createNativeQuery(
        "SELECT " +
        "    p.id, " +
        "    p.title, " +
        "    p.status, " +
        "    psi.name, " +
        "    psi.description " +
        "FROM post p " +
        "INNER JOIN post_status_info psi ON p.status = psi.id " +
        "WHERE p.id = :postId", Tuple.class
)
.setParameter( "postId", 1L )
.getSingleResult();

assertEquals(
    "PENDING", 
    tuple.get( "name" )
);

assertEquals(
    "Posts waiting to be approved by the admin", 
    tuple.get( "description" )
);

Cool, right?

Mapping a Java Enum to a database-specific Enumerated column type

Since we have been using PostgreSQL, let’s use the dedicated Enum column type.

First, we need to create a new PostgreSQL enum type to mirror the Java PostStatus enumeration:

CREATE TYPE post_status_info AS ENUM (
    'PENDING', 
    'APPROVED', 
    'SPAM'
)

Now, to map this custom PostgreSQL type, we can no longer use the default Hibernate-specific org.hibernate.type.EnumType because PostgreSQL expects an Object type, not a VARCHAR or an INT.

Luckily, we can easily get the PostgreSQLEnumType from the Hypersistence Utils project.

First, you need to add the following dependency to your project:

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

For more info about which dependency to use based on the Hibernate version you are using, check out the Hypersistence Utils GitHub repository.

That’s it. Now, we can use the PostgreSQLEnumType as follows.

For Hibernate 6, the mapping will look as follows:

@Entity(name = "Post")
@Table(name = "post")
public class Post {

    @Id
    private Long id;

    private String title;

    @Enumerated(EnumType.STRING)
    @Column(columnDefinition = "post_status_info")
    @Type(PostgreSQLEnumType.class)
    private PostStatus status;

    //Getters and setters omitted for brevity
}

And for Hibernate 5, like this:

@Entity(name = "Post")
@Table(name = "post")
@TypeDef(name = "pgsql_enum", typeClass = PostgreSQLEnumType.class)
public class Post {

    @Id
    private Long id;

    private String title;

    @Enumerated(EnumType.STRING)
    @Column(columnDefinition = "post_status_info")
    @Type(type = "pgsql_enum")
    private PostStatus status;

    //Getters and setters omitted for brevity
}

When inserting the Post entity, our JDBC statement interceptor will see the enum as a String, although in the database a dedicated Enum type is going to be used:

INSERT INTO post (
    status, 
    title, 
    id
) 
VALUES (
    'PENDING', 
    'High-Performance Java Persistence', 
    1
)

I'm running an online workshop on the 11th of October about High-Performance SQL.

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

Conclusion

As I explained in my book, the choice of database types can have a significant impact on application performance, and Enum types are no different.

Although you might be tempted to use the STRING EnumType, in reality, this is the least efficient option. So, if you have hundreds of millions of entries, it’s better to use either the ORDINAL EnumType or the database-specific type. PostgreSQL uses 4 bytes for an Enumerated type, therefore the ORDINAL, which can use a SMALLINT is the most compact option. Although the MySQL Enum takes either 1 or 2 bytes, the MySQL Enum type has many downsides.

However, there are downsides to using ORDINAL as well. In case you need to reorder enum values or add/remove/rename entries, the STRING or the database-specific type will require a minimum amount of effort. On the other hand, removing the first element from ann Enum will require to shift all entries in case you are using ORDINAL.

Therefore, it’s all about trade-offs, so choose wisely.

Insert details about how the information is going to be processed

DOWNLOAD NOW