使用Spring Data JPA和QueryDsl进行分页查询时，排序会出现问题。

Question

给定以下查询语句，我的结果按订单号降序排序。在尝试返回分页结果时，排序似乎会出现问题。我尝试了删除.orderBy()表达式和将QSort实例添加到我的QPageRequest中。我尝试直接调用applySorting()方法并传递一个QSort，但是出于所有强调目的，无论我尝试什么样的排序，分页调用都会破坏它。我正在使用QueryDsl 3.3.4和Spring Data JPA 1.6.4，由Spring IO BOM 1.0.3导入。

public List<Object[]> findAll(PersonProfile personProfile) {
    QOrderStop shipper = new QOrderStop("shipper");
    QOrderStop consignee = new QOrderStop("consignee");
    JPQLQuery query = this.from(order)
            .leftJoin(order.orderStops, shipper).on(shipper.originalStopSequence.eq(BigDecimal.valueOf(1)))
            .leftJoin(order.orderStops, consignee).on(consignee.originalStopSequence.intValue().eq(order.orderStops.size()))
            .where(order.company.eq(company).and(OrderExpressions.customerCodePredicate(order, personProfile)))
            .orderBy(order.number.desc());

    //this.getQuerydsl().applyPagination(new QPageRequest(0, 10), query);

    return query
            .list(
                    order.number, order.status, order.shipperReferenceNumber, order.consigneeReferenceNumber,
                    order.shipperCustomer.billingCity, order.shipperCustomer.state,
                    shipper.latestScheduleDate, shipper.latestScheduleTime,
                    order.consigneeCustomer.billingCity, order.consigneeCustomer.state,
                    consignee.latestScheduleDate, consignee.latestScheduleTime
            ).stream()
            .map(Tuple::toArray)
            .collect(toList());
}

编辑 - 使用.limit(20)进行分页而非.applyPagination()

在query.limit(20)之前的query.toString()：

select order1
from Order order1
  left join order1.orderStops as shipper on shipper.originalStopSequence = ?1
  left join order1.orderStops as consignee on cast(consignee.originalStopSequence integer) = size(order1.orderStops)
where order1.company = ?2 and (order1.customer = ?3 or order1.billTo in ?4 or order1.shipper in ?5 or order1.consignee in ?5)
order by order1.number desc

在query.limit(20)之后使用query.toString() - 没有变化

select order1
from Order order1
  left join order1.orderStops as shipper on shipper.originalStopSequence = ?1
  left join order1.orderStops as consignee on cast(consignee.originalStopSequence integer) = size(order1.orderStops)
where order1.company = ?2 and (order1.customer = ?3 or order1.billTo in ?4 or order1.shipper in ?5 or order1.consignee in ?5)
order by order1.number desc

EclipseLink 最终的查询输出

SELECT * FROM (SELECT * FROM (SELECT EL_TEMP.*, ROWNUMBER() OVER() AS EL_ROWNM FROM (SELECT t0.OHOR# AS a1, t0.OHORST AS a2, t0.OHSRF# AS a3, t0.OHCRF# AS a4, t1.CUBCTY AS a5, t1.CUBST AS a6, t2.BSLSDT AS a7, t2.BSLSTM AS a8, t3.CUBCTY AS a9, t3.CUBST AS a10, t4.BSLSDT AS a11, t4.BSLSTM AS a12 FROM LIB.DPORDHP t0 LEFT OUTER JOIN LIB.DPORDSP t2 ON (((t2.BSOR# = t0.OHOR#) AND ((t2.BSOL# = t0.OHORS#) AND (t2.BSCM = t0.OHCM))) AND (t2.BSRSQS = ?)) LEFT OUTER JOIN LIB.DPORDSP t4 ON (((t4.BSOR# = t0.OHOR#) AND ((t4.BSOL# = t0.OHORS#) AND (t4.BSCM = t0.OHCM))) AND (CAST(t4.BSRSQS AS integer) = (SELECT COUNT(t5.BSOR#) FROM LIB.DPORDSP t5 WHERE ((t5.BSOR# = t0.OHOR#) AND ((t5.BSOL# = t0.OHORS#) AND (t5.BSCM = t0.OHCM)))))), LIB.SWCUSTP t3, LIB.SWCUSTP t1 WHERE (((t0.OHCM = ?) AND ((((t0.OHCUST = ?) OR (t0.OHBID IN (?,?))) OR (t0.OHSID IN (?,?,?,?,?,?,?,?,?,?))) OR (t0.OHCID IN (?,?,?,?,?,?,?,?,?,?)))) AND (((t1.CUCID = t0.OHSID) AND (t1.CUCM = t0.OHCM)) AND ((t3.CUCID = t0.OHCID) AND (t3.CUCM = t0.OHCM)))) ORDER BY t0.OHOR# DESC) AS EL_TEMP) AS EL_TEMP2 WHERE EL_ROWNM <= ?) AS EL_TEMP3 WHERE EL_ROWNM > ?

- Patrick Grimard

---

你在应用分页调用之后，query.toString()是什么样子？在调用之前呢？ - Timo Westkämper

Answer 1

我不知道applyPagination如何破坏排序，但在这里使用query.limit和query.offset可能更直观，因为你在一个存储库方法内。

...
.orderBy(order.number.desc()).
.limit(pageSize)
.offset((page-1) * pageSize);

- Timo Westkämper

---

我还没有尝试过那个。由于我是QueryDsl的新手，我的背景更多是Spring Data JPA，所以我正在尝试使用Spring Data方法进行分页和排序。我会尝试一下，看看它是否更好用 :) - Patrick Grimard

我尝试使用.limit(20)，但排序似乎仍然出现问题。我已经通过删除.limit(20)来确认这一点，然后排序就正常工作了。不确定问题出在哪里。我已经编辑了原始帖子并添加了更多细节。 - Patrick Grimard

看起来是EclipseLink的问题，Querydsl正确渲染了order by，但limit部分没有在JPQL中渲染，而是设置在JPA查询中。 - Timo Westkämper