JPA-在子句中使用“复合值"查询 [英] JPA - Query with 'composite values' in where-clause

问题描述

我想用Eclipselink作为实现在JPA 2.1中实现以下语句(对于此示例:MySQL):

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;

我想要实现的是无休止滚动而不在查询中使用偏移量的seek方法的实现.专注于where子句，我无法找到此构造的正确名称.在某些地方，它被称为复合值"，但是我无法通过该名称查找结果，但是它似乎符合SQL-92标准.

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.

此语句将由过滤器参数扩展，因此自然地我想使用JPA标准API来构建它.

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

我搜索API已有一段时间，现在是否有人以及如何做到这一点?

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

推荐答案

意识到这一点

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.

这篇关于JPA-在子句中使用“复合值"查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！