JPA CriteriaQuery with LEAST and GREATEST functions

Question

I am have a problem where i need to join two tables using the LEAST and GREATEST functions, but using JPA CriteriaQuery. Here is the SQL that i am trying to duplicate...

select * from TABLE_A a
inner join TABLE_X x on
(
  a.COL_1 = least(x.COL_Y, x.COL_Z)
  and
  a.COL_2 = greatest(x.COL_Y, x.COL_Z)
);

I have looked at CriteriaBuilder.least(..) and greatest(..), but am having a difficult time trying to understand how to create the Expression<T> to pass to either function.

Answer 1

The simplest way to compare two columns and get the least/greatest value is to use the CASE statement.

In JPQL, the query would look like

select a from EntityA a join a.entityXList x
  where a.numValueA=CASE WHEN x.numValueY <= x.numValueZ THEN x.numValueY ELSE x.numValueZ END
  and   a.numValueB=CASE WHEN x.numValueY >= x.numValueZ THEN x.numValueY ELSE x.numValueZ END 

You can code the equivalent using CriteriaBuilder.selectCase() but I've never been a big fan of CriteriaBuilder. If requirements forces you to use CriteriaBuilder then please let me know and I can try to code the equivalent.

CriteriaBuilder least/greatest is meant to get the min/max value of all the entries in one column. Let's say you want to get the Entity that had the alphabetically greatest String name. The code would look like

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery query = cb.createQuery(EntityX.class);
Root<EntityX> root = query.from(EntityX.class);

Subquery<String> maxSubQuery = query.subquery(String.class);
Root<EntityX> fromEntityX = maxSubQuery.from(EntityX.class);
maxSubQuery.select(cb.greatest(fromEntityX.get(EntityX_.nameX)));
query.where(cb.equal(root.get(EntityX_.nameX), maxSubQuery));

I created a sample Spring Data JPA app that demonstrates these JPA examples at

https://github.com/juttayaya/stackoverflow/tree/master/JpaQueryTest