It is necessary to sort the table data (ORDER BY) on the frontend. Now requests to the database (PostgreSQL) are made using @Query annotations. There are 5 columns in the table, and I don’t want to do a lot of @Query for each column in the table, how to create a query in the database dynamically, so that the frontend would specify what needs to be sorted?

    2 answers 2

    Use PagingAndSortingRepository and transfer to Sort in repository methods.

    • Getting the List from Page easiest with the getContent () method. But when calling a method with Sort , Iterable should be returned, not Page . - Sergey Gornostaev
    • and the filter (Like) can be done in this way? - user9046477
    • Yes, you can : discontCardRepository.findBySomeFieldLike(sort) - Sergey Gornostaev
    • and key = value? That frontend itself specified columns and values? - user9046477
    • Yeah, I forgot, after sort second parameter values ​​for someField must be specified. - Sergey Gornostaev

    The easiest, most convenient and flexible option, in my opinion, is to use the QueryDSL extensions and Web support . Out of the box we get dynamic filtering, with support for sorting and pagination!

    First you need to connect QueryDSL to the project:

     <dependency> <groupId>com.querydsl</groupId> <artifactId>querydsl-jpa</artifactId> </dependency> <dependency> <groupId>com.querydsl</groupId> <artifactId>querydsl-apt</artifactId> </dependency> <build> <plugins> <plugin> <groupId>com.mysema.maven</groupId> <artifactId>apt-maven-plugin</artifactId> <version>1.1.3</version> <executions> <execution> <goals> <goal>process</goal> </goals> <configuration> <outputDirectory>target/generated-sources/annotations</outputDirectory> <processor>com.querydsl.apt.jpa.JPAAnnotationProcessor</processor> </configuration> </execution> </executions> </plugin> </plugins> </build>

    Then you need to compile the project to create Q-files for the entities (for example, mvn compile ).

    After, create a repo, inheriting, including, from QueryDslPredicateExecutor and, optionally, from QuerydslBinderCustomizer, for example:

     interface MyEntityRepo extends JpaRepository<MyEntity, Long>, QueryDslPredicateExecutor<MyEntity>, QuerydslBinderCustomizer<MyEntity> { }

    If the project uses Spring Data REST , you can immediately use such queries:

     /myEntities?field1=value1&field2=value2&sort=field2,desc&size=10&page=2

    If you need to create your controller, this is done, for example, as follows:

     @RestController @RequestMapping("/myEntities") public class ProfileController { @Autowired private MyEntityRepo repo; @GetMapping public ResponseEntity<?> getAll(@QuerydslPredicate(root = MyEntity.class, bindings = MyEntityRepo.class) Predicate predicate, Pageable pageable) { Page<MyEntity> entities = repo.findAll(predicate, pageable); return ResponseEntity.ok(entities); } }

    To customize the filter, you must use the customize QuerydslBinderCustomizer method a:

     public interface MyEntityRepo extends JpaRepository<MyEntity, Long>, QueryDslPredicateExecutor<MyEntity>, QuerydslBinderCustomizer<QMyEntity> { @Override default void customize(QuerydslBindings bindings, QMyEntity entity) { bindings.excluding( // исключаем не нужные поля из фильтра entity.id, entity.version, // ... ); // реализуем фильтр between по полую field1 bindings.bind(entity.field1).all((path, value) -> { Iterator<? extends Integer> it = value.iterator(); Integer from = it.next(); if (value.size() >= 2) { Integer to = it.next(); return path.between(from, to)); // between: если поле field1 указано в строке запроса два раза } else { return path.goe(from); // greter than: если поле field1 указано один раз } }); // фильтр like по полю field2 bindings.bind(entity.field2).first(StringExpression::containsIgnoreCase); } }