Home

Get Back in Control of Your SQL with JPA

FluentJPA is a Language Integrated Query (LINQ) technology for relational (SQL) databases and JPA. It allows you to use Java to write strongly typed queries by directly integrating into the language.

How does FluentJPA integrate into Java?

At first glance it looks like we need a hook into a Java compiler. But in fact we have full access to the resulting bytecode, which has all the "knowledge" we need. This is the way FluentJPA does its magic - it reads the bytecode and translates to SQL.

As a result the integration is full and FluentJPA supports all the Java language constructs, including functions, variables, etc - anything the compiler can compile and also makes sense in the SQL context. See Java Language Support for details.

We already have JPA, JPA repositories and other technologies

FluentJPA aims to complement JPA where a developer wants a full control over SQL

FluentJPA declares SQL clauses (like SELECT, FROM, WHERE) as first class Java methods, so the queries are visually similar:

// Java
FluentJPA.SQL((Person p) -> {
    SELECT(p);
    FROM(p);
    WHERE(p.getName() == name);
});

-- SQL
SELECT t0.*
FROM PERSON_TABLE t0
WHERE (t0.name = ?)

As a result, with FluentJPA you can write SQL without loosing type safety, intellisense, refactoring.

JPA Integration

FluentJPA reads JPA annotations to map entities to SQL table names and properties to column names. Then it uses JPA native query for execution. As a result the solution is integrated with JPA pipeline and transactions, calls to JPA and FluentJPA can be freely mixed producing correct results.

SQL Support

FluentJPA supports the entire modern SQL DML standard. In addition to SQL-92, where JPQL is stuck, it supports SQL-99 Common Table Expressions (WITH clause), SQL-2003 Window Functions (OVER clause), SQL-2003 MERGE (UPSERT clause), Dynamic Queries without Criteria API and many, many more.

In addition to standard SQL, FluentJPA supports proprietary extensions provided by the 4 most popular databases, see static imports. Follow links in Basic/Advanced SQL DML Statements from the sidebar to see examples.

• All functions mapped to SQL counterparts follow SQL naming convention - capitals with underscores as delimiters. As a result your code looks like SQL, but is Java with intellisense and compiler validation!
• All helper functions follow standard Java naming convention. They are either Library methods or Directives.

FluentJPA.SQL()

This is an "entry-point" method to the FluentJPA. It accepts a Java lambda and translates it to SQL query. There are few conventions:

• Lambda parameters must be entity types. This way we declare the table references to be used in this query. Like in SQL, if there is a self join, there will be 2 parameters of the same entity type. For example:

  FluentQuery query = FluentJPA.SQL((Staff emp,
                                     Staff manager,
                                     Store store) -> {
      // returns store name, employee first name and its manager first name
      // ordered by store and manager
      SELECT(store.getName(), emp.getFirstName(), manager.getFirstName());
      FROM(emp).JOIN(manager).ON(emp.getManager() == manager)
               .JOIN(store).ON(emp.getStore() == store);
      ORDER(BY(emp.getStore()), BY(emp.getManager()));
  
  });

  • In Java entity represents SQL Table or more generally a column set
  • Having entities as parameters makes clear which tables this query works on

• Every time, where SQL expects a table reference (e.g. FROM), an entity should be passed. FluentJPA will read the required Table information via JPA annotations.

• FluentJPA translates Lambda's body SQL clauses (written in Java) in the same order as they appear. Thus the content of the sample above is translated to exactly 3 lines:

  SELECT t2.store_name, t0.first_name, t1.first_name
  FROM staffs AS t0 INNER JOIN staffs AS t1 ON (t0.manager_id = t1.staff_id) INNER JOIN stores AS t2 ON (t0.store_id = t2.store_id)
  ORDER BY t0.store_id, t0.manager_id

• Finally, call FluentQuery.createQuery() to get a standard JPA Query instance (see JPA Integration for details):

  TypedQuery<X> typedQuery = query.createQuery(entityManager, <X>.class);
  // execute the query
  typedQuery.getResultList(); // or getSingleResult() / executeUpdate()