How to order entity subclasses by their class type using JPA and Hibernate

(Last Updated On: January 4, 2018)

Introduction

One of my blog readers asked me a very good question, and so I decided to turn the answer it into a blog post.

In this article, we are going to see how to order entity subclasses when executing a JPQL 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;

    //Gettters and setters omitted for brevity
}

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

    private String content;

    //Gettters and setters omitted for brevity
}

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

    @Temporal(TemporalType.TIMESTAMP)
    private Date validUntil;

    //Gettters 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;

    //Gettters 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 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 SINGLE_TABLE inheritance strategy with an INTEGER discriminator column type.

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

To provide a specific discriminator column, we need to supply a @DiscriminatorClass 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;

    //Gettters and setters omitted for brevity
}

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

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.

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.

Subscribe to our Newsletter

* indicates required
10 000 readers have found this blog worth following!

If you subscribe to my newsletter, you'll get:
  • A free sample of my Video Course about running Integration tests at warp-speed using Docker and tmpfs
  • 3 chapters from my book, High-Performance Java Persistence, 
  • a 10% discount coupon for my book. 
Get the most out of your persistence layer!

Advertisements

4 thoughts on “How to order entity subclasses by their class type using JPA and Hibernate

  1. Thanks Vlad for answering my question. The JOINED inheritance was the tricky one in case of ordering. That’s interesting that the explicit ORDER BY CASE with type() comparison actually guarantees the order does not depend on how the entity classes get loaded. Thanks.

  2. Could we introduce a common virtual column with @Formula overridden in each subclass that would act as explicit discriminator? It may not be optimal solution but I’m curious if it is doable.

Leave a Reply

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