Directives

FluentJPA exposes several special methods to close semantic gaps between Java and SQL. They are intended to be used in the body of SQL Lambda.

• aggregateBy(): entry point for Window Functions.
• alias(): creates a column or table alias. See detailed description.
• aliasOf(): Use previously created alias outside of SELECT to beautify the produced SQL. (Inside SELECT it's used automatically, where it actually has an effect).
• byRef(): reference the CTE which will be declared later. See detailed description.
• comment(): generates an SQL comment (prefixed with --).
• discardSQL(): discards any SQL produced by the passed statements. Used for dynamic mapping generation, e.g. @ManyToMany.
• injectSQL(): includes the passed {@code sql} as is.
• noAlias(): by default FluentJPA always generates an alias for a table reference. In some very rare vendor cases this is forbidden (e.g. Oracle's multi table INSERT). This directive instructs processor to prevent automatic table aliasing: noAlias(myTable);
• parameter(): register an argument as a query parameter. Is required for Dynamic Queries, optional in other cases.
• recurseOn(): specifies a table to recurse in recursive Common Table Expressions.
• semicolon(): generates an SQL block terminator - ;.
• subQuery(): creates a sub query.
• sequence(): creates a SEQUENCE in Oracle or SQL Server. See detailed description.
• typeOf(): generates a filter based on @DiscriminatorColumn and @DiscriminatorValue.
• varargs(): Prepends an additional element to an existing varargs array. Useful for constructing varargs for Dynamic Queries.
• viewOf(): generates a column names list. Used mostly in INSERT and MERGE. See detailed description.
• windowFrame(): lets specify a window frame in Window Functions.

Selected Detailed Descriptions

alias()

Column Aliases

Column aliases are required for mapping the query results to the entities. (FluentJPA reads JPA annotations of the entity fields and matches them with the returned columns aliases). Aliasing is not always required. See examples:

FluentQuery query = FluentJPA.SQL((Product p) -> {

    // the standard Product's column names will return
    // we can safely map the result to Product entity
    // no need to "re-map"
    SELECT(p);
    ...

    // the fields id, name will be aliased as the corresponding columns.
    // in case of COUNT, it's vendor specific.
    // Need to alias!
    SELECT(p.getId(), p.getName(), COUNT(...));
    ...
}

How to use?

// Tuple we want to return
@Tuple
@Getter
public static class CTECategoryCounts {
    private int categoryId;
    private String categoryName;
    private int productCount;
}

...

// aliasing
Integer catId = alias(cat.getId(), CTECategoryCounts::getCategoryId);
String catName = alias(cat.getName(), CTECategoryCounts::getCategoryName);
Integer productCount = alias(COUNT(), CTECategoryCounts::getProductCount);

// now you can use the alias in the same places as in SQL
SELECT(catId, catName, productCount);
...
WHERE(productCount > 45);
...
GROUP(BY(catId), BY(catName));

Table Alias

Is not required in FluentJPA since Java has variables. Affects the produced SQL only and is auto-generated if not specified.

Note, that if you specify a table alias, it's you responsibility to ensure there is no clashes.

byRef()

Reference the CTE which will be WITHed later. Useful in cases where multiple CTEs are defined and one refers another. Since at the point of referral the referred CTE is not declared with WITH, this method should be used to avoid duplicate declaration. For example:

FluentQuery query = FluentJPA.SQL(() -> {

    DeptCost deptCost = subQuery((Employee e,
                                    Department d) -> {
        Integer deptTotal = alias(SUM(e.getSalary()), DeptCost::getDeptTotal);
        String deptName = alias(d.getDepartmentName(), DeptCost::getDepartmentName);

        SELECT(deptName, deptTotal);
        FROM(e, d);
        WHERE(e.getDepartment() == d);
        GROUP(BY(deptName));
    });

    AvgCost avgCost = subQuery(() -> {

        Integer avg = alias(SUM(deptCost.getDeptTotal()) / COUNT(), AvgCost::getAvg);

        SELECT(avg);
        // at this point FluentJPA is unaware that deptCost will be declared
        // using WITH, and without byRef() will generate a sub select
        FROM(byRef(deptCost));
    });

    WITH(deptCost, avgCost);

    selectAll(deptCost);
    WHERE(deptCost.getDeptTotal() > pick(avgCost, avgCost.getAvg()));
    ORDER(BY(deptCost.getDepartmentName()));

});

viewOf()

Encapsulates column names list and operations on it. Used in INSERT amd MERGE. E.g.:

View<Link> viewOfLink = viewOf(link, Link::getUrl, Link::getName, Link::getLastUpdate);

INSERT().INTO(viewOfLink);
VALUES(viewOfLink.from(toInsert, DEFAULT()));

produces:

INSERT   INTO  link AS t0 (url, name, last_update)  
VALUES (?1, ?2, DEFAULT)

sequence()

Oracle and SQL Server only.

If a shared sequence is used, it's recommended to declare it statically:

public static final Sequence<Long> HibernateSequence = sequence("Hibernate");

then it can be used it a vendor specific way:

HibernateSequence.NEXTVAL() // Oracle
NEXT_VALUE_FOR(HibernateSequence).AS() // SQL Server. supports OVER clause