英文:
Server-side filtering in DB by using pagination and sorting with Spring Data JDBC
问题
以下是翻译好的部分：
"这是情况：
我需要能够在服务器端进行过滤/排序，最好直接在数据库查询上，而不是在应用程序级别获取所有数据然后进行过滤/排序。
然后，由于：
- 没有切换到JPA的选项，我们继续使用JDBC
- 需要分页和排序，
- 需要动态SQL查询，因为需要根据一个或多个数据库列进行过滤，还需要多个过滤条件选择
- 不能使用PagingAndSortingRepository，因为无法将Page响应对象与自定义查询注释结合使用
Caused by: java.lang.UnsupportedOperationException: Page queries are not supported using string-based queries.
有一个（简化的）示例：
@Repository
public interface SomeRepository extends CrudRepository<SomeEntity, Long> {
@Query("SELECT * FROM some_table p WHERE p.some_column = :someValue LIMIT :pageSize OFFSET :offset")
List<SomeEntity> search(String someValue, int pageSize, long offset);
}
上述代码可以正常工作，但缺少分页和排序功能。添加 ORDER BY :sortColumn，然后使用类似 @Param("sortColumn") String sortColumn 的参数最终会产生带引号的字符串，如 SELECT * FROM some_table p WHERE p.some_column = 'some value' ORDER BY 'some_column' LIMIT 25 OFFSET 0
显然，Spring Data JDBC自定义查询替换只适用于值替换，而不是整个查询替换。
现在，我下一个优雅的选项是什么，以使排序成为可能？"
英文:
here is the situation:
I need to be able to filter/sort on server side, preferably directly on DB query, not to fetch all and filter/sort on app level.
Then, due to:
- no option to switch to JPA, we stick to JDBC
- pagination and sorting is required,
- dynamic SQL query is required, since filters by one or more DB columns is required and also multiple filter selection
- cannot use PagingAndSortingRepository since Page response object cannot be combined with custom Query annotation
Caused by: java.lang.UnsupportedOperationException: Page queries are not supported using string-based queries.
there is (simplified):
@Repository
public interface SomeRepository extends CrudRepository<SomeEntity, Long> {
@Query("SELECT * FROM some_table p WHERE p.some_column = :someValue LIMIT :pageSize OFFSET :offset")
List<SomeEntity> search(String someValue, int pageSize, long offset);
}
Above works fine, but there is the pagination and sorting missing. Adding ORDER BY :sortColumn and then having a param like @Param("sortColumn") String sortColumn ends up producing quoted string like SELECT * FROM some_table p WHERE p.some_column = 'some value' ORDER BY 'some_column' LIMIT 25 OFFSET 0
Obviously the spring data jdbc custom query substitution with named params is not meant to work for overall query substitution, but only for value one.
Now what is my next elegant option to go with to make sorting possible?
答案1
得分: 1
目前您最好的选择是编写一个自定义方法实现，以构建所需的SQL，并使用 JdbcTemplate 或 NamedParameterJdbcTemplate 执行它。
当然，您可能还想考虑使用 MyBatis、Jooq 或 Querydsl 来实现这个方法。
英文:
Currently your best option is to write a custom method implementation that constructs the required SQL as desired and executes it using JdbcTemplate or NamedParameterJdbcTemplate.
Of course you might want to consider MyBatis, Jooq or Querydsl for implementing that method.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论