Dynamic Search Filter Using Querydsl [Spring Boot]

In this tutorial we will be creating a dynamic search filter. For any field it will generate sql query dynamically from the provided search parameter.

We will generate a Dynamic JPA query for Branch entity class from the search parameter provided.

Branch.class

@Getter
@Setter
@Entity
@Table(name = "BRANCH")
public class Branch implements Serializable {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Basic(optional = false)
@Column(name = "ID")
private Long id;
@Basic(optional = false)
@Column(name = "BRANCH_CODE", nullable = false, length = 20)
private String branchCode;
@Basic(optional = false)
@Column(name = "BRANCH_NAME", nullable = false, length = 150)
private String branchName;
@Basic(optional = false)
@Column(name = "BRANCH_LOCATION", nullable = false)
private String branchLocation;
@Column(name = "LATITUDE")
private String latitude;
@Basic(optional = false)
@Column(name = "LONGITUDE")
private String longitude;
@Column(name = "CONTACT_NO")
private String contactNo;
}

From the Branch entity any field can be queried dynamically. For this we will prepare Query parameter which will be used to build query from Branch entity.

FilterDetails.class
@Getter
@Setter
public class FilterDetails implements Serializable {

    private List<FieldQueryParameter> filterFieldParams;
}

FieldQueryParameter will contains the dynamic search parameter.

FieldQueryParameter.class

@Getter
@Setter
public class FieldQueryParameter implements Serializable {

    //column name
    private String fieldKey;
    //column value
    private Object fieldValue;
    /*
    and, or
     */
    private String fieldCondition;
    // like and order
    private String fieldOperator;
}

In above FieldQue.ryParameter.class fieldKey is for the field name in Branch entity. fieldValue contains value to be searched. fieldCondition is to define if condition is “OR” or “AND”. fieldOperator will contains operator like greater than, less than, like equal and so on.

    FilterDetails filterDetails = new FilterDetails();

        List<FieldQueryParameter> fieldQueryParameterlist = new ArrayList<>();
        FieldQueryParameter queryParameter = new FieldQueryParameter();
        queryParameter.setFieldCondition("&&");
        queryParameter.setFieldKey("branchName");
        queryParameter.setFieldOperator("::");
        queryParameter.setFieldValue("test");
        fieldQueryParameterlist.add(queryParameter);

        FieldQueryParameter queryParameter1 = new FieldQueryParameter();
        queryParameter1.setFieldCondition("&&");
        queryParameter1.setFieldKey("branchCode");
        queryParameter1.setFieldOperator("==");
        queryParameter1.setFieldValue("9843559404");
        fieldQueryParameterlist.add(queryParameter1);

        filterDetails.setFilterFieldParams(fieldQueryParameterlist);

We will be defining operator task as

import com.querydsl.core.types.Operator;
import com.querydsl.core.types.Ops;
  
protected Map<String, Operator> operators = ImmutableMap.<String, Operator>builder()
            .put("::", Ops.STRING_CONTAINS)
            .put("==", Ops.EQ)
            .put("!=", Ops.NE)
            .put(">", Ops.GT)
            .put("<", Ops.LT)
            .put(">=", Ops.GOE)
            .put("<=", Ops.LOE)
            .put("NOT_NULL", Ops.IS_NOT_NULL)
            .put("&&", Ops.AND)
            .put("||", Ops.OR)
            .build();

For “::” operator supplied “like” query will be build and “==” operator will build equals to query.

 @Autowired
 private BranchRepository branchRepository;

  GenericPredicate genericPredicate = new GenericPredicate(fieldQueryParameter, QBranch.class);
        Page<Branch> branchList = branchRepository.findAll(genericPredicate.getPredicate(), pageable);

QBranch.class is the Qentity for Branch entity. Qentity is generated by querydsl. Genrate Qentity using querydsl.

GenericPredicate.class
package com.f1soft.smartbank.Specification;

import com.f1soft.smartbank.common.service.dto.FieldQueryParameter;
import com.google.common.collect.ImmutableMap;
import com.querydsl.core.BooleanBuilder;
import com.querydsl.core.types.Operator;
import com.querydsl.core.types.Ops;
import com.querydsl.core.types.Predicate;
import com.querydsl.core.types.dsl.Expressions;
import lombok.extern.slf4j.Slf4j;
import org.springframework.util.ReflectionUtils;
import org.springframework.util.StringUtils;

import java.util.List;
import java.util.Map;

/**
 * @author Rashim Dhaubanjar
 */
@Slf4j
public class GenericPredicate {

    private List<FieldQueryParameter> parameters;
    private Class model;

    public GenericPredicate(List<FieldQueryParameter> parameters, Class model) {
        this.parameters = parameters;
        this.model = model;
    }

    static Map<String, Operator> operators = ImmutableMap.<String, Operator>builder()
            .put("::", Ops.STRING_CONTAINS)
            .put("==", Ops.EQ)
            .put("!=", Ops.NE)
            .put(">", Ops.GT)
            .put("<", Ops.LT)
            .put(">=", Ops.GOE)
            .put("<=", Ops.LOE)
            .put("NOT_NULL", Ops.IS_NOT_NULL)
            .put("&&", Ops.AND)
            .put("||", Ops.OR)
            .build();

    public Predicate getPredicate() {

        BooleanBuilder booleanBuilder = new BooleanBuilder();
        parameters
                .stream()
                .forEach(param -> {
                    if (ReflectionUtils.findField(model, param.getFieldKey()) == null) {
                        log.warn("Skipping predicate matching on [%s]. It is not a known field on domainType %s", param, model.getName());
                        return;
                    }
                    if (StringUtils.hasLength(String.valueOf(param.getFieldValue()))) {
                        if (StringUtils.hasLength(String.valueOf(param.getFieldCondition()))) {
                            if (String.valueOf(param.getFieldCondition()).equalsIgnoreCase("||")) {
                                booleanBuilder.or(matchesProperty(param, model));
                            } else {
                                booleanBuilder.and(matchesProperty(param, model));
                            }
                        } else {
                            booleanBuilder.and(matchesProperty(param, model));
                        }
                    }
                });

        log.debug("Boolean builder value : {}", booleanBuilder.getValue());
        return booleanBuilder;
    }

    private Predicate matchesProperty(FieldQueryParameter param, Class<?> model) {
        return Expressions.predicate(operators.get(param.getFieldOperator()), Expressions.path(model, param.getFieldKey()), Expressions.constant(param.getFieldValue()));
    }
}

Above generic predicate will build the where clause for the search filter.

BranchRepository.class
public interface BranchRepository extends BaseRepository<Branch> {
}
BaseRepository.class
@NoRepositoryBean
public interface BaseRepository<T> extends JpaRepository<T, Long>, JpaSpecificationExecutor<T>, QuerydslPredicateExecutor<T> {
}