How to map a JPA @ManyToOne relationship to a SQL query using the Hibernate @JoinFormula annotation
Imagine having a tool that can automatically detect JPA and Hibernate performance issues. Wouldn’t that be just awesome?
Well, Hypersistence Optimizer is that tool! And it works with Spring Boot, Spring Framework, Jakarta EE, Java EE, Quarkus, or Play Framework.
So, enjoy spending your time on the things you love rather than fixing performance issues in your production system on a Saturday night!
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 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.
And there is more!
You can earn a significant passive income stream from promoting all these amazing products that I have been creating.
If you're interested in supplementing your income, then join my affiliate program.
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.
