将WHERE IN子句添加到JPA规范中

问题描述

我正在尝试实现受IN子句限制的搜索功能:

I'm trying to implement search functionality limited by IN clause:

我想实施具有过滤器限制的搜索实现:

I want to implement search implementation with filter limitation:

    @GetMapping("find")
    public Page<MerchantUserDTO> getAllBySpecification(
            @And({
                @Spec(path = "name", spec = LikeIgnoreCase.class),
                @Spec(path = "login", spec = LikeIgnoreCase.class),
                @Spec(path = "email", spec = LikeIgnoreCase.class),
            }) Specification<Users> specification,
            @SortDefault(sort = "login", direction = Sort.Direction.DESC) Pageable pageable
    ) {        
        return merchantUserService.getAllBySpecification(specification, pageable)
                .map(g -> MerchantUserDTO.builder()                   
                        .id(g.getId())
                        .login(g.getLogin())                        
                        .build()
                );
    }

    @Override
    public Page<Users> getAllBySpecification(Specification<Users> specification, Pageable pageable) {
        return dao.findAllByTypeIn(specification, pageable, "MerchantUser");
    }

存储库:

@Repository
public interface MerchantUserRepository extends JpaRepository<Users, Integer>, JpaSpecificationExecutor<Users> {

    Page<Users> findAllByTypeIn(Pageable page, String... types);

    Page<Users> findAllByTypeIn(Specification<Users> specification, Pageable pageable, String... types);
}

用IN子句扩展规范的正确方法是什么?

What is the proper way to extend the specification with IN clause?

specification.and(path.in(types))路径是一个属性，但是如何正确实现呢?

specification.and(path.in(types)) path is a attribute but how to implement it properly?

推荐答案

通常可以通过以下方式实现:

Generally this can be achieved this way:

1)创建规范实现

public class MerchantUserSpecification implements Specification<Users> {

    private final List<String> types;

    public MerchantUserSpecification(List<String> types) {
        this.types = types;
    }

    @Override
    public Predicate toPredicate(Root<Users> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
        if (types != null && !types.isEmpty()) {
            return root.get(Users_.type).in(types);
        } else {
            // always-true predicate, means that no filtering would be applied
            return cb.and(); 
        }
    }

2)使用方法页面findAll(@Nullable规格说明，可分页的可分页); 继承自界面，而不是使用自定义的findAllByTypeIn(Specification<Users> specification....)

2) Use method Page findAll(@Nullable Specification spec, Pageable pageable); inherited from JpaSpecificationExecutor interface instead of using your custom findAllByTypeIn(Specification<Users> specification....)

@Override
public Page<Users> getAllBySpecification(Specification<Users> specification, Pageable pageable) {
    // combine original specification (passed from outside) and filter-by-types specification
    Specification<Users> finalSpec = specification
           .and(new MerchantUserSpecification(Arrays.asList("MerchantUser")))
    return dao.findAll(finalSpec, pageable)
}

PS

对于Java 8+，对于简单的情况(如您的情况)，代码可能会减少得更多.除了在单独的类中实现Specification<T>之外，您还可以创建方法

With Java 8+ and for simple cases (like yours) the code may be reduced even more. Instead of implementing Specification<T> in separate class you can just create a method

private Specification<Users> typeIn(List<String> types) {
    return (root, query, cb) -> {
        if (types != null && !types.isEmpty()) {
           return root.get(Users_.type).in(types);
        } else {
           // always-true predicate, means that no filtering would be applied
           return cb.and(); 
        }
    }
}

@Override
public Page<Users> getAllBySpecification(Specification<Users> specification, Pageable pageable) {
    // combine original specification (passed from outside) and filter-by-types specification
    Specification<Users> finalSpec = specification
            .and(typeIn(Arrays.asList("MerchantUser")))
    return dao.findAll(finalSpec, pageable)
}

更新:最快捷的方法

@Override
public Page<Users> getAllBySpecification(Specification<Users> specification, Pageable pageable) {
    // combine original specification (passed from outside) and filter-by-types specification
    Specification<Users> finalSpec = specification
            .and((root, query, cb) -> root.get(Users_.type).in(Arrays.asList("MerchantUser"))
    return dao.findAll(finalSpec, pageable)
}

这篇关于将WHERE IN子句添加到JPA规范中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！