jOOQ-meta. A “hard-core SQL” proof of concept
Join the DZone community and get the full member experience.
Join For Freejooq-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:
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
- 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
- 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/
Opinions expressed by DZone contributors are their own.
Comments