Mastering Hibernate @Formula: Real-World Spring Boot 3 Cases & Best Practices

Introduction

When using JPA and Hibernate, developers often need to express complex queries or derived values directly in entity mappings. Repeating such queries or embedding them in the service layer leads to verbose and hard‑to‑maintain code. Hibernate offers the @Formula annotation, which allows an SQL expression to be embedded directly in an entity field.

What is @Formula?

The annotation is useful for derived values, aggregates, or conditional expressions that do not need a separate column but are frequently accessed in application logic. It keeps the derived logic close to the domain model.

It works well for mathematical calculations, string manipulation, date arithmetic, and table aggregations, but is not suitable for writable fields, complex Java business logic, or when the application must be database‑agnostic.

Practical Cases

2.1 Basic Usage

The annotation takes a single‑value SQL expression. Example: calculating a discounted price.

@Entity
@Table(name = "t_book")
public class Book {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String title;
    private String isbn;
    private String description;
    private Integer page;
    private BigDecimal price;

    // Use @Formula to calculate discounted price
    @Formula("price * 0.9")
    private BigDecimal discountedPrice;
    // ...
}

When a Book entity is loaded, Hibernate adds the formula to the SELECT clause. Example SQL:

select b1_0.id,
       b1_0.description,
       b1_0.price * 0.9,
       b1_0.isbn,
       b1_0.page,
       b1_0.price,
       b1_0.title
from t_book b1_0
limit ?, ?

2.2 Advanced Usage

Advanced @Formula can use subqueries, aggregates, and complex SQL to compute values such as total amount, item count, and average price for an order.

@Entity
@Table(name = "x_order")
public class Order {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String orderNo;
    private LocalDateTime orderDate = LocalDateTime.now();

    @Formula("(SELECT COALESCE(SUM(oi.quantity * oi.price), 0) FROM x_order_items oi WHERE oi.order_id = id)")
    private BigDecimal totalAmount;

    @Formula("(SELECT COUNT(*) FROM x_order_items oi WHERE oi.order_id = id)")
    private Integer itemCount;

    @Formula("(SELECT COALESCE(AVG(oi.price), 0) FROM x_order_items oi WHERE oi.order_id = id)")
    private BigDecimal averageItemPrice;

    @OneToMany(cascade = CascadeType.ALL, fetch = FetchType.EAGER, mappedBy = "order")
    private Set<OrderItem> items = new HashSet<>();
}

@Entity
@Table(name = "x_order_items")
public class OrderItem {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    @Column(nullable = false)
    private Integer quantity;
    @Column(nullable = false)
    private BigDecimal price;
    @JsonIgnore
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "order_id", nullable = false)
    private Order order;
}

Generated SQL includes subqueries for each @Formula field.

select o1_0.id,
       (SELECT COALESCE(AVG(oi.price), 0) FROM x_order_items oi WHERE oi.order_id = o1_0.id),
       (SELECT COUNT(*) FROM x_order_items oi WHERE oi.order_id = o1_0.id),
       o1_0.order_date,
       o1_0.order_no,
       (SELECT COALESCE(SUM(oi.quantity * oi.price), 0) FROM x_order_items oi WHERE oi.order_id = o1_0.id)
from x_order o1_0

2.3 Other Scenarios

Aggregate values :

@Formula("(select count(o.id) from orders o where o.customer_id = id)")

Conditional fields :

@Formula("(case when status = 'ACTIVE' then true else false end)")

Derived attributes : @Formula("concat(first_name, ' ', last_name)") Cross‑table calculations :

@Formula("(select coalesce(sum(p.amount), 0) from payments p where p.customer_id = id)")

Business flags :

@Formula("(case when balance < 0 then true else false end)")

2.4 Performance Impact & Best Practices

Performance considerations : Each @Formula is evaluated when the entity is loaded; complex subqueries on large datasets can degrade performance.

Read‑only nature : @Formula fields cannot be updated; attempts to persist changes are ignored.

Database‑specific SQL : The expression ties the entity to the current database dialect, reducing portability.

Debugging complexity : Since the SQL is generated by Hibernate, tracing issues may be less straightforward than with explicit queries.

The article also announces that the Spring Boot 3 practical case collection PDF has been updated to 181 examples, with a promise of permanent free updates for subscribers.