Skip to content

Dynamic Queries

Jump to bottom
Konstantin Triger edited this page Jun 4, 2020 · 12 revisions

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()
    Function1<Person, Boolean> dynamicFilter = getAgeFilter(age, filterByAge);

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

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

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

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

    return Function1.FALSE();
// this is injected -^^^^^^
// if filterByAge is false
}

Lambdas can be easily composed in different ways (all options below produce the same result):

// chaining
Function1<Person, Boolean> dynamicFilter = chain(getAgeFilter(age, filterByAge));

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

// "fluent" style
Function1<Person, Boolean> dynamicFilter = getAgeFilter(age, filterByAge)
                                                    .or(Person::isActive);

// full lambda style
Function1<Person, Boolean> dynamicFilter = p ->
                  getAgeFilter(age, filterByAge).apply(p) || p.isActive();

In a loop boolean composition:

List<Person> getByNameLike(List<String> likes) {

    Function1<Person, Boolean> dynamicFilter = buildOr(likes);

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

        WHERE(dynamicFilter.apply(p));
    });

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

private Function1<Person, Boolean> buildOr(List<String> likes) {
    // for boolean 'OR' composition the seed must be FALSE
    Function1<Person, Boolean> criteria = Function1.FALSE();

    for (String like : likes)
        // boolean (or/and) composition
        criteria = criteria.or(p -> p.getName().matches(parameter(like)));

    return criteria;
}

In a loop varargs composition:

The common batch insert case is handled by the framework, see INSERT. Below is an advanced usage where full control over List<> to arguments conversion is required.

int insertMultiple(List<String> likes) {

    // each record has int and String values.
    // we want to insert a record per "like"
    Function0<Record2<Integer, String>[]> arguments = buildVarargs(likes);

    FluentQuery query = FluentJPA.SQL((NumberLetter nl) -> {
        INSERT().INTO(nl);
        VALUES(arguments.get());
    });

    return query.createQuery(em).executeUpdate();
}

private Function0<Record2<Integer, String>[]> buildVarargs(List<String> likes) {

    // start with an empty array
    Function0<Record2<Integer, String>[]> args = Function0.emptyArray();

    int x = 0;
    for (String like : likes) {
        Function0<Record2<Integer, String>[]> arg = args;
        int y = x++;
        // varargs directive combines a new row with existing rows
        args = () -> varargs(row(y, parameter(like)), arg.get());
    }

    return args;
}

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:

    Function1<Person, Boolean> dynamicFilter =
    //                 intended to be part of SQL  [ - - - - -> ]
    p -> getAgeFilter(age, filterByAge).apply(p) || 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 query internal variables should be passed via Lambda arguments. All external parameters that should be sent as part of the query to the database must be set using parameter() method. Let's understand it by getAgeFilter() example:

    • Why the return type is Function1<Person, Boolean>?
    • The expression we need depends on Person and age parameters. And we also use filterByAge to decide which expression to take.
    • Person is an internal variable, therefore is passed via Lambda argument.
    • age is an external parameter that we want to be sent to the database, passed with parameter().
    • What about filterByAge? It's used for logic to build the query itself. We don't intend to send it to the database, therefore is used as usual.

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

Getting Started

Examples

Basic SQL DML Statements

Advanced SQL DML Statements

Advanced Topics

Clone this wiki locally