SQL Zone is brought to you in partnership with:

I am the founder and CEO of Data Geekery GmbH, located in Zurich, Switzerland. With our company, we have been selling database products and services around Java and SQL since 2013. Ever since my Master's studies at EPFL in 2006, I have been fascinated by the interaction of Java and SQL. Most of this experience I have obtained in the Swiss E-Banking field through various variants (JDBC, Hibernate, mostly with Oracle). I am happy to share this knowledge at various conferences, JUGs, in-house presentations and on our blog. Lukas is a DZone MVB and is not an employee of DZone and has posted 225 posts at DZone. You can read more from them at their website. View Full User Profile

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

11.18.2011
| 4054 views |
  • submit to reddit

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:

-- Simple query

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

 

From http://lukaseder.wordpress.com/2011/11/14/jooq-meta-a-hard-core-sql-proof-of-concept/

Published at DZone with permission of Lukas Eder, author and DZone MVB.

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)

Comments

Amara Amjad replied on Sun, 2012/03/25 - 1:18am

You might want to fix the spelling of PostgreSQL on your homepage. There is no upper-case G in the name (check out their homepage..)

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.