Library

SQL is quite old, yet it lacks quite many useful feature existing in other languages. One of them is a preprocessor or something similar. No worries, I'm familiar with the criticism and mostly agree with it. Yet, it must be admitted, that the functionality lacks. And considering how repetitive SQL is, the problem does exist and is serious.

FluentJPA supports functions and therefore offers an out-of-the box solution. You can now write your own library of queries in Java and forget about SQL limitations.

FluentSQL offers a quite useful Library of functions of its own. All of them are up to 4 lines, yet them save boilerplate code and improve readability:

collect()

Returns a single column table projection as a Java Collection. Is very powerful in combination with IN operator. Takes 5 minutes to learn, but definitely worth it. See step-by-step guide.

collectRows()

Same concept as collect, extended to multiple columns. Not supported by some vendors (e.g. SQL Server). See ROW sub queries.

COUNT()

A shortcut to COUNT(*).

ISNULL()

Use if your vendor does not support it natively. Implemented as:

static <T extends Comparable<? super T>> T ISNULL(T expression1,
                                                  T expression2) {
    return COALESCE(expression1, expression2);
}

LIMIT()

Translates to:

static void LIMIT(long rowCount) {
    FETCH(rowCount).ROWS();
}

pick()

Returns a single column/single row table projection as a scalar. Same benefits as collect. See step-by-step guide.

pickRows()

Same concept as pick, extended to multiple columns.

selectAll(), selectMany()

Similar and simple functions, self explaining implementation:

static void selectAll(Object tableRef) {
    SELECT(tableRef); // produces tableRef.*
    FROM(tableRef);
}

static void selectMany(Object tableRef,
                       Object... fields) {
    SELECT(fields);
    FROM(tableRef);
}

Note, those functions are not sub queries and behave much like macros.

Operators

Unfortunately Java operators are neither generic nor extensible. For example, comparison and mathematic operators are defined on primitives and numbers only. In SQL it's not always the case. FluentJPA provides a library of operators as functions. When they are translated to SQL a correct symbol and expression are generated.

Examples of usage:

add(bigInt1, bigInt2);
divide(bigInt1, bigInt2);
lessEqual(bigInt1, bigInt2);