CriteriaQuery and CriteriaBuilder not giving expected result

Question

I'm using CriteriaQuery and CriteriaBuilder to mount a dynamic where. I have some filtering values that user selects and can be informed or not. But using a conjunction predicate doesn't return a correct where clause. This is my code:

// Create CriteriaBuilder
CriteriaBuilder builder = session.getCriteriaBuilder();
// Create CriteriaQuery
CriteriaQuery<Tarea> criteria = builder.createQuery(Tarea.class);
// Set root
Root<Tarea> root = criteria.from(Tarea.class);
criteria.select(root);
if(getSortColumn()!= null && !getSortColumn().equals("")) {
    criteria.orderBy(builder.asc(root.get(getSortColumn())));
}
Predicate predFinal = builder.conjunction();
if(filtro.getClasificacionTarea() != null && filtro.getClasificacionTarea().size()>0) {
    Predicate predClasificacion = builder.disjunction();
    for(Clasificacion clas : filtro.getClasificacionTarea()) {
        predClasificacion.getExpressions().add(builder.equal(root.get("clasificacion").get("id"), clas.getId()));
    }
    predFinal.getExpressions().add(predClasificacion);
}
if(filtro.getProyecto() != null) {
    Predicate predicateProyecto;
    predicateProyecto = builder.and(builder.equal(root.get("OT").get("proyecto"), filtro.getProyecto()));
    predFinal.getExpressions().add(predicateProyecto);
}
if(filtro.getCliente() != null) {
    Predicate predicateCliente;
    predicateCliente = builder.and(builder.equal(root.get("ot").get("cliente").get("id"), filtro.getCliente().getId()));
    predFinal.getExpressions().add(predicateCliente);
}
if(filtro.getProvincia() != null) {
    Predicate predicateProvincia;
    predicateProvincia = builder.and(builder.equal(root.get("ot").get("provincia").get("id"), filtro.getProvincia().getId()));
    predFinal.getExpressions().add(predicateProvincia);
}
if(filtro.getPais() != null) {
    Predicate predicatePais;
    predicatePais = builder.and(builder.equal(root.get("ot").get("pais").get("id"), filtro.getPais().getId()));
    predFinal.getExpressions().add(predicatePais);
}
predFinal.getExpressions().add(builder.and(builder.isNull(root.get("fechaBaja"))));
criteria.where(predFinal);
// Query execution
Query<Tarea> query = session.createQuery(criteria);

And the resulting query, when filtering by any of the fields:

Hibernate: 
    select
        t1_0.id,
        t1_0.fk_idPedidoVenta,
        t1_0.fk_idClasificacionTarea,
        t1_0.confirmado,
        t1_0.fk_idUsuarioCreacion,
        t1_0.fechaCreacion,
        t1_0.fechaBaja,
        t1_0.fechaInicio,
        t1_0.fechaFin,
        t1_0.finalizado,
        t1_0.horaInicio,
        t1_0.horaFin,
        t1_0.observaciones,
        t1_0.fk_idTipoTarea 
    from
        Tarea t1_0 
    where
        1=1 
    order by
        t1_0.fechaInicio

Where clause is always 1=1 so it returns all records.

It's my first time using CriteriaBuilder so maybe I'm using it wrong? Thanks for your help

Answer 1

That is probably because you use Predicate.getExpressions to gather the predicates. According to the documentation, adding predicates directly to this list does not affect the query, i.e., the method most likely returns a defensive copy of the list of predicates. To fix it, I would create a list of Predicates manually:

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

add the individual predicates to it (based on the if conditions) and then use the CriteriaBuilder.and to get a conjunction:

criteria.select(root).where(predicates.toArray(Predicate[]::new));

Answer 2

You can't update the expressions of a Predicate. From Predicate.getExpressions() (emphasis added):

Return the top-level conjuncts or disjuncts of the predicate. Returns empty list if there are no top-level conjuncts or disjuncts of the predicate. Modifications to the list do not affect the query.

It seems like conjuction() is not the way to go:

Create a conjunction (with zero conjuncts). A conjunction with zero conjuncts is true.

So instead of using conjuction(), create a List<Predicate>, add to that, and then use it:

criteria.where(predicateList.toArray(Predicate[]::new));

Alternatively, and I think this is why conjuction() exists, don't add to its expressions but replace the predicate itself:

// predFinal.getExpressions().add(predClasificacion);
predFinal = builder.and(predFinal, predClasificacion);