jOOQ as a PL/Java language

Some people who get in touch with PL/SQL, PL/pgSQL, T-SQL, or any other proprietary procedural language for SQL interaction are probably missing out on a couple of language integration features in the Java world. Most Java APIs see SQL as an external domain-specific language that is “best” dealt with using string concatenation. Such APIs include:

Other APIs aim to abstract SQL away, in favour of a “higher-level” mapping to objects. These, again, include

As can be seen quickly, a lot of tool vendors and developers have travelled down similar ORM roads to try to tackle the “mapping problem” from a slightly (never fundamentally) different approach.

But not all people want ORM. Many people want SQL. A nice, general opinion about the old ORM vs. SQL discussion was phrased by Ken Downs a while ago:
http://database-programmer.blogspot.ch/2010/12/historical-perspective-of-orm-and.html

SQL as an internal domain-specific language

We can all agree that SQL itself is a domain-specific language, a language specific to the domain of database querying and database manipulation. As mentioned before, SQL is enhanced on some platforms by proprietary, procedural extensions, some of which even made it into the SQL standard (although barely implemented in the standard form, apart from HSQLDB).

The main advantage of such procedural SQL language extensions is the fact that imperative control flow can be combined with declarative SQL statement execution. Both language paradigms have their place. One is ideal to model control flows, the other is ideal to model queries, abstracting boring querying algorithms.

But imperative programming is quite limited itself. It is difficult to profit from advantages offered by object-oriented or functional paradigms, implemented by popular languages like Java or Scala. Those who have tried Oracle PL/SQL’s “object-oriented” extensions may know what I mean. Furthermore, each procedural extension is vendor-specific and has its own learning curve.

jOOQ models SQL as an internal domain-specific language in Java, and can thus be seen as enhancing Java with some procedural aspects. This has been shown previously on this blog, through an example using H2 database triggers, written in Java/jOOQ. What was meant to be a proof of concept and a nice idea was now re-created by Ronny Guillaume, who wrote an interesting article (in French) about using jOOQ as PL/Java within a Postgres database! The article can be seen here:

http://ronnyguillaume.developpez.com/introduction-pl-java

In essence, you can use another third-party tool called pljava, compile and wrap jOOQ code into a jar file and deploy that jar file into your Postgres database before using it in regular Postgres SQL, or as a trigger. Similar things can be done in Java databases, such as Derby, H2, and HSQLDB, and even in the Oracle database (for the brave among you).

Looking forward to finding more interesting articles about using jOOQ for PL/Java in the wild!

Use jOOQ inside your H2 database

I recently became aware of an interesting use-case for jOOQ when I was optimising my own H2 database integration tests:

H2 stored functions

H2 knows two operation modes for stored functions:

  1. “Inline mode” with source code provided
  2. “Reference mode” referencing a public static method of a Java class on the databases’ classpath

The above terms are not official H2-speak. For more information, consider the documentation:

http://www.h2database.com/html/grammar.html#create_alias
http://www.h2database.com/html/features.html#user_defined_functions

Now, regardless of the operation mode, your H2 stored functions are always written in Java. There is currently no PL/SQL-like procedural language. When your function needs to access the database again for further data processing, you’re back to JDBC, and that’s a pity. That makes writing stored functions quite verbose again.

Using jOOQ within H2 stored functions

… so why not just use jOOQ within the H2 database? Here’s a simple example about how you could do that:

Write your stored function

When writing your own stored function, you can use source code previously generated by jOOQ. This means that the SQL within your stored functions (triggers, etc) will actually compile!

package org.jooq.test.h2;

import static test.generated.Tables.*;

import java.sql.Connection;
import java.sql.SQLException;

import org.jooq.SQLDialect;
import org.jooq.impl.DSL;
import test.generated.tables.TBook;

public class Functions {
  /**
   * This function returns the number of
   * books written by a given author.
   */
  public static int countBooks(
    Connection connection, 
    Integer authorId) 
  throws SQLException {
    return DSL.using(connection, SQLDialect.H2)
              .selectCount()
              .from(T_BOOK)
              .where(T_BOOK.AUTHOR_ID.eq(authorId))
              .fetchOne(0, int.class);
  }
}

Declare the above method as an ALIAS to H2

CREATE ALIAS countBooks 
   FOR "org.jooq.test.h2.Functions.countBooks";

Use the function in SQL

select t_author.last_name, countBooks(id) 
from t_author

… or with jOOQ’s generated classes

jOOQ generates a Routines class containing static access to all of your stored functions. This means that also your client code can make compile-safe use of your stored functions.

package org.jooq.test.h2;

import static test.generated.Tables.*;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

import org.jooq.SQLDialect;
import org.jooq.impl.DSL;
import test.generated.Routines;

public class Test {
  public static void main(String[] args) 
  throws SQLException {
    Connection connection = 
      DriverManager.getConnection(
        "jdbc:h2:~/test", "sa", "");

    System.out.println(
    DLS.using(connection, SQLDialect.H2)
       .select(
             T_AUTHOR.LAST_NAME,
             Routines.countbooks(T_AUTHOR.ID))
       .from(T_AUTHOR)
       .fetch());
  }
}

The logged output of the above will be

+---------+---------------------+
|LAST_NAME|"PUBLIC"."COUNTBOOKS"|
+---------+---------------------+
|Orwell   |                    2|
|Coelho   |                    2|
|Hesse    |                    0|
+---------+---------------------+

Conclusion

jOOQ can also be used within your database, wherever Java is supported. In such a case, jOOQ acts like a PL/SQL-like extension to your favourite database’s Java stored procedure / stored function support. Give it a try, yourself!