Spring JPA原生查询排序——向字段名添加前缀

Question

我有一个关于排序的问题。

仓库方法：

@Query(nativeQuery = true,
    value = "SELECT D.ID as dealerId , D.NAME as dealerName, K.ID as kpiId, " +
    "K.NAME as kpiName FROM REGION R, DEALER D, KPI K "
    + "WHERE R.IMPORTER_ID = :importerId "
    + "AND D.REGION_ID = R.ID "
    + "AND K.IMPORTER_ID = :importerId ")

Page<DealersKpiTargets> getDealersKpiTargets(@Param("importerId") Long importerId, Pageable pageable);

可分页对象：

Page request [number: 0, size 20, sort: name: DESC]

Hibernate日志：

Hibernate: SELECT D.ID as dealerId , D.NAME as dealerName, K.ID as kpiId, K.NAME as kpiName FROM REGION R, DEALER D, KPI K WHERE R.IMPORTER_ID = ? AND D.REGION_ID = R.ID AND K.IMPORTER_ID = ?  order by R.name desc limit ?

我不明白在 Hibernate 的 order by 部分中，R.name 前缀来源于哪里。

为了参考，我正在使用以下版本：

spring-data-jpa 版本 2.0.7.RELEASE

spring-boot-starter-data-jpa 版本 2.0.2.RELEASE

更新： 我通过将查询从本地查询更改为 JPA 查询并将笛卡尔乘积更改为连接版本来解决了此问题。现在已经可以正常工作了。

        @Query("SELECT dealerEntity.id AS dealerId , dealerEntity.name AS dealerName, kpiEntity.id AS kpiId, " +
        "kpiEntity.name AS kpiName FROM KpiEntity kpiEntity "
        + "JOIN RegionEntity regionEntity ON regionEntity.importerEntity = kpiEntity.importerEntity "
        + "JOIN DealerEntity dealerEntity ON dealerEntity.importerEntity = regionEntity.importerEntity "
        + "WHERE kpiEntity.importerEntity = :importerEntity ")
Page<DealersKpiTargets> getDealersKpiTargets(@Param("importerEntity") ImporterEntity importerEntity, Pageable pageable);

- Pavel Konir

Answer 1

这可能有点晚回答这个问题。但是我想分享一下我如何解决这个问题的。 对于原生查询，似乎Hibernate在应用排序条件时尝试使用查询中使用的第一个表格的别名。在您的情况下，第一个表格别名为R，因此您会在Hibernate生成的查询中看到R.name desc。

解决此问题的一种方法是将查询包装在select子句中，并将其命名为R，例如：

"SELECT * FROM(SELECT D.ID as dealerId , D.NAME as dealerName, K.ID as kpiId, " +
    "K.NAME as kpiName FROM REGION R, DEALER D, KPI K "
    + "WHERE R.IMPORTER_ID = :importerId "
    + "AND D.REGION_ID = R.ID "
    + "AND K.IMPORTER_ID = :importerId ) R"

这样在运行时，Hibernate会将排序条件应用于与R相对应的查询之上。

- amishra

---

我也做了同样的事情。虽然很糟糕，但它能够工作。 - Adrian

这个可以工作，但像@Adrian所说的那样，它很混乱，而且还会显著减慢查询速度。如果您不介意，我想知道您是如何得出这个解决方案的。谢谢。 - kenoly

刚刚不得不回到这个问题，因为我找到了一种替代方法，即删除所有表别名。虽然有点混乱，但至少你仍然可以保持速度。 - kenoly

Answer 2

这是一个包含更多详细信息的JIRA工单，可能对解决问题至关重要(https://jira.spring.io/browse/DATAJPA-1613)。

QueryUtils.ALIAS_MATCH

该内容无需翻译。

(?<=from)(?:\s)+([._[\P\\{Z}&&\P\\{Cc}&&\P\\{Cf}&&\P\\{P}]]+)(?:\sas)*(?:\s)+(?!(?:where|group\s*by|order\s*by))(\w+)

负责不正确的别名提取。我这种情况的解决方案是重写本地查询，使其不匹配提供的正则表达式。

- vitali_y

Answer 3

我曾经遇到过类似的问题，特别是在复杂查询中使用ORDER BY时。由于在ORDER BY之前添加了一个,，导致语法错误。

我解决这个问题的方法是创建一个VIEW，其中包含所需的结果集字段和WHERE条件的SELECT查询（因此您可以针对VIEW运行带有WHERE条件的参数查询）。然后编写本地查询以从VIEW中SELECT FROM。

CREATE VIEW my_view AS (// your complex select query with required fields);

@Query("SELECT field1 AS alias1, field2  AS alias2 FROM my_view  "
        + "WHERE field3  = :param1 AND field4 = :param2")
Page<MyDto> myFunction(@Param("param1") Long param1, @Param("param1") String param2, Pageable pageable);

- Thanthu

Answer 4

它有一个排序类，你可以使用它。此外，它很容易使用。

https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.query-methods.sorting

- Hatice

---

我还需要分页，因此需要一个Pageable对象。我已通过将查询从本地查询更改为jpa查询来解决此问题，并且它正在工作。错误在本地查询处理中的某个地方。 - Pavel Konir

Answer 5

对于任何寻找简单解决方案的人，你可以使用JPASort.by()代替，它不会给结果查询添加别名。

- anon