Sub Queries

Probably the most important feature missing in JPA is Sub Query. We think that any serious SQL starts with them (just look here or here for few examples).

Using sub queries in FluentJPA is simple. Create them in the same way as the "root" query and pass the Lambda to subQuery() directive:

FluentJPA.SQL((Film film) -> {

    // To create a sub query we call subQuery()
    // The result is casted to what we need
    float avgRentalRate = subQuery(() -> {
        SELECT(AVG(film.getRentalRate()));
        FROM(film);
    });

    SELECT(film);
    FROM(film);
    WHERE(film.getRentalRate() > avgRentalRate);
});

Standard Java parameter referencing logic applies: if inside the sub query you use en external scope entity (e.g. Product p), it will have the same alias as in the outer scope. This is important for creation of correlated sub queries:

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

    SELECT(DISTINCT(p.getName()));
    FROM(p);
    WHERE(EXISTS(subQuery((ProductModel model) -> {
        SELECT(model);
        FROM(model);
        WHERE(p.getModel() == model
                           && model.getName().matches("Long-Sleeve Logo Jersey%"));
    })));

});

which produces this SQL:

SELECT DISTINCT t0.name  
FROM Production.Product t0
WHERE EXISTS (SELECT t1.*
FROM Production.Model t1
WHERE ((t0.ProductModelID = t1.ProductModelID) 
                       AND (t1.name LIKE 'Long-Sleeve Logo Jersey%' )) )

Of course, if you need another Product, just declare it as a parameter of your sub query, like we did with ProductModel model.

Sub Query inside Sub Query

You can nest sub queries as you like

Sub Query Results

In SQL sub query always returns a result set. If this result set has just one column and one row, in scalar context it implicitly converts to a scalar (like in the first example above). Therefore, in Java Sub Query result maps to Entity, Tuple or a primitive (scalar). You are responsible to specify the result type:

// returns scalar to use in scalar context
float avgRentalRate = subQuery(() -> {
    SELECT(AVG(film.getRentalRate()));
    FROM(film);
});

// returns an entity
ProductModel productModel = subQuery((ProductModel model) -> {
    SELECT(model);
    FROM(model);
    WHERE(p.getModel() == model && model.getName().matches("Long-Sleeve Logo Jersey%"));
});

See also Returning Results.

Code Structuring

Using Lambda expressions whenever possible in Java is probably not a best advice. Sub query is a Lambda, so the same considerations apply. Just take the lambda with its subQuery(), i.e. do this (refactored previous example):

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

    SELECT(DISTINCT(p.getName()));
    FROM(p);
    WHERE(EXISTS(getProductModel(p, "Long-Sleeve Logo Jersey%")));

});

// must be static
private static ProductModel getProductModel(Product p,
                                            String matchCriteria) {
    return subQuery((ProductModel model) -> {
        SELECT(model);
        FROM(model);
        WHERE(p.getModel() == model && model.getName().matches(matchCriteria));
    });
}