Server-side filtering in DB by using pagination and sorting with Spring Data JDBC

Question

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?

Answer 1

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.