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

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

Enter your email address to follow this blog and receive notifications of new posts by email.

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

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s