CODEWITHSUNDEEP
javasqlspring-bootjooq
Tuco
Tuco

Reputation: 1052

Dynamically building conditions in JOOQ

I have an endpoint that receives a string with all the columns and value they want to filter by. Here's an example of what the filters param looks like: ?filter=active=true,type=ADMIN, basically each filter is separated by a , and then the column name and filter value are separated by an = So I made this method to dynamically build a condition statement with all the different filters for any table:

public Condition mapFilterToCondition(String tableName, String filters) {
    Condition condition = DSL.trueCondition();
    String[] filterFields = filters.split(",");
    Table<?> table = PUBLIC.getTable(tableName);
    Field<?>[] fields = table.fields();
    for(String filterField : filterFields) {
        String[] filter = filterField.split("=");
        for(Field<?> field : fields) {
            String fieldName = CaseFormat.LOWER_CAMEL.to(CaseFormat.LOWER_UNDERSCORE, filter[0]);
            if(field.getName().equals(fieldName)) {
                if(field.getType() == String.class) {
                    condition.and(table.field(field).like(filter[1]));
                } else if (Boolean.valueOf(filter[1])) {
                    condition.and(table.field(field).isTrue());
                } else if (!Boolean.valueOf(filter[1])) {
                    condition.and(table.field(field).isFalse());
                }
            }
        }
    }
    return condition;
}

I then use the condition built by this method to call a repository that makes the sql request:

public List<UserAccount> findAllByFilter(Condition condition, Pageable pageable) {
    return dsl.select()
            .from(USER_ACCOUNT)
            .where(condition)
            .limit(pageable.getPageSize())
            .offset((int) pageable.getOffset())
            .fetchInto(UserAccount.class);
}

As I understood, the condition should have something like this:

.where(USER_ACCOUNT.TYPE.like("ADMIN")) .where(USER_ACCOUNT.ACTIVE.isTrue())

based on the condition I built, but whenever I make the call, it returns every single user, not taking into account the filters. While debugging I found out the condition is never changing from 1 = 1, which is the value it has from DSL.trueCondition();, so my question is how I should be handling the construction of this condition? Am I using condition.and(...) wrong, or should I be using something else instead?

Any help is appreciated!

EDIT

@GetMapping(value = "/admin", produces = "application/json")
public ResponseEntity listAll(String filters, Pageable pageable) {
    Condition condition = filterMapService.mapFilterToCondition("user_account", filters);
    List<UserAccount> adminAccounts = userAccountRepository.findAllByFilter(condition, pageable);
    if (adminAccounts.isEmpty()) {
        return new ResponseEntity(HttpStatus.NO_CONTENT);
    }
    List<AdminDTO> accounts = adminAccounts.stream()
            .map(account -> modelMapper.map(account, AdminDTO.class)).collect(Collectors.toList());
    return new ResponseEntity<>(accounts, HttpStatus.OK);
}

Upvotes: 1

Views: 4419

Answers (1)

Rick Ridley
Rick Ridley

Reputation: 583

Change all references to 

condition.and(...)

to:

condition = condition.and(...)

The value of the Condition object is not actually getting updated.

Upvotes: 3

Related Questions