CODEWITHSUNDEEP
javasortingspring-data-jpanativequery
Pavel Konir
Pavel Konir

Reputation: 81

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

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

Upvotes: 8

Views: 8157

Answers (5)

anon
anon

Reputation: 1

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

Upvotes: 0

Thanthu
Thanthu

Reputation: 5058

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

Upvotes: 0

amishra
amishra

Reputation: 951

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.

Upvotes: 3

vitali_y
vitali_y

Reputation: 510

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.

Upvotes: 3

Hatice
Hatice

Reputation: 944

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

Upvotes: 0

Related Questions