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

(Last Updated On: January 4, 2018)

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:

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 static 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.

Subscribe to our Newsletter

* indicates required
10 000 readers have found this blog worth following!

If you subscribe to my newsletter, you'll get:
  • A free sample of my Video Course about running Integration tests at warp-speed using Docker and tmpfs
  • 3 chapters from my book, High-Performance Java Persistence, 
  • a 10% discount coupon for my book. 
Get the most out of your persistence layer!

Advertisements

2 thoughts on “How to map a JPA @ManyToOne relationship to a SQL query using the Hibernate @JoinFormula annotation

  1. Interesting, vlad! I’ve never used @JoinFormula before!

    One question: In this case, when Hibernate executes this @JoinFormula’s SQL does it load the whole entity or only its ID?

Leave a Reply

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