4

I just started learning the use of Specification in Spring queries. I have written a rather complex Specification that filters a database by a type. For demonstration I will show a simple version with the same idea:

Specification<Animal> animalWithType(AnimalType type) {
    return (root, cq, cb) -> {
        return cb.equal(root.get(Animal_.type), type);
    };
}

I can call it like this animalRepository.findAll(animalWithType(AnimalType.Dog)) and I get all dogs.

Now I also have humans which have a favourite type of animal. I need to make a query where I get all humans for which we have an animal of their favourite type. For this I want to reuse the first specification, because it is a very complication one. So I want to do something like this:

Specification<Human> weHaveFavouriteAnimal() {
    return (root, cq, cb) -> {
        return cb.exists(animalWithType(root.get(Human_.favouriteAnimalType)));
    };
}

I cannot do this for two reasons:

  • root.get(Human_.favouriteAnimalType) will give me Expression<AnimalType> which I cannot pass to my animalWithType function. I also don't have an easy way to convert a raw AnimalType to Expression<AnimalType> and pass it then, because I need an Instance of QueryBuilder to do that.

  • The function animalWithType returns a Specification but I need a Predicate. I assume I need to make a SubQuery of some kind here, but I can't pass a SubQuery to the function Specification::toPredicate because it requires a CriteriaQuery.

Is there a way to reuse a Specification as an "exists" subquery in another Specification for a different Entity?

In SQL the first specification would do something like select * from animals where type == Dog and the second one should have this one as a subquery and look something like this select * from humans where exists (select * from animals where type == humans.favouriteAnimalType). My SQL is a bit rusty but I hope this is not too far off. This seems like the first query is very well reused in the second query. I want to do something similar with Specifications.

findusl
  • 2,454
  • 8
  • 32
  • 51
  • I think this might help: https://stackoverflow.com/questions/12061502/spring-data-subquery-within-a-specification – Adithya Dec 06 '19 at 20:42
  • @Adithya thanks but sadly I can't easily reuse a specification this way because specifications expect criteriaQueries not SubQueries. – findusl Dec 06 '19 at 20:44
  • Specification weHaveFavouriteAnimal() { return (root, cq, cb) -> { Subquery animalSubQuery = cq.subquery(Animal.class); Root animal = sub.from(Animal.class); Expression> favoriteAnimals = animal.get(Human_.favouriteAnimalType); .... }; } – Adithya Dec 06 '19 at 21:22
  • @Adithya so you suggest copy and pasting the code instead of reusing it? – findusl Dec 06 '19 at 21:54

0 Answers0