Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
907 views
in Technique[技术] by (71.8m points)

java - Spring Data JPA - Named query ignoring null parameters

I have the following repository:

@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);
}

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);

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).

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.

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());
}

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

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.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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
    }
}

Then your EntityRepository have to extend JpaSpecificationExecutor

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

}

Usage:

@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);        
    }
}

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...