“Stop Hating Java” – A Funny Post by Igor Polevoy

I just discovered a funny post about Java / Ruby by Igor Polevoy (the creator of ActiveJDBC, a product similar to jOOQ with a slightly different focus). Some extract:

It is interesting to see that Ruby developers […] seem to have all drank several cool-aids: Ruby/Rails/Apple MBPs/iPhones/TextMate. They are “green”, look to be “democratic”, wear sloppy clothes, and are “laid back”. They certainly fall into a few stereotypical descriptions. They religiously follow another big gorilla (Apple), whose policies are even more tight than that of Microsoft.

I can’t help but smirk at his remarks, thinking about a sarcastic comment I recently had by a Ruby developer. The remark was about jOOQ being legacy due to it being written in Java, not in Ruby… :-) In any case, I won’t go into judging languages or developers. I think, Igor’s opinion is interesting, because I share his thoughts about what he says concerning Java standardising too many things:

Standards – the biggest flop in the Java history was Java Enterprise. This does not require any explanation, I hope. Standards are a plague of Java. They are designed to make different implementations [work] together, but this is not happening. Instead, they take years to “standardize”, when Internet years are even shorter than dog years. There is a handful of low level good standards: JDBC, Servlets, JMS, but the rest is just a waste of time.

Igor has a strong opinion, and maybe neglects too many other good standards, such as XML, RMI, the concurrency packages of Java 5, etc. But the more controversial standards also include EJB, JPA and most specifically the Criteria Query API, for which jOOQ aims to be a viable alternative. It’s surely be worth following Igor’s blog a little more. Read the full post here:


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
  RETURN number_array(1, 2, 3);

-- 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
  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:
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:
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.


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
  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()
                           .fetch()) {

    // This will print 1, 2, 3

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!

jOOQ knowledge brings job opportunities

Apart from other testimonials showing that Hibernate/JPA can be trouble due to complexity and performance, there are now first job offerings showing up on the web where experience with jOOQ are reported to be a plus!

See more on the job description:


Developer blog started for interesting insights in Java, SQL and jOOQ

On this blog, I’d like to share my thoughts about developing in Java and SQL, and my “middleware passion” jOOQ.

Java and databases are my professional passion. When they work together, great software can evolve. Many proprietary and standard ideas have been around to make them work together. I feel that there is yet one missing piece glueing them together more intuitively. That’s why I created jOOQ:

jOOQ effectively combines complex SQL, typesafety, source code generation, active records, stored procedures, advanced data types, and Java in a fluent, intuitive DSL.