CODEWITHSUNDEEP
javaspring-data-jpaquerydsl
Abdel
Abdel

Reputation: 35

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?

Upvotes: 0

Views: 1818

Answers (3)

DaniyalVaghar
DaniyalVaghar

Reputation: 179

You can implement the Specification interface in JPA:

  1. first of all, create a Filter class with static predicate-methods to handle fields in WHERE clause:
public class Filter{

    public static Specification<BlocageDeblocageCompte> hasName(String name) {
        return (root, query, cb) -> {
            if (name == null || name.equals("")) {
                 return cb.isTrue(cb.literal(true)); // always true = no filtering
            }
            return cb.like(root.get("name"), "%" + name + "%");
        };
    }

    public static Specification<ActiveAlarm> hasId(Integer id) {
        return (root, query, cb) -> {
            if (id == null) {
                return cb.isTrue(cb.literal(true)); // always true = no filtering
            }
            return cb.equal(root.get("id"), id);
        };
    }
}
  1. then, use the Filter class in your methods:
repository.findAll(where(Filter.hasName(filters.getName()).and(Filter.hasId(filters.getId()));

For more details, see:

Upvotes: 1

Abdel
Abdel

Reputation: 35

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()))
    }

Upvotes: -1

hc_dev
hc_dev

Reputation: 9377

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:

Upvotes: 1

Related Questions