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 作为可选的请求参数(底层服务也使用相同的参数):

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 作为参数被传递时，命名查询出现问题，JpaRepository 使用 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 的解决方案是:

My workaround solution based on Optional is:

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 Data- 如果参数为空值 并且创建了票证，则忽略参数 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这样的实用类

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

Then your EntityRepository have to extend 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屋！