Spring jpa native query sorting adding prefix to order by field name

Question

I have problem with sorting.

Repository method:

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

Pageable object:

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

Hibernate log:

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 ?

I don't understand where R.name prefix came from, in the order by part in Hibernate (towards the end).

For reference, I am using:

spring-data-jpa version 2.0.7.RELEASE

spring-boot-starter-data-jpa version 2.0.2.RELEASE

UPDATE

I have solved this by changing the query from the native query to jpa query and it's working. And I changed cartesian to join version.

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

score 3 · Answer 1 · answered Oct 17 '19 at 15:29

here is jira ticket with more details which can be key for resolution (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+)

responsible to incorrect alias extraction. The solution for my case was rewrite native query, so it doesn't match the provided regexp.

amishra · Answer 2 · 2022-03-25T01:54:47.683

3

This may be a little late to answer this question. But thought to share how I got around this issue. For native queries, it seems like hibernate tries to use the alias of the first table used in the query when it applies the sorting criteria. In your case, the first table alias is R hence you see R.name desc in the query generated by hibernate.

One way to get around this issue is to wrap your query in a select clause and name it as R, like

"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"

This way at runtime hibernate would apply the sort criteria on top of your query which corresponds to R now.

edited Mar 25 '22 at 01:54

answered Aug 21 '20 at 22:29

amishra

951
9
12

I did the same thing. It's horrible, but it works. – Adrian Mar 23 '22 at 23:19
This works but like @Adrian said, it's messy and it also considerably slows down the query. I'd like to know how you came about this solution if you don't mind. Thanks – kenoly May 05 '23 at 06:49
1

Just had to come back to this as I found an alternative method which is removing all table aliases. Messy but at least you still keep the speed. – kenoly May 05 '23 at 14:57

score 0 · Answer 3 · answered May 22 '18 at 20:13

0

It has Sort class for this you can use this maybe. Besides, it is easy to use.

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

answered May 22 '18 at 20:13

Hatice

874
1
8
17

I also need paging, so a Pageable object is needed. I have solved this by changing the query from the native query to jpa query and it's working. The error is somewhere in the native query processing – Pavel Konir May 24 '18 at 07:24

Thanthu · Answer 4 · 2022-04-29T03:40:13.893

I faced similar issue especially in case of complex queries where there is ORDER BY with in the query. I was getting syntax error because a , was getting added before ORDER BY.

The way I solved this issue was to create a VIEW with the SELECT query having necessary fields required for result set and WHERE condition (so you can run query with params in WHERE condition against the VIEW). And write native query to SELECT FROM the VIEW

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

score 0 · Answer 5 · answered Jul 12 '23 at 09:44

0

for anyone searching for a simple solution you can use JPASort.by() instead, it won't add an alias to the resulting query

answered Jul 12 '23 at 09:44

anon

1

Spring jpa native query sorting adding prefix to order by field name

5 Answers5