How do you make a spring data custom query method that has the following signature '(x OR y) AND z'

Question

I have the following repository with 2 custom query methods:

@Repository
public interface CropVarietyNameDao extends JpaRepository<CropVarietyName, Long> {
    //https://stackoverflow.com/questions/25362540/like-query-in-spring-jparepository
    Set<CropVarietyName> findAllByNameIgnoreCaseContainingOrScientificNameIgnoreCaseContaining(String varietyName, String scientificName);
    Set<CropVarietyName> findAllByNameIgnoreCaseStartsWithOrScientificNameIgnoreCaseStartsWith(String varietyName, String scientificName);
}

I need to add an additional condtion, namely parent entity called crop deleted = false.

If i change a method to the following:

findAllByNameIgnoreCaseContainingOrScientificNameIgnoreCaseContainingAndCrop_deletedIsFalse(String varietyName, String scientificName);

Then most likely it will interpret the query as (Name containing OR (scientificNameContaining AND crop_deleted = false), but thats not how i want it. It needs to be (NameContaining OR scientificNameContaining) AND crop_deleted = false)

My guess is that I have to add the AND crop_deleted part to both parts, but that seems inefficient. How can i write a method thats essentially (NameContaining OR scientificNameContaining) AND crop_deleted = false) without having to use @Query?

score 4 · Accepted Answer · answered Jan 02 '20 at 08:19

You could try JPA Specification -

@Repository
public interface CropVarietyNameDao extends JpaRepository<CropVarietyName, Long>,
        JpaSpecificationExecutor<CropVarietyName> {
}

public class CropVarietySpecs {

        public static Specification<CropVarietyName> cropPredicate(String varietyName, String sciName, boolean cropStatus) {
        return new Specification<CropVarietyName>() {
            @Override
            public Predicate toPredicate(Root<CropVarietyName> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
                List<Predicate> predicates = new ArrayList<>();
                Predicate varietyContainingIgnoreCasePredicate = criteriaBuilder.like(criteriaBuilder.lower(root.get("<column_name>")), varietyName.toLowerCase());
                Predicate scientificContainingIgnoreCasePredicate = criteriaBuilder.like(criteriaBuilder.lower(root.get("<column_name>")), sciName.toLowerCase());
                Predicate cropStatusPredicate = criteriaBuilder.equal(root.get("<column_name>"), cropStatus);
                predicates.add(varietyContainingIgnoreCasePredicate);
                predicates.add(scientificContainingIgnoreCasePredicate);
                criteriaBuilder.or(predicates.toArray(new Predicate[predicates.size()]));
                return criteriaBuilder.and(cropStatusPredicate);
            }
        };

    }
}

Then you can call the findAll() method of your repository like -

List<CropVarietyName> entities = cropVarietyNameDao.findAll(CropVarietySpecs.cropPredicate("varietyName", "sciName", false));

How do you make a spring data custom query method that has the following signature '(x OR y) AND z'

1 Answers1