Mastering JPA with @Formula, @SQLRestriction, and @Filter in Spring Boot 3

1. Introduction

When using Spring Boot with JPA, developers often encounter scattered query logic, duplicated calculation logic, and hard‑coded data‑permission filters, which cause code bloat, high maintenance cost, and tight coupling between business logic and database operations.

2. Solution

The three Hibernate annotations @Formula , @SQLRestriction (or @Where ) and @Filter directly address these issues.

@Formula

Maps a derived property to a native SQL expression, eliminating the need for redundant fields. Example:

<code>private String name;
private BigDecimal price;

@Formula("(concat(name, '/', price))")
private String info;
</code>

When queried, the generated SQL includes concat(name, '/', price) as a selected column.

It can also embed sub‑queries, e.g.:

<code>@Formula("(select sum(s.sale_price * s.quantity) from sales_detail s where s.product_id = id)")
private BigDecimal salePrice;
</code>

The resulting SQL selects the computed value alongside other columns.

@SQLRestriction

Adds a static native SQL condition to the entity or collection. Example adds deleted = 0 to every query on Product :

<code>@Entity
@Table(name = "product")
@SQLRestriction("deleted = 0")
public class Product {
    // ... other fields
    @Column(columnDefinition = "int default 0")
    private Integer deleted;
}
</code>

The generated SQL automatically contains WHERE (deleted = 0) . The annotation can also be placed on collection mappings.

@Filter

Provides a parameterized, session‑level filter. Define the filter:

<code>@FilterDef(name = "filterByDeletedAndStock",
    parameters = {
        @ParamDef(name = "state", type = Integer.class),
        @ParamDef(name = "stock", type = Integer.class)
    })
@Filters({
    @Filter(name = "filterByDeletedAndStock",
            condition = "deleted = :state and stock > :stock")
})
public class Product {
    // ... fields
}
</code>

Enable it at runtime (e.g., via an AOP aspect) and set parameters to dynamically add the condition:

<code>@Component
@Aspect
public class FilterAspect {
    @PersistenceContext
    private EntityManager entityManager;

    @Around("@annotation(com.pack.formula.annotation.EnableFilter)")
    public Object doProcess(ProceedingJoinPoint joinPoint) throws Throwable {
        try {
            int state = 0;
            int stock = 80;
            org.hibernate.Filter filter = entityManager.unwrap(Session.class)
                .enableFilter("filterByDeletedAndStock");
            filter.setParameter("state", state);
            filter.setParameter("stock", stock);
            return joinPoint.proceed();
        } finally {
            entityManager.unwrap(Session.class).disableFilter("filterByDeletedAndStock");
        }
    }
}
</code>

When the annotated method is executed, the generated SQL includes the dynamic condition; without the annotation, the original SQL is used.

Using these three annotations can reduce code volume by up to 60 % while improving maintainability and performance.