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?

CodePudding user response：

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:

• Dynamic search term SQL query with Spring JPA or QueryDSL
• JPA QueryBuilder
• How to write JPA query with boolean condition

References:

• QueryDSL Reference Guide: 3.1.1. Complex predicates
• QueryDSL Issue on GitHub: BooleanBuilder with multiple AND conditions · Discussion #2936
• Baeldung's Tutorial: A Guide to Querydsl with JPA

CodePudding user response：

you can use Specification in JPA:here is the link that i used myself

https://www.baeldung.com/rest-api-search-language-spring-data-specifications

CodePudding user response：

I found out a simple solution, when we do WHERE on all of them it transform automatically the second where to AND. thank u guys.

    JPAQuery<MyEntity> myEntityJPAQUERY=
            getJPAQueryFactory()
                    .selectFrom(QmyEntity);
    
    if (filtresDTO.getId() != null) {
        myEntityJPAQUERY=myEntityJPAQUERY.where(QmyEntity.id.eq(ffiltresDTO.getId()));

    if (filtresDTO.getName() != null) {
myEntityJPAQUERY=myEntityJPAQUERY.where(QmyEntity.name.eq(ffiltresDTO.getName()))
    }