How to map a @ManyToOne association to the result of an SQL query

Question

We are working on a migration project and we are migrating from HBM files to annotations.

We are facing repeated column mapping issue when I try to set a value on a property, which basically is an instance of the same class.

public class Salary{

    // All the below are coming from lookup table like empLookUp, MonthLookup, 
    // YearLookup, CurrencyLookUp and they are joined using their primary key

    private int empId;
    private int month;
    private int year;
    private String currency;

    // Issue here: previousMonthSalary actually needs to be populated when the 
    // Salary is loaded, but for previous month. How do I achieve this.

    private Salary previousMonthSalary;

}

How to map the previousMonthSalary?

Answer 1

Most likely, you need to use the @JoinFormula annotation:

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