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:
- Dec 17: How jOOQ Leverages Generic Type Safety in its DSL
- Dec 18: How jOOQ Allows for Fluent Functional-Relational Interactions in Java 8
- Dec 19: How jOOQ Helps Pretend that Your Stored Procedures are a Part of Java
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:
- The jOOQ documentation page
- The Java Fluent API Designer Crash Course
- A webinar with Arun Gupta from Red Hat about jOOQ and JavaEE
- A presentation about jOOQ at GeeCON Krakow
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!
Author: Lukas Eder
Founder and CEO at Data Geekery, the company behind jOOQ