Really dynamic JPA CriteriaBuilder

Question

I need to create a "real" dynamic JPA CriteriaBuilder. I get an Map<String, String> with the statements. It looks like:

name : John
surname : Smith
email : email@email.de

...more pairs possible

Here is what i implement:

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<User> query = cb.createQuery(User.class);
Root<User> userRoot = query.from(User.class);
query.select(userRoot);

List<Predicate> predicates = new ArrayList<Predicate>();
Iterator<String> column = statements.keySet().iterator();
while (column.hasNext()) {

    // get the pairs
    String colIndex = column.next();
    String colValue = statements.get(colIndex);

    // create the statement
    Predicate pAnd = cb.conjunction();
    pAnd = cb.and(pAnd, cb.equal(userRoot.get(colIndex), colValue));
    predicates.add(pAnd);
}

// doesn't work, i don't know how many predicates i have -> can not address them
query.where(predicates.get(0), predicates.get(1), ...);

// doesn't work, because it is a list of predicates
query.where(predicates);

// doesn't work, because the actual predicate overwrites the old predicate
for (Predicate pre : predicates) {
     query.where(pre)
}

I tried to build a big Predicate, which contains all other predicates and add this to the query.where(), but again the predicates overwrites old values. Looks like there is no possibility to add a Predicate instead of change a Predicate :-(

The real project is even more complicated, because some pairs requires an equal and some other a like. And that is not even enough: There could a extra statement with or included like type : 1;4;7. Here the value have to split up and create a statement like:

<rest of statement> AND (type = 1 OR type = 4 OR type = 7)

UPDATE and SOLUTION Got two lists, first List for AND works well. Second list contains OR statements like exspected:

final List<Predicate> andPredicates = new ArrayList<Predicate>();
final List<Predicate> orPredicates = new ArrayList<Predicate>();
for (final Entry<String, String> entry : statements.entrySet()) {
    final String colIndex = entry.getKey();
    final String colValue = entry.getValue();
    if (colIndex != null && colValue != null) {

        if (!colValue.contains(";")) {
            if (equals) {
                andPredicates.add(cb.equal(userRoot.get(colIndex), colValue));
            } else {
                andPredicates.add(cb.like(userRoot.<String> get(colIndex), "%" + colValue + "%"));
            }
        } else {
            String[] values = colValue.split(";");
            for (String value : values) {
                orPredicates.add(cb.or(cb.equal(userRoot.get(colIndex), value)));
            }
        }       
    }
}

// Here goes the magic to combine both lists
if (andPredicates.size() > 0 && orPredicates.size() == 0) {
    // no need to make new predicate, it is already a conjunction
    query.where(andPredicates.toArray(new Predicate[andPredicates.size()]));
} else if (andPredicates.size() == 0 && orPredicates.size() > 0) {
    // make a disjunction, this part is missing above
    Predicate p = cb.disjunction();
    p = cb.or(orPredicates.toArray(new Predicate[orPredicates.size()]));
    query.where(p);
} else {
    // both types of statements combined
    Predicate o = cb.and(andPredicates.toArray(new Predicate[andPredicates.size()]));
    Predicate p = cb.or(orPredicates.toArray(new Predicate[orPredicates.size()]));
    query.where(o, p);
}

query.where(predicates.toArray(new Predicate[predicates.size()]));
users = em.createQuery(query).getResultList();

score 63 · Accepted Answer · answered Jun 21 '12 at 12:32

63

You can pass an array of predicates to the CriteriaBuilder, deciding on equal or like as you go. For this, build a list and pack the contents of the list into an array in a single and statement. Like this:

final List<Predicate> predicates = new ArrayList<Predicate>();

for (final Entry<String, String> e : myPredicateMap.entrySet()) {

    final String key = e.getKey();
    final String value = e.getValue();

    if ((key != null) && (value != null)) {

        if (value.contains("%")) {
            predicates.add(criteriaBuilder.like(root.<String> get(key), value));
        } else {
            predicates.add(criteriaBuilder.equal(root.get(key), value));
        }
    }
}

query.where(criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()])));
query.select(count);

In case you need to distingiush between and and or, use two lists.

answered Jun 21 '12 at 12:32

kostja

60,521
48
179
224

Okay, for my AND statements that work. I create a second List and stored my OR statements in that list. At the end, I do what? Copy both list togehter results in AND for both types of statements. My `orPredicates` list contains the correct elements. Even when I only add this list to the query, all statements put together with AND. – Felix Jun 21 '12 at 14:32
1

You can create a disjunction (or) the same way you create a conjunction (and) - by calling `or` and `and` methods on the CriteriaBuilder. The return is a single `Predicate` in both cases - so if you dont have some sort of complex grouping, simply call `and` on the two resulting Predicates and use the result in the `where` – kostja Jun 21 '12 at 15:25
Maybe I got it now, I updated the main question and post my solution. – Felix Jun 22 '12 at 07:01

score 22 · Answer 2 · answered Jun 21 '12 at 12:34

One option is to use the fact that method with variable number of arguments can take an array:

query.where(predicates.toArray(new Predicate[predicates.size()]));

Alternatively, you can combine them into a single predicate (note that if you don't do it, you don't need to create a conjunction as in your example);:

Predicate where = cb.conjunction();
while (column.hasNext()) {
    ...
    where = cb.and(where, cb.equal(userRoot.get(colIndex), colValue));
}

query.where(where);

score 6 · Answer 3 · answered Nov 24 '14 at 03:53

I have always been thinking that creation of solution like that is like reinventing the bicycle. Here https://github.com/sasa7812/jfbdemo is my solution. It was tested on EclipseLink and Hibernate (EclipseLink in production, we used it in several projects for simple cases). Sometimes you just need a quick solution to make a dataTable with sorting and filtering, nothing fancy. It is able to filter and sort on joined fields, and even on collections. Project contains demo on Primefaces showing the abilities of FilterCriteriaBuilder. In the heart of it you just need this:

 public List<T> loadFilterBuilder(int first, int pageSize, Map<String, Boolean> sorts, List<FieldFilter> argumentFilters, Class<? extends AbstractEntity> entityClass) {
    FilterCriteriaBuilder<T> fcb = new FilterCriteriaBuilder<>(getEntityManager(), (Class<T>) entityClass);
    fcb.addFilters(argumentFilters).addOrders(sorts);
    Query q = getEntityManager().createQuery(fcb.getQuery());
    q.setFirstResult(first);
    q.setMaxResults(pageSize);
    return q.getResultList();
}

to get the results from database.

I really need someone to tell me that it is usefull and is used somewhere to continue my work on this library.

score 2 · Answer 4 · answered Oct 04 '19 at 21:35

2

You can convert to Predicate ... as follows

query.where( predicates.stream().toArray( Predicate[]::new ) );

answered Oct 04 '19 at 21:35

David

156
8

1

or even shorter query.where( predicates.toArray( Predicate[]::new ) ); – Andrii Fedorenko Jul 18 '21 at 13:41

score 0 · Answer 5 · answered Nov 07 '16 at 04:17

0

For Predicate you can create new ArrayList add all your predicate to the list and then at the end you can create a predicate array and convert list to predicate array and directly assign it to criteriabuilder.where(new Pridicate[])

answered Nov 07 '16 at 04:17

Rahul Jain

1

Really dynamic JPA CriteriaBuilder

5 Answers5

Linked

Related