Tag Archive | jooq

Flyway and jOOQ for Unbeatable SQL Development Productivity


When performing database migrations, we at Data Geekery recommend using jOOQ with Flyway – Database Migrations Made Easy. In this post, we’re going to look into a simple way to get started with the two frameworks.

Philosophy

There are a variety of ways how jOOQ and Flyway could interact with each other in various development setups. In this tutorial we’re going to show just one variant of such framework team play – a variant that we find particularly compelling for most use cases.

The general philosophy and workflow behind the following approach can be summarised as this:

  • 1. Database increment
  • 2. Database migration
  • 3. Code re-generation
  • 4. Development

The four steps above can be repeated time and again, every time you need to modify something in your database. More concretely, let’s consider:

  • 1. Database increment – You need a new column in your database, so you write the necessary DDL in a Flyway script
  • 2. Database migration – This Flyway script is now part of your deliverable, which you can share with all developers who can migrate their databases with it, the next time they check out your change
  • 3. Code re-generation – Once the database is migrated, you regenerate all jOOQ artefacts (see code generation), locally
  • 4. Development – You continue developing your business logic, writing code against the udpated, generated database schema

0.1. Maven Project Configuration – Properties

The following properties are defined in our pom.xml, to be able to reuse them between plugin configurations:

<properties>
    <db.url>jdbc:h2:~/flyway-test</db.url>
    <db.username>sa</db.username>
</properties>

0.2. Maven Project Configuration – Dependencies

While jOOQ and Flyway could be used in standalone migration scripts, in this tutorial, we’ll be using Maven for the standard project setup. You will also find the source code of this tutorial on GitHub, and the full pom.xml file here.

These are the dependencies that we’re using in our Maven configuration:

<!-- We'll add the latest version of jOOQ 
     and our JDBC driver - in this case H2 -->
<dependency>
    <groupId>org.jooq</groupId>
    <artifactId>jooq</artifactId>
    <version>3.4.0</version>
</dependency>
<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <version>1.4.177</version>
</dependency>

<!-- For improved logging, we'll be using 
     log4j via slf4j to see what's going
     on during migration and code generation -->
<dependency>
    <groupId>log4j</groupId>
    <artifactId>log4j</artifactId>
    <version>1.2.16</version>
</dependency>
<dependency>
    <groupId>org.slf4j</groupId>
    <artifactId>slf4j-log4j12</artifactId>
    <version>1.7.5</version>
</dependency>

<!-- To esnure our code is working, we're
     using JUnit -->
<dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
    <version>4.11</version>
    <scope>test</scope>
</dependency>

0.3. Maven Project Configuration – Plugins

After the dependencies, let’s simply add the Flyway and jOOQ Maven plugins like so. The Flyway plugin:

<plugin>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-maven-plugin</artifactId>
    <version>3.0</version>

    <!-- Note that we're executing the Flyway
         plugin in the "generate-sources" phase -->
    <executions>
        <execution>
            <phase>generate-sources</phase>
            <goals>
                <goal>migrate</goal>
            </goals>
        </execution>
    </executions>

    <!-- Note that we need to prefix the db/migration
         path with filesystem: to prevent Flyway
         from looking for our migration scripts
         only on the classpath -->
    <configuration>
        <url>${db.url}</url>
        <user>${db.username}</user>
        <locations>
            <location>filesystem:src/main/resources/db/migration</location>
        </locations>
    </configuration>
</plugin>

The above Flyway Maven plugin configuration will read and execute all database migration scripts from src/main/resources/db/migrationprior to compiling Java source code. While the official Flyway documentation suggests that migrations be done in the compile phase, the jOOQ code generator relies on such migrations having been done prior to code generation.

After the Flyway plugin, we’ll add the jOOQ Maven Plugin. For more details, please refer to the manual’s section about the code generation configuration.

<plugin>
    <groupId>org.jooq</groupId>
    <artifactId>jooq-codegen-maven</artifactId>
    <version>${org.jooq.version}</version>

    <!-- The jOOQ code generation plugin is also 
         executed in the generate-sources phase,
         prior to compilation -->
    <executions>
        <execution>
            <phase>generate-sources</phase>
            <goals>
                <goal>generate</goal>
            </goals>
        </execution>
    </executions>

    <!-- This is a minimal working configuration. 
         See the manual's section about the code
         generator for more details -->
    <configuration>
        <jdbc>
            <url>${db.url}</url>
            <user>${db.username}</user>
        </jdbc>
        <generator>
            <database>
                <includes>.*</includes>
                <inputSchema>FLYWAY_TEST</inputSchema>
            </database>
            <target>
                <packageName>org.jooq.example.flyway.db.h2</packageName>
                <directory>target/generated-sources/jooq-h2</directory>
            </target>
        </generator>
    </configuration>
</plugin>

This configuration will now read the FLYWAY_TEST schema and reverse-engineer it into the target/generated-sources/jooq-h2 directory, and within that, into the org.jooq.example.flyway.db.h2 package.

1. Database increments

Now, when we start developing our database. For that, we’ll create database increment scripts, which we put into thesrc/main/resources/db/migration directory, as previously configured for the Flyway plugin. We’ll add these files:

  • V1__initialise_database.sql
  • V2__create_author_table.sql
  • V3__create_book_table_and_records.sql

These three scripts model our schema versions 1-3 (note the capital V!). Here are the scripts’ contents

-- V1__initialise_database.sql
DROP SCHEMA flyway_test IF EXISTS;

CREATE SCHEMA flyway_test;
-- V2__create_author_table.sql
CREATE SEQUENCE flyway_test.s_author_id START WITH 1;

CREATE TABLE flyway_test.author (
  id INT NOT NULL,
  first_name VARCHAR(50),
  last_name VARCHAR(50) NOT NULL,
  date_of_birth DATE,
  year_of_birth INT,
  address VARCHAR(50),

  CONSTRAINT pk_t_author PRIMARY KEY (ID)
);
-- V3__create_book_table_and_records.sql
CREATE TABLE flyway_test.book (
  id INT NOT NULL,
  author_id INT NOT NULL,
  title VARCHAR(400) NOT NULL,

  CONSTRAINT pk_t_book PRIMARY KEY (id),
  CONSTRAINT fk_t_book_author_id FOREIGN KEY (author_id) REFERENCES flyway_test.author(id)
);


INSERT INTO flyway_test.author VALUES (next value for flyway_test.s_author_id, 'George', 'Orwell', '1903-06-25', 1903, null);
INSERT INTO flyway_test.author VALUES (next value for flyway_test.s_author_id, 'Paulo', 'Coelho', '1947-08-24', 1947, null);

INSERT INTO flyway_test.book VALUES (1, 1, '1984');
INSERT INTO flyway_test.book VALUES (2, 1, 'Animal Farm');
INSERT INTO flyway_test.book VALUES (3, 2, 'O Alquimista');
INSERT INTO flyway_test.book VALUES (4, 2, 'Brida');

2. Database migration and 3. Code regeneration

The above three scripts are picked up by Flyway and executed in the order of the versions. This can be seen very simply by executing:

mvn clean install

And then observing the log output from Flyway…

[INFO] --- flyway-maven-plugin:3.0:migrate (default) @ jooq-flyway-example ---
[INFO] Database: jdbc:h2:~/flyway-test (H2 1.4)
[INFO] Validated 3 migrations (execution time 00:00.004s)
[INFO] Creating Metadata table: "PUBLIC"."schema_version"
[INFO] Current version of schema "PUBLIC": <>
[INFO] Migrating schema "PUBLIC" to version 1
[INFO] Migrating schema "PUBLIC" to version 2
[INFO] Migrating schema "PUBLIC" to version 3
[INFO] Successfully applied 3 migrations to schema "PUBLIC" (execution time 00:00.073s).

… and from jOOQ on the console:

[INFO] --- jooq-codegen-maven:3.5.0-SNAPSHOT:generate (default) @ jooq-flyway-example ---
[INFO] Using this configuration:
...
[INFO] Generating schemata      : Total: 1
[INFO] Generating schema        : FlywayTest.java
[INFO] ----------------------------------------------------------
[....]
[INFO] GENERATION FINISHED!     : Total: 337.576ms, +4.299ms

4. Development

Note that all of the previous steps are executed automatically, every time someone adds new migration scripts to the Maven module. For instance, a team member might have committed a new migration script, you check it out, rebuild and get the latest jOOQ-generated sources for your own development or integration-test database.

Now, that these steps are done, you can proceed writing your database queries. Imagine the following test case

import org.jooq.Result;
import org.jooq.impl.DSL;
import org.junit.Test;

import java.sql.DriverManager;

import static java.util.Arrays.asList;
import static org.jooq.example.flyway.db.h2.Tables.*;
import static org.junit.Assert.assertEquals;

public class AfterMigrationTest {

    @Test
    public void testQueryingAfterMigration() throws Exception {
        try (Connection c = DriverManager.getConnection("jdbc:h2:~/flyway-test", "sa", "")) {
            Result<?> result =
            DSL.using(c)
               .select(
                   AUTHOR.FIRST_NAME,
                   AUTHOR.LAST_NAME,
                   BOOK.ID,
                   BOOK.TITLE
               )
               .from(AUTHOR)
               .join(BOOK)
               .on(AUTHOR.ID.eq(BOOK.AUTHOR_ID))
               .orderBy(BOOK.ID.asc())
               .fetch();

            assertEquals(4, result.size());
            assertEquals(asList(1, 2, 3, 4), result.getValues(BOOK.ID));
        }
    }
}

If you run the mvn clean install again, the above integration test will now compile and pass!

Reiterate

The power of this approach becomes clear once you start performing database modifications this way. Let’s assume that the French guy on our team prefers to have things his way (no offense intended ;-) ):

-- V4__le_french.sql
ALTER TABLE flyway_test.book 
  ALTER COLUMN title RENAME TO le_titre;

They check it in, you check out the new database migration script, run

mvn clean install

And then observe the log output:

[INFO] --- flyway-maven-plugin:3.0:migrate (default) @ jooq-flyway-example ---
[INFO] --- flyway-maven-plugin:3.0:migrate (default) @ jooq-flyway-example ---
[INFO] Database: jdbc:h2:~/flyway-test (H2 1.4)
[INFO] Validated 4 migrations (execution time 00:00.005s)
[INFO] Current version of schema "PUBLIC": 3
[INFO] Migrating schema "PUBLIC" to version 4
[INFO] Successfully applied 1 migration to schema "PUBLIC" (execution time 00:00.016s).

So far so good, but later on:

[ERROR] COMPILATION ERROR :
[INFO] -------------------------------------------------------------
[ERROR] C:\...\AfterMigrationTest.java:[24,19] error: cannot find symbol
[INFO] 1 error

When we go back to our Java integration test, we can immediately see that the TITLE column is still being referenced, but it no longer exists:

public class AfterMigrationTest {

    @Test
    public void testQueryingAfterMigration() throws Exception {
        try (Connection c = DriverManager.getConnection("jdbc:h2:~/flyway-test", "sa", "")) {
            Result<?> result =
            DSL.using(c)
               .select(
                   AUTHOR.FIRST_NAME,
                   AUTHOR.LAST_NAME,
                   BOOK.ID,
                   BOOK.TITLE
                   //   ^^^^^ This column no longer exists. 
                   //   We'll have to rename it to LE_TITRE
               )
               .from(AUTHOR)
               .join(BOOK)
               .on(AUTHOR.ID.eq(BOOK.AUTHOR_ID))
               .orderBy(BOOK.ID.asc())
               .fetch();

            assertEquals(4, result.size());
            assertEquals(asList(1, 2, 3, 4), result.getValues(BOOK.ID));
        }
    }
}

Conclusion

This tutorial shows very easily how you can build a rock-solid development process using Flyway and jOOQ to prevent SQL-related errors very early in your development lifecycle – immediately at compile time, rather than in production!

Visit the Flyway website and the jOOQ website.

SQL Server ALTER TABLE SET DEFAULT


Most databases that support default values on their column DDL, it is also possible to actually alter that default. An Oracle example:

CREATE TABLE t (
  val NUMBER(7) DEFAULT 1 NOT NULL
);

-- Oops, wrong default, let us change it
ALTER TABLE t MODIFY val DEFAULT -1;

-- Now that is better

Unfortunately, this isn’t possible in SQL Server, where the DEFAULT column property is really a constraint, and probably a constraint whose name you don’t know because it was system generated.

But luckily, jOOQ 3.4 now supports DDL and can abstract this information away from you by generating the following Transact-SQL program:

DECLARE @constraint NVARCHAR(max);
DECLARE @command NVARCHAR(max);

SELECT @constraint = name
FROM sys.default_constraints
WHERE parent_object_id = object_id('t')
AND parent_column_id = columnproperty(
    object_id('t'), 'val', 'ColumnId');

IF @constraint IS NOT NULL
BEGIN
  SET @command = 'ALTER TABLE t DROP CONSTRAINT ' 
    + @constraint;
  EXECUTE sp_executeSQL @command

  SET @command = 'ALTER TABLE t ADD CONSTRAINT ' 
    + @constraint + ' DEFAULT -1 FOR val';
  EXECUTE sp_executeSQL @command
END
ELSE
BEGIN
  SET @command = 'ALTER TABLE t ADD DEFAULT -1 FOR val';
  EXECUTE sp_executeSQL @command
END

This program will either drop and create a new constraint with the same name, or create an entirely new constraint with a system-generated name.

With jOOQ, you can execute this statement as such:

DSL.using(configuration)
   .alterTable(T)
   .alter(T.VAL)
   .defaultValue(-1)
   .execute();

jOOQ Newsletter: June 18, 2014 – jOOQ goes JavaOne™


jOOQ Goes JavaOne™

We’re super excited to announce that our CEO and Head of R&D Lukas will be heading to San Francisco this fall to talk about jOOQ at JavaOne™! This is not just great for Data Geekery and jOOQ, but also for the whole Java / SQL ecosystem, as we believe that the Java / SQL integration deserves much more focus at conferences, where buzzwords like Big Data and NoSQL dominate the agenda disproportionally.

From our perspective, the JVM is the best platform for general purpose languages, whereas SQL is the best tool for database interaction – with Oracle SQL being a leader in the industry. So…

2014 will be a great year for Java and SQL

Prior to JavaOne™, we have also been talking at the awesome 33rd Degree and GeekOut conferences, the latter having been hosted by our friends over at ZeroTurnaround who have launched XRebel, a very promising tool to help you find rogue SQL statements in your application. Stay tuned as we’ll be trying out XRebel to compare jOOQ with Hibernate on our blog, soon.

Stay informed about 2014 events on www.jooq.org/news.

Tweet of the Day

Our customers, users, and followers are sharing their love for jOOQ with the world and we can hardly catch up with them! Here are:

Alessio Harri, who is having a great time using OpenJPA (the workhorse) with jOOQ (the artist)

The Vertabelo team over at e-point, who like using jOOQ as a database consumer for their SaaS, collaborative ERD tool.

Petri Kainulainen, who is working around performance problems by getting back in control of his SQL.

Thanks for the shouts, guys! You make the jOOQ experience rock!

Upcoming License Improvements

From our recent negotiations with site license leads, we’ve come to two conclusions that will benefit all of the jOOQ Professional and jOOQ Enterprise customer base.

  • When you buy a car or a TV, you probably don’t run to the supplier every time you encounter a small defect that prevents you from fully enjoying your product. You’ll fix it yourself. We want to do the same in the future. As we trust our customers, and as we already ship our sources, we will soon allow you to implement urgent fixes to jOOQ yourselves, as we believe that this will improve the jOOQ experience for everyone and add further value to your own experience.
  • We understand the requirements of purchasing departments in large organisations. Often, it is easier to purchase a site license from a supplier rather than going through the hassles of adapting workstation-based subscriptions all the time. To respond to this need, we’ll soon publish a discounted, tiered pricing model for large-volume purchases of our perpetual licenses.

Both of these improvements will be deployed to all of our customer base in the beginning of July.

Do you already have any questions regarding what will change / improve? Do not hesitate to contact us.

Community Zone – The jOOQ aficionados have been active!

The jOOQ community has been very active again in the last month. We’re happy to point out these editor’s picks from our radar:

Vlad Mihalcea is a very active blogger on the subject of Hibernate integrations, transaction mangagement and connection pooling performance. We’re looking forward to his future blog posts about how to integrate ORMs with SQL/jOOQ, e.g. by applying emerging architecture patterns such as CQRS. One of his most recent, very interesting blog posts deals precisely with that subject.

Micha Kops has been blogging about a variety of Java tool integrations and has now published this comprehensive and very useful jOOQ tutorial. It is great to see fresh opinions from people just getting to know the platform and blogging about it.

SQL Zone – More common SQL mistakes

Our popular blog series “Top 10 mistakes Java developers make when writing SQL”has been enhanced with yet another must-read article for the Java/SQL community:

Did you know about all those common mistakes? We bet you didn’t. If you haven’t seen enough, see also our previous articles:

SQL Zone – Don’t roll your own OFFSET pagination emulation

One of the great reasons why you should use jOOQ is the fact that jOOQ abstracts away all the hard parts of your SQL dialect. If you’re using Oracle (prior to 12c), SQL Server (prior to 2012), or DB2, you might need to emulate what other databases know as OFFSET pagination. While most people get the simple use-cases right, we’ve tried to outline all of the other issues that may arise when you try to do it yourself in our blog post:

A must-read for all SQL transformation aficionados.

Feedback zone

You’ve read to the end of this newsletter, that’s great! Did you like it? What did we do great? What can we improve? What other subjects would you like us to cover?

We’d love to hear from you, so if you want to reach out to us, just drop a message to contact@datageekery.com. Looking forward to hearing from you!

Conference Legal Disclaimers


After deep consideration with our lawyers, we would like to follow suit with Oracle and provide you with the following legal disclaimer about our jOOQ-related conference talks, as presented at the awesome GeekOut conference in Tallinn. Please do read them and take them seriously.

THE FOLLOWING IS COMMUNICATED TO YOU SOLELY FOR ENTERTAINMENT PURPOSES. NO ONE SANE WOULD BELIEVE A GUY WHO CLAIMS HE IS A SQL AFICIONADO OR WORSE WHO CLAIMS THAT SQL IS ANYTHING NEAR BEAUTIFUL. IF YOU STILL FIND THE FOLLOWING INTERESTING AND IF YOU BASE YOUR PURCHASING DECISIONS UPON THAT, YOU DEFINITELY NEED PROFESSIONAL HELP. WE ACTUALLY PROVIDE SUCH HELP.

Java 8 Friday: 10 Subtle Mistakes When Using the Streams API


At Data Geekery, we love Java. And as we’re really into jOOQ’s fluent API and query DSL, we’re absolutely thrilled about what Java 8 will bring to our ecosystem.

Java 8 Friday

Every Friday, we’re showing you a couple of nice new tutorial-style Java 8 features, which take advantage of lambda expressions, extension methods, and other great stuff. You’ll find the source code on GitHub.

10 Subtle Mistakes When Using the Streams API

We’ve done all the SQL mistakes lists:

But we haven’t done a top 10 mistakes list with Java 8 yet! For today’s occasion (it’s Friday the 13th), we’ll catch up with what will go wrong in YOUR application when you’re working with Java 8. (it won’t happen to us, as we’re stuck with Java 6 for another while)

1. Accidentally reusing streams

Wanna bet, this will happen to everyone at least once. Like the existing “streams” (e.g. InputStream), you can consume streams only once. The following code won’t work:

IntStream stream = IntStream.of(1, 2);
stream.forEach(System.out::println);

// That was fun! Let's do it again!
stream.forEach(System.out::println);

You’ll get a

java.lang.IllegalStateException: 
  stream has already been operated upon or closed

So be careful when consuming your stream. It can be done only once

2. Accidentally creating “infinite” streams

You can create infinite streams quite easily without noticing. Take the following example:

// Will run indefinitely
IntStream.iterate(0, i -> i + 1)
         .forEach(System.out::println);

The whole point of streams is the fact that they can be infinite, if you design them to be. The only problem is, that you might not have wanted that. So, be sure to always put proper limits:

// That's better
IntStream.iterate(0, i -> i + 1)
         .limit(10)
         .forEach(System.out::println);

3. Accidentally creating “subtle” infinite streams

We can’t say this enough. You WILL eventually create an infinite stream, accidentally. Take the following stream, for instance:

IntStream.iterate(0, i -> ( i + 1 ) % 2)
         .distinct()
         .limit(10)
         .forEach(System.out::println);

So…

  • we generate alternating 0’s and 1’s
  • then we keep only distinct values, i.e. a single 0 and a single 1
  • then we limit the stream to a size of 10
  • then we consume it

Well… the distinct() operation doesn’t know that the function supplied to the iterate() method will produce only two distinct values. It might expect more than that. So it’ll forever consume new values from the stream, and the limit(10) will never be reached. Tough luck, your application stalls.

4. Accidentally creating “subtle” parallel infinite streams

We really need to insist that you might accidentally try to consume an infinite stream. Let’s assume you believe that the distinct() operation should be performed in parallel. You might be writing this:

IntStream.iterate(0, i -> ( i + 1 ) % 2)
         .parallel()
         .distinct()
         .limit(10)
         .forEach(System.out::println);

Now, we’ve already seen that this will turn forever. But previously, at least, you only consumed one CPU on your machine. Now, you’ll probably consume four of them, potentially occupying pretty much all of your system with an accidental infinite stream consumption. That’s pretty bad. You can probably hard-reboot your server / development machine after that. Have a last look at what my laptop looked like prior to exploding:

If I were a laptop, this is how I'd like to go.

If I were a laptop, this is how I’d like to go.

5. Mixing up the order of operations

So, why did we insist on your definitely accidentally creating infinite streams? It’s simple. Because you may just accidentally do it. The above stream can be perfectly consumed if you switch the order of limit() and distinct():

IntStream.iterate(0, i -> ( i + 1 ) % 2)
         .limit(10)
         .distinct()
         .forEach(System.out::println);

This now yields:

0
1

Why? Because we first limit the infinite stream to 10 values (0 1 0 1 0 1 0 1 0 1), before we reduce the limited stream to the distinct values contained in it (0 1).

Of course, this may no longer be semantically correct, because you really wanted the first 10 distinct values from a set of data (you just happened to have “forgotten” that the data is infinite). No one really wants 10 random values, and only then reduce them to be distinct.

If you’re coming from a SQL background, you might not expect such differences. Take SQL Server 2012, for instance. The following two SQL statements are the same:

-- Using TOP
SELECT DISTINCT TOP 10 *
FROM i
ORDER BY ..

-- Using FETCH
SELECT *
FROM i
ORDER BY ..
OFFSET 0 ROWS
FETCH NEXT 10 ROWS ONLY

So, as a SQL person, you might not be as aware of the importance of the order of streams operations.

jOOQ, the best way to write SQL in Java

6. Mixing up the order of operations (again)

Speaking of SQL, if you’re a MySQL or PostgreSQL person, you might be used to the LIMIT .. OFFSET clause. SQL is full of subtle quirks, and this is one of them. The OFFSET clause is applied FIRST, as suggested in SQL Server 2012’s (i.e. the SQL:2008 standard’s) syntax.

If you translate MySQL / PostgreSQL’s dialect directly to streams, you’ll probably get it wrong:

IntStream.iterate(0, i -> i + 1)
         .limit(10) // LIMIT
         .skip(5)   // OFFSET
         .forEach(System.out::println);

The above yields

5
6
7
8
9

Yes. It doesn’t continue after 9, because the limit() is now applied first, producing (0 1 2 3 4 5 6 7 8 9). skip() is applied after, reducing the stream to (5 6 7 8 9). Not what you may have intended.

BEWARE of the LIMIT .. OFFSET vs. "OFFSET .. LIMIT" trap!

7. Walking the file system with filters

We’ve blogged about this before. What appears to be a good idea is to walk the file system using filters:

Files.walk(Paths.get("."))
     .filter(p -> !p.toFile().getName().startsWith("."))
     .forEach(System.out::println);

The above stream appears to be walking only through non-hidden directories, i.e. directories that do not start with a dot. Unfortunately, you’ve again made mistake #5 and #6. walk() has already produced the whole stream of subdirectories of the current directory. Lazily, though, but logically containing all sub-paths. Now, the filter will correctly filter out paths whose names start with a dot “.”. E.g. .git or .idea will not be part of the resulting stream. But these paths will be: .\.git\refs, or .\.idea\libraries. Not what you intended.

Now, don’t fix this by writing the following:

Files.walk(Paths.get("."))
     .filter(p -> !p.toString().contains(File.separator + "."))
     .forEach(System.out::println);

While that will produce the correct output, it will still do so by traversing the complete directory subtree, recursing into all subdirectories of “hidden” directories.

I guess you’ll have to resort to good old JDK 1.0 File.list() again. The good news is, FilenameFilter and FileFilter are both functional interfaces.

8. Modifying the backing collection of a stream

While you’re iterating a List, you must not modify that same list in the iteration body. That was true before Java 8, but it might become more tricky with Java 8 streams. Consider the following list from 0..9:

// Of course, we create this list using streams:
List<Integer> list = 
IntStream.range(0, 10)
         .boxed()
         .collect(toCollection(ArrayList::new));

Now, let’s assume that we want to remove each element while consuming it:

list.stream()
    // remove(Object), not remove(int)!
    .peek(list::remove)
    .forEach(System.out::println);

Interestingly enough, this will work for some of the elements! The output you might get is this one:

0
2
4
6
8
null
null
null
null
null
java.util.ConcurrentModificationException

If we introspect the list after catching that exception, there’s a funny finding. We’ll get:

[1, 3, 5, 7, 9]

Heh, it “worked” for all the odd numbers. Is this a bug? No, it looks like a feature. If you’re delving into the JDK code, you’ll find this comment in ArrayList.ArraListSpliterator:

/*
 * If ArrayLists were immutable, or structurally immutable (no
 * adds, removes, etc), we could implement their spliterators
 * with Arrays.spliterator. Instead we detect as much
 * interference during traversal as practical without
 * sacrificing much performance. We rely primarily on
 * modCounts. These are not guaranteed to detect concurrency
 * violations, and are sometimes overly conservative about
 * within-thread interference, but detect enough problems to
 * be worthwhile in practice. To carry this out, we (1) lazily
 * initialize fence and expectedModCount until the latest
 * point that we need to commit to the state we are checking
 * against; thus improving precision.  (This doesn't apply to
 * SubLists, that create spliterators with current non-lazy
 * values).  (2) We perform only a single
 * ConcurrentModificationException check at the end of forEach
 * (the most performance-sensitive method). When using forEach
 * (as opposed to iterators), we can normally only detect
 * interference after actions, not before. Further
 * CME-triggering checks apply to all other possible
 * violations of assumptions for example null or too-small
 * elementData array given its size(), that could only have
 * occurred due to interference.  This allows the inner loop
 * of forEach to run without any further checks, and
 * simplifies lambda-resolution. While this does entail a
 * number of checks, note that in the common case of
 * list.stream().forEach(a), no checks or other computation
 * occur anywhere other than inside forEach itself.  The other
 * less-often-used methods cannot take advantage of most of
 * these streamlinings.
 */

Now, check out what happens when we tell the stream to produce sorted() results:

list.stream()
    .sorted()
    .peek(list::remove)
    .forEach(System.out::println);

This will now produce the following, “expected” output

0
1
2
3
4
5
6
7
8
9

And the list after stream consumption? It is empty:

[]

So, all elements are consumed, and removed correctly. The sorted() operation is a “stateful intermediate operation”, which means that subsequent operations no longer operate on the backing collection, but on an internal state. It is now “safe” to remove elements from the list!

Well… can we really? Let’s proceed with parallel(), sorted() removal:

list.stream()
    .sorted()
    .parallel()
    .peek(list::remove)
    .forEach(System.out::println);

This now yields:

7
6
2
5
8
4
1
0
9
3

And the list contains

[8]

Eek. We didn’t remove all elements!? Free beers (and jOOQ stickers) go to anyone who solves this streams puzzler!

This all appears quite random and subtle, we can only suggest that you never actually do modify a backing collection while consuming a stream. It just doesn’t work.

9. Forgetting to actually consume the stream

What do you think the following stream does?

IntStream.range(1, 5)
         .peek(System.out::println)
         .peek(i -> { 
              if (i == 5) 
                  throw new RuntimeException("bang");
          });

When you read this, you might think that it will print (1 2 3 4 5) and then throw an exception. But that’s not correct. It won’t do anything. The stream just sits there, never having been consumed.

As with any fluent API or DSL, you might actually forget to call the “terminal” operation. This might be particularly true when you use peek(), as peek() is an aweful lot similar to forEach().

This can happen with jOOQ just the same, when you forget to call execute() or fetch():

DSL.using(configuration)
   .update(TABLE)
   .set(TABLE.COL1, 1)
   .set(TABLE.COL2, "abc")
   .where(TABLE.ID.eq(3));

Oops. No execute()

jOOQ, the best way to write SQL in Java

Yes, the “best” way – with 1-2 caveats ;-)

10. Parallel stream deadlock

This is now a real goodie for the end!

All concurrent systems can run into deadlocks, if you don’t properly synchronise things. While finding a real-world example isn’t obvious, finding a forced example is. The following parallel() stream is guaranteed to run into a deadlock:

Object[] locks = { new Object(), new Object() };

IntStream
    .range(1, 5)
    .parallel()
    .peek(Unchecked.intConsumer(i -> {
        synchronized (locks[i % locks.length]) {
            Thread.sleep(100);

            synchronized (locks[(i + 1) % locks.length]) {
                Thread.sleep(50);
            }
        }
    }))
    .forEach(System.out::println);

Note the use of Unchecked.intConsumer(), which transforms the functional IntConsumer interface into a org.jooq.lambda.fi.util.function.CheckedIntConsumer, which is allowed to throw checked exceptions.

Well. Tough luck for your machine. Those threads will be blocked forever :-)

The good news is, it has never been easier to produce a schoolbook example of a deadlock in Java!

For more details, see also Brian Goetz’s answer to this question on Stack Overflow.

Conclusion

With streams and functional thinking, we’ll run into a massive amount of new, subtle bugs. Few of these bugs can be prevented, except through practice and staying focused. You have to think about how to order your operations. You have to think about whether your streams may be infinite.

Streams (and lambdas) are a very powerful tool. But a tool which we need to get a hang of, first.

Stay tuned for more exciting Java 8 articles on this blog.

Stop Trying to Emulate SQL OFFSET Pagination with Your In-House DB Framework!


I’m pretty sure you’ve gotten it wrong in numerous ways, so far. And you probably won’t get it right any time soon. So why waste your precious time on SQL tweaking, when you could be implementing business logic?

Let me explain…

It hasn’t been until the recent SQL:2008 standard that what MySQL users know as LIMIT .. OFFSET was standardised into the following simple statement:

SELECT * 
FROM BOOK 
OFFSET 2 ROWS 
FETCH NEXT 1 ROWS ONLY

Yes. So many keywords.

SQL is indeed a very verbose language. Personally, we really like the conciseness of MySQL’s / PostgreSQL’s LIMIT .. OFFSET clause, which is why we chose that for the jOOQ DSL API

In SQL:

SELECT * FROM BOOK LIMIT 1 OFFSET 2

In jOOQ:

select().from(BOOK).limit(1).offset(2);

Now, when you’re a SQL framework vendor, or when you’re rolling your own, in-house SQL abstraction, you might think about standardising this neat little clause. Here’s a couple of flavours from databases that natively support offset pagination:

-- MySQL, H2, HSQLDB, Postgres, and SQLite
SELECT * FROM BOOK LIMIT 1 OFFSET 2

-- CUBRID supports a MySQL variant of the 
-- LIMIT .. OFFSET clause
SELECT * FROM BOOK LIMIT 2, 1

-- Derby, SQL Server 2012, Oracle 12, SQL:2008
SELECT * FROM BOOK 
OFFSET 2 ROWS FETCH NEXT 1 ROWS ONLY

-- Ingres. Eek, almost the standard. Almost!
SELECT * FROM BOOK 
OFFSET 2 FETCH FIRST 1 ROWS ONLY

-- Firebird
SELECT * FROM BOOK ROWS 2 TO 3

-- Sybase SQL Anywhere
SELECT TOP 1 ROWS START AT 3 * FROM BOOK

-- DB2 (without OFFSET)
SELECT * FROM BOOK FETCH FIRST 1 ROWS ONLY

-- Sybase ASE, SQL Server 2008 (without OFFSET)
SELECT TOP 1 * FROM BOOK

So far, so good. These can all be handled. Some databases put offsets before limits, others put limits before offsets, and the T-SQL family puts the whole TOP clause before the SELECT list. This is easy to emulate. Now what about:

  • Oracle 11g and less
  • SQL Server 2008 and less
  • DB2 with OFFSET

(note that you can enable various alternative syntaxes in DB2)

When you google for this, you will find millions of ways to emulate OFFSET .. FETCH in those older databases. The optimal solutions always involve:

  • Using doubly-nested derived tables with ROWNUM filtering in Oracle
  • Using single-nested derived tabels with ROW_NUMBER() filtering in SQL Server and DB2

So you’re emulating it.

Do you think you will get it right?

;-)

Let us go through a couple of issues that you may not have thought about.

First off, Oracle. Oracle obviously wanted to create a maximum vendor-lockin, which is only exceeded by Apple’s recent introduction of Swift. This is why ROWNUM solutions perform best, even better than SQL:2003 standard window function based solutions. Don’t believe it? Read this very interesting article on Oracle offset pagination performance.

So, the optimal solution in Oracle is:

-- PostgreSQL syntax:
SELECT ID, TITLE 
FROM BOOK 
LIMIT 1 OFFSET 2

-- Oracle equivalent:
SELECT *
FROM (
  SELECT b.*, ROWNUM rn
  FROM (
    SELECT ID, TITLE
    FROM BOOK
  ) b
  WHERE ROWNUM <= 3 -- (1 + 2)
)
WHERE rn > 2

So that’s really the equivalent?

Of course not. You’re selecting an additional column, the rn column. You might just not care in most cases, but what if you wanted to make a limited subquery to be used with an IN predicate?

-- PostgreSQL syntax:
SELECT *
FROM BOOK
WHERE AUTHOR_ID IN (
  SELECT ID
  FROM AUTHOR
  LIMIT 1 OFFSET 2
)

-- Oracle equivalent:
SELECT *
FROM BOOK
WHERE AUTHOR_ID IN (
  SELECT * -- Ouch. These are two columns!
  FROM (
    SELECT b.*, ROWNUM rn
    FROM (
      SELECT ID
      FROM AUTHOR
    ) b
    WHERE ROWNUM <= 3
  )
  WHERE rn > 2
)

So, as you can see, you’ll have to do some more sophisticated SQL transformation. If you’re manually emulating LIMIT .. OFFSET, then you might just patch the ID column into the subquery:

SELECT *
FROM BOOK
WHERE AUTHOR_ID IN (
  SELECT ID -- better
  FROM (
    SELECT b.ID, ROWNUM rn -- better
    FROM (
      SELECT ID
      FROM AUTHOR
    ) b
    WHERE ROWNUM <= 3
  )
  WHERE rn > 2
)

So, that’s more like it, right? But since you’re not writing this manually every time, you’re about to start creating your own nifty in-house SQL framework covering the 2-3 use cases that you’ve encountered so far, right?

You can do it. So you’ll regex-search-replace column names automagically to produce the above.

So now, it is correct?

Of course not! Because you can have ambiguous column names in top-level SELECTs, but not in nested selects. What if you want to do this:

-- PostgreSQL syntax:
-- Perfectly valid repetition of two ID columns
SELECT BOOK.ID, AUTHOR.ID
FROM BOOK
JOIN AUTHOR
ON BOOK.AUTHOR_ID = AUTHOR.ID
LIMIT 1 OFFSET 2

-- Oracle equivalent:
SELECT *
FROM (
  SELECT b.*, ROWNUM rn
  FROM (
    -- Ouch! ORA-00918: column ambiguously defined
    SELECT BOOK.ID, AUTHOR.ID
    FROM BOOK
    JOIN AUTHOR
    ON BOOK.AUTHOR_ID = AUTHOR.ID
  ) b
  WHERE ROWNUM <= 3
)
WHERE rn > 2

Nope. And the trick of manually patching ID columns from the previous example doesn’t work, because you have multiple ID instances. And renaming the columns to random values is nasty, because the user of your home-grown in-house database framework wants to receive well-defined column names. I.e. ID and… ID.

So, the solution is to rename the columns twice. Once in each derived table:

-- Oracle equivalent:
-- Rename synthetic column names back to original
SELECT c1 ID, c2 ID
FROM (
  SELECT b.c1, b.c2, ROWNUM rn
  FROM (
    -- synthetic column names here
    SELECT BOOK.ID c1, AUTHOR.ID c2
    FROM BOOK
    JOIN AUTHOR
    ON BOOK.AUTHOR_ID = AUTHOR.ID
  ) b
  WHERE ROWNUM <= 3
)
WHERE rn > 2

But now, we’re done?

Of course not! What if you doubly nest such a query? Will you think about doubly renaming ID columns to synthetic names, and back? … ;-) Let’s leave it here and talk about something entirely different:

Does the same thing work for SQL Server 2008?

Of course not! In SQL Server 2008, the most popular approach is to use window functions. Namely, ROW_NUMBER(). So, let’s consider:

-- PostgreSQL syntax:
SELECT ID, TITLE 
FROM BOOK 
LIMIT 1 OFFSET 2

-- SQL Server equivalent:
SELECT b.*
FROM (
  SELECT ID, TITLE, 
    ROW_NUMBER() OVER (ORDER BY ID) rn
  FROM BOOK
) b
WHERE rn > 2 AND rn <= 3

So that’s it, right?

Of course not! ;-)

OK, we’ve already had this issue. We should not select *, because that would generate too many columns in the case that we’re using this as a subquery for an IN predicate. So let’s consider the correct solution with synthetic column names:

-- SQL Server equivalent:
SELECT b.c1 ID, b.c2 TITLE
FROM (
  SELECT ID c1, TITLE c2,
    ROW_NUMBER() OVER (ORDER BY ID) rn
  FROM BOOK
) b
WHERE rn > 2 AND rn <= 3

But now we got it, right?

Make an educated guess: Nope!

What happens, if you add an ORDER BY clause to the original query?

-- PostgreSQL syntax:
SELECT ID, TITLE 
FROM BOOK 
ORDER BY SOME_COLUMN
LIMIT 1 OFFSET 2

-- Naive SQL Server equivalent:
SELECT b.c1 ID, b.c2 TITLE
FROM (
  SELECT ID c1, TITLE c2,
    ROW_NUMBER() OVER (ORDER BY ID) rn
  FROM BOOK
  ORDER BY SOME_COLUMN
) b
WHERE rn > 2 AND rn <= 3

Now, that doesn’t work in SQL Server. Subqueries are not allowed to have an ORDER BY clause, unless they also have a TOP clause (or an OFFSET .. FETCH clause in SQL Server 2012).

OK, we can probably tweak this using TOP 100 PERCENT to make SQL Server happy.

-- Better SQL Server equivalent:
SELECT b.c1 ID, b.c2 TITLE
FROM (
  SELECT TOP 100 PERCENT
    ID c1, TITLE c2,
    ROW_NUMBER() OVER (ORDER BY ID) rn
  FROM BOOK
  ORDER BY SOME_COLUMN
) b
WHERE rn > 2 AND rn <= 3

Now, that’s correct SQL according to SQL Server, although you do not have a guarantee that the ordering of the derived table will survive after query execution. It may well be that the ordering is changed again by some influence.

If you wanted to order by SOME_COLUMN in the outer query, you’d have to again transform the SQL statement to add another synthetic column:

-- Better SQL Server equivalent:
SELECT b.c1 ID, b.c2 TITLE
FROM (
  SELECT TOP 100 PERCENT
    ID c1, TITLE c2,
    SOME_COLUMN c99,
    ROW_NUMBER() OVER (ORDER BY ID) rn
  FROM BOOK
) b
WHERE rn > 2 AND rn <= 3
ORDER BY b.c99

That does start getting a bit nasty. And let’s guess whether:

This is the correct solution!

Of course not! What if the original query had DISTINCT in it?

-- PostgreSQL syntax:
SELECT DISTINCT AUTHOR_ID
FROM BOOK 
LIMIT 1 OFFSET 2

-- Naive SQL Server equivalent:
SELECT b.c1 AUTHOR_ID
FROM (
  SELECT DISTINCT AUTHOR_ID c1,
    ROW_NUMBER() OVER (ORDER BY AUTHOR_ID) rn
  FROM BOOK
) b
WHERE rn > 2 AND rn <= 3

Now, what happens if an author has written several books? Yes, the DISTINCT keyword should remove such duplicates, and effectively, the PostgreSQL query will correctly remove duplicates first, and then apply LIMIT and OFFSET.

However, the ROW_NUMBER() predicate always generates distinct row numbers before DISTINCT can remove them again. In other words, DISTINCT has no effect.

Luckily, we can tweak this SQL again, using this neat little trick:

-- Better SQL Server equivalent:
SELECT b.c1 AUTHOR_ID
FROM (
  SELECT DISTINCT AUTHOR_ID c1,
    DENSE_RANK() OVER (ORDER BY AUTHOR_ID) rn
  FROM BOOK
) b
WHERE rn > 2 AND rn <= 3

Read more about this trick here:

SQL Trick: row_number() is to SELECT what dense_rank() is to SELECT DISTINCT.

Watch out that the ORDER BY clause must contain all columns from the SELECT field list. Obviously, this will limit the acceptable columns in the SELECT DISTINCT field list to columns that are allowed in a window function’s ORDER BY clause (e.g. no other window functions).

We could of course try to fix that as well using common table expressions, or we consider

Yet another issue??

Yes, of course!

Do you even know what the column(s) in the window function’s ORDER BY clause should be? Have you just picked any column, at random? What if that column doesn’t have an index on it, will your window function still perform?

The answer is easy when your original SELECT statement also has an ORDER BY clause, then you should probably take that one (plus all the columns from the SELECT DISTINCT clause if applicable).

But what if you don’t have any ORDER BY clause?

Yet another trick! Use a “constant” variable:

-- Better SQL Server equivalent:
SELECT b.c1 AUTHOR_ID
FROM (
  SELECT AUTHOR_ID c1,
    ROW_NUMBER() OVER (ORDER BY @@version) rn
  FROM BOOK
) b
WHERE rn > 2 AND rn <= 3

Yes, you need to use a variable, because constants are not allowed in those ORDER BY clauses, in SQL Server. Painful, I know.

Read more about this @@version trick here.

Are we done yet!?!?

Probably not ;-) But we have probably covered around 99% of the common and edge cases. We can sleep nicely, now.

Note that all of these SQL transformations are implemented in jOOQ. jOOQ is the only SQL abstraction framework that takes SQL seriously (with all its warts and caveats), standardising over all of this madness.

As mentioned in the beginning, with jOOQ, you just write:

// Don't worry about general emulation
select().from(BOOK).limit(1).offset(2);

// Don't worry about duplicate column names
// in subselects
select(BOOK.ID, AUTHOR.ID)
.from(BOOK)
.join(AUTHOR)
.on(BOOK.AUTHOR_ID.eq(AUTHOR.ID))
.limit(1).offset(2);

// Don't worry about invalid IN predicates
select()
.from(BOOK)
.where(BOOK.AUTHOR_ID).in(
    select(AUTHOR.ID)
    .from(AUTHOR)
    .limit(1).offset(2)
);

// Don't worry about the ROW_NUMBER() vs.
// DENSE_RANK() distinction
selectDistinct(AUTHOR_ID)
    .from(BOOK).limit(1).offset(2);

With jOOQ, you can just write your Oracle SQL or Transact SQL as if it were as awesome as PostgreSQL! … without jumping the SQL ship entirely, and moving on to JPA.

jOOQ, the best way to write SQL in Java

Keyset paging

Now, of course, if you have been reading our blog, or our partner blog SQL Performance Explained, you should know by now that OFFSET pagination is often a bad choice in the first place. You should know that keyset pagination almost always outperforms OFFSET pagination.

Read about how jOOQ natively supports keyset pagination using the SEEK clause, here.

Java 8 Friday: JavaScript goes SQL with Nashorn and jOOQ


At Data Geekery, we love Java. And as we’re really into jOOQ’s fluent API and query DSL, we’re absolutely thrilled about what Java 8 will bring to our ecosystem.

Java 8 Friday

Every Friday, we’re showing you a couple of nice new tutorial-style Java 8 features, which take advantage of lambda expressions, extension methods, and other great stuff. You’ll find the source code on GitHub.

JavaScript goes SQL with Nashorn and jOOQ

This week, we’ll look into some awesome serverside SQL scripting with Nashorn and Java 8. Only few things can be found on the web regarding the use of JDBC in Nashorn. But why use JDBC and take care of painful resource management and SQL string composition, when you can use jOOQ? Everything works out of the box!

Let’s set up a little sample JavaScript file as such:

var someDatabaseFun = function() {
    var Properties = Java.type("java.util.Properties");
    var Driver = Java.type("org.h2.Driver");

    var driver = new Driver();
    var properties = new Properties();

    properties.setProperty("user", "sa");
    properties.setProperty("password", "");

    try {
        var conn = driver.connect(
            "jdbc:h2:~/test", properties);

        // Database code here
    }
    finally {
        try { 
            if (conn) conn.close();
        } catch (e) {}
    }
}

someDatabaseFun();

This is pretty much all you need to interoperate with JDBC and a H2 database. So we could be running SQL statements with JDBC like so:

try {
    var stmt = conn.prepareStatement(
        "select table_schema, table_name " + 
        "from information_schema.tables");
    var rs = stmt.executeQuery();

    while (rs.next()) {
        print(rs.getString("TABLE_SCHEMA") + "."
            + rs.getString("TABLE_NAME"))
    }
}
finally {
    if (rs)
        try {
            rs.close();
        }
        catch(e) {}

    if (stmt)
        try {
            stmt.close();
        }
        catch(e) {}
}

Most of the bloat is JDBC resource handling as we unfortunately don’t have a try-with-resources statement in JavaScript. The above generates the following output:

INFORMATION_SCHEMA.FUNCTION_COLUMNS
INFORMATION_SCHEMA.CONSTANTS
INFORMATION_SCHEMA.SEQUENCES
INFORMATION_SCHEMA.RIGHTS
INFORMATION_SCHEMA.TRIGGERS
INFORMATION_SCHEMA.CATALOGS
INFORMATION_SCHEMA.CROSS_REFERENCES
INFORMATION_SCHEMA.SETTINGS
INFORMATION_SCHEMA.FUNCTION_ALIASES
INFORMATION_SCHEMA.VIEWS
INFORMATION_SCHEMA.TYPE_INFO
INFORMATION_SCHEMA.CONSTRAINTS
...

Let’s see if we can run the same query using jOOQ:

var DSL = Java.type("org.jooq.impl.DSL");

print(
    DSL.using(conn)
       .fetch("select table_schema, table_name " +
              "from information_schema.tables")
);

This is how you can execute plain SQL statements in jOOQ, with much less bloat than with JDBC. The output is roughly the same:

+------------------+--------------------+
|TABLE_SCHEMA      |TABLE_NAME          |
+------------------+--------------------+
|INFORMATION_SCHEMA|FUNCTION_COLUMNS    |
|INFORMATION_SCHEMA|CONSTANTS           |
|INFORMATION_SCHEMA|SEQUENCES           |
|INFORMATION_SCHEMA|RIGHTS              |
|INFORMATION_SCHEMA|TRIGGERS            |
|INFORMATION_SCHEMA|CATALOGS            |
|INFORMATION_SCHEMA|CROSS_REFERENCES    |
|INFORMATION_SCHEMA|SETTINGS            |
|INFORMATION_SCHEMA|FUNCTION_ALIASES    |
 ...

But jOOQ’s strength is not in its plain SQL capabilities, it lies in the DSL API, which abstracts away all the vendor-specific SQL subtleties and allows you to compose queries (and also DML) fluently. Consider the following SQL statement:

// Let's assume these objects were generated
// by the jOOQ source code generator
var Tables = Java.type(
    "org.jooq.db.h2.information_schema.Tables");
var t = Tables.TABLES;
var c = Tables.COLUMNS;

// This is the equivalent of Java's static imports
var count = DSL.count;
var row = DSL.row;

// We can now execute the following query:
print(
    DSL.using(conn)
       .select(
           t.TABLE_SCHEMA, 
           t.TABLE_NAME, 
           c.COLUMN_NAME)
       .from(t)
       .join(c)
       .on(row(t.TABLE_SCHEMA, t.TABLE_NAME)
           .eq(c.TABLE_SCHEMA, c.TABLE_NAME))
       .orderBy(
           t.TABLE_SCHEMA.asc(),
           t.TABLE_NAME.asc(),
           c.ORDINAL_POSITION.asc())
       .fetch()
);

Note that there is obviously no typesafety in the above query, as this is JavaScript. But I would imagine that the IntelliJ, Eclipse, or NetBeans creators will eventually detect Nashorn dependencies on Java programs, and provide syntax auto-completion and highlighting, as some things can be statically analysed.

Things get even better if you’re using the Java 8 Streams API from Nashorn. Let’s consider the following query:

DSL.using(conn)
   .select(
       t.TABLE_SCHEMA,
       t.TABLE_NAME,
       count().as("CNT"))
   .from(t)
   .join(c)
   .on(row(t.TABLE_SCHEMA, t.TABLE_NAME)
       .eq(c.TABLE_SCHEMA, c.TABLE_NAME))
   .groupBy(t.TABLE_SCHEMA, t.TABLE_NAME)
   .orderBy(
       t.TABLE_SCHEMA.asc(),
       t.TABLE_NAME.asc())

// This fetches a List<Map<String, Object>> as
// your ResultSet representation
   .fetchMaps()

// This is Java 8's standard Collection.stream()
   .stream()

// And now, r is like any other JavaScript object
// or record!
   .forEach(function (r) {
       print(r.TABLE_SCHEMA + '.' 
           + r.TABLE_NAME + ' has ' 
           + r.CNT + ' columns.');
   });

The above generates this output:

INFORMATION_SCHEMA.CATALOGS has 1 columns.
INFORMATION_SCHEMA.COLLATIONS has 2 columns.
INFORMATION_SCHEMA.COLUMNS has 23 columns.
INFORMATION_SCHEMA.COLUMN_PRIVILEGES has 8 columns.
INFORMATION_SCHEMA.CONSTANTS has 7 columns.
INFORMATION_SCHEMA.CONSTRAINTS has 13 columns.
INFORMATION_SCHEMA.CROSS_REFERENCES has 14 columns.
INFORMATION_SCHEMA.DOMAINS has 14 columns.
...

If your database supports arrays, you can even access such array columns by index, e.g.

r.COLUMN_NAME[3]

So, if you’re a server-side JavaScript aficionado, download jOOQ today, and start writing awesome SQL in JavaScript, now! For more Nashorn awesomeness, consider reading this article here.

Stay tuned for more awesome Java 8 content on this blog.

QueryDSL vs. jOOQ. Feature Completeness vs. Now More Than Ever


This week, Timo Westkämper from QueryDSL has announced feature completeness on the QueryDSL user group, along with his call for contributions and increased focus on bugfixes and documentation.

Timo and us, we have always been in close contact, observing each other’s products. In the beginning of jOOQ in 2009, QueryDSL was ahead of us.

But we learned quickly and removed all of our shortcomings such that jOOQ and QueryDSL were quickly at eye level by 2011. Ever since, we have been taking inspiration from one another, as in the end, we have had similar goals. Today, whenever someone is looking for a querying DSL, people tend to recommend either of our tools:

QueryDSL is often a good choice in JPA-based environments, while jOOQ is mostly the best choice in SQL-based environments, although jOOQ has already been given some credit in JPA-based environments as well:

Anyway, today, we’d like to congratulate Timo to his new job, and to QueryDSL’s feature completeness.

jOOQ, on the other hand, is far from feature complete.

jOOQ is what SQLJ should have been from the beginning.

We’re only at the beginning. Java and SQL are the two platforms that are used by most of the developers on this planet. According to db-engines.com, almost every popular DBMS is a SQL-based relational DBMS. According to TIOBE, Java currently ranks #2 among all languages.

We strongly believe that all of these developers are in dire need for better SQL integration into the Java language. While ORMs and JPA are very well integrated, SQL is not, and that is what we are working on. jOOQ will be feature complete when the Java compiler can natively compile actual SQL code and SQL code fragments into jOOQ, which will serve as its backing AST model for further SQL transformation.

Until we reach that goal, we’ll be adding support for more SQL goodness. A small selection of things that we already support, beyond QueryDSL’s “feature completeness”:

  • Table-valued functions
  • PIVOT tables
  • DDL (with jOOQ 3.4)
  • MERGE statement
  • Derived tables and derived column lists
  • Row value expressions
  • Flashback query
  • Window functions
  • Ordered aggregate functions
  • Common table expressions (with jOOQ 3.4)
  • Object-oriented PL/SQL
  • User-defined types
  • Hierarchical SQL
  • Custom SQL transformation
  • 16 supported RDBMS (even MS Access!)
  • … you name it

Our roadmap is full of great ideas. There’s plenty of work, so let’s get going! Join us, your partner for…

jOOQ is the best way to write SQL in Java

jOOQ Newsletter: May 21, 2014 – jOOQ Community Video Tutorials


Subscribe to this newsletter here

Tweet of the Day

Our customers, users, and followers are sharing their love for jOOQ with the world. Here are:

Chris Martin who has switched jobs and is now really missing jOOQ. Too bad we can’t offer jOOQ for Python, Chris!

Moutaz Salem who had been waiting for jOOQ for all these years. But we’re here now, Moutaz. The pain has ended!

Simon Martinelli who is contemplating migration to jOOQ from Hibernate. Go for it Simon!

Thanks for the shouts, guys! You make the jOOQ experience rock!

Closing in on jOOQ 3.4

We’re almost there! In early June, we’ll ship the awesome jOOQ 3.4 – a very ambitious project in which we’re finally going to tackle a couple of very important missing features. The ones that have made it through our roadmap optimisations are:

  • Support for type safe DDL statements.
  • Support for common table expressions.
  • A unified SQL transformation API.

SQL transformation can be very effective for dialect standardisation, multi-tenancy, soft-deleting, row-level security and many more features. Have you used it yet? You should!

Better SQL transformation APIs will make your VisitListeners even more powerful, and custom QueryParts even easier to implement. If you’re a hard-core SQL transformation aficionado, this is great news for you as you can exert even more control over jOOQ’s SQL rendering.

In an earlier newsletter, we had promised to implement Informix support, but we had to optimise our roadmap to ensure the above gets shipped with the usual quality that we’re offering to our customers. We’re very sorry if that has affected you. We’ll try to have this in jOOQ 3.5, though.

Of course, there are lots of other minor improvements, so stay tuned for the release, very soon

Community Zone – The jOOQ aficionados have been active!

In previous newsletters, we’ve advertised jOOQ community blog post contributions, which are a really awesome way to show your love – thank you very much, again!

In the previous weeks, however, we’ve discovered these two video contributions:

A jOOQ video tutorial by Ruben Alexander More Valencia (Spanish)

A jOOQ and Flyway presentation by Dmitry Lebedev and Rustam Arslanov (Latvian)

This is really great, thank you so much, guys! If you want to present jOOQ to your local JUG, do so today! If you need any brand material, example slides, example projects, or other material, please do contact us. We’re more than happy to provide you with what you need.

SQL Zone – Sort indirection

Every now and then, you may want to sort your records in a bit of a “quirky” way, i.e. by explicitly putting 1-2 values at the top and all the other values below. We refer to this as “sort indirection”, which can be achieved with a clever ORDER BY clause along with a CASE expression:

ORDER BY
  CASE WHEN name = 'val1' THEN 1
       WHEN name = 'val2' THEN 2
       WHEN name = 'val3' THEN 3
       WHEN name = 'val4' THEN 4
  END

Of course, jOOQ natively implements a couple of convenience methods to achieve the same.

Read the full article here:
http://blog.jooq.org/2014/05/07/how-to-implement-sort-indirection-in-sql/

SQL Zone – Identifier madness

We’ve said it before. We’ll say it again.

You should NEVER rely on case-sensitivity (or case-insensitivity) when writing vendor-agnostic SQL. It is hard enough to remember the rules in one single database, but if you ship your software to support various RDBMS, always force your identifiers to be case-sensitive (and settle for either all-uppercase or all-lowercase).

Fortunately, jOOQ takes care of case-sensitivity by default, quoting all your identifier as they were reported from the database.

You want to know why this is important? Consider reading this article where the various caveats and differences between identifier parts are explained and compared between databases:
http://wiki.ispirer.com/sqlways/sybase/identifiers

Upcoming Events

GeeCON in Kraków is already over. Great conference and many great speakers!

As always in recent conferences, topics mainly included JavaScript, Akka, Node.js and Vert.x and other hip subjects. But we’re always amazed to see how big of a crowd can still be motivated into a “plain old SQL” talk like ours. SQL (and the latest standards) is still very awesome, and every time we explain the merits of window functions, we see hundreds of wide-open eyes, with awe and revelation.

;-)

Have you missed any of our previous jOOQ talks? Soon you’ll get another chance to hear us talk about jOOQ or SQL in general in any of these upcoming events:

Stay informed about 2014 events on www.jooq.org/news.

Java 8 Friday: API Designers, be Careful


At Data Geekery, we love Java. And as we’re really into jOOQ’s fluent API and query DSL, we’re absolutely thrilled about what Java 8 will bring to our ecosystem.

Java 8 Friday

Every Friday, we’re showing you a couple of nice new tutorial-style Java 8 features, which take advantage of lambda expressions, extension methods, and other great stuff. You’ll find the source code on GitHub.

Lean Functional API Design

With Java 8, API design has gotten a whole lot more interesting, but also a bit harder. As a successful API designer, it will no longer suffice to think about all sorts of object-oriented aspects of your API, you will now also need to consider functional aspects of it. In other words, instead of simply providing methods like:

void performAction(Parameter parameter);

// Call the above:
object.performAction(new Parameter(...));

… you should now think about whether your method arguments are better modelled as functions for lazy evaluation:

// Keep the existing method for convenience
// and for backwards compatibility
void performAction(Parameter parameter);

// Overload the existing method with the new
// functional one:
void performAction(Supplier<Parameter> parameter);

// Call the above:
object.performAction(() -> new Parameter(...));

This is great. Your API can be Java-8 ready even before you’re actually targeting Java 8. But if you’re going this way, there are a couple of things to consider.

JDK dependency

The above example makes use of the JDK 8 Supplier type. This type is not available before the JDK 8, so if you’re using it, you’re going to limit your APIs use to the JDK 8. If you want to continue supporting older Java versions, you’ll have to roll your own supplier, or maybe use Callable, which has been available since Java 5:

// Overload the existing method with the new
// functional one:
void performAction(Callable<Parameter> parameter);

// Call the above:
object.performAction(() -> new Parameter(...));

One advantage of using Callable is the fact that your lambda expressions (or “classic” Callable implementations, or nested / inner classes) are allowed to throw checked exceptions. We’ve blogged about another possibility to circumvent this limitation, here.

Overloading

While it is (probably) perfectly fine to overload these two methods

void performAction(Parameter parameter);
void performAction(Supplier<Parameter> parameter);

… you should stay wary when overloading “more similar” methods, like these ones:

void performAction(Supplier<Parameter> parameter);
void performAction(Callable<Parameter> parameter);

If you produce the above API, your API’s client code will not be able to make use of lambda expressions, as there is no way of disambiguating a lambda that is a Supplier from a lambda that is a Callable. We’ve also mentioned this in a previous blog post.

“void-compatible” vs “value-compatible”

I’ve recently (re-)discovered this interesting early JDK 8 compiler bug, where the compiler wasn’t able to disambiguate the following:

void run(Consumer<Integer> consumer);
void run(Function<Integer, Integer> function);

// Remember, the above types are roughly:
interface Consumer<T> {
    void accept(T t);
//  ^^^^ void-compatible
}

interface Function<T, R> {
    R apply(T t);
//  ^ value-compatible
}

The terms “void-compatible” and “value-compatible” are defined in the JLS §15.27.2 for lambda expressions. According to the JLS, the following two calls are not ambiguous:

// Only run(Consumer) is applicable
run(i -> {});

// Only run(Function) is applicable
run(i -> 1);

In other words, it is safe to overload a method to take two “similar” argument types, such as Consumer and Function, as lambda expressions used to express method arguments will not be ambiguous.

This is quite useful, because having an optional return value is very elegant when you’re using lambda expressions. Consider the upcoming jOOQ 3.4 transaction API, which is roughly summarised as such:


// This uses a "void-compatible" lambda
ctx.transaction(c -> {
    DSL.using(c).insertInto(...).execute();
    DSL.using(c).update(...).execute();
});

// This uses a "value-compatible" lambda
Integer result =
ctx.transaction(c -> {
    DSL.using(c).update(...).execute();
    DSL.using(c).delete(...).execute();

    return 42;
});

In the above example, the first call resolves to TransactionalRunnable whereas the second call resolves to TransactionalCallable whose API are like these:

interface TransactionalRunnable {
    void run(Configuration c) throws Exception;
}

interface TransactionalCallable<T> {
    T run(Configuration c) throws Exception;
}

Note, though, that as of JDK 1.8.0_05 and Eclipse Kepler (with the Java 8 support patch), this ambiguity resolution does not yet work because of these bugs:

So, in order to stay on the safe side, maybe you could just simply avoid overloading.

Generic methods are not SAMs

Do note that “SAM” interfaces that contain a single abstract generic method are NOT SAMs in the sense for them to be eligible as lambda expression targets. The following type will never form any lambda expression:

interface NotASAM {
    <T> void run(T t);
}

This is specified in the JLS §15.27.3

A lambda expression is congruent with a function type if all of the following are true:

  • The function type has no type parameters.
  • [ ... ]

What do you have to do now?

If you’re an API designer, you should now start writing unit tests / integration tests also in Java 8. Why? For the simple reason that if you don’t you’ll get your API wrong in subtle ways for those users that are actually using it with Java 8. These things are extremely subtle. Getting them right takes a bit of practice and a lot of regression tests. Do you think you’d like to overload a method? Be sure you don’t break client API that is calling the original method with a lambda.

That’s it for today. Stay tuned for more awesome Java 8 content on this blog.

Follow

Get every new post delivered to your Inbox.

Join 1,728 other followers

%d bloggers like this: