jOOQ-meta. A “hard-core SQL” proof of concept

jOOQ-meta is more than just meta data navigation for your database schema. It is also a proof of concept for the more complex jOOQ queries. It is easy for you users to believe that the simple vanilla queries of this form will work:

create.selectFrom(AUTHOR).where(LAST_NAME.equal("Cohen"));

But jOOQ claims to be a “hard-core SQL library”. Or lets say, Timo Westkämper from QueryDSL claims that jOOQ is a “hard-core SQL library”. As a SQL enthusiast, I take that as a compliment. See Timo’s comment here:

http://stackoverflow.com/questions/5620985/is-there-any-good-dynamic-sql-builder-library-in-java#5623141

A “hard-core SQL” example

So let’s have a little look at some of jOOQ-meta’s hard-core SQL. Here’s a nice Postgres query that maps Postgres stored functions to jOOQ’s common concept of routines. There are two very curious features in Postgres, which are modelled by the example query

1. Postgres only knows functions. If functions have one OUT parameter, then that parameter can be treated as the function return value. If functions have more than one OUT parameter, then those parameters can be treated as a function return cursor. In other words, all functions are tables. Quite interesting indeed. But for now, jOOQ doesn’t support that, so several OUT parameters need to be treated as a void result
2. Postgres allows for overloading standalone functions (which isn’t allowed in Oracle, for instance). So in order to generate an overload index for every function directly in a SQL statement, I’m running a SELECT COUNT(*) subselect within a CASE expression, defaulting to null

Beware. SQL ahead! No dummy query!

Let’s have a look at the SQL:

Routines r1 = ROUTINES.as("r1");
Routines r2 = ROUTINES.as("r2");

for (Record record : create().select(
        r1.ROUTINE_NAME,
        r1.SPECIFIC_NAME,

        // 1. Ignore the data type when there is at least one out parameter
        decode()
            .when(exists(create()
                .selectOne()
                .from(PARAMETERS)
                .where(PARAMETERS.SPECIFIC_SCHEMA.equal(r1.SPECIFIC_SCHEMA))
                .and(PARAMETERS.SPECIFIC_NAME.equal(r1.SPECIFIC_NAME))
                .and(upper(PARAMETERS.PARAMETER_MODE).notEqual("IN"))),
                    val("void"))
            .otherwise(r1.DATA_TYPE).as("data_type"),
        r1.NUMERIC_PRECISION,
        r1.NUMERIC_SCALE,
        r1.TYPE_UDT_NAME,

        // 2. Calculate overload index if applicable
        decode().when(
        exists(
            create().selectOne()
                .from(r2)
                .where(r2.ROUTINE_SCHEMA.equal(getSchemaName()))
                .and(r2.ROUTINE_NAME.equal(r1.ROUTINE_NAME))
                .and(r2.SPECIFIC_NAME.notEqual(r1.SPECIFIC_NAME))),
            create().select(count())
                .from(r2)
                .where(r2.ROUTINE_SCHEMA.equal(getSchemaName()))
                .and(r2.ROUTINE_NAME.equal(r1.ROUTINE_NAME))
                .and(r2.SPECIFIC_NAME.lessOrEqual(r1.SPECIFIC_NAME)).asField())
        .as("overload"))
    .from(r1)
    .where(r1.ROUTINE_SCHEMA.equal(getSchemaName()))
    .orderBy(r1.ROUTINE_NAME.asc())
    .fetch()) {

    // [...] do the loop

The above SQL statement is executed when you generate source code for Postgres and works like a charm. With jOOQ 2.0, the DSL will become even less verbose and more powerful. You couldn’t write much less SQL when using JDBC directly. And you can forget it immediately, with other products, such as JPA, JPQL, HQL, etc .

For a comparison, this is what jOOQ renders (For better readability, I removed the escaping of table/field names):

select
  r1.routine_name,
  r1.specific_name,
  case when exists (
            select 1 from information_schema.parameters
            where (information_schema.parameters.specific_schema
              = r1.specific_schema
            and information_schema.parameters.specific_name
              = r1.specific_name
            and upper(information_schema.parameters.parameter_mode)
              <> 'IN'))
       then 'void'
       else r1.data_type
       end as data_type,
  r1.numeric_precision,
  r1.numeric_scale,
  r1.type_udt_name,
  case when exists (
            select 1 from information_schema.routines as r2
            where (r2.routine_schema = 'public'
            and r2.routine_name = r1.routine_name
            and r2.specific_name <> r1.specific_name))
       then (select count(*)
             from information_schema.routines as r2
             where (r2.routine_schema = 'public'
             and r2.routine_name = r1.routine_name
             and r2.specific_name <= r1.specific_name))
       end as overload
from information_schema.routines as r1
where r1.routine_schema = 'public'
order by r1.routine_name asc