JPA - Query with 'composite values' in whe

问题:

I want to realize the following (for this example: MySQL) statement in JPA 2.1 with Eclipselink as the implementation:

select *
from account
where (mailing_zip_code, id) > (56237, 275)
order by mailing_zip_code asc, id asc 
limit 10;

What I want to achieve is an implementation of the seek method for endless scrolling without using an offset in the query. With focus on the where clause, I am not able to find a correct name for this construct. In some places it is called 'composite value', but I am not able to find results by this name, but it seems to be compliant with the SQL-92 standard.

This statement will be extended by filter parameters, so naturally I would like to build it with the JPA criteria API.

I searched the API for some time now, does anybody now if and how this is possible?

回答1:

After realizing that

select *
from account
where (mailing_zip_code, id) > (56237, 275)
order by mailing_zip_code asc, id asc 
limit 10;

is just a shorter way to write

select *
from account
where mailing_zip_code > '56237'
or (mailing_zip_code = '56237' AND id > 276)
order by mailing_zip_code asc, id asc 
limit 10;

the criteria query was not that hard after all (appended just the predicate):

if (null != startId) {
     Predicate dateGreater = cb.greaterThan(entity.get(sortBy), startValue);

     Predicate dateEqual = cb.equal(entity.get(sortBy), startValue);
     Predicate idGreater = cb.greaterThan(entity.get("id"), startId);
     Predicate dateEqualAndIdGreater = cb.and(dateEqual, idGreater);

     cb.or(dateGreater, dateEqualAndIdGreater);
}

If there is a nicer way, I would be very happy to learn about it.