Home

Purpose

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 SQL SELECT, 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 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.
• naturally extends JPA. 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. Under the covers FluentJPA reads all the JPA annotations to map properties and objects to SQL 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.

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. Full conversation.

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!