为 JPA 和 Spring boot 实现搜索功能 [英] Implement search functionality for JPA and Spring boot

问题描述

我正在尝试为数据库表正确实现搜索功能.我试过这种方法:

I'm trying to implement properly search functionality for database table. I tried this approach:

控制器:

    @GetMapping
    public Page<TransactionDTO> find(TransactionFilterDTO filter, Pageable page) {
        return searchRepository
                .findTransactionsByFilter(mapper.toFilter(filter), page)
                .map(mapper::toDTO);
    }

文件管理器 DTO:

public class TransactionFilterDTO {

    private String name;

    private Integer id;

    @DateTimeFormat(iso = DateTimeFormat.ISO.DATE_TIME)
    private LocalDateTime from;

    @DateTimeFormat(iso = DateTimeFormat.ISO.DATE_TIME)
    private LocalDateTime to;
    ... // getters and setter
}

搜索实现:

@Repository
public class TransactionSearchRepositoryImpl implements TransactionSearchRepository {

    @Autowired
    private TransactionRepository transactionRepository;

    @Autowired
    private TransactionSpecification specification;

    @Override
    public Page<Transaction> findTransactionsByFilter(TransactionFilter filter, @Nullable Pageable page) {


        List<Transaction> transactions = transactionRepository
                .findAll(specification.getFilter(filter));

        int totalCount = transactions.size();

        if(page != null) {
           transactions = transactions
                   .stream()
                   .skip(page.getOffset())
                   .limit(page.getPageSize())
                   .collect(Collectors.toList());
        }

        return new PageImpl<>(transactions, page, totalCount);
    }
}

存储库:

public interface TransactionSearchRepository {

    Page<Transaction> findTransactionsByFilter(TransactionFilter filter, Pageable page);
}

是否有更好的方法来实现搜索功能?在我看来，这个解决方案非常丑陋.

Is there some better way to implement a search functionality? This solution is very ugly in my view.

推荐答案

我可以推荐几种方法来解决这样的任务:

I can recommend several approaches to solve such a task:

1) 简单但不灵活:只需根据过滤器属性(name、from、to)在控制器中使用请求参数并在您的 repo 中准备相应的查询，例如:

1) Simple but not flexible: just use request parameters in your controller according to your filter properties (name, from, to) and prepare the corresponding query in your repo, for example:

控制器:

@GetMapping("/q")
public List<ResponseDto> getAllByQuery(
    @RequestParam(value = "name", required = false) String name,
    @RequestParam(value = "from", required = false) @DateTimeFormat(iso = ISO.DATE) LocalDate from,
    @RequestParam(value = "to", required = false) @DateTimeFormat(iso = ISO.DATE) LocalDate to,
    Pageable pageable
) {
    return service.getByQuery(name, from, to, pageable);
}

服务:

public Page<ResponseDto> getByQuery(String name, LocalDate from, LocalDate to, Pageable pageable) {
    return repo.getByQuery(name, from, to, pageable).map(mapper::toResponseDto);
}

存储库:

@Query("select m from MyEntity m where " +
       "(?1 is null or upper(m.name) like concat('%', upper(?1), '%')) " +
       "and (?2 is null or m.createdAt >= ?2) " +
       "and (?3 is null or m.createdAt <= ?3)")
Page<MyEntity> getByQuery(String name, final LocalDate from, final LocalDate to, final Pageable pageable);

然后执行一个请求:

GET http://localhost:8080/q?name=john&from=2019-04-19&to=2019-04-19

2) 使用 QueryDsl.您应该将它添加到您的项目中(您可以在此处找到详细信息)，从 QuerydslPredicateExecutor 扩展您的 repo 和QuerydslBinderCustomizer，给它添加一些调整":

2) Using QueryDsl. You should add it to your project (you can find details here), extend your repo from QuerydslPredicateExecutor and QuerydslBinderCustomizer, add some 'tuning' to it:

public interface MyEntityRepo extends JpaRepository<MyEntity, Integer>, QuerydslPredicateExecutor<MyEntity>, QuerydslBinderCustomizer<QMyEntity> {
    @Override
    default void customize(@NonNull QuerydslBindings bindings, @NonNull QMyEntity entity) {

        // Make case-insensitive 'like' filter for all string properties
        bindings.bind(String.class).first((SingleValueBinding<StringPath, String>) StringExpression::containsIgnoreCase);

        // Add 'between' and 'greater or equal' filter date property
        bindings.bind(entity.createdAt).all((path, value) -> {
            Iterator<? extends LocalDate> it = value.iterator();
            LocalDate from = it.next();
            if (value.size() >= 2) {
                LocalDate to = it.next();
                return Optional.of(path.between(from, to)); // between
            } else {
                return Optional.of(path.goe(from)); // greater than or equal
            }
        });
    }

添加服务方法:

public Page<ResponseDto> getAllByQueryDsl(Predicate predicate, Pageable pageable) {
    return repo.findAll(predicate, pageable).map(mapper::toResponseDto);
}

添加控制器方法:

@GetMapping("/query-dsl")
public Page<ResponseDto> getAllByQueryDsl(
        @QuerydslPredicate(root = MyEntity.class, bindings = MyEntityRepo.class) Predicate predicate,
        Pageable pageable
) {
    return service.getAllByQueryDsl(predicate, pageable);
}

并在实体的日期"属性中添加 @DateTimeFormat 注释:

And add @DateTimeFormat annotation to 'Date' properties of your entity:

@Entity
public class MyEntity {
    // ...
    @DateTimeFormat(iso = ISO.DATE) private LocalDate createdAt;
}

然后就可以执行这样的请求了:

Then you can perform such a request:

GET http://localhost:8080/query-dsl?name=john&createdAt=2019-04-15&createdAt=2019-04-19

其中第一个日期是from"参数，第二个日期是to"参数.如果您只使用一个日期 - 它将是 'from' 参数(大于或等于).

Where the first date is 'from' parameter and the second date is 'to' parameter. If you use only one date - it will be 'from' parameter (greater than or equal).

3) 使用 specification-arg-resolver 库.将其添加到您的项目中(请参阅说明:1 和 2)，然后扩展你的仓库来自 JpaSpecificationExecutor:

3) Using specification-arg-resolver library. Add it to your project (see instruction: 1 and 2), then extend your repo from JpaSpecificationExecutor:

public interface MyEntityRepo extends JpaRepository<MyEntity, Integer>, JpaSpecificationExecutor<MyEntity> {}

将这样的方法添加到您的控制器中:

Add such a method to your controller:

@GetMapping("/specification")
public Page<ResponseDto> getAllBySpecification(
        @And({
                @Spec(path = "name", spec = LikeIgnoreCase.class),
                @Spec(path = "createdAt", params = "from", spec = GreaterThanOrEqual.class),
                @Spec(path = "createdAt", params = "to", spec = LessThanOrEqual.class)
        }) Specification<MyEntity> specification,
        Pageable pageable
) {
    return service.getAllBySpecification(specification, pageable);
}

更新您的服务:

public Page<ResponseDto> getAllBySpecification(final Specification<MyEntity> specification, final Pageable pageable) {
    return repo.findAll(specification, pageable).map(mapper::toResponseDto);
}

然后请求您的数据:

GET http://localhost:8080/specification?name=john&from=2019-04-10&to=2019-04-19

4) 手动构建规范:

创建过滤器类:

@Data
public class MyFilter implements Specification<MyEntity> {
    private String name;
    @DateTimeFormat(iso = ISO.DATE) private LocalDate from;
    @DateTimeFormat(iso = ISO.DATE) private LocalDate to;

    @Override
    public Predicate toPredicate(Root<MyEntity> root, CriteriaQuery<?> query, CriteriaBuilder builder) {
        List<Predicate> predicates = new ArrayList<>();
        if (name != null) predicates.add(builder.like(builder.upper(root.get("name")), "%" + name.toUpperCase() + "%"));
        if (from != null) predicates.add(builder.greaterThanOrEqualTo(root.get("createdAt"), from));
        if (to != null) predicates.add(builder.lessThanOrEqualTo(root.get("createdAt"), to));
        return builder.and(predicates.toArray(new Predicate[0]));
    }
}

创建控制器方法:

@GetMapping("/filter")
public Page<ResponseDto> getAllByMyFilter(MyFilter filter, Pageable pageable) {
    return service.getAllBySpecification(filter, pageable);
}

然后运行请求:

GET http://localhost:8080/filter?name=john&from=2019-04-10&to=2019-04-19

这篇关于为 JPA 和 Spring boot 实现搜索功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！