CODEWITHSUNDEEP
springspring-data-jpaquerydsl
Vasyl Hoshovsky
Vasyl Hoshovsky

Reputation: 237

QueryDSL dynamic predicates

I need help with QueryDSL querying. I'm using this library with Spring Data JPA. My service class:

@Service("tblActivityService")
public class TblActivityService implements AbstractService<TblActivity> {

    @Resource
    private TblActivityRepository tblActivityRepository;

    @Override
    public List<TblActivity> findAll(Predicate predicate) {
        return (List<TblActivity>)tblActivityRepository.findAll(predicate);
    }
}

I have dynamic list of filters:

@Entity
@Table(name = "sys_filters")
public class SysFilter implements Serializable {
private static final long serialVersionUID = 1L;

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Basic(optional = false)
@Column(name = "filter_id")
private Integer filterId;

@JoinColumn(name = "user_id", referencedColumnName = "user_id")
@ManyToOne(fetch = FetchType.EAGER)
private SysUser userId;

@Size(max = 45)
@Column(name = "table_name")
private String tableName;

@Size(max = 45)
@Column(name = "column_name")
private String columnName;

@Size(max = 45)
@Column(name = "condition")
private String condition;

@Size(max = 100)
@Column(name = "value")
private String value;


// getters & setters

I have column name (e.g. title) I have condition (e.g. ==, !=, >= etc.) - I can store it as symbols or words (equals etc.) And finally I have value.

The question is "how to dynamically generate predicate for my service?" Table has about 25 fields.

Predicate looks like that:

public BooleanExpression buildFilteredResult(List<SysFilter> filters) {
    //TODO do it!
    return QTblActivity.tblActivity.title.eq("Value"); 
    // I need to do it dynamically for each filter in the list
}

The problem is how to invoke columnName by its string value. Do you have any suggestions?

Upvotes: 5

Views: 14301

Answers (3)

Pete_ch
Pete_ch

Reputation: 1321

A newer solution was released with spring data Gosling/Fowler. If you are creating a web app, you can use the querydsl web support that does the work for you -it reads the get parameters into a predicate and then you can use this predicate from your controller - no need to manually do that - You can customize your repository based on the search criteria (equal, like ...) needed for a particular datatype or particular entity's field. see the documentation here

Upvotes: 1

Timo Westk&#228;mper
Timo Westk&#228;mper

Reputation: 22200

It might be easier to use a mapping filter conditions to operators

Map<String, Operator> operators = ImmutableMap.of(
  "==", Ops.EQ, "!=", Ops.NE, ">", Ops.GT, "<", Ops.LT,
  ">=", Ops.GOE, "<=", Ops.LOE);

Expressions.predicate(operators.get(condition), 
  stringPath, Expressions.constant(filterValue));

Also make sure you combine your predicates properly

predicates.and(...)

returns a new predicate and leaves predicates untouched.

Maybe BooleanBuilder is what you are after?

Upvotes: 7

Vasyl Hoshovsky
Vasyl Hoshovsky

Reputation: 237

I found the solution:

public BooleanExpression buildFilteredResult(List<SysFilter> filters) {
    //TODO do it!

    QTblActivity qTblActivity = QTblActivity.tblActivity;
    BooleanExpression expression = qTblActivity.recordState;

    for (SysFilter filter : filters) {
        StringPath stringPath = new StringPath(qTblActivity, filter.getColumnName());
        switch (filter.getCondition()) {
            case "==":
                expression.and(stringPath.eq(filter.getValue()));
                break;
            case "!=":
                expression.and(stringPath.ne(filter.getValue()));
                break;
            case ">":
                expression.and(stringPath.gt(filter.getValue()));
                break;
            case "<":
                expression.and(stringPath.lt(filter.getValue()));
                break;
            case ">=":
                expression.and(stringPath.goe(filter.getValue()));
                break;
            case "<=":
                expression.and(stringPath.loe(filter.getValue()));
                break;
            default:
                break;
        }
    }

    return expression;

}

Upvotes: 0

Related Questions