Home

Get Back in Control of Your SQL with JPA

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:

• allows you to use Java to write strongly typed queries. We support operators, parameters, variables, methods, etc and translate them to the SQL.
• naturally extends JPA: use entities in FROM clauses, getters and setters to write expressions, store intermediate calculations in variables, pass them to methods as you usually do to program your business logic. FluentJPA reads all the JPA annotations to retrieve column and table names, then it uses JPA native query for execution. As a result the solution is integrated with JPA pipeline and transaction, calls to JPA and FluentJPA can be freely mixed producing correct results.
• covers the entire modern SQL DML standard. SQL has changed since SQL-92, where JPQL is stuck: 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. FluentJPA offers this power as a handy Java library.

Competition

How is FluentJPA different (better) than its competitors, jOOQ in particular? This question was asked on Reddit by Mr. Lukas Eder, CEO of Data Geekery GmbH, the developer of jOOQ. Here is the answer, showing how FluentJPA solves real problems.

SQL Support

FluentSQL declares most of the standard SQL DML including 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.

Java Support

Most of the Java language is supported, see Java Language Support.

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
  • Of course you never need to call this lambda - you only pass it to FluentJPA

• 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()

How does it work?

• Translation: FluentJPA reads the Java Byte Code from the .class files in runtime and translates it to SQL.
• SQL mapping: FluentJPA looks for special annotations on the methods. If found, it generates SQL-style "method call" for them. See Extensibility for more details.

FluentJPA lets you create SQL with the same coding quality standards as rest of your code!