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!

What are procedures and functions after all?

Many RDBMS support the concept of “routines”, usually calling them procedures and/or functions. These concepts have been around in programming languages for a while, also outside of databases. Famous languages distinguishing procedures from functions are:

  • Ada
  • BASIC
  • Pascal
  • etc…

The general distinction between (stored) procedures and (stored) functions can be summarised like this:

Procedures:

  • Are called using JDBC CallableStatement
  • Have no return value
  • Usually support OUT parameters

Functions:

  • Can be used in SQL statements
  • Have a return value
  • Usually don’t support OUT parameters

But there are exceptions to these rules:

  • DB2, H2, and HSQLDB don’t allow for JDBC escape syntax when calling functions. Functions must be used in a SELECT statement
  • H2 only knows functions (without OUT parameters)
  • Oracle functions may have OUT parameters
  • Oracle knows functions that mustn’t be used in SQL statements for transactional reasons
  • Postgres only knows functions (with all features combined). OUT parameters can also be interpreted as return values, which is quite elegant/freaky, depending on your taste
  • The Sybase jconn3 JDBC driver doesn’t handle null values correctly when using the JDBC escape syntax on functions

In general, it can be said that the field of routines (procedures / functions) is far from being standardised in modern RDBMS. Every database has its ways and JDBC only provides little abstraction over the great variety of procedures / functions implementations, especially when advanced data types such as cursors / UDT’s / arrays are involved.

Oracle scalar subquery caching

The importance of being able to fully control executed SQL (using jOOQ, or plain JDBC) on large-scale systems becomes obvious every time you need to fine-tune your SQL queries for a specific RDBMS. In this case, we’re looking at Oracle and its miraculous scalar subquery caching mechanisms:

Usually, the context switch from SQL to PL/SQL and back is quite expensive for an average Oracle query, so we should normally omit putting stored functions as filtering, grouping, sorting criteria, even if they’re DETERMINISTIC. When we have large result sets, however, even functions in the query projection may turn out to be an execution plan nightmare. I recently raised the question on Stack Overflow, as I’m curious if there is any caching mechanism, that I could take advantage of for a concrete use-case:

http://stackoverflow.com/questions/7270467/is-there-a-pl-sql-pragma-similar-to-deterministic-but-for-the-scope-of-one-singl

And most interestingly, there are several. The accepted answer points to this very relevant article on Ask Tom’s Q/A:

http://www.oracle.com/technetwork/issue-archive/2011/11-sep/o51asktom-453438.html

Apparently, in scalar subqueries, function results may be cached! While this can be very useful for performance, it can also be very dangerous for consistency – as with any caching mechanism, but this one is highly implicit! So this will be cached:

-- my_function is NOT deterministic but it is cached!
select t.x, t.y, (select my_function(t.x) from dual)
from t

-- logically equivalent to this, uncached
select t.x, t.y, my_function(t.x) from t

Knowing this, I think I have to review quite a bit of SQL…

Oracle’s object-oriented PL/SQL extensions

I have recently re-discovered an interesting feature of Oracle‘s PL/SQL language. Not only can you define your own types very easily, you can also associate “methods” to them, as in other object-oriented languages. Oracle calls those “methods” member functions and member procedures. This is documented here, for example:

http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28371/adobjbas.htm#i477669

So you can define your own type like this

create type circle as object (
   radius number,
   member procedure draw,
   member function  circumference return number
);

create type body circle as
   member procedure draw is
   begin
     null; -- draw the circle
   end draw;

   member function circumference return number is
   begin
     return 2 * 3.141 * radius;
   end circumference;
end;

In PL/SQL, you can instanciate that type and call its member procedures and functions easily:

declare
   c circle;
begin
   c := circle(5);
   dbms_output.put_line(to_char(c.circumference));
end;

The same function can be called in JDBC with this statement

CallableStatement call = c.prepareCall(
  "{ ? = call circle(5).circumference() }");

It would only feel natural for jOOQ-generated UDT records to provide access to the underlying member procedures and functions. The jOOQ UDTRecord is an attachable object. That means, that if it is fetched from the database, it holds a reference to a jOOQ Configuration, and thus to a JDBC Connection. So if you create a procedure that returns a circle, you can call procedures and functions directly on that circle. Your Java code might look like this:

// Call the stored function that returns a new circle type
CircleRecord circle = Functions.getNewCircle(configuration);

// Use the attached CircleRecord to calculate the circumference
BigDecimal circumference = circle.circumference();

// And draw the circle in the database
circle.draw();

Look out in future versions of jOOQ for this exciting new feature! This awesome feature has been part of jOOQ for a long time now!

The power of REF CURSOR types

Many RDBMS have started implementing support for some sort of CURSOR, REF CURSOR or ARRAY/TABLE types. These types have roughly the same semantics as JDBC‘s  java.sql.Array and java.sql.ResultSet. In principle, such types can appear anywhere in SQL, even if some RDBMS limit support to a certain functionality subset. What exactly are these types?

ARRAY types

ARRAY types are the easiest to understand. An array is usually implemented as a typed collection of values. They can be used both for table columns and with stored procedures. Most database schema designers would agree that using ARRAY types in tables is not necessarily a good idea, as this will lead to a schema that is normalised in a “non-first normal form“. On the other hand, ARRAY types can be very powerful when used as stored procedure parameters and especially as stored function results. In both scenarios (table columns, function results), arrays can be unnested using operators such as TABLE(…) or UNNEST(…). These operators will make the contents of an array available to any SQL clause that takes tables as arguments. An example (in HSQLDB):

-- unnest an ad-hoc anonymously typed array
SELECT element
FROM unnest(array[1, 2, 3]) AS unnested(element);

A similar example (in Oracle, which doesn’t support anonymous ARRAY types):

-- create a typed array of numbers
CREATE TYPE number_array AS VARRAY(10) OF NUMBER(7);

-- unnest an ad-hoc array instance. Oracle will name
-- the column of such an unnested array "COLUMN_VALUE"
SELECT column_value
FROM table(number_array(1, 2, 3));

Both constructs will result in a simple table holding one column and three records: 1, 2, 3. As mentioned previously, the power of such ARRAY types becomes obvious, when using them as results in stored functions. In Oracle, for instance, you could define a function like this:

-- a simple example function
CREATE FUNCTION get_array RETURN number_array IS
BEGIN
  RETURN number_array(1, 2, 3);
END;

-- unnest results from a function returning an array
SELECT column_value
FROM TABLE(get_array);

With these syntactical outlines, you can define very complex functions returning well-defined types, which can then again be unnested into SQL tables. The power of this functionality knows little limits, when you combine ARRAY types with OBJECT types, in those RDBMS that support it. Again, with Oracle you can write:

-- A simple, reusable person type
CREATE TYPE person AS OBJECT(
  id NUMBER(7),
  name VARCHAR2(100)
);

-- An array of such users
CREATE TYPE person_array AS VARRAY(10) OF person;

-- An unnested array of such users used in SQL:
SELECT *
FROM TABLE(person_array(
  person(1, 'Jim'),
  person(2, 'Joe')
));

The above SELECT statement will intuitively result in a two-column, two-record table looking like this:

ID NAME
1 Jim
2 Joe

Again, this could be used in combination with stored functions that do a lot more complex processing first to calculate the above result including Jim and Joe.

TABLE types

Some RDBMS (e.g. Oracle) distinguish between in-memory ARRAY types (e.g. the VARRAY type we’ve seen before), and in-memory TABLE types. The main difference for this article is the fact that VARRAY types have a maximum size, whereas TABLE types can be extended to any arbitrary length. Also, Oracle’s API for manipulating nested tables directly in SQL is richer than that of VARRAY types, if you want to add a record to a table nested in another table, for instance. A thorough comparison would be out of scope here, though.

REF CURSORS

Cursors, and in particular REF CURSORS, are handled differently in a way that they don’t directly contain all of their data, but can be iterated (or “looped”) over. Also, a REF CURSOR is a weakly typed object, which means that the number and types of a REF CURSOR’s columns cannot be known at SQL compile-time (or “parse-time”), but only when the SQL statement is executed. This makes it harder to use REF CURSOR directly in SQL. Oracle’s TABLE(…) function, for instance, does not support REF CURSOR types as parameter. See also an overview of what’s possible and what isn’t on this Stack Overflow question:

http://stackoverflow.com/questions/6410452/fetch-oracle-table-type-from-stored-procedure-using-jdbc

Nevertheless, a REF CURSOR can be returned from a stored procedure or from a stored function, and retrieved in JDBC like any other java.sql.ResultSet.

jOOQ’s support for ARRAY/CURSOR types

One of jOOQ’s major goals is to allow its users to easily integrate advanced RDBMS concepts into Java, a non-trivial endeavour when using JDBC. These concepts have not yet (to my knowledge) been standardised in SQL:2008. Every RDBMS that supports those concepts, employs its own syntax. The most curious (yet also the most powerful) syntax I have encountered is that of the H2 database, where unnesting of arrays can be done like this:

-- unnest an ad-hoc anonymously typed array
SELECT *
FROM TABLE(
  ID INT=(1, 2),
  NAME VARCHAR=('Hello', 'World'));

The above example can be found here:

http://www.h2database.com/html/functions.html#table

Apart from hiding these many SQL syntax facts from the user, jOOQ also aims at hiding JDBC statement preparing and type mapping from the user. Passing arrays to a prepared statement is not trivial with Oracle, fetching ResultSets from stored functions isn’t either. And none of todays major frameworks, including Hibernate/JPA, Spring, myBatis, etc, support an easy way to integrate such data types and stored procedures automatically into Java – although Spring allows you to write one custom mapper per procedure.

With jOOQ this is going to be possible. While ARRAY types have been supported by jOOQ for quite some time now, unnesting them is part of the currently running “project CURSOR”. The API is being extended to handle the examples given in SQL, above. An example of such API usage is this:

// Create the usual jOOQ factory
Factory create = new OracleFactory(connection);

// Loop over the values returned from thegenerated getArray function
for (Record record : create.select()
                           .from(create.table(getArray()))
                           .fetch()) {

    // This will print 1, 2, 3
    System.out.println(record.getValue(0));
}

Advanced data types as a future investment

The power of these data types have long been known (and loved) by DBA and database programmers using PL/SQL and other database languages. They have been avoided by Java developers mainly because of the awkwardness (or even lack) of JDBC and/or JPA support. It is difficult for an unexperienced JDBC developer, to correctly bind arrays of object to an Oracle prepared statement, or to fetch a REF CURSOR from a CallableStatement.

The depicted functionality is already available with jOOQ 1.6.3. Many other features dealing with CURSOR and ARRAY types will be implemented in the near future.

…so start using the full functionality set of your database!