@Subselect Annotation in Hibernate

1. Introduction

In this tutorial, we’ll review the @Subselect annotation in Hibernate, how to use it, and its benefits. We’ll also see Hibernate’s constraints on entities annotated as @Subselect and their consequences.

2. @Subselect Annotation Overview

@Subselect allows us to map an immutable entity to the SQL query. So let’s unroll this explanation a bit, starting from what entity to SQL query mapping even means.

2.1. Mapping To SQL Query

Normally, when we create our entities in Hibernate, we annotate them with @Entity. This annotation indicates that this is an entity, and it should be managed by persistence context. We may, optionally, provide @Table annotation as well to indicate to what table Hibernate should map this entity exactly. So, by default, whenever we create an entity in Hibernate, it assumes that an entity is mapped directly to a particular table. In most cases, that is exactly what we want, but not always.

Sometimes, our entity is not directly mapped into a particular table in DB but is a result of a SQL query execution. As an example, we might have a Client entity, where each instance of this entity is a row in a ResultSet of a SQL query (or SQL view) execution:

SELECT 
  u.id        as id,
  u.firstname as name,
  u.lastname  as lastname,
  r.name      as role
FROM users AS u
INNER JOIN roles AS r 
ON r.id = u.role_id
WHERE u.type = 'CLIENT'

The important thing is that there might be no dedicated clients table in DB at all. So that’s what mapping an entity into a SQL query means – we fetch entities from a sub-select SQL query, not from a table. This query may select from any tables and perform any logic within – Hibernate doesn’t care.

2.2. Immutability

So, we may have an entity that is not mapped to a particular table. As a direct consequence, it is unclear how to perform any INSERT/UPDATE statements. There is simply no clients (as in the example above) table that we can just insert records to.

Indeed, Hibernate doesn’t have a clue about what kind of SQL we execute to retrieve the data. Therefore, Hibernate cannot do any write operations on such an entity – it becomes read-only. The tricky thing here is that we can still ask Hibernate to insert this entity, but it will fail since it is impossible (at least according to ANSI SQL) to issue an INSERT into the sub-select.

3. Usage Example

Now, once we understand what @Subselect annotation does, let’s try to get our hands dirty and try to use it. Here, we have a simple RuntimeConfiguration:

@Data
@Entity
@Immutable
// language=sql
@Subselect(value = """
    SELECT
      ss.id,
      ss.key,
      ss.value,
      ss.created_at
    FROM system_settings AS ss
    INNER JOIN (
      SELECT
        ss2.key as k2,
        MAX(ss2.created_at) as ca2
      FROM system_settings ss2
      GROUP BY ss2.key
    ) AS t ON t.k2 = ss.key AND t.ca2 = ss.created_at
    WHERE ss.type = 'SYSTEM' AND ss.active IS TRUE
    """)
public class RuntimeConfiguration {
    @Id
    private Long id;

    @Column(name = "key")
    private String key;

    @Column(name = "value")
    private String value;

    @Column(name = "created_at")
    private Instant createdAt;
}

This entity represents a runtime parameter of our application. But, to just return the set of the up-to-date parameters that belong to our application, we need to execute a specific SQL query over system_settings table. As we can see, @Subselect annotation’s body contains that SQL statement. Now, because each RuntimeConfiguration entry is essentially a key value pair, we might want to implement a simple query – fetch the most recent active RuntimeConfiguration record that has a specific key.

Please also note that we marked our entity with @Immutable. Hibernate will, therefore, disable any dirty check tracking for our entity to avoid accidental UPDATE statements.

So, if we want to fetch RuntimeConfiguration with a specific key, we can do something like this:

@Test
void givenEntityMarkedWithSubselect_whenSelectingRuntimeConfigByKey_thenSelectedSuccessfully() {
    String key = "config.enabled";
    CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();

    CriteriaQuery<RuntimeConfiguration> query = criteriaBuilder.createQuery(RuntimeConfiguration.class);

    var root = query.from(RuntimeConfiguration.class);

    RuntimeConfiguration configurationParameter = entityManager
      .createQuery(query.select(root).where(criteriaBuilder.equal(root.get("key"), key))).getSingleResult();

    Assertions.assertThat(configurationParameter.getValue()).isEqualTo("true");
}

Here, we’re using Hibernate Criteria API to query RuntimeConfiguration by key. Now, let’s check what query Hibernate actually produces in order to fulfill our request:

select
    rc1_0.id,
    rc1_0.created_at,
    rc1_0.key,
    rc1_0.value 
from
    ( SELECT
        ss.id,
        ss.key,
        ss.value,
        ss.created_at 
    FROM
        system_settings AS ss 
    INNER JOIN
        (   SELECT
            ss2.key as k2,     MAX(ss2.created_at) as ca2   
        FROM
            system_settings ss2   
        GROUP BY
            ss2.key ) AS t 
            ON t.k2 = ss.key 
            AND t.ca2 = ss.created_at 
    WHERE
        ss.type = 'SYSTEM' 
        AND ss.active IS TRUE  ) rc1_0 
where
    rc1_0.key=?

As we can see, Hibernate just selects records from the SQL statement provided in @Subselect. Now, each filter that we provide will be applied to a resulting subselect records set.

4. Alternatives

Experienced Hibernate developers might notice that there are already some ways to achieve a similar result. One of these is using projection mapping into a DTO, and another is view mapping. Those two have their pros and cons. Let’s discuss them one by one.

4.1. Projection Mapping

So, let’s talk about DTO projections a bit. It allows the SQL queries to be mapped into a DTO projection that is not an entity. It is also considered faster to work with DTO projections than with entities. DTO projections are also immutable, which means Hibernate doesn’t manage such entities and doesn’t apply any dirty checks on them.

Though all of the above is true, DTO projections themselves have limitations. One of the most important is that DTO projections do not support associations. This is pretty obvious since we’re dealing with a DTO projection, which is not a managed entity. That makes DTO projections fast in Hibernate, but it also implies that Persistence Context does not manage these DTOs. So, we cannot have any OneToX or ManyToX fields on DTOs.

However, if we’re mapping an entity to an SQL statement, we’re still mapping an entity. It might have managed associations. So, this constraint is not applicable to entity-to-query mapping. 

Another significant, essential, and conceptual difference is that @Subselect allows us to represent the entity as an SQL query. Hibernate will do what the annotation name suggests. It’ll just use the provided SQL query to select from it (so our query becomes a sub-select) and then apply additional filters. So, let’s assume that to fetch entity X, we need to perform some filtering, grouping, etc.. Then, if we use DTO projections, we would always have to write filters, groupings, etc., down in each JPQL or native query. When using @Subselect, we can specify this query once and select from it.

4.2. View Mapping

Although this is not widely known, Hibernate can map our entities to SQL views out of the box. That is very similar regarding entity to SQL query mapping. The view itself is almost always read-only in DB. Some exceptions exist in different RDBMS, like simple views in PostgreSQL, but that is entirely vendor-specific. It implies that our entities are also immutable, and we’ll just read from the underlying view, we won’t update/insert any data.

In general, the difference between @Subselect and entity-to-view mapping is very small. The former uses the exact SQL statement that we provide in annotation, while the latter uses an existing view. Both approaches support managed associations, so choosing one of these options entirely depends on our requirements.

5. Conclusion

In this article, we discussed how to use @Subselect to select entities not from a particular table but from a sub-select query. This is very convenient if we do not want to duplicate the same parts of SQL statements to fetch our entities. But this implies that our entities that use @Subselect are de-facto immutable, and we should not try to persist them from the application code. There are some alternatives to @Subselect, for instance, entity to view mapping in Hibernate or even DTO projection usage. They both have their advantages and disadvantages, therefore in order to make a choice we need to, as always, comply to requirements and common sense.

As always, the source code is available over on GitHub.