How to add dynamic sql query in JPA query annotation in spring boot?

Question

I want to add a sorting column in the query at run time. I have used query annotation for build query. I have used param "sortCol" for adding a sort column in the query. But somehow query annotation not recognize the "sortCol" parma and hence not using it. Not giving any error as well.

 SELECT
        * 
    FROM
        request r 
    WHERE
        r.asset_type IN (
            ?, ?, ?
        ) 
        AND r.request_type IN (
            ?, ?
        ) 
        AND r.status IN  (
            ?, ?, ?, ?
        ) 
        AND r.assign_to = ?  
        AND (
            r.request_custom_id LIKE ? 
            OR r.project_name LIKE ? 
            OR r.asset_type LIKE ? 
            OR r.request_type LIKE ? 
        ) 
    ORDER BY
        ? limit ?"

In this after the order by column name is missing, which I have added. At the run time:

    @Query(value ="SELECT * FROM request r WHERE r.asset_type IN (:assetType) AND r.request_type IN (:requestType) AND r.status IN  (:statusList) AND r.assign_to = :assignTo  AND ( r.request_custom_id LIKE %:searchTerm% OR r.project_name LIKE %:searchTerm1% OR r.asset_type LIKE %:searchTerm2% OR r.request_type LIKE %:searchTerm3% ) ORDER BY :sort",nativeQuery = true)
    Page<Request> findRequestdata(Pageable pageable,
            @Param("assetType") List<String> assetType,
            @Param("requestType") List<String> requestType,
            @Param("statusList") List<Integer> statusList,
            @Param("assignTo") Long assignTo,
            @Param("searchTerm") String searchTerm,
            @Param("searchTerm1") String searchTerm1,
            @Param("searchTerm2") String searchTerm2,
            @Param("searchTerm3") String searchTerm3,
            @Param("sort") String sortCol);

All parma is setting properly except "sortCol". Please suggest where am missing anything.

Simon Martinelli · Accepted Answer · 2019-02-05 09:42:03Z

3

You are already passing a Pageable object to your method.

So when you create the paging you can also add sorting:

Pageable sortedByName = PageRequest.of(0, 3, Sort.by("name"));

findRequestdata(sortedByName, ...)

Read more here: https://www.baeldung.com/spring-data-jpa-pagination-sorting

answered Feb 5, 2019 at 9:42

Simon Martinelli

36.6k6 gold badges62 silver badges92 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to add dynamic sql query in JPA query annotation in spring boot?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related