The best way to map the @DiscriminatorColumn with JPA and Hibernate

Introduction

As previously explained, the SINGLE_TABLE inheritance is the most efficient entity inheritance strategy.

However, for JPQL query such as this one:

List<Post> posts = entityManager
.createQuery(
    "select p " +
    "from Post p " +
    "where p.board = :board", Post.class)
.setParameter("board", board)
.getResultList();

Hibernate generates a SQL query which filters by the associated discriminator column (e.g. DTYPE by default):

SELECT t.id AS id2_1_,
       t.board_id AS board_id8_1_,
       t.createdOn AS createdO3_1_,
       t.owner AS owner4_1_,
       t.title AS title5_1_,
       t.content AS content6_1_
FROM   topic t
WHERE  t.DTYPE = 'Post'
       AND t.board_id = 1

So, because we are filtering by the discriminator column, we might want to index it or include it to speed up queries.

However, the default STRING DiscriminatorType expects a VARCHAR column that must hold the longest entity subclass name. For the Announcement class, we need at least 12 bytes to store the entity class name while for the Post entity, 4 bytes are required.

If the discriminator type column is indexed and we store 1 million Announcement and 100 million Post entities, the index will require 393 MB (12 + 400 million bytes). On the other hand, if the discriminator column is a TINYINT (only 1 byte is needed to store a discriminator value), we need only 96 MB (1 + 100 million bytes).

In this article, I’m going to explain how you can get the most out of the INTEGER DiscriminatorType while still retaining the descriptiveness of the default STRING DiscriminatorType.

Domain Model

Considering we have following entities in our system:

The Topic, Post, and Announcement class hierarchy is mapped onto a single table in the database:

The Topic base class entity is mapped as follows:

@Entity(name = "Topic")
@Table(name = "topic")
@Inheritance(strategy = InheritanceType.SINGLE_TABLE)
@DiscriminatorColumn(
    discriminatorType = DiscriminatorType.INTEGER,
    name = "topic_type_id",
    columnDefinition = "TINYINT(1)"
)
public class Topic {

    @Id
    @GeneratedValue
    private Long id;

    private String title;

    private String owner;

    @Temporal(TemporalType.TIMESTAMP)
    private Date createdOn = new Date();

    @ManyToOne(fetch = FetchType.LAZY)
    private Board board;

    //Getters and setters omitted for brevity
}

Notice the @DiscriminatorColumn annotation which declares that an INTEGER discriminator is expected in the topic_type_id column which is of type TINYINT(1).

While for the default STRING discriminator, you don’t need to specify a certain value for each subclass entity since the class name is used, for INTEGER discriminator type, each subclass entity must provide a @DiscriminatorValue with a unique integer value.

The Post subclass will use the value of 1 in the topic_type_id column:

@Entity(name = "Post")
@Table(name = "post")
@DiscriminatorValue("1")
public class Post extends Topic {

    private String content;

    //Getters and setters omitted for brevity
}

While Announcement subclass will use the value of 2 in the topic_type_id column:

@Entity(name = "Announcement")
@Table(name = "announcement")
@DiscriminatorValue("2")
public class Announcement extends Topic {

    @Temporal(TemporalType.TIMESTAMP)
    private Date validUntil;

    //Getters and setters omitted for brevity
}

Testing time

When inserting one Post and an Announcement entity as well:

Post post = new Post();
post.setOwner("John Doe");
post.setTitle("Inheritance");
post.setContent("Best practices");
post.setBoard(board);

entityManager.persist(post);

Announcement announcement = new Announcement();
announcement.setOwner("John Doe");
announcement.setTitle("Release x.y.z.Final");
announcement.setValidUntil(
    Timestamp.valueOf(
        LocalDateTime.now().plusMonths(1)
    )
);
announcement.setBoard(board);

entityManager.persist(announcement);

Hibernate generates the following SQL statements:

Query:["insert into topic (board_id, createdOn, owner, title, content, topic_type_id, id) values (?, ?, ?, ?, ?, 1, ?)"], 
Params:[(1, 2017-06-02 16:30:35.963, John Doe, Inheritance, Best practices, 2)]

Query:["insert into topic (board_id, createdOn, owner, title, validUntil, topic_type_id, id) values (?, ?, ?, ?, ?, 2, ?)"], 
Params:[(1, 2017-06-02 16:30:35.974, John Doe, Release x.y.z.Final, 2017-07-02 16:30:35.98, 3)]

Notice the 1 and 2 literal values in the executed SQL INSERT statement.

Adding a description to each numeric value

Even if the INTEGER discriminator type is much more compact than its STRING counterpart, it nevertheless lacks expressivity because when you see a value of 2, you don’t automatically think that it represents an Announcement row.

So, how can we fix it?

Actually, the fix is simpler than you might think. What we need to do is to introduce a new table which holds a description for each numeric value, which will represent the table Primary Key.

We can initialize the topic_type as follows:

INSERT INTO topic_type (
    description, 
    name, 
    id
) 
VALUES (
    'Post is a subclass of the Topic base class', 
    'com.vladmihalcea.book.hpjp.hibernate.inheritance.discriminator.Post', 
    1
)

INSERT INTO topic_type (
    description, 
    name, 
    id
) 
VALUES (
    'Announcement is a subclass of the Topic base class', 
    'com.vladmihalcea.book.hpjp.hibernate.inheritance.discriminator.Announcement', 
    2
)

Now, when we need to find the type of a given topic record, you just have to join it with the topic_type table:

SELECT 
    tt.name,
    t.id,
    t.createdOn,
    t.owner,
    t.title,
    t.content,
    t.validUntil,
    t.board_id
FROM topic t
INNER JOIN topic_type tt ON t.topic_type_id = tt.id

That’s it!

If you enjoyed this article, I bet you are going to love my book as well.

Conclusion

Like any column that needs to be indexed, the discriminator type is very important to application performance, and you should always choose the most compact type available.

Although the default STRING DiscriminatorType is very convenient, it’s much better to use an INTEGER discriminator type. To mitigate the lack of expressiveness, you can add a new table to hold the description for each particular discriminator type value.

If you liked this article, you might want to subscribe to my newsletter too.

Advertisements

2 thoughts on “The best way to map the @DiscriminatorColumn with JPA and Hibernate

  1. Hi Vlad,
    I imidiatly adapted my entities after reading your article.

    But why did you declare the classes static?

    Kind regards
    Stephan

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s