How to order entity subclasses by their class type using 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, we are going to see how to order entity subclasses when executing a JPA query with Hibernate.

Domain Model

Assuming you have the following entity relationship model:

And, considering we have persisted the following entities:

Board board = new Board();
board.setName("Hibernate");

entityManager.persist(board);

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

Ordering entities

Now, the requirement is too order entities as follows:

List<Topic> topics = entityManager
.createQuery(
    "select t " +
    "from Topic t " +
    "where t.board = :board " +
    "order by t.class", Topic.class)
.setParameter("board", board)
.getResultList();

assertEquals(2, topics.size());
assertTrue(topics.get(0) instanceof Announcement);
assertTrue(topics.get(1) instanceof Post);

@Inheritance(strategy = InheritanceType.SINGLE_TABLE)

The mapping for the SINGLE_TABLE inheritance looks as follows:

@Entity(name = "Topic")
@Table(name = "topic")
@Inheritance(
    strategy = InheritanceType.SINGLE_TABLE
)
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
}

@Entity(name = "Post")
public class Post extends Topic {

    private String content;

    //Getters and setters omitted for brevity
}

@Entity(name = "Announcement")
public class Announcement extends Topic {

    @Temporal(TemporalType.TIMESTAMP)
    private Date validUntil;

    //Getters and setters omitted for brevity
}

By default, for SINGLE_TABLE inheritance, a discriminator column DTYPE will be used to differentiate between subclasses.

Hence, when executing the ORDER BY JPQL query, Hibernate the following SQL query:

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_,
       t.validUntil AS validUnt7_1_,
       t.DTYPE AS DTYPE1_1_
FROM topic t
WHERE t.board_id=?
ORDER BY t.DTYPE

Therefore, we get the expected results.

However, if we override the SINGLE_TABLE inheritance @DiscriminatorColumn strategy and provide a specific @DiscriminatorValue for each entity class, then the ordering will not follow the class name, but the one provided explicitly during entity mapping.

@Inheritance(strategy = InheritanceType.JOINED)

When using the JOINED inheritance strategy, the entity mappings looks as follows:

@Entity(name = "Topic")
@Table(name = "topic")
@Inheritance(strategy = InheritanceType.JOINED)
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
}

The table diagram for the JOINED inheritance strategy looks like this:

Since, this time, there is no discriminator column, our ORDER BY query will not be guaranteed to work and Hibernate generates the following SQL query:

SELECT t.id AS id1_3_,
       t.board_id AS board_id5_3_,
       t.createdOn AS createdO2_3_,
       t.owner AS owner3_3_,
       t.title AS title4_3_,
       t1_.content AS content1_2_,
       t2_.validUntil AS validUnt1_0_,
       CASE
           WHEN t1_.id IS NOT NULL THEN 1
           WHEN t2_.id IS NOT NULL THEN 2
           WHEN t.id IS NOT NULL THEN 0
       END AS clazz_
FROM topic t
LEFT OUTER JOIN post t1_ ON t.id=t1_.id
LEFT OUTER JOIN announcement t2_ ON t.id=t2_.id
WHERE t.board_id=?
ORDER BY 
    CASE
        WHEN t1_.id IS NOT NULL THEN 1
        WHEN t2_.id IS NOT NULL THEN 2
        WHEN t.id IS NOT NULL THEN 0
    END

The virtual discriminator (e.g. clazz_) is assigned during bootstrap and it’s based on how Hibernate entity classes got loaded. Although we can arrange the entity declarations so that the org.hibernate.mapping.Subclass.subclassId matches the entity class name in alphabetical order, this is not very reliable in the long run.

However, you can declare your own ORDER BY clause and provide a specific ordering criteria, as illustrated by the following example:

List<Topic> topics = entityManager
.createQuery(
    "select t " +
    "from Topic t " +
    "where t.board = :board " +
    "order by " +
    "   case " +
    "   when type(t) = Announcement then 10" +
    "   when type(t) = Post then 20 " +
    "   end", Topic.class)
.setParameter("board", board)
.getResultList();

When running the JPQL query above, Hibernate will generate the following SQL statement:

SELECT t.id AS id1_3_,
       t.board_id AS board_id5_3_,
       t.createdOn AS createdO2_3_,
       t.owner AS owner3_3_,
       t.title AS title4_3_,
       t1_.content AS content1_2_,
       t2_.validUntil AS validUnt1_0_,
       CASE
           WHEN t1_.id IS NOT NULL THEN 1
           WHEN t2_.id IS NOT NULL THEN 2
           WHEN t.id IS NOT NULL THEN 0
       END AS clazz_
FROM topic t
LEFT OUTER JOIN post t1_ ON t.id=t1_.id
LEFT OUTER JOIN announcement t2_ ON t.id=t2_.id
WHERE t.board_id=?
ORDER BY 
    CASE
        WHEN 
            CASE
                WHEN t1_.id IS NOT NULL THEN 1
                WHEN t2_.id IS NOT NULL THEN 2
                WHEN t.id IS NOT NULL THEN 0
            END = 2 
        THEN 10
        WHEN 
            CASE
                WHEN t1_.id IS NOT NULL THEN 1
                WHEN t2_.id IS NOT NULL THEN 2
                WHEN t.id IS NOT NULL THEN 0
            END = 1 
        THEN 20
    END

You can also use the same trick when you want to order entity subclasses by their entity name when using the SINGLE_TABLE inheritance strategy with an INTEGER discriminator column type.

@Inheritance(strategy = InheritanceType.JOINED) with @DiscriminatorColumn

To provide a specific discriminator column, we need to supply a @DiscriminatorColumn on the superclass entity mapping declaration:

@Entity(name = "Topic")
@Table(name = "topic")
@Inheritance(strategy = InheritanceType.JOINED)
@DiscriminatorColumn
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
}


Now, the base class table will contain a `DTYPE` column just like the `SINGLE_TABLE` entity inheritance strategy:

<a href="https://vladmihalcea.com/wp-content/uploads/2017/12/joinedwithdiscriminatorcolumn.png"><img src="https://vladmihalcea.com/wp-content/uploads/2017/12/joinedwithdiscriminatorcolumn.png" alt="Joined inheritance with discriminator column" class="alignnone size-large wp-image-8553" /></a>

So, when executing the ORDER BY JPQL query, Hibernate will generate the following SQL query:


SELECT t.id AS id2_3_,
       t.board_id AS board_id6_3_,
       t.createdOn AS createdO3_3_,
       t.owner AS owner4_3_,
       t.title AS title5_3_,
       t1_.content AS content1_2_,
       t2_.validUntil AS validUnt1_0_,
       t.DTYPE AS DTYPE1_3_
FROM topic t
LEFT OUTER JOIN post t1_ ON t.id=t1_.id
LEFT OUTER JOIN announcement t2_ ON t.id=t2_.id
WHERE t.board_id=?
ORDER BY t.DTYPE

Although normally you'd never need to use a @DiscriminatorColumn with the JOINED inheritance strategy, in this case, it is the only way to make sure you can order the entity subclasses by their class name.

Nevertheless, this implies adding a new column in the base class table which has to use the default STRING-based discriminator strategy.

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 demonstrated many times on my blog, Hibernate is very flexible when it comes to addressing various data access requirements. In this article, you have seen that even the JOINED inheritance strategy can use a @DiscriminatorColumn as well as the use of CASE expressions based on the entity type.

Follow @vlad_mihalcea