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.

The employee table has to rows:

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

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.

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.

Insert details about how the information is going to be processed

DOWNLOAD NOW