Fluent API for writing type-safe SQL queries in Java (full documentation)
JPA is a great technology that maps the database relational model to the Java object oriented model. It lets retrieve data and persist back the changes very easily. But it lacks the ability to perform advanced queries. In fact, all the advanced SQL capabilities are simply locked to the Java developer until she chooses to write a hard-to-maintain SQL as a hard-coded string.
FluentJPA fills this gap in three ways:
- lets write native SQL in Java!
And by saying Java, we mean Java. There is no DSL or semantic gap. You use
+for addition and-for subtraction. You use getter to get a property value and setter to set it. You use functions and variables, so when you call SQLSELECT, you call it like any other library method. And when you need a sub query, you will probably prefer to put it in a separate function ... as you usually do when you code rest of your business logic. To accomplish this, FluentJPA reads the Java Byte Code from the .class files in runtime and translates it all the way to SQL. - covers the entire SQL DML standard. A Lot Has Changed Since SQL-92, e.g. SQL-99 Common Table Expressions (
WITHclause), SQL-2003 Window Functions (OVERclause), SQL-2003 MERGE (UPSERTclause), Dynamic Queries without Criteria API. FluentJPA offers this power as a handy Java library. - naturally extends the JPA model. Once you mapped your entities, forget about mapping. Use JPA entity getters and setters to write expressions and joins, store intermediate calculations in variables, pass them to methods - we seamlessly translate it to SQL. FluentJPA lets you use SQL to program your business logic.
There is no bootstrap, code generation step or anything else needed to use FluentJPA. Add dependencies to your project build system and unlock the full power of type-safe Object Oriented SQL in your JPA project without compromises!
Probably the most important feature missing in JPA is Sub Query. We think that any serious SQL starts with them (just look here for few examples). Not only FluentJPA supports sub queries, it also lets put them into a separate Java(!) function. So the code looks 100% natural to a Java developer.
Let's start easy and look on the simplest query possible and understand the entire flow. (A bit spiced with passing an external parameter and optional JPA Repository integration)
Example 0 - testPassArguments()
@Repository
public interface PersonRepository extends CrudRepository<Person, Long>, EntityManagerSupplier {
default List<Person> getAllByName(String name) {
FluentQuery query = FluentJPA.SQL((Person p) -> {
SELECT(p);
FROM(p);
WHERE(p.getName() == name);
});
return query.createQuery(getEntityManager(), Person.class).getResultList();
}
}SQL query that gets generated, name is passed via a parameter:
SELECT t0.*
FROM PERSON_TABLE t0
WHERE (t0.name = ?)FluentJPA supports any query, here we brought few examples with sub queries to show the power of FluentJPA. There is a link to the test file source code labeled as a method name above each example. And a link to the original SQL where we borrowed the use case from. Best when seen side-by-side.
Example 1 - testCorrelatedWithHaving()
1 sub query "converted" to a Java function (original SQL comes from SQL Server documentation).
Citing original docs: This example finds the product models for which the maximum list price is more than twice the average for the model.
// Product is a standard JPA Entity
FluentQuery query = FluentJPA.SQL((Product p1) -> {
SELECT(p1.getModel());
FROM(p1);
GROUP(BY(p1.getModel()));
HAVING(MAX(p1.getListPrice()) >= ALL(avgPriceForProductModel(p1.getModel())));
// sub query in SQL, function in Java ^^^^^^^^^^^^^^^^^^^^
});
...
// The result is an int since the sub query returns 1 row/column
private static int avgPriceForProductModel(ProductModel model) {
return subQuery((Product p2) -> {
SELECT(AVG(p2.getListPrice()));
FROM(p2);
WHERE(model == p2.getModel());
});
}Example 2 - testInsertFromOUTPUT()
3 sub queries "converted" to functions (original SQL comes from SQL Server documentation).
// Arguments are automatically captured and passed in via JPA's Query.setParameter()
String orderDate; // passed by an external parameter
FluentQuery query = FluentJPA.SQL(() -> {
// returns an entity!
SalesOrderDetail sales = salesByProducts(orderDate);
// previous result is an argument for the next function
Change change = updateInventoryWithSales(sales);
trackNoInventory(change);
});
...
// the result is SalesOrderDetail since the SELECTed columns are aliased to its fields
private static SalesOrderDetail salesByProducts(String orderDate) {
return subQuery((SalesOrderDetail sod,
SalesOrderHeader soh) -> {
// since the function returns SalesOrderDetail, alias
// SELECTed columns to SalesOrderDetail's fields (type safety is kept)
Product product = alias(sod.getProduct(), SalesOrderDetail::getProduct);
int orderQty = alias(SUM(sod.getOrderQty()), SalesOrderDetail::getOrderQty);
SELECT(product, orderQty);
FROM(sod).JOIN(soh)
.ON(sod.getSalesOrderID() == soh.getSalesOrderID() && soh.getOrderDate() == orderDate);
GROUP(BY(product));
});
}
private static Change updateInventoryWithSales(SalesOrderDetail order) {
return subQuery((ProductInventory inv) -> {
ProductInventory deleted = DELETED();
MERGE().INTO(inv).USING(order).ON(inv.getProduct() == order.getProduct());
// Non foreign key Object JOIN -----------------^^^^^^^^
WHEN_MATCHED_AND(inv.getQuantity() - order.getOrderQty() <= 0).THEN(DELETE());
WHEN_MATCHED().THEN(MERGE_UPDATE().SET(() -> {
inv.setQuantity(inv.getQuantity() - order.getOrderQty());
}));
// since the function returns Change, alias
// OUTPUTed columns to Change's fields
MergeAction action = alias($action(), Change::getAction);
int productID = alias(deleted.getProduct().getProductID(), Change::getProductID);
OUTPUT(action, productID);
});
}
private static void trackNoInventory(Change change) {
subQuery((ZeroInventory zi) -> {
INSERT().INTO(viewOf(zi, ZeroInventory::getDeletedProductID, ZeroInventory::getRemovedOnDate));
SELECT(change.getProductID(), GETDATE());
FROM(change);
WHERE(change.getAction() == MergeAction.DELETE);
});
}Example 3 - testCTE_Recursive_DELETE()
Recursive sub query (original SQL comes from PostgreSQL documentation).
Citing original docs: This query would remove all direct and indirect subparts of a product.
If you don't know what recursiveWITHis, it's worth learning. Since you will be able to use it now with FluentJPA 😉.
FluentJPA.SQL((Part allParts) -> {
Part included_parts = subQuery((Part it,
Part parts,
Part subParts) -> {
// initial
SELECT(parts.getSubPart(), parts.getName());
FROM(parts);
WHERE(parts.getName() == "our_product");
UNION_ALL();
// recursive
SELECT(subParts.getSubPart(), subParts.getName());
// recurse
FROM(recurseOn(it), subParts);
WHERE(it.getSubPart() == subParts.getName());
});
WITH(RECURSIVE(included_parts));
DELETE().FROM(allParts);
WHERE(collect(included_parts, included_parts.getName()).contains(allParts.getName()));
});Example 4 - getByNameLike()
Dynamic Queries without Criteria API:
// build the criteria dynamically
Function1<CoverageMaster, Boolean> dynamicFilter = buildOr1(likes);
FluentQuery query = FluentJPA.SQL((UtilizationDTL util,
UtilizationCoverageDTL utilizationCover,
CoverageMaster coverMaster) -> {
SELECT(DISTINCT(util.getId()));
FROM(util).JOIN(utilizationCover)
.ON(utilizationCover.getUtilization() == util)
.JOIN(coverMaster)
.ON(utilizationCover.getMaster() == coverMaster);
WHERE(dynamicFilter.apply(coverMaster) && util.isCompleted());
// ^^^^^^^^^^^^^^^^^^^--- inject the criteria,
// rest of the query is unaffected
ORDER(BY(util.getId()));
});
private Function1<CoverageMaster, Boolean> buildOr1(List<String> likes) {
Function1<CoverageMaster, Boolean> criteria = Function1.FALSE();
for (String like : likes)
criteria = criteria.or(p -> p.getCoverageName().toLowerCase()
.matches(parameter(like)));
return criteria;
}