javaspring-data-jpaquerydsl

Build WHERE clause dynamically based on filter in queryDSL

I have a query that needs a dynamic SQL WHERE clauses based on filters.

Issue

It may give me a NullPointerException (NPE) if I'm adding an "AND" while the previous filter is not existing.

Code

public List<BlocageDeblocageCompte> getMyQuery(FiltersDTO filters) {

    JPAQuery<MY_ENTITY> myEntity = getJPAQueryFactory().selectFrom(myEntity).limit(20);
    BooleanExpression whereClause = null;

    boolean whereClauseAdded = false;
    boolean ifNoFilter = Stream.of(myEntity).allMatch(Objects::isNull);

    if (ifNoFilter) {
        return new ArrayList<>(myEntity.fetchAll().fetch());
    }
            
    if (filters.getId() != null) {
        whereClause = myEntity.id.eq(filters.getId());
        whereClauseAdded = true;
    }

    if (filters.getName() != null) {
        if (whereClauseAdded) {
            whereClause = whereClause.and(myEntity.id.eq(filters.getName()));
        } else {
            whereClause = myEntity.id.eq(filters.getName());
            whereClauseAdded = true;
        }
    }

    // remaining code
}

Question

Is there a better way to add the filters, without risking a NPE?

Solution

  • To construct complex boolean queries like this you can use com.querydsl.core.BooleanBuilder:

    public List<MyEntity> getMyQuery(FiltersDTO filters) {
    MyEntity myEntity = QModel.myEntity;

    // build an optional WHERE clause with predicates using AND conjunctions 
    BooleanBuilder builder = new BooleanBuilder();

    if (filters.getId() != null) {
        builder.and(myEntity.id.eq(filters.getId()));   
    }
    
    if (filters.getName() != null) {
        builder.and(myEntity.id.eq(filters.getName()));   
    }
    
    // construct the query
    return getJPAQueryFactory().selectFrom(myEntity)
        .limit(20)
        .where(builder)  // myEntity.id eq id1 AND myEntity.name eq name1
        .fetch();            
}

    See also

    Similar questions:

    References: