Debug logging SQL with jOOQ

This nice little feature is not much advertised in the jOOQ manual, but probably it’s something that most of us developers want and love when we have it. When you put log4j or slf4j on the classpath along with jOOQ, then jOOQ will use those frameworks for logging according to your configuration. While jOOQ is pretty quiet on ERROR/WARN/INFO level logging, it becomes quite a chatty tool on DEBUG/TRACE level.

DEBUG level logging example

Check out some sample DEBUG level output when executing this query:

Result<Record> result = create
  .select(TAuthor.LAST_NAME, create.count().as("c"))
  .from(T_BOOK)
  .join(T_AUTHOR)
  .on(TBook.AUTHOR_ID.equal(TAuthor.ID))
  .where(TBook.TITLE.notEqual("1984"))
  .groupBy(TAuthor.LAST_NAME)
  .having(create.count().equal(2))
  .fetch();

And the output is:


Executing query          : select "t_author"."last_name", 
  count(*) as "c" from "t_book" join "t_author" on 
  "t_book"."author_id" = "t_author"."id" where 
  "t_book"."title" <> '1984' group by "t_author"."last_name"
  having count(*) = 2

Fetched result           : +---------+----+
                         : |last_name|   c|
                         : +---------+----+
                         : |Coelho   |   2|
                         : +---------+----+
Statement executed       : Total: 3.911ms


The query text is printed to the log output with inlined parameters (i.e. bind variables are replaced for logging). Then a text table representation of the result’s first 5 rows is printed before the query execution time.

TRACE level logging example

On trace level, you can see even more of jOOQ’s internals, although usually this is only needed when debugging jOOQ:


Executing query          : select "t_author"."last_name", 
  count(*) as "c" from "t_book" join "t_author" on 
  "t_book"."author_id" = "t_author"."id" where 
  "t_book"."title" <> '1984' group by "t_author"."last_name"
  having count(*) = 2

Preparing statement      : select "t_author"."last_name", 
  count(*) as "c" from "t_book" join "t_author" on 
  "t_book"."author_id" = "t_author"."id" where 
  "t_book"."title" <> cast(? as varchar) 
  group by "t_author"."last_name" 
  having count(*) = cast(? as int)

Statement prepared       : Total: 1.451ms, +0.435ms
Binding variable 1       : 1984 (class java.lang.String)
Binding variable 2       : 2 (class java.lang.Integer)
Variables bound          : Total: 1.808ms, +0.356ms
Attaching                : RecordImpl [ RecordImpl [values=[null, null]] ]
Fetching record          : RecordImpl [values=[Coelho, 2]]
Fetched result           : +---------+----+
                         : |last_name|   c|
                         : +---------+----+
                         : |Coelho   |   2|
                         : +---------+----+
Statement executed       : Total: 6.29ms, +4.481ms


In addition to the previously shown DEBUG-level output, you’ll also find the true SQL statement that is executed, including bind variables and some additional type-casts introduced by jOOQ. Time measuring is divided into 3 steps: statement preparing, variable binding, and statement execution. Also, all bind variables are documented, and every fetched record is documented. That’s a lot of logging. Be sure to turn it off when not needed!

Logging configuration

In future versions of jOOQ, logging will become more configurable

Cloud Fever now also at Sybase

After SQL Server (SQL Azure) and MySQL (Google Cloud SQL), there is now also a SQL Anywhere database available in the cloud:

http://www.sybase.ch/fujibeta

It’s called Sybase SQL Anywhere OnDemand or code name Fuji. I guess the connotation is that your data might as well be relocated to Fuji. Or your DBA might as well work from Fuji. Who knows ;-)

I don’t know where to start adding integration tests for jOOQ with all those cloud-based SQL solutions. Anyway, exciting times are coming!

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.

SQL Trouble with dummy tables

As I’m mostly using Oracle for work projects, the concept of the DUAL dummy table has become quite intuitive. I hardly ever think about the days when I was playing around with that table to find out its purpose (e.g. writing into it when DUAL was still a physical object, and thus killing the whole database…)

In many other RDBMS, there is no need for dummy tables, as you can issue statements like these:

SELECT 1;
SELECT 1 + 1;
SELECT SQRT(2);

These are the RDBMS, where the above is generally possible:

  • H2
  • MySQL
  • Ingres
  • Postgres
  • SQLite
  • SQL Server
  • Sybase ASE

In other RDBMS, dummy tables are required, like in Oracle. Hence, you’ll need to write things like these:

SELECT 1       FROM DUAL;
SELECT 1 + 1   FROM DUAL;
SELECT SQRT(2) FROM DUAL;

These are the RDBMS and their respective dummy tables:

  • DB2: SYSIBM.DUAL
  • Derby: SYSIBM.SYSDUMMY1
  • H2: Optionally supports DUAL
  • HSQLDB: INFORMATION_SCHEMA.SYSTEM_USERS
  • MySQL: Optionally supports DUAL
  • Oracle: DUAL
  • Sybase SQL Anywhere: SYS.DUMMY

Trouble when avoiding dummy tables

While some might find avoiding dummy tables in H2 or MySQL better as SQL becomes more readable, it is worth mentioning that you can run into trouble when doing so:

MySQL’s avoiding DUAL

Clauses such as the following one seem to cause trouble in MySQL in some contexts:

-- this can cause trouble
exists (select 1 where 1 = 0)

-- this will work
exists (select 1 from dual where 1 = 0)

Other similar clauses exist

Ingres has no DUAL, but would actually need it

In Ingres, you cannot have a WHERE, GROUP BY or HAVING clause without a FROM clause. Without dummy tables, you will have to create your own dummy subquery:

SELECT 1 WHERE 1 = 1

-- this is a workaround with a nested select
SELECT 1 FROM (SELECT 1) AS DUAL WHERE 1 = 1

In general, jOOQ will hide these facts from client code, allowing to always use the simple form without dummy table. You don’t have to worry about overly restrictive syntactic rules in some SQL dialects

Google Cloud SQL, the next step for jOOQ?

“The Cloud” is probably the biggest IT buzzword in 2011. It may as well be as meaningless and as short-lived as its predecessors “web 2.0” and “dotcom”, but clearly, the big companies are aiming for “The Cloud” right now. After Microsoft’s all-out marketing campaign for Windows Azure and its sub-product SQL Azure, there is now a comparable Google offensive on Google Labs:

http://code.google.com/apis/sql/docs/developers_guide_java.html

OK, marketing-wise, the term “offensive” is way over the top: Google Labs products often look quite geeky and by far less professional than those by Microsoft. But the approach is interesting, especially the choice of using MySQL as a SQL platform in the cloud. NoSQL was a response to traditional SQL’s inability to scale horizontally. If you buy a big box for your Oracle database, you’ll add memory and CPU power to make it scale vertically as your application grows. You’ll fine-tune your SQL, preferably with jOOQ ;-), to prevent performance bottlenecks in single queries. You’ll pay expensive DBA’s for the job.

When SQL goes to the cloud, however, horizontal scaling might become more realistic… I’m very curious to see where this goes. Clearly, jOOQ should be one of the first Java database abstraction tools to fully support Google Cloud SQL as well as SQL Azure.

Recursive queries with Oracle’s CONNECT BY clause

Recursive or hierarchical queries are an awkward thing in SQL. Some RDBMS allow for recursiveness in Common Table Expressions (CTE’s), but those queries tend to be quite unreadable. That’s not the case for Oracle, which, in addition to recursive CTE’s also supports a dedicated CONNECT BY clause. The general syntax for this clause looks something like this:

--   SELECT ..
--     FROM ..
--    WHERE ..
 CONNECT BY [NOCYCLE] condition [AND condition, ...]
[START WITH condition]
-- GROUP BY ..

Iterative queries are very simple to express with jOOQ as well. Just use the connectBy() method as such:

// Some Oracle-specific features are only available
// from the OracleFactory
OracleFactory create = new OracleFactory(connection);

// Get a table with elements 1, 2, 3, 4, 5
create.select(create.rownum())
      .connectBy(create.level().lessOrEqual(5))
      .fetch();

Recursive queries are simple to express as well. Let’s say, you have a DIRECTORY table with ID, PARENT_ID, NAME columns. In order to recursively fetch all directories and calculate their absolute paths, you could issue a query like this in jOOQ. Note the usage of Oracle’s CONNECT BY, START WITH, and PRIOR keywords, as well as the SYS_CONNECT_BY_PATH function:

 OracleFactory ora = new OracleFactory(connection);

 List<?> paths =
 ora.select(ora.sysConnectByPath(Directory.NAME, "/").substring(2))
    .from(Directory)
    .connectBy(ora.prior(Directory.ID).equal(Directory.PARENT_ID))
    .startWith(Directory.PARENT_ID.isNull())
    .orderBy(ora.literal(1))
    .fetch(0);

jOOQ’s output could then look like this:

+------------------------------------------------+
|substring                                       |
+------------------------------------------------+
|C:                                              |
|C:/eclipse                                      |
|C:/eclipse/configuration                        |
|C:/eclipse/dropins                              |
|C:/eclipse/eclipse.exe                          |
+------------------------------------------------+
|...21 record(s) truncated...

In the near future, jOOQ is going to project the CONNECT BY syntax and API to other RDBMS’s Common Table Expression syntax. That way, you can express hierarchical queries in any database supporting CTE’s using Oracle’s CONNECT BY syntax.

For more details, see also :