Spring Data JPA-忽略空参数的命名查询 [英] Spring Data JPA - Named query ignoring null parameters

问题描述

我有以下存储库:

@Repository
public interface EntityRepository extends JpaRepository<Entity, Long> {

    List<Entity> findAllByFirstId(Long firstId);
    List<Entity> findAllBySecondId(Long secondId);
    List<Entity> findAllByFirstIdAndSecondId(Long firstId, Long secondId);
}

实现由io.swagger:swagger-codegen-maven-plugin生成的接口的构造函数使用Optional<Long>作为可选请求参数(基础服务也使用相同的参数):

The constructor implementing an interface generated with io.swagger:swagger-codegen-maven-plugin uses Optional<Long> as optional request parameters (the underlying service uses also the same parameters):

ResponseEntity<List<Entity>> entities(Optional<Long> firstId, Optional<Long> secondId);

我想根据参数firstId和secondId过滤实体，这些参数在数据库中从来都不是null，但是可以通过构造函数传递(用于搜索的参数是可选的).

I would like to filter the entities based on the parameters firstId and secondId which are never nulls at the database but can be passed through the constructor (the parameter for searching is optional).

当传递null作为参数是可选的时，命名查询会带来问题，JpaReposotory使用null作为在数据库中搜索的标准.那就是我所不希望的-我想忽略基于此参数的过滤，只要它是null.

The problem comes with the named queries when the null is passed as the parameter is optional, the JpaReposotory uses the null as a criterion for the searching in the database. That's what I don't want - I want to ignore the filtering based on this parameter as long as it is null.

我基于Optional的解决方法是:

public List<Entity> entities(Optional<Long> firstId, Optional<Long> secondId) {

    return firstId
        .or(() -> secondId)
        .map(value -> {
            if (firstId.isEmpty()) {
                return entityRepository.findAllBySecondId(value);
            }
            if (secondId.isEmpty()) {
                return entityRepository.findAllByFirstId(value);
            }
            return entityRepository.findAllByFirstIdAndSecondId(
            firstId.get(), secondId.get());
        })
        .orElse(entityRepository.findAll())
        .stream()
        .map(...)     // Mapping between DTO and entity. For sake of brevity
                      // I used the same onject Entity for both controler and repository 
                      // as long as it not related to the question   

        .collect(Collectors.toList());
}

已经问过这个问题: Spring数据-如果参数具有空值并创建票证，则忽略该参数 DATAJPA-209 .

This issue has been already asked: Spring Data - ignore parameter if it has a null value and a ticket created DATAJPA-209.

只要这个问题已有3年之久，并且票证可以追溯到2012年，我想问一下是否存在一种更舒适，更通用的方法来避免处理Optional和复制存储库方法的开销. 2个此类参数的解决方案看起来可以接受，但是我想对4-5个参数实施完全相同的过滤.

As long as the question is almost 3 years old and the ticket dates back to 2012, I would like to ask if there exists a more comfortable and universal way to avoid the overhead of handling the Optional and duplicating the repository methods. The solution for 2 such parameters looks acceptable, however I'd like to implement the very same filtering for 4-5 parameters.

推荐答案

您需要这样的Specification实用程序类

You need Specification utility class like this

public class EntitySpecifications {
    public static Specification<Entity> firstIdEquals(Optional<Long> firstId) {// or Long firstId. It is better to avoid Optional method parameters.
        return (root, query, builder) -> 
            firstId.isPresent() ? // or firstId != null if you use Long method parameter
            builder.equal(root.get("firstId"), firstId.get()) :
            builder.conjunction(); // to ignore this clause
    }

    public static Specification<Entity> secondIdEquals(Optional<Long> secondId) {
        return (root, query, builder) -> 
            secondId.isPresent() ? 
            builder.equal(root.get("secondId"), secondId.get()) :
            builder.conjunction(); // to ignore this clause
    }
}

然后您的EntityRepository必须扩展JpaSpecificationExecutor

@Repository
public interface EntityRepository 
    extends JpaRepository<Entity, Long>, JpaSpecificationExecutor<Entity> {

}

用法:

@Service
public class EntityService {    

    @Autowired
    EntityRepository repository;

    public List<Entity> getEntities(Optional<Long> firstId, Optional<Long> secondId) {
        Specification<Entity> spec = 
            Specifications.where(EntitySpecifications.firstIdEquals(firstId)) //Spring Data JPA 2.0: use Specification.where
                          .and(EntitySpecifications.secondIdEquals(secondId));

        return repository.findAll(spec);        
    }
}

这篇关于Spring Data JPA-忽略空参数的命名查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！