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 ?

我不明白r.name前缀来自哪里，在hibernate中的order by部分（接近末尾）。

作为参考，我使用：

        @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);

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。