Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Custom query with sort parameter [DATAJDBC-589] #811

Open
spring-projects-issues opened this issue Aug 6, 2020 · 3 comments
Open

Custom query with sort parameter [DATAJDBC-589] #811

spring-projects-issues opened this issue Aug 6, 2020 · 3 comments
Assignees
Labels
in: repository type: enhancement

Comments

@spring-projects-issues
Copy link

@spring-projects-issues spring-projects-issues commented Aug 6, 2020

kremerkp opened DATAJDBC-589 and commented

I try to add an dynamicly "order by" clause by passing a parameter inside a custom query. Limit and offset are working fine as parameters, only the order by is not recognized

Example:

// code public interface CustomerRepository extends PagingAndSortingRepository<Customer, Long> 

     @Query("select * from person where firstname = :name" + " order by :order")
           List<CustomerfindByFirstNameCustomQuery(@Param("name"String namePageable page, @Param("order"String order);

The query that I normaly use is more complex, so that I wont be able to use query creation from method names.

Is there a workaround or will it be "fixed/added" in a future release?

I also added a small GitHub Repo do demonstrate the problem;

https://github.com/kremerkp/spring-data-jdbc-pageable-h2

 


Reference URL: https://stackoverflow.com/questions/63285038/spring-data-customized-query-with-order-parmeter-nor-working

@spring-projects-issues
Copy link
Author

@spring-projects-issues spring-projects-issues commented Sep 22, 2020

Mark Paluch commented

Using @Query with Sort or Pageable is not supported. The reason is that the provided SQL statement would require to be parsed and rewritten so that Spring Data JDBC could augment it with pagination information (such as LIMIT/OFFSET) and the ORDER BY clause.

Spring Data JPA has partial support for dynamic sorting and we've learned from there that parsing SQL comes with quite some complexity and it's easy to break the parser using e.g. joins or subselects.

Therefore we don't plan to support @Query with Sort or Pageable

@spring-projects-issues
Copy link
Author

@spring-projects-issues spring-projects-issues commented Oct 12, 2020

Jens Schauder commented

As Mark explained above, currently we would need to parse the SQL statement which we don't want to get into.
Once other changes are in place it should be possible to have the user only specify the where clause and possibly separately an order by clause so that we can do the necessary tweaking without parsing SQL.

This will take a while though.

@NickYadance
Copy link

@NickYadance NickYadance commented Feb 22, 2022

@spring-projects-issues kremerkp is not issuing adding dynamic Sort and Pageable to custom query like JPA. It's that the :order parameter won't work in this query.

@Query("select * from person where firstname = :name" + " order by :order")

stackoverflow Here is a good explanation about the root cause. It's not about Spring Data Jdbc, but still, user can get confused about the result.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
in: repository type: enhancement
Projects
None yet
Development

No branches or pull requests

3 participants