How to Write Type-Safe SQL Queries With JPA

JPA is a great technology that maps the database relational model to the Java object-oriented model. It retrieves 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 project aims to fill this gap in two ways:

Let's write native SQL in Java! And by saying Java, I 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 SQL SELECT, you call it like any other library method. And when you need a subquery, 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.

Naturally extending 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.

Enough talk, let's see it in practice.

Example 1: simple query helping to understand the entire flow. (A bit spiced with passing an external parameter and optional JPA Repository integration):

@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 auto-bound and passed via a parameter:

SELECT t0.*

FROM PERSON_TABLE t0

WHERE (t0.name = ?)

Example 2: let’s take a complex query from SQL Server documentation and write it in Java:

// 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());

});

}

Yes, functions can be used and re-used between different queries the same way they are used and re-used for the rest of the business logic. The same objects — Product , ProductModel — are used interchangeably, without any semantic gap.

Example 3: even more complex query 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);
});

}

There is no limit on the query complexity. But with Java, you structure the query to functions, use objects and variables, and enjoy type-safety, IntelliSense, and refactoring. With Java, this query can be written and maintained by a human.

Today, JPA and Spring Data offer easy and simple data retrieval and persistence. In case of something more complex, even a basic subquery, the options are either writing a string (SQL, JPQL) or using Criteria API. FluentJPA unlocks the entire SQL as if it’s a handy Java library.

Thanks for reading. If you liked this post, share it with all of your programming buddies!

Further reading

☞ Java Programming Masterclass for Software Developers

☞ Selenium WebDriver with Java -Basics to Advanced+Frameworks

☞ Java In-Depth: Become a Complete Java Engineer!

☞ Top 4 Spring Annotations for Java Developer in 2019

☞ Java Tutorial for Absolute Beginners

☞ 100+ Java Interview Questions and Answers In 2019

☞ Python vs Java: Understand Object Oriented Programming

Originally published on https://dzone.com

#jpa #java #sql #mysql