Joining Entities by a Range - Hibernate

Question

I have a question regarding Hibernate that's been bothering me for some time. Consider I have two entities:

@Entity
public class Statement {
    @Id
    @Column
    private int id;

    @Column
    private Date startDate;

    @Column
    private Date endDate;

    @OneToMany
    // @JoinFormula???
    private List<Transaction> transactions;

    ...
}

@Entity
public class Transaction {
    @Id
    @Column
    private int id;

    @Column
    private Date transactionDate;

    ...
}

As you can see, a Statement consists of multiple Transactions.

Reasonably, the Statement should contain only Transactions that occur between the statement's startDate and endDate. This would obviously be dead simple in native SQL (BETWEEN), but I haven't seen a good solution for mapping this to entities in Hibernate without a hacky @JoinFormula involving a subquery. Is there a way to do this I don't know about? If a @JoinFormula is my only option, can I avoid a subquery?

Answer 1

@FilterDef and @Filter might be the solution for you.

You can define a filter using @FilterDef.

@FilterDef(name = "transactionDateFilter", parameters = {
    @ParamDef(name = "startDate", type = "date"),
    @ParamDef(name = "endDate", type = "date")
})

As shown in the code above, you can specify the filter's name and the names and types of the parameters to be used in the filter.

After that, you can apply the filter using @Filter.

@Filter(name = "transactionDateFilter", condition = "transactionDate between :startDate and :endDate")

You can specify the filter's name and conditions as follows. The conditions are written similarly to SQL's WHERE clause.

@Component
@RequiredArgsConstructor
public class FilterManager {

    private final EntityManager entityManager;

    public void enableFilter(String filterName, String paramName, Object paramValue) {
        Session session = entityManager.unwrap(Session.class);
        org.hibernate.Filter filter = session.enableFilter(filterName);
        filter.setParameter(paramName, paramValue);
    }

    public void disableFilter(String filterName) {
        Session session = entityManager.unwrap(Session.class);
        session.disableFilter(filterName);
    }
}

Once you enable the filter, it will be applied correctly.

---

With the above filter, you can include only the transactions that fall between startDate and endDate.

Full Code

@FilterDef(name = "transactionDateFilter", parameters = {
    @ParamDef(name = "startDate", type = "date"),
    @ParamDef(name = "endDate", type = "date")
})
@Entity
public class Statement {
    @Id
    @Column
    private int id;

    @Column
    private Date startDate;

    @Column
    private Date endDate;

    @OneToMany(mappedBy = "statement")
    @Filter(name = "transactionDateFilter", condition = "transactionDate between :startDate and :endDate")
    private List<Transaction> transactions;

}

have a great day - kevin