How jOOQ Helps Pretend that Your Stored Procedures are a Part of Java

In this year’s Java Advent Calendar, we’re thrilled to have been asked to feature a mini-series showing you a couple of advanced and very interesting topics that we’ve been working on when developing jOOQ.

The series consists of:

Don’t miss any of these!

How jOOQ helps pretend that your stored procedures are a part of Java

This article was originally published fully on the jOOQ blog

Stored procedures are an interesting way to approach data processing. Some Java developers tend to stay clear of them for rather dogmatic reasons, such as:

  • They think that the database is the wrong place for business logic
  • They think that the procedural aspect of those languages is ill-suited for their domain

But in practice, stored procedures are an excellent means of handling data manipulations simply for the fact that they can execute complex logic right where the data is. This completely removes all effects that network latency and bandwidth will have on your application, otherwise. As we’re looking into supporting SAP HANA for jOOQ 3.6, we can tell you that running jOOQ’s 10000 integration test queries connecting from a local machine to the cloud takes a lot longer. If you absolutely want to stay in Java land, then you better also deploy your Java application into the cloud, close to that database (SAP HANA obviously offers that feature). But much better than that, move some of the logic into the database!

If you’re doing calculations on huge in-memory data sets, you should better get your code into that same memory, rather than shuffling around memory pieces between possibly separate physical memory addresses. Companies like Hazelcast essentially do the same, except that their in-memory database is written in Java, so you can also write your “stored procedure” in Java.

With SQL databases, procedural SQL languages are king. And because of their tight integration with SQL, they’re much superior for the job than any Java based stored procedure architecture.

I knoow, but JDBC’s CallableStatement… Arrrgh!

Yes. As ever so often (and as mentioned before in our previous articles, one very important reason why many Java developers don’t like working with SQL is JDBC. Binding to a database via JDBC is extremely tedious and keeps us from working efficiently. Let’s have a look at a couple of PL/SQL binding examples:

Assume we’re working on an Oracle-port of the popular Sakila database (originally created for MySQL). This particular Sakila/Oracle port was implemented by DB Software Laboratory and published under the BSD license.

Here’s a partial view of that Sakila database.

ERD created with vertabelo.comlearn how to use Vertabelo with jOOQ

Now, let’s assume that we have an API in the database that doesn’t expose the above schema, but exposes a PL/SQL API instead. The API might look something like this:

CREATE TYPE LANGUAGE_T AS OBJECT (
language_id SMALLINT,
name CHAR(20),
last_update DATE
);
/

CREATE TYPE LANGUAGES_T AS TABLE OF LANGUAGE_T;
/

CREATE TYPE FILM_T AS OBJECT (
film_id int,
title VARCHAR(255),
description CLOB,
release_year VARCHAR(4),
language LANGUAGE_T,
original_language LANGUAGE_T,
rental_duration SMALLINT,
rental_rate DECIMAL(4,2),
length SMALLINT,
replacement_cost DECIMAL(5,2),
rating VARCHAR(10),
special_features VARCHAR(100),
last_update DATE
);
/

CREATE TYPE FILMS_T AS TABLE OF FILM_T;
/

CREATE TYPE ACTOR_T AS OBJECT (
actor_id numeric,
first_name VARCHAR(45),
last_name VARCHAR(45),
last_update DATE
);
/

CREATE TYPE ACTORS_T AS TABLE OF ACTOR_T;
/

CREATE TYPE CATEGORY_T AS OBJECT (
category_id SMALLINT,
name VARCHAR(25),
last_update DATE
);
/

CREATE TYPE CATEGORIES_T AS TABLE OF CATEGORY_T;
/

CREATE TYPE FILM_INFO_T AS OBJECT (
film FILM_T,
actors ACTORS_T,
categories CATEGORIES_T
);
/

You’ll notice immediately, that this is essentially just a 1:1 copy of the schema in this case modelled as Oracle SQL OBJECT and TABLE types, apart from the FILM_INFO_T type, which acts as an aggregate.

Now, our DBA (or our database developer) has implemented the following API for us to access the above information:

CREATE OR REPLACE PACKAGE RENTALS AS
FUNCTION GET_ACTOR(p_actor_id INT) RETURN ACTOR_T;
FUNCTION GET_ACTORS RETURN ACTORS_T;
FUNCTION GET_FILM(p_film_id INT) RETURN FILM_T;
FUNCTION GET_FILMS RETURN FILMS_T;
FUNCTION GET_FILM_INFO(p_film_id INT) RETURN FILM_INFO_T;
FUNCTION GET_FILM_INFO(p_film FILM_T) RETURN FILM_INFO_T;
END RENTALS;
/

This, ladies and gentlemen, is how you can now…

… tediously access the PL/SQL API with JDBC

So, in order to avoid the awkward CallableStatement with its OUT parameter registration and JDBC escape syntax, we’re going to fetch a FILM_INFO_T record via a SQL statement like this:

try (PreparedStatement stmt = conn.prepareStatement(
"SELECT rentals.get_film_info(1) FROM DUAL");
ResultSet rs = stmt.executeQuery()) {

// STRUCT unnesting here...
}

So far so good. Luckily, there is Java 7’s try-with-resources to help us clean up those myriad JDBC objects. Now how to proceed? What will we get back from this ResultSet? A java.sql.Struct:

while (rs.next()) {
Struct film_info_t = (Struct) rs.getObject(1);

// And so on...
}

Now, the brave ones among you would continue downcasting the java.sql.Struct to an even more obscure and arcane oracle.sql.STRUCT, which contains almost no Javadoc, but tons of deprecated additional, vendor-specific methods.

For now, let’s stick with the “standard API”, though. Let’s continue navigating our STRUCT

while (rs.next()) {
Struct film_info_t = (Struct) rs.getObject(1);

Struct film_t = (Struct) film_info_t.getAttributes()[0];
String title = (String) film_t.getAttributes()[1];
Clob description_clob = (Clob) film_t.getAttributes()[2];
String description = description_clob.getSubString(1, (int) description_clob.length());

Struct language_t = (Struct) film_t.getAttributes()[4];
String language = (String) language_t.getAttributes()[1];

System.out.println("Film : " + title);
System.out.println("Description: " + description);
System.out.println("Language : " + language);
}

This could go on and on. The pain has only started, we haven’t even covered arrays yet. The details can be seen here in the original article.

Anyway. Now that we’ve finally achieved this, we can see the print output:

Film       : ACADEMY DINOSAUR
Description: A Epic Drama of a Feminist And a Mad
Scientist who must Battle a Teacher in
The Canadian Rockies
Language : English
Actors :
PENELOPE GUINESS
CHRISTIAN GABLE
LUCILLE TRACY
SANDRA PECK
JOHNNY CAGE
MENA TEMPLE
WARREN NOLTE
OPRAH KILMER
ROCK DUKAKIS
MARY KEITEL

When will this madness stop?

It’ll stop right here!

So far, this article read like a tutorial (or rather: medieval torture) of how to deserialise nested user-defined types from Oracle SQL to Java (don’t get me started on serialising them again!)

In the next section, we’ll see how the exact same business logic (listing Film with ID=1 and its actors) can be implemented with no pain at all using jOOQ and its source code generator. Check this out:

// Simply call the packaged stored function from
// Java, and get a deserialised, type safe record
FilmInfoTRecord film_info_t = Rentals.getFilmInfo1(configuration, new BigInteger("1"));

// The generated record has getters (and setters)
// for type safe navigation of nested structures
FilmTRecord film_t = film_info_t.getFilm();

// In fact, all these types have generated getters:
System.out.println("Film : " + film_t.getTitle());
System.out.println("Description: " + film_t.getDescription());
System.out.println("Language : " + film_t.getLanguage().getName());

// Simply loop nested type safe array structures
System.out.println("Actors : ");
for (ActorTRecord actor_t : film_info_t.getActors()) {
System.out.println(
" " + actor_t.getFirstName()
+ " " + actor_t.getLastName());
}

System.out.println("Categories : ");
for (CategoryTRecord category_t : film_info_t.getCategories()) {
System.out.println(category_t.getName());
}

Is that it?

Yes!

Wow, I mean, this is just as though all those PL/SQL types and procedures / functions were actually part of Java. All the caveats that we’ve seen before are hidden behind those generated types and implemented in jOOQ, so you can concentrate on what you originally wanted to do. Access the data objects and do meaningful work with them. Not serialise / deserialise them!

Not convinced yet?

I told you not to get me started on serialising the types to JDBC. And I won’t, but here’s how to serialise the types to jOOQ, because that’s a piece of cake!

Let’s consider this other aggregate type, that returns a customer’s rental history:

CREATE TYPE CUSTOMER_RENTAL_HISTORY_T AS OBJECT (
customer CUSTOMER_T,
films FILMS_T
);
/

And the full PL/SQL package specs:

CREATE OR REPLACE PACKAGE RENTALS AS
FUNCTION GET_ACTOR(p_actor_id INT) RETURN ACTOR_T;
FUNCTION GET_ACTORS RETURN ACTORS_T;
FUNCTION GET_CUSTOMER(p_customer_id INT) RETURN CUSTOMER_T;
FUNCTION GET_CUSTOMERS RETURN CUSTOMERS_T;
FUNCTION GET_FILM(p_film_id INT) RETURN FILM_T;
FUNCTION GET_FILMS RETURN FILMS_T;
FUNCTION GET_CUSTOMER_RENTAL_HISTORY(p_customer_id INT) RETURN CUSTOMER_RENTAL_HISTORY_T;
FUNCTION GET_CUSTOMER_RENTAL_HISTORY(p_customer CUSTOMER_T) RETURN CUSTOMER_RENTAL_HISTORY_T;
FUNCTION GET_FILM_INFO(p_film_id INT) RETURN FILM_INFO_T;
FUNCTION GET_FILM_INFO(p_film FILM_T) RETURN FILM_INFO_T;
END RENTALS;
/

So, when calling RENTALS.GET_CUSTOMER_RENTAL_HISTORY we can find all the films that a customer has ever rented. Let’s do that for all customers whose FIRST_NAME is “JAMIE”, and this time, we’re using Java 8:

// We call the stored function directly inline in
// a SQL statement
dsl().select(Rentals.getCustomer(
CUSTOMER.CUSTOMER_ID
))
.from(CUSTOMER)
.where(CUSTOMER.FIRST_NAME.eq("JAMIE"))

// This returns Result<Record1<CustomerTRecord>>
// We unwrap the CustomerTRecord and consume
// the result with a lambda expression
.fetch()
.map(Record1::value1)
.forEach(customer -> {
System.out.println("Customer : ");
System.out.println("- Name : " + customer.getFirstName() + " " + customer.getLastName());
System.out.println("- E-Mail : " + customer.getEmail());
System.out.println("- Address : " + customer.getAddress().getAddress());
System.out.println(" " + customer.getAddress().getPostalCode() + " " + customer.getAddress().getCity().getCity());
System.out.println(" " + customer.getAddress().getCity().getCountry().getCountry());

// Now, lets send the customer over the wire again to
// call that other stored procedure, fetching his
// rental history:
CustomerRentalHistoryTRecord history =
Rentals.getCustomerRentalHistory2(dsl().configuration(), customer);

System.out.println(" Customer Rental History : ");
System.out.println(" Films : ");

history.getFilms().forEach(film -> {
System.out.println(" Film : " + film.getTitle());
System.out.println(" Language : " + film.getLanguage().getName());
System.out.println(" Description : " + film.getDescription());

// And then, let's call again the first procedure
// in order to get a film's actors and categories
FilmInfoTRecord info =
Rentals.getFilmInfo2(dsl().configuration(), film);

info.getActors().forEach(actor -> {
System.out.println(" Actor : " + actor.getFirstName() + " " + actor.getLastName());
});

info.getCategories().forEach(category -> {
System.out.println(" Category : " + category.getName());
});
});
});

… and a short extract of the output produced by the above:

Customer  : 
- Name : JAMIE RICE
- E-Mail : [email protected]
- Address : 879 Newcastle Way
90732 Sterling Heights
United States
Customer Rental History :
Films :
Film : ALASKA PHANTOM
Language : English
Description : A Fanciful Saga of a Hunter
And a Pastry Chef who must
Vanquish a Boy in Australia
Actor : VAL BOLGER
Actor : BURT POSEY
Actor : SIDNEY CROWE
Actor : SYLVESTER DERN
Actor : ALBERT JOHANSSON
Actor : GENE MCKELLEN
Actor : JEFF SILVERSTONE
Category : Music
Film : ALONE TRIP
Language : English
Description : A Fast-Paced Character
Study of a Composer And a
Dog who must Outgun a Boat
in An Abandoned Fun House
Actor : ED CHASE
Actor : KARL BERRY
Actor : UMA WOOD
Actor : WOODY JOLIE
Actor : SPENCER DEPP
Actor : CHRIS DEPP
Actor : LAURENCE BULLOCK
Actor : RENEE BALL
Category : Music

If you’re using Java and PL/SQL…

… then you should click on the below banner and download the free trial right now to experiment with jOOQ and Oracle:

The Oracle port of the Sakila database is available from this URL for free, under the terms of the BSD license:

https://github.com/jOOQ/jOOQ/tree/master/jOOQ-examples/Sakila/oracle-sakila-db

Finally, it is time to enjoy writing PL/SQL again!

And things get even better!

jOOQ is free and Open Source for use with Open Source databases, and it offers commercial licensing for use with commercial databases. So, if you’re using Firebird, MySQL, or PostgreSQL, you can leverage all your favourite database’s procedural SQL features and bind them easily to Java for free!

For more information about jOOQ or jOOQ’s DSL API, consider these resources:

That’s it with this year’s mini-series on jOOQ. Have a happy Holiday season!
This post is part of the Java Advent Calendar and is licensed under the Creative Commons 3.0 Attribution license. If you like it, please spread the word by sharing, tweeting, FB, G+ and so on!

How jOOQ Allows for Fluent Functional-Relational Interactions in Java 8

In this year’s Java Advent Calendar, we’re thrilled to have been asked to feature a mini-series showing you a couple of advanced and very interesting topics that we’ve been working on when developing jOOQ.

The series consists of:

Don’t miss any of these!

How jOOQ allows for fluent functional-relational interactions in Java 8

In yesterday’s article, we’ve seen How jOOQ Leverages Generic Type Safety in its DSL when constructing SQL statements. Much more interesting than constructing SQL statements, however, is executing them.

Yesterday, we’ve seen a sample PL/SQL block that reads like this:

BEGIN
FOR rec IN (
SELECT first_name, last_name FROM customers
UNION
SELECT first_name, last_name FROM staff
)
LOOP
INSERT INTO people (first_name, last_name)
VALUES rec.first_name, rec.last_name;
END LOOP;
END;

And you won’t be surprised to see that the exact same thing can be written in Java with jOOQ:

for (Record2<String, String> rec : 
dsl.select(CUSTOMERS.FIRST_NAME, CUSTOMERS.LAST_NAME).from(CUSTOMERS)
.union(
select(STAFF.FIRST_NAME, STAFF.LAST_NAME ).from(STAFF))
) {
dsl.insertInto(PEOPLE, PEOPLE.FIRST_NAME, PEOPLE.LAST_NAME)
.values(rec.getValue(CUSTOMERS.FIRST_NAME), rec.getValue(CUSTOMERS.LAST_NAME))
.execute();
}

This is a classic, imperative-style PL/SQL inspired approach at iterating over result sets and performing actions 1-1.

Java 8 changes everything!

With Java 8, lambdas appeared, and much more importantly, Streams did, and tons of other useful features. The simplest way to migrate the above foreach loop to Java 8’s “callback hell” would be the following

dsl.select(CUSTOMERS.FIRST_NAME, CUSTOMERS.LAST_NAME).from(CUSTOMERS)
.union(
select(STAFF.FIRST_NAME, STAFF.LAST_NAME ).from(STAFF))
.forEach(rec -> {
dsl.insertInto(PEOPLE, PEOPLE.FIRST_NAME, PEOPLE.LAST_NAME)
.values(rec.getValue(CUSTOMERS.FIRST_NAME), rec.getValue(CUSTOMERS.LAST_NAME))
.execute();
}

This is still very simple. How about this. Let’s fetch a couple of records from the database, stream them, map them using some sophisticated Java function, reduce them into a batch update statement! Whew… here’s the code:

dsl.selectFrom(BOOK)
.where(BOOK.ID.in(2, 3))
.orderBy(BOOK.ID)
.fetch()
.stream()
.map(book -> book.setTitle(book.getTitle().toUpperCase()))
.reduce(
dsl.batch(update(BOOK).set(BOOK.TITLE, (String) null).where(BOOK.ID.eq((Integer) null))),
(batch, book) -> batch.bind(book.getTitle(), book.getId()),
(b1, b2) -> b1
)
.execute();

Awesome, right? Again, with comments

// Here, we simply select a couple of books from the database
dsl.selectFrom(BOOK)
.where(BOOK.ID.in(2, 3))
.orderBy(BOOK.ID)
.fetch()

// Now, we stream the result as a Java 8 Stream
.stream()

// Now we map all book titles using the "sophisticated" Java function
.map(book -> book.setTitle(book.getTitle().toUpperCase()))

// Now, we reduce the books into a batch update statement...
.reduce(

// ... which is initialised with empty bind variables
dsl.batch(update(BOOK).set(BOOK.TITLE, (String) null).where(BOOK.ID.eq((Integer) null))),

// ... and then we bind each book's values to the batch statement
(batch, book) -> batch.bind(book.getTitle(), book.getId()),

// ... this is just a dummy combiner function, because we only operate on one batch instance
(b1, b2) -> b1
)

// Finally, we execute the produced batch statement
.execute();

Awesome, right? Well, if you’re not too functional-ish, you can still resort to the “old ways” using imperative-style loops. Perhaps, your coworkers might prefer that:

BatchBindStep batch = dsl.batch(update(BOOK).set(BOOK.TITLE, (String) null).where(BOOK.ID.eq((Integer) null))),

for (BookRecord book :
dsl.selectFrom(BOOK)
.where(BOOK.ID.in(2, 3))
.orderBy(BOOK.ID)
) {
batch.bind(book.getTitle(), book.getId());
}

batch.execute();

So, what’s the point of using Java 8 with jOOQ?

Java 8 might change a lot of things. Mainly, it changes the way we reason about functional data transformation algorithms. Some of the above ideas might’ve been a bit over the top. But the principal idea is that whatever is your source of data, if you think about that data in terms of Java 8 Streams, you can very easily transform (map) those streams into other types of streams as we did with the books. And nothing keeps you from collecting books that contain changes into batch update statements for batch execution.

Another example is one where we claimed that Java 8 also changes the way we perceive ORMs. ORMs are very stateful, object-oriented things that help manage database state in an object-graph representation with lots of nice features like optimistic locking, dirty checking, and implementations that support long conversations. But they’re quite terrible at data transformation. First off, they’re much much inferior to SQL in terms of data transformation capabilities. This is topped by the fact that object graphs and functional programming don’t really work well either.

With SQL (and thus with jOOQ), you’ll often stay on a flat tuple level. Tuples are extremely easy to transform. The following example shows how you can use an H2 database to query for INFORMATION_SCHEMA meta information such as table names, column names, and data types, collect those information into a data structure, before mapping that data structure into new CREATE TABLE statements:

DSL.using(c)
.select(
COLUMNS.TABLE_NAME,
COLUMNS.COLUMN_NAME,
COLUMNS.TYPE_NAME
)
.from(COLUMNS)
.orderBy(
COLUMNS.TABLE_CATALOG,
COLUMNS.TABLE_SCHEMA,
COLUMNS.TABLE_NAME,
COLUMNS.ORDINAL_POSITION
)
.fetch() // jOOQ ends here
.stream() // Streams start here
.collect(groupingBy(
r -> r.getTableName(),
LinkedHashMap::new,
mapping(
r -> r,
toList()
)
))
.forEach(
(table, columns) -> {
// Just emit a CREATE TABLE statement
System.out.println(
"CREATE TABLE " + table + " (");

// Map each "Column" type into a String
// containing the column specification,
// and join them using comma and
// newline. Done!
System.out.println(
columns.stream()
.map(col -> " " + col.getName() +
" " + col.getType())
.collect(Collectors.joining(",n"))
);

System.out.println(");");
}
);

The above statement will produce something like the following SQL script:

CREATE TABLE CATALOGS(
CATALOG_NAME VARCHAR
);
CREATE TABLE COLLATIONS(
NAME VARCHAR,
KEY VARCHAR
);
CREATE TABLE COLUMNS(
TABLE_CATALOG VARCHAR,
TABLE_SCHEMA VARCHAR,
TABLE_NAME VARCHAR,
COLUMN_NAME VARCHAR,
ORDINAL_POSITION INTEGER,
COLUMN_DEFAULT VARCHAR,
IS_NULLABLE VARCHAR,
DATA_TYPE INTEGER,
CHARACTER_MAXIMUM_LENGTH INTEGER,
CHARACTER_OCTET_LENGTH INTEGER,
NUMERIC_PRECISION INTEGER,
NUMERIC_PRECISION_RADIX INTEGER,
NUMERIC_SCALE INTEGER,
CHARACTER_SET_NAME VARCHAR,
COLLATION_NAME VARCHAR,
TYPE_NAME VARCHAR,
NULLABLE INTEGER,
IS_COMPUTED BOOLEAN,
SELECTIVITY INTEGER,
CHECK_CONSTRAINT VARCHAR,
SEQUENCE_NAME VARCHAR,
REMARKS VARCHAR,
SOURCE_DATA_TYPE SMALLINT
);

That’s data transformation! If you’re as excited as we are, read on in this article how this example works exactly.

Conclusion

Java 8 has changed everything in the Java ecosystem. Finally, we can implement functional, transformative algorithms easily using Streams and lambda expressions. SQL is also a very functional and transformative language. With jOOQ and Java 8, you can extend data transformation directly from your type safe SQL result into Java data structures, back into SQL. These things aren’t possible with JDBC. These things weren’t possible prior to Java 8.

jOOQ is free and Open Source for use with Open Source databases, and it offers commercial licensing for use with commercial databases.

For more information about jOOQ or jOOQ’s DSL API, consider these resources:

Stay tuned for tomorrow’s article “How jOOQ helps pretend that your stored procedures are a part of Java”
This post is part of the Java Advent Calendar and is licensed under the Creative Commons 3.0 Attribution license. If you like it, please spread the word by sharing, tweeting, FB, G+ and so on!

How jOOQ Leverages Generic Type Safety in its DSL

In this year’s Java Advent Calendar, we’re thrilled to have been asked to feature a mini-series showing you a couple of advanced and very interesting topics that we’ve been working on when developing jOOQ.

The series consists of:

Don’t miss any of these!

How jOOQ leverages generic type safety in its DSL

Few Java developers are aware of this, but SQL is a very type safe language. In the Java ecosystem, if you’re using JDBC, you’re operating on dynamically constructed SQL strings, which are sent to the server for execution – or failure. Some IDEs may have started to be capable of introspecting parts of your static SQL, but often you’re concatenating predicates to form a very dynamic query:

String sql = "SELECT a, b, c FROM table WHERE 1 = 1";

if (someCondition)
sql += " AND id = 3";

if (someOtherCondition)
sql += " AND value = 42";

These concatenations quickly turn nasty and are one of the reasons why Java developers don’t really like SQL

SQL as written via JDBC. Image (c) by Greg Grossmeier. License CC-BY-SA 2.0

But interestingly, PL/SQL or T-SQL developers never complain about SQL in this way. In fact, they feel quite the opposite. Look at how SQL is nicely embedded in a typical PL/SQL block:

BEGIN

-- The record type of "rec" is inferred by the compiler
FOR rec IN (

-- This compiles only when I have matching
-- degrees and types of both UNION subselects!
SELECT first_name, last_name FROM customers
UNION
SELECT first_name, last_name FROM staff
)
LOOP

-- This compiles only if rec really has
-- first_name and last_name columns
INSERT INTO people (first_name, last_name)

-- Obviously, VALUES must match the above target table
VALUES (rec.first_name, rec.last_name);
END LOOP;
END;

Now, we can most certainly discuss syntax. Whether you like SQL’s COBOLesque syntax or not is a matter of taste and a matter of habit, too. But one thing is clear, SQL is absolutely type safe, and most sane people would consider that a very good thing. Read The Inconvenient Truth About Dynamic vs. Static Typing for more details.

The same can be achieved in Java!

JDBC’s lack of type safety is a brilliant feature for the low-level API that JDBC is. At some point, we need an API that can simply send SQL strings over the wire without knowing anything about the wire protocol, and retrieve back cursors of arbitrary / unknown type. However, if we don’t execute our SQL directly via JDBC, but maintain a type safe SQL AST (Abstract Syntax Tree) prior to query execution, then we might actually anticipate the returned type of our statements.

jOOQ’s DSL API (Domain-specific language) works exactly like that. When you create SQL statements with jOOQ, you’re implicitly creating an AST both for your Java compiler, but also for your runtime environment. Here’s how that works:

DSL.using(configuration)
.select(CUSTOMERS.FIRST_NAME, CUSTOMERS.LAST_NAME).from(CUSTOMERS)
.union(
select(STAFF.FIRST_NAME, STAFF.LAST_NAME ).from(STAFF))
.fetch();

If we look closely at what the above query really does, we’ll see that we’re calling one of several overloaded select() methods on jOOQ’s DSLContext class, namely DSLContext.select(Field, Field), the one that takes two argument columns.

The whole API looks like this, and we’ll see immediately after why this is so useful:

<T1> SelectSelectStep<Record1<T1>> 
select(Field<T1> field1);
<T1, T2> SelectSelectStep<Record2<T1, T2>>
select(Field<T1> field1, Field<T2> field2);
<T1, T2, T3> SelectSelectStep<Record3<T1, T2, T3>>
select(Field<T1> field1, Field<T2> field2, Field<T3> field3);
// and so on...

So, by explicitly passing two columns to the select() method, you have chosen the second one of the above methods that returns a DSL type that is parameterised with Record2, or more specifically, with Record2<String, String>. Yes, the String parameter bindings are inferred from the very columns that we passed to the select() call, because jOOQ’s code generator reverse-engineers your database schema and generates those classes for you.

The generated Customers class really looks like this (simplified):

// All table references are listed here:
class Tables {
Customers CUSTOMERS = new Customers();
Staff STAFF = new Staff();
}

// All tables have an individual class each, with columns inside:
class Customers {
final Field<String> FIRST_NAME = ...
final Field<String> LAST_NAME = ...
}

As you can see, all type information is already available to you, automatically, as you have defined those types only once in the database. No need to define them again in Java.

Generic type information is ubiquitous

The interesting part is the UNION. The union() method on the DSL API simply looks like this:

public interface SelectUnionStep<R extends Record> {
SelectOrderByStep<R> union(Select<? extends R> select);
}

If we go back to our statement, we can see that the type of the object upon which we call union() is really this type:

SelectUnionStep<Record2<String, String>>

… thus, the method union() that we’re calling is really expecting an argument of this type:

union(Select<? extends Record2<String, String>> select);

… which essentially means that we’ll get a compilation error if we don’t provide two string columns also in the second subselect:

DSL.using(configuration)
.select(CUSTOMERS.FIRST_NAME, CUSTOMERS.LAST_NAME).from(CUSTOMERS)
.union(
// ^^^^^ doesn't compile, wrong argument type!
select(STAFF.FIRST_NAME).from(STAFF))
.fetch();

or also:

DSL.using(configuration)
.select(CUSTOMERS.FIRST_NAME, CUSTOMERS.LAST_NAME).from(CUSTOMERS)
.union(
// ^^^^^ doesn't compile, wrong argument type!
select(STAFF.FIRST_NAME, STAFF.DATE_OF_BIRTH).from(STAFF))
.fetch();

Static type checking helps finding bugs early

… indeed! All of the above bugs can be found at compile-time because your Java compiler will not accept the wrong SQL statements. When writing dynamic SQL, this can be incredibly subtle, as the different UNION subselects may not be created all at the same place. You may have a complex DAO that generates the SQL across several methods. With this kind of generic type safety, you can continue to do so, safely.

As mentioned before, this extends through the whole API. Check out…

IN predicates

This compiles:

// Get all customers whose first name corresponds to a staff first name
DSL.using(configuration)
.select().from(CUSTOMERS)
.where(CUSTOMERS.FIRST_NAME.in(
select(STAFF.FIRST_NAME).from(STAFF)
))
.fetch();

This doesn’t compile:

DSL.using(configuration)
.select().from(CUSTOMERS)
.where(CUSTOMERS.FIRST_NAME.in(
// ^^ wrong argument type!
select(STAFF.FIRST_NAME, STAFF.LAST_NAME).from(STAFF)
))
.fetch();

But this compiles:

// Get all customers whose first and last names both correspond
// to a staff first and last names
DSL.using(configuration)
.select().from(CUSTOMERS)
.where(row(CUSTOMERS.FIRST_NAME, CUSTOMERS.LAST_NAME).in(
select(STAFF.FIRST_NAME, STAFF.LAST_NAME).from(STAFF)
))
.fetch();

Notice the use of row() to construct a row value expression, an extremely useful but little known SQL feature.

INSERT statements

This compiles:

DSL.using(configuration)
.insertInto(CUSTOMERS, CUSTOMERS.FIRST_NAME, CUSTOMERS.LAST_NAME)
.values("John", "Doe")
.execute();

This doesn’t compile:

DSL.using(configuration)
.insertInto(CUSTOMERS, CUSTOMERS.FIRST_NAME, CUSTOMERS.LAST_NAME)
.values("John")
// ^^^^^^ Invalid number of arguments
.execute();

Conclusion

Internal domain-specific languages can express a lot of type safety in Java, almost as much as the external language really implements. In the case of SQL – which is a very type safe language – this is particularly true and interesting.

jOOQ has been designed to create as little cognitive friction as possible for any Java developer who wants to write embedded SQL in Java, i.e. the Java code will look and feel exactly like the SQL code that it represents. At the same time, jOOQ has been designed to offer as much compile-time type safety as possible in the Java language (or also in Scala, Groovy, etc.).

jOOQ is free and Open Source for use with Open Source databases, and it offers commercial licensing for use with commercial databases.

For more information about jOOQ or jOOQ’s DSL API, consider these resources:

Stay tuned for tomorrow’s article “How jOOQ allows for fluent functional-relational interactions in Java 8”
This post is part of the Java Advent Calendar and is licensed under the Creative Commons 3.0 Attribution license. If you like it, please spread the word by sharing, tweeting, FB, G+ and so on!

013 Things that Will go Horribly Wrong on This Friday, Dec 13, 2013

I’m a defensive programmer. A wise man once taught me:

Whatever can go wrong will go wrong

Another wise man taught me to…

expect the unexpected

Today, this will finally all make sense, as today, on this Friday the 13th, the following 10 programming-related things will go terribly wrong. In other words, your software is about to explode…

1. … because you have never really thought about names

Today, you’ll terribly regret never having thought about names, because that user will sign up to your application. That user’s name is null. I mean, literally, Null. Like Jack Null, Heather Null, Null Smith. Whatever. You hadn’t thought of the implications of such a user, and now you’ll have to fix it. And while you’re at it, please consider all the other mistakes and misbeliefs that you might have been making over the past years. Like:

  • Thinking that people’s names are case sensitive.
  • Thinking that people’s names are case insensitive.
  • Thinking that people’s names have any case at all.

Read this scary article about Falsehoods Programmers Believe About Names, to learn more about why your software will explode today because of people’s names.

2. … because you get SQL wrong

Yes. SQL. Some hate it, others love it. SQL has its own ways. But today, you’ll regret never having thought about SQL NULL. Today you’ll regret never having considered the difference between UNION and UNION ALL. Today, you’ll run out of memory, because you have thought you could perform that JOIN in Java (or C#, PHP, you name it), rather than in SQL.

Today, you’ll commit any of these 10 Common Mistakes Java Developers Make When Writing SQL

3. … because you still get SQL NULL wrong

As a matter of fact, you still didn’t understand SQL NULL. You’re not to blame, no one really understands SQL NULL. This must have been some elaborate Halloween joke by E. F. Codd, a sophisticated academic gag he lay upon us and upon the next 15 generations of programmers. But all that cynicism won’t help you, because today, NULL is going to explode right in your application.

If you don’t believe it, learn about NULL in SQL: Explaining its Behavior. And then, go back and check your SQL while you still can!

4. … because your Java code is overdesigned. Or underdesigned

It doesn’t help that you now think you know SQL. Because 80% of your application (which will blow up today, on this Friday, Dec 13th) is written in Java. And if it isn’t, then consider your Android phone, which is. And if that isn’t, either, then chances are, that any other device of yours is running Java.

And those who wrote that Java code, they didn’t write object-oriented Java code, but Perl-esque or C-esque Java code leading to a horrible amount of spaghetti. And then, when your architect (or you?) attempted to clean up the spaghetti, they just went mad on all the design patterns that we have had in Java for the last 10 years. Now, no one can maintain this application, which leads to it blowing up right now.

While this happens, consider reading Watch Out for These 10 Common Pitfalls of Experienced Java Developers & Architects. At least, you’ll know whom to blame. You should blame the humble architect.

5. … because your fellow developers keep finding new excuses

There’s no way around that. You and your fellow developers didn’t do your work. And you just keep finding new excuses for things you did (or didn’t do). Like:

  • Oh, you said you DIDN’T want that to happen?
  • The project manager said no one would want that feature

And that’s just the beginning. Today, all those Programming Excuses will unravel and show you the true nature of your application, explaining why it is about to explode.

6. … because you really really get SQL wrong

Really. It actually isn’t because of those developers or because of Java. It’s really SQL’s fault that your application explodes today. Who can ever memorise those caveats anyway? Because your application suffers from SQL injection and syntax errors due to the lack of bind variable usage. Because you didn’t use enough constraints. Because you thought that 50ms at development time was fast query execution. Yes, that “fast” query has now been running for 30 minutes in production and within a bit, it will make your application explode, because you haven’t read:

10 More Common Mistakes Java Developers Make When Writing SQL

7. … because you got dates and times wrong

Oh yes. That business-critical account statement. It goes from October 1 to October 31, 2010. Yes, and oh, your customer is using the CET / CEST time zone. Which means that chances are high that on this given end date, your customer’s country was switching from daylight savings time to winter time. As you’re an U.S. based developer, you hadn’t thought of that and did some date time arithmetic based on 24 hour dates, when in fact that particular day had 25 hours.

And don’t get me started on leap seconds. They jump right at you before you have updated your OS and/or JVM to get the latest in calendar technology. Too bad, you’re writing high-frequency-trading software instead of some geology software, where you don’t care about the odd leap second. Or leap year. Or leap millenium.

Sigh. When will we introduce the metric system also for our calendars and count in terms of Star Trek space time? But before that happens, let us learn about Falsehoods Programmers Believe About Time. And on a more serious note, Some Notes About Time. Clock’s ticking!

8. … because you get XML namespaces wrong

Now, this will go so terribly wrong, I don’t even want to go into this topic. Just a hint. This easy-to-read document will help you understand where you went wrong.

9. … because you’ll get CSS wrong

Finally, Microsoft has freed us from IE6 and previous horrible implementations of HTML, CSS et al. Unsupporting IE6 was a great step forward, helping us collectively forget what happened in the “dark ages of HTML” when we needed to double all HTML, CSS, JavaScript, to make things work on both Internet Explorer and all the other browser. Or did they?

Wait, mobile browsers appeared, HTML5 wasn’t standardised (or implemented?) quickly enough, Webkit set new defacto standards, aaagh. Like the mythological Hydra, the two-headed browser era has now transformed into an era where browsers have 360 heads. All different, utterly unmaintainable, according to Stefan Baumgartner whose hilarious talk about Mobile Browsers at Topconf 2013 has at least kept me from losing faith in mankind. Because at least we haven’t lost our humour.

Yet, today is the day your application will explode, and it is most likely (or just as likely) because of a CSS bug. Don’t believe it? Read The IE CSS Bug That Cost Me a Month’s Salary. And then go learn from Nicole Sullivan who has taught us about the 5 Mistakes of Massive CSS.

10. … because you get your encÕÐing wron错误的编码

Sigh. So you’re thinking that I am a Java and SQL person, and since you’re using some fancy language like Ruby or Python, you’re safe? Nope, you’re not. Not this time. Even your application will explode today, on this Friday, 13th because you got your encoding wrong. No one ever gets this right. Ever. You think that UTF-8 will save you? Did you hear of UTF-16 and UTF-32? Things can and will go wrong with UTF-8. Specifically because your application exports to CSV, which is consumed by Excel, which expects data to be encoded in Cp1251. Or maybe even an encoding without the € (Euro) sign. Sigh.

You know why we have ISO / IEC 8859-1 AND ISO / IEC 8859-15? Obviously, today, your application will explode precisely because you’ve chosen the wrong one. Yet, I know the truth. Don’t believe ISO / IEC’s or Wikipedia’s “official” story. As Thomas Müller (the H2 developer) once explained to me, it happened like this:

Once upon a time, an ISO engineer thought, we need a new encoding standard. So he went and created ISO 8859-1. Obviously, he didn’t name it ISO 8859, because he knew he was getting it wrong and his smart alec co worker will fix it just to prove he got it wrong. So he added the “dash one”. Then, a couple of years later, his co worker actually did show up and did complain that the euro sign was missing. And a couple of other very useful characters for 99% of us developers: ŠšŽžŒœŸ.

So the first guy said, “Oh well, who cares. Let’s leave things as they are”. And the other guy said: “No I’ll fix this”. And the first guy said, “No, don’t do it, it’ll cause so much pain”, and the other guy said: “No this is just wrong”. Much like in “Someone is Wrong in the ISO”. And after lots more of arguing, we now have even more permutations of encoding pairs to get wrong.

The above is how most things happened in the past, by the way. Which is only cold comfort for you as you are about to notice that your application will explode, today. Since you’re very unlikely to actually learn how this works correctly (most importantly not today, on this Friday, 13th), you might at least use this Python script to fix all wrong characters back from UTF-8 to ISO-8859-1 (or vice versa? Try both…). Read Fixing Common Unicode Mistakes with Python — After They’ve Been Made. Another good read is UTF-8: The Secret of Character Encoding. And finally: Geek and Poke’s point of view on the matter:

When it all began by Geek and Poke, Licensed CC-BY 3.0

11. (or 013) … because you get radixes wrong

If you’re unfortunate enough to code in JavaScript (and that is unfortunate, as we all know), you’ll run the risk of forgetting that parsing an int automatically “detects” the implicit radix of your number string. So, today, you’ll think that 013 has anything to do with the decimal 13, the hexadecimal 0x0D. You couldn’t get this any more wrong.

Because today is yet another day where you fell for JavaScript’s accepting leading zeroes as octal numbers. Read more about this behaviour here.

As you may have noticed, this list contains 013 things that will go wrong on this Dec 13. I.e. octal 13 on this decimal 13th day. Even this article got it wrong! It got it wrong twice, because the URL can no longer be changed from 10 things to 013 things. Today you won’t only get radixes wrong, but you’ll get lots of broken links or outdated websites, too!

TL;DR Today, everything will go wrong

Today is Friday the 13th. More specifically, 13.12.13 in my country’s date notation. See #7 to learn about what will go wrong with dates and times today, when you try to parse that date in other countries than mine. I wouldn’t be surprised if my clock showed 13.13.13 because of yet another piece of Code That Made Me Cry that went wrong today, and it would prove that today is a really bad day.

And don’t think about grabbing a coffee to cheer up after this article, because today, on this Friday the 13th, your coffee machine’s clock isn’t working because someone forgot a leap second. And even if it did work, there’s only DECAF left. Time to call it a weekend! Have a nice one, and don’t write any code, today!

Meta: this post is part of the Java Advent Calendar and is licensed under the Creative Commons 3.0 Attribution license. If you like it, please spread the word by sharing, tweeting, FB, G+ and so on!