Dynamic Queries

If we need a dynamic query in JPA, the only option to do it is with Criteria API. It works, but requires an effort to learn. In addition, it's very verbose compared to the original query, not type-safe, contains hard coded names as strings. In short, a maintenance headache.

Solution

FluentJPA is about writing SQL in Java. How do we make a Java code dynamic? Usually we think of a template with placeholders to inject the dynamic parts. For each dynamic part we will need a Lambda that will dynamically supply its content (logic). This is the approach FluentJPA takes. Let's see an example:

@Data
@Entity
@Table(name = "PERSON_TABLE")
public class Person {
    @Id
    private int id;
    private String name;
    private int age;
    private boolean active;
}

public List<Person> getByNameAndAge(String name,
                                   int age,
                                   boolean filterByAge) {

    // prepare dynamic filter before a call to FluentJPA.SQL()
    Function2<Person, Integer, Boolean> dynamicFilter = getAgeFilter(filterByAge);

    // build dynamic query
    FluentQuery query = FluentJPA.SQL((Person p) -> {
        SELECT(p);
        FROM(p);

        WHERE(p.getName() == name && dynamicFilter.apply(p, age));
        // use filter  --------------^^^^^^^^^^^^
    });

    return query.createQuery(em, Person.class).getResultList();
}

@Local // required for inline Lambda, see below
private Function2<Person, Integer, Boolean> getAgeFilter(boolean filterByAge) {
    if (filterByAge)
        return (person, age) -> person.getAge() == age;
        // this is injected     [ - - - - - - - - -> ]
        // if filterByAge is true

    return (x, y) -> true;
// this is injected -^^^^
// if filterByAge is false
}

Lambdas can be easily composed:

Function2<Person, Integer, Boolean> dynamicFilter = chain(getAgeFilter(filterByAge));

private static Function2<Person, Integer, Boolean> 
         chain(Function2<Person, Integer, Boolean> filter) {
    return filter.or((p, i) -> p.isActive());
}

Lambda can be created inline:

Function2<Person, Integer, Boolean> dynamicFilter =
        (p, i) -> getAgeFilter(filterByAge).apply(p, i) && p.isActive();

Formal Guide

1. The dynamic part should be supplied in the form of a Lambda.

2. Lambda(s) must be resolved before the call to FluentJPA.SQL() (as shown in the example) and not inside it.

3. Lambda functional type must be Serializable. Simply use the supplied Function0-6 or Consumer0-16 interfaces (see in examples).

4. If you mix "Java side" calls with SQL, like this:

   Function2<Person, Integer, Boolean> dynamicFilter =
       //                  intended to be part of SQL  [ - - - - -> ]
       (p, i) -> getAgeFilter(filterByAge).apply(p, i) && p.isActive();
       //       [ - - - - - - - - - - -> ] intended for "local" evaluation

   Local (Java) side function must be annotated with @Local, like getAgeFilter() function. @Local annotation instructs FluentJPA to evaluate the function instead of translating it.

5. Parameters - the trickiest part. All (and only those) parameters that should be sent as part of the query to the database must be Lambda parameters. Let's understand it on getAgeFilter() example:

   • Why the return type is Function2<Person, Integer, Boolean>?
   • The expression we need depends on Person and age parameters. And we also use filterByAge to decide which expression to take.
   • With Person argument it's clear - it's declared in the scope of SQL anyway, so getting it via a parameter is the easiest way to obtain it.
   • What is the second Integer argument? It's for age parameter. Since we want it to be sent to the database, it's a Lambda parameter as well.
   • What about filterByAge? Should it be a Lambda parameter as well? No, it's used for logic to build the query itself. We don't intend to send it to the database.

   To review the generated query, call FluentQuery.toString().