How to map a JPA @ManyToOne relationship to a SQL query using the Hibernate @JoinFormula annotation

Imagine having a tool that can automatically detect if you are using JPA and Hibernate properly. Hypersistence Optimizer is that tool!

Introduction

Someone asked me to answer the following StackOverflow question, and, because the question is very interesting from an SQL perspective, I decided to turn the answer it into a blog post.

In this article, we are going to see how to map a JPA @ManyToOne association to the result of a SQL query using the Hibernate-specific @JoinFormula annotation.

Domain Model

Let’s assume we have the following tables in our relational database.

Employee and salary tables

The employee table has to rows:

id name title
1 ‘Alice’ ‘CEO’
2 ‘Bob’ ‘Developer’

And, the salary table registers the monthly salary for every employee.

id year month employee_id amount_cents
1 2015 11 1 10000
2 2015 11 2 7000
3 2015 12 1 11000
4 2015 12 2 7500
5 2016 1 1 11500
6 2016 1 2 7900
7 2016 2 1 11900
8 2016 2 2 8500

Finding the previous month salary using SQL

As stated in the StackOverflow question, we want to figure out what was the last month salary for any given salary. This can be expressed in SQL using the following query:

private Long getPreviousSalaryId(long salaryId) {
    return doInJPA( entityManager -> {

        Salary salary = entityManager.find(
            Salary.class, 
            salaryId
        );

        Number prevSalaryId = (Number) entityManager
        .createNativeQuery(
            "SELECT prev_salary.id " +
            "FROM salary prev_salary " +
            "WHERE " +
            "   prev_salary.employee_id = :employeeId AND " +
            "   ( CASE WHEN :month = 1 " +
            "       THEN prev_salary.year + 1 = :year AND " +
            "            prev_salary.month = 12 " +
            "       ELSE prev_salary.year = :year AND " +
            "            prev_salary.month + 1 = :month " +
            "   END ) = true ")
        .setParameter("employeeId", salary.getEmployee().getId())
        .setParameter("year", salary.getYear())
        .setParameter("month", salary.getMonth())
        .getSingleResult();

        return prevSalaryId.longValue();
    } );
}

We can validate that this query method works fine by giving the following assert statements:

assertEquals(Long.valueOf(1L), getPreviousSalaryId(3L));
assertEquals(Long.valueOf(2L), getPreviousSalaryId(4L));
assertEquals(Long.valueOf(3L), getPreviousSalaryId(5L));
assertEquals(Long.valueOf(4L), getPreviousSalaryId(6L));
assertEquals(Long.valueOf(5L), getPreviousSalaryId(7L));
assertEquals(Long.valueOf(6L), getPreviousSalaryId(8L));

Using @JoinFormula to map the query to a @ManyToOne relationship

Now, we can map the previous month salary using the @JoinFormula annotation:

@Entity(name = "Salary")
@Table(name = "salary")
public class Salary {

    @Id
    private Long id;

    @ManyToOne(fetch = FetchType.LAZY)
    private Employee employee;

    private int month;

    private int year;

    @Column(name = "amount_cents")
    private long amountCents;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinFormula(
        "( " +
        "   SELECT prev_salary.id " +
        "   FROM salary prev_salary " +
        "   WHERE " +
        "       prev_salary.employee_id = employee_id AND " +
        "       ( " +
        "           CASE WHEN month = 1 " +
        "           THEN prev_salary.year + 1 = year AND " +
        "                prev_salary.month = 12 " +
        "           ELSE prev_salary.year = year AND " +
        "                prev_salary.month + 1 = month " +
        "           END " +
        "       ) = true " +
        ")"
    )
    private Salary previousMonthSalary;

    //Getters and setters omitted for brevity
}

Testing time

Now, we can validate if the mapping works as expected:

assertEquals(
    Long.valueOf(1L),
    entityManager.find(Salary.class, 3L)
        .getPreviousMonthSalary().getId()
);

assertEquals(
    Long.valueOf(2L),
    entityManager.find(Salary.class, 4L)
        .getPreviousMonthSalary().getId()
);

assertEquals(
    Long.valueOf(3L),
    entityManager.find(Salary.class, 5L)
        .getPreviousMonthSalary().getId()
);

assertEquals(
    Long.valueOf(4L),
    entityManager.find(Salary.class, 6L)
        .getPreviousMonthSalary().getId()
);

assertEquals(
    Long.valueOf(5L),
    entityManager.find(Salary.class, 7L)
        .getPreviousMonthSalary().getId()
);

assertEquals(
    Long.valueOf(6L),
    entityManager.find(Salary.class, 8L)
        .getPreviousMonthSalary().getId()
);

And, it does! Cool, right?

If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.

Seize the deal! 50% discount. Seize the deal! 50% discount.

Conclusion

As demonstrated, the Hibernate-specific @JoinFormula annotation allows you to map complex entity relationships, beyond the capabilities of the JPA specification.

So, when you need to map the result of a SQL query to a @ManyToOne association, @JoinFormula ill allows you to do that.

FREE EBOOK

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.