Query with Collection in JPA Spring Data

Question

Good afternoon, everyone, I have a question that might help other people. In my project I have a vehicle entity, this entity has a collection of another entity called Optional (Vehicle Optional), an example of optional would be Cd Player, Airbag, Alarm and others. In the Repository of the vehicle I have a method that searches vehicles by model and by an optional Collection, something like that:

List<Veiculo> findDistinctByModeloMarcaNomeIgnoreCaseAndOpcionaisNomeIn(String marca, List<String> opcionais);

Obs: The vehicle is related to Model that is related to Brand.

However, with this generated method it returns me a list of vehicles that has at least one of the elements of the list of options, is not the goal. The idea is that he return me a list of vehicles that have all the optional list elements and not just one or the other.

In search of a solution I found this topic: https://stackoverflow.com/questions/50106207/spring-jpa-query-by-example-collection

In this topic it is possible to see that a colleague informs us that it would be necessary to create a filter in java, because there would be no way to do using Spring Data. I wonder if there is any more recommended way to do this, or if this way it was recommended would be the best option.

Thanks for your help! Hugs!

Answer 1

Before proceeding with the solution it is worth remembering that I am a student, so I’m not sure if the way the solution was formed if it is correct, I believe that has much to improve yet but that is a start, I hope to be able to help more people with this solution.

I believe I have found a solution to my problem following your tip, first I created a class called Vehicles, in this class was encapsulated the logic to return a Specification that will be used in Veiculorepository. Remembering that the idea was for the user to inform the brand of the desired vehicle and its options to return a list of vehicles following these criteria, so in Veiculospecs we have a method that does this for us, as shown below:

public class VeiculoSpecs {


        public static Specification<Veiculo> findByMarcaAndOpcionais(String marca, List<String> opcionais) {
            return new Specification<Veiculo>() {
                private static final long serialVersionUID = 1L;

                public Predicate toPredicate(Root<Veiculo> root, CriteriaQuery<?> query, CriteriaBuilder builder) {
                    List<Predicate> predicates = new ArrayList<>();

                    if(!marca.isEmpty()) {
                        predicates.add(builder.equal(builder.lower(root.get("modelo").get("marca").get("nome")), marca.toLowerCase()));
                    }

                    if(!opcionais.isEmpty()) {
                        for(String nome: opcionais) {
                            predicates.add(builder.like(builder.lower(root.join("opcionais").get("nome")), "%" + nome.toLowerCase() + "%"));

                        }
                    }

                    query.distinct(true);

                    return builder.and(predicates.toArray(new Predicate[1]));
                }
            };
        }

    }

1. A list is created to store predicates.
2. He picks up the brand of the informed vehicle and checks which vehicles belong to that mark.
3. In FOR it goes through the list of options(names), which was received method parameters, and creates a query that will be added to the list of predicates using LIKE for each optional(name) of list.

In the Repository it receives this Specification in the search all method, remembering that the Repository should extend the Jpaspecificationexecutor<>interface, as shown below:

@Repository
public interface VeiculoRepository extends JpaRepository<Veiculo, Integer>, JpaSpecificationExecutor<Veiculo>{

    List<Veiculo> findAll(Specification<Veiculo> spec);
}

Finally, in the Vehicle classSeervice in the brand search/optional method the method call is made in the Repository to search everyone by passing in the parameter the result of the method that is in Vehicles passing the brand and the list of names of the options as parameter, code:

@Service
public class VeiculoService {

    @Autowired
    private VeiculoRepository repo;

    public List<Veiculo> findByMarcaAndOpcionaisSpecification(String marca, List<String> opcionais) {
        return repo.findAll(VeiculoSpecs.findByMarcaAndOpcionais(marca, opcionais));
    }

}

Testing fictitious:

• These tests are only to demonstrate how it would be +- in practice. Remembering that in this test everything has been simplified to demonstrate only what is necessary.

  Vehicle v1 = new Vehicle ("Ford"); v1.setOptionais(Arrays.asList("Airbag, Alarm, Radio"));

  Vehicle v2 = new Vehicle("Ford"); v2.setOptionais(Arrays.asList("Airbag, Alarm"));

At the end if the user performs a search aiming to receive a list containing vehicles of the brand Ford with the optional "Airbag, Alarm, Radio", he will receive only the vehicle 1. If he puts that want to receive a vehicle of the brand Ford with the optional "Airbag, Alarm" he will receive vehicle 1 and 2.

Answer 2

I think the @Query() annotation solves your problem. Take a look here: Youtube video (english)