How to Order by Multiple Columns with Calculations in Spring Data JPA (Without Errors)

Introduction

Ever tried using an expression like (st.totalMargin / st.totalRevenue) in your ORDER BY clause, only to have Spring Data JPA throw an error? You’re not alone.

This common issue arises from JPQL’s limitations when handling arithmetic operations in sorting. In this guide, we’ll explore why JPQL fails with calculated fields and walk through three effective solutions to order by calculations and multiple columns—without crashing your app.

Why JPQL Fails with Calculated ORDER BY Clauses

JPQL (Java Persistence Query Language) restricts certain expressions in ORDER BY, especially arithmetic operations like division. For example:

@Query("SELECT st FROM Strategy st WHERE st.unId = ?1 ORDER BY (st.totalMargin / st.totalRevenue) DESC")

This results in an IllegalArgumentException or parsing error because JPQL cannot process complex expressions inside ORDER BY.

Solution 1: Use Native SQL Queries

Native queries offer full SQL power and allow you to include any expression in ORDER BY.

@Query(value = """
    SELECT st.* FROM STRATEGY st
    WHERE st.un_id = ?1
    ORDER BY (st.total_margin / st.total_revenue) DESC
""", nativeQuery = true)
List<Strategy> findByUnIdWithRatio(Long unId);

✅ Pros:

• Supports all SQL operations and calculations.
• No JPQL syntax limitations.

⚠️ Cons:

• Tied to a specific database dialect (less portable).
• Harder to maintain with complex mappings.

Solution 2: Use @Formula for Calculated Fields

Hibernate’s @Formula allows you to define a derived field based on SQL expressions, which you can reference in JPQL just like a regular field.

@Entity
public class Strategy {

    @Formula("total_margin / total_revenue")
    private Double marginRatio;

    public Double getMarginRatio() {
        return marginRatio;
    }
}

Then use it in JPQL:

@Query("SELECT st FROM Strategy st WHERE st.unId = ?1 ORDER BY st.marginRatio DESC")
List<Strategy> findByUnIdWithRatio(Long unId);

✅ Pros:

• Keeps queries clean and readable.
• Works with JPQL (database-agnostic).

⚠️ Note: Ensure your DB handles division by zero gracefully or wrap it with NULLIF.

Solution 3: Sort by Multiple Columns (Including Calculated Ones)

You can combine multiple columns in the ORDER BY clause, whether you’re using native queries or @Formula.

With native query:

@Query(value = """
    SELECT st.* FROM STRATEGY st
    WHERE st.un_id = ?1
    ORDER BY (st.total_margin / NULLIF(st.total_revenue, 0)) DESC, st.created_date ASC
""", nativeQuery = true)

With @Formula:

@Query("SELECT st FROM Strategy st WHERE st.unId = ?1 ORDER BY st.marginRatio DESC, st.name ASC")

Common Pitfalls & Fixes

1. Getter Without @Formula:
   JPQL cannot use custom Java methods for sorting unless they’re mapped with @Formula.
2. Division by Zero:
   Prevent runtime errors by handling zero or null values in SQL: ORDER BY (COALESCE(st.total_margin, 0) / NULLIF(st.total_revenue, 0)) DESC

Best Practices

• ✅ Use nativeQuery only when necessary and sanitize inputs to prevent SQL injection.
• ✅ Prefer @Formula for calculated fields to maintain database independence.
• ✅ Always test edge cases like nulls or zeros in your arithmetic operations.
• ✅ Keep sorting logic close to your domain model where possible, for maintainability.

Conclusion

While JPQL has limitations, they’re easy to work around. Whether you use native SQL, Hibernate’s @Formula, or multi-column sorting, you can implement powerful, flexible ordering in Spring Data JPA without errors.

Want to optimize further? Check out our full Spring Data JPA Performance Tuning Guide 🚀

Tags: Spring Data JPA, JPQL ORDER BY error, sorting by calculated column, @Formula in Hibernate, native query JPA, Spring Boot JPA sorting, multi-column order by, JPA division error, Hibernate custom field, JPQL troubleshooting

Let me know if you’d like a code-only version or a downloadable PDF format!