Hibernate Subquery with @Formula annotation

@Formula allows you to add a computed field to the data model that is not stored in the database but is generated by an SQL expression each time a query is made. Let's look at how to use @Formula, it and discuss its limitations, potential issues, and scenarios where it is most effective.

Hibernate Sub Query with @Formula annotation

Hi, this is Paul, and welcome to this article, we explore how to use @Formula annotation to define subquery in entity mapping.

Usage Example

As an example, let’s take the Customer class, which represents a client. We will add a field that displays the full name, consisting of the first and last names:

import jakarta.persistence.*;
import lombok.*;
import org.hibernate.annotations.Formula;

import java.util.Objects;

@Entity
@Getter
@Setter
@Builder
@ToString
@NoArgsConstructor
@AllArgsConstructor
@Table(name = "customers")
public class Customer {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(name = "first_name")
    private String firstName;

    @Column(name = "last_name")
    private String lastName;

    @Formula("concat(first_name, ' ', last_name)")
    private String fullName;

    // equals and hashCode
}

And SQL table:

CREATE TABLE customers
(
    id         BIGSERIAL PRIMARY KEY,
    first_name VARCHAR(255) NOT NULL,
    last_name  VARCHAR(255) NOT NULL
);

How Hibernate Handles @Formula

@Formula incorporates the SQL expression directly into the main SQL query that Hibernate generates to retrieve entity data. This means that if you request to retrieve an object or a list of objects, Hibernate will generate an SQL query that includes the @Formula expressions for each entity in a single SQL query.

Example of a generated SQL query:

SELECT id,
       first_name,
       last_name,
       concat(first_name, ' ', last_name) AS fullName
FROM customer;

In the case of using subqueries in @Formula, for example:

@Formula("(SELECT count(*) FROM orders o WHERE o.customer_id = id)")
private int orderCount;

The SQL query will include this subquery for each row (N+1), which could potentially lead to performance issues:

SELECT id,
       first_name,
       last_name,
       (SELECT count(*)
        FROM orders o
        WHERE o.customer_id = id) as orderCount
FROM customer;

When Using @Formula Is a Good Choice

• Adding additional data without changing the database structure
• Calculating a field based on other data in the same table
• Conditional formatting or classification of data
• Extracting data from complex or related queries
• Creating adaptive data models for different business rules

Conclusion

@Formula offers unique opportunities for integrating computed fields into the data model, but its use should be carefully considered. By weighing the advantages and potential drawbacks, you can effectively integrate this tool into your development, enhancing the flexibility and functionality of applications using Hibernate.

Thank you for reading until the end. Before you go:

• Please consider clapping and following the writer! 👏
• Follow us on Twitter(X), LinkedIn

Paul Ravvich