Specification with Spring Data Jpa passing null parameter

Question

I’m using the Specifications Spring Data JPA, but I’m having a hard time! By not passing any parameter in the two fields I want it to return all records to me. But when making the query it is returning me only those that have the two respective null/empty fields in the database!

      public class SistemaSpecs {

public static Specification<Sistema> descricao(String descricao) {
    return (root, query, builder) -> 
      builder.or(builder.equal(root.get(Sistema_.descricao),  descricao));
     }
public static Specification<Sistema> sigla(String sigla) {
    return (root, query, builder) -> 
      builder.or(builder.equal(root.get(Sistema_.sigla),sigla));
     }
  }

The method that calls and consults is this.

 public Page<Sistema> pesquisar(Sistema sistema, Pageable pageable) {
    Specifications<Sistema> where = 
         where(SistemaSpecs.descricao(sistema.getDescricao()))
            .or(SistemaSpecs.sigla(sistema.getSigla()));
     return sistemaRepository.findAll(where, pageable);
 }

Someone how can I recover all the records when the parameter passed is null, without according to the same structure ?

Answer 1

In the class Systemsspecs that you created each method represents a criterion for your query, it is the responsibility of the class user to perform the checks, and as you commented, it ends up generating a lot of code if I have to repeat the validations everywhere I need to perform this query.

My suggestion is that instead of creating a class with query criteria methods, you create a class that implements Specification and within this class you define your query clauses placing the necessary validations, hence your validations will be in a single place.

Although I do not know the code of your System class I will create a random example to exemplify this situation:

We have the following class Entity for states (São Paulo, Rio de Janeiro, etc):

@Entity
    public classe Estado Serializable{

        private static final long serialVersionUID = 1L;

        private Long id;
        private String nomeEstado;
        private String sigla;

        //Contrutores, Gets e Sets

From this class you will create your repository (I understand that this using this approach), however the repository should inherit the Jpaspecificationexecutor interface related to the entity involved, in this case the State class.

@Repository
public interface EsdadoRepository extends JpaRepository<Estado,Long >, JpaSpecificationExecutor<Estado> { 
    }

Great, our scenario ready! Now comes the creation of a class that implements the Specification interface referring to our entity State. It is in this class that we will put our criteria and field validations.

public class EstadoSpecification implements Specification<Estado> {

        private static final long serialVersionUID = 1L;

        private String nomeEstado;
        private String sigla;

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

    public EstadoSpecification(String nomeEstado,String sigla) {
        super();
        this.nomeEstado = nomeEstado;
        this.estado = sigla;
    }

    @Override
    public Predicate toPredicate(Root<ItemVendedor> root, CriteriaQuery<?> query, CriteriaBuilder builder) {

        if (!nomeEstado.isEmpty()) {
            predicates.add(builder.like(builder.upper(root.get("nomeEstado")), "%" + nomeEstado.toUpperCase() + "%"));; 
        }

        if(!sigla.isEmpty()) {
           predicates.add(builder.like(builder.upper(root.get("sigla")), "%" + sigla.toUpperCase() + "%"));;
        }   
        return builder.and(predicates.toArray(new Predicate[1]));
        }
}

Then, within a state query method in your service class, vc informs the query parameters (description and status) as follows:

    public List<Estado> buscarEstados(String nomeEstado, String sigla) {

            Specification<Estado> spec = new EstadoSpecification(nomeEstado,estado); 

            List<Estado> lista = EstadoRepository.findAll(spec);

    return lista;
}

ready, the query will use as a criterion only the field you fill, if you omit the two will bring all states.

That’s what I understood from your post, a way to create a dynamic query in which I as a query user can omit some parameter value without it generating an error. I hope I’ve helped.

Answer 2

You have to put a check inside your function to not perform the search when the parameters are null.

public Page<Sistema> pesquisar(Sistema sistema, Pageable pageable) {
     if(sistema.getDescricao() != null && sistema.getSigla() != null){
          Specifications<Sistema> where = 
          where(SistemaSpecs.descricao(sistema.getDescricao()))
             .or(SistemaSpecs.sigla(sistema.getSigla()));
          return sistemaRepository.findAll(where, pageable);
    }else{
          return sistemaRepository.findAll();
    }
}

I hope I’ve helped.