Query with multiple Ids and date with JPA and Spring via Restapi

Question

Good morning, I’m trying to set up a query with Spring JPA and Rest where I can insert several Ids and initial and final date. The query works if I only put an ID with the dates. The code is like this:

@GetMapping("/search")
public List<TagsDay> pesquisar(@RequestParam(value = "ids") List<Long> ids, TagsDayFilter tagsDayFilter){
    return tagsDayRepository.filtrar(ids, tagsDayFilter);
}

Interface:

public interface TagsDayRepositoryQuery {

    public List<TagsDay> filtrar(List<Long> ids, TagsDayFilter tagsDayFilter);

}

and the implementation class:

public class TagsDayRepositoryImpl implements TagsDayRepositoryQuery{

    @PersistenceContext
    private EntityManager manager;

    @Override
    public List<TagsDay> filtrar(List<Long> ids, TagsDayFilter tagsDayFilter) {

        CriteriaBuilder builder = manager.getCriteriaBuilder();
        CriteriaQuery<TagsDay> criteria = builder.createQuery(TagsDay.class);

        Root<TagsDay> root = criteria.from(TagsDay.class);

        //criar as restrições
        Predicate[] predicates = criarRestricoes(ids, tagsDayFilter, builder, root);
        criteria.where(predicates);

        TypedQuery<TagsDay> query = manager.createQuery(criteria);
        return query.getResultList();
    }

    private Predicate[] criarRestricoes(List<Long> ids, TagsDayFilter tagsDayFilter, CriteriaBuilder builder, Root<TagsDay> root) {

        List<Predicate> predicates = new ArrayList<>();     

        if(ids.size() > 0) {

            for(int i = 0; i < ids.size(); i++) {
                predicates.add(builder.equal(root.get("id"), ids.get(i)));  //adiciona vários ids para consulta         
            }


        }
        if(tagsDayFilter.getDataInicial() != null) {
            predicates.add(builder.greaterThanOrEqualTo(root.get("data_coleta"), tagsDayFilter.getDataInicial()));
        }
        if(tagsDayFilter.getDataFinal() != null) {
            predicates.add(builder.lessThanOrEqualTo(root.get("data_coleta"), tagsDayFilter.getDataFinal()));
        }

        return predicates.toArray(new Predicate[predicates.size()]);
    }



}

and in the URL do: localhost:8080/tagsday/search?ids=1,2&dataInicial=2018-09-27&dataFinal=2018-09-28

Answer 1

To pass several ids

Simply add to query stop that attribute several times !

// Exemplo passando um queryParam com vários ids
localhost:8080/tagsday/search?ids=1&ids=2&dataInicial=2018-09-27&dataFinal=2018-09-28

Note that it appears "? ids=1&ids=2" this repetition is understood as an array(or List) by the method !

About the doubt of adding OR to the Criteria call

There is a way to make an OR yes !!

Here I created a method to return the predicates in OR form :

protected Predicate[] orPredicates(CriteriaBuilder builder, List<Predicate> predicates) {
    List<Predicate> returnPredicate = new ArrayList<>();

    if(predicates.size() > 0) {
        Predicate orPredicate = builder.or(predicates.toArray(new Predicate[predicates.size()]));
        returnPredicate.add(orPredicate);
    }

    return returnPredicate.toArray(new Predicate[returnPredicate.size()]);  
}

and use as follows, rather than return:

return predicates.toArray(new Predicate[predicates.size()]);

I return the method:

return orPredicates(builder, predicates);

.

But, what if you need to have some predicates with AND and others with OR ?

You may have noticed that the previous solution will cause ALL predicates to get OR!! Because of this, when I want to include one part of the querie with OR and the other with AND I do a "Local Predicate List" and add the return of the OR method in the mandatory predicates as follows :

...
// Predicados locais que devem ser do tipo OR
List<Predicate> predicatesLocal = new ArrayList<>();

if(tagsDayFilter.getDataInicial() != null) {
    predicatesLocal.add(
        builder.greaterThanOrEqualTo(root.get("data_coleta"), tagsDayFilter.getDataInicial())
    );
}

if(tagsDayFilter.getDataFinal() != null) {
    predicatesLocal.add(
        builder.lessThanOrEqualTo(root.get("data_coleta"), tagsDayFilter.getDataFinal())
    );
}       

Predicate pred = builder.or( orPredicates(builder, predicatesLocal) );
predicates.add(pred);
...

At the end he assembles the predicate as it should be mounted !

Final restriction method code:

private Predicate[] criarRestricoes(List<Long> ids, TagsDayFilter tagsDayFilter, 
                                    CriteriaBuilder builder, Root<TagsDay> root) {
    List<Predicate> predicates = new ArrayList<>();     

    if(ids.size() > 0) {
        for(int i = 0; i < ids.size(); i++) {
            //adiciona vários ids para consulta 
            predicates.add(builder.equal(root.get("id"), ids.get(i)));  
        }
    }

    // ----------------- PREDICADOS COM OR -----------------        
    // Predicados locais que devem ser do tipo OR
    List<Predicate> predicatesLocal = new ArrayList<>();

    if(tagsDayFilter.getDataInicial() != null) {
        predicatesLocal.add(
            builder.greaterThanOrEqualTo(root.get("data_coleta"), tagsDayFilter.getDataInicial())
        );
    }

    if(tagsDayFilter.getDataFinal() != null) {
        predicatesLocal.add(
            builder.lessThanOrEqualTo(root.get("data_coleta"), tagsDayFilter.getDataFinal())
        );
    }       

    Predicate pred = builder.or( orPredicates(builder, predicatesLocal) );
    predicates.add(pred);
    // -----------------------------------------------------

    return predicates.toArray(new Predicate[predicates.size()]);
}

This method will return an SQL similar to :

SELECT TAG.id, TAG.data_coleta, TAG.tags_id, TAG.valor
  FROM tags_day TAG
 WHERE TAG.id = 1
   AND TAG.id = 2
   AND (TAG.data_coleta >= ? OR TAG.data_coleta <= ?)