How to FlatMap a JDBC ResultSet with Java 8?

You’re not into the functional mood yet? Then the title might not resonate with you – but the article will! Trust me.

Essentially, we want this:

+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
| A    | B    | C    | row 1
| D    | E    | F    | row 2
| G    | H    | I    | row 3
+------+------+------+

to be “flat mapped” into this:

+------+
| cols |
+------+
| A    |\ 
| B    | | row 1
| C    |/
| D    |\
| E    | | row 2
| F    |/
| G    |\
| H    | | row 3
| I    |/
+------+

How to do it with Java 8?

It’s easy, when you’re using jOOQ. Let’s create the database first:

CREATE TABLE t (
  col1 VARCHAR2(1),
  col2 VARCHAR2(1),
  col3 VARCHAR2(1)
);

INSERT INTO t VALUES ('A', 'B', 'C');
INSERT INTO t VALUES ('D', 'E', 'F');
INSERT INTO t VALUES ('G', 'H', 'I');

Now let’s add some jOOQ and Java 8!

List<String> list =
DSL.using(connection)
   .fetch("SELECT col1, col2, col3 FROM t")
   .stream()
   .flatMap(r -> Arrays.stream(r.into(String[].class)))
   .collect(Collectors.toList());

System.out.println(list);

… and that’s it! The output is:

[A, B, C, D, E, F, G, H, I]

(I’ve also given this solution to this Stack Overflow question)

How do you read the above? Simply like this:

List<String> list =

// Get a Result<Record>, which is essentially a List
// from the database query
DSL.using(connection)
   .fetch("SELECT col1, col2, col3 FROM t")

// Stream its records
   .stream()

// And generate a new stream of each record's String[]
// representation, "flat mapping" that again into a
// single stream
   .flatMap(r -> Arrays.stream(r.into(String[].class)))
   .collect(Collectors.toList());

Note that if you’re not using jOOQ to render and execute your query, you can still use jOOQ to transform the JDBC ResultSet into a jOOQ Result to produce the same output:

try (ResultSet rs = ...) {
    List<String> list =
    DSL.using(connection)
       .fetch(rs) // unwind the ResultSet here
       .stream()
       .flatMap(r -> Arrays.stream(r.into(String[].class)))
       .collect(Collectors.toList());

    System.out.println(list);
}

Bonus: The SQL way

The SQL way to produce the same result is trivial:

SELECT col1 FROM t UNION ALL
SELECT col2 FROM t UNION ALL
SELECT col3 FROM t
ORDER BY 1

Or, of course, if you’re using Oracle or SQL Server, you can use the magic UNPIVOT clause (the opposite of the PIVOT clause):

SELECT c
FROM t
UNPIVOT (
  c FOR col in (col1, col2, col3)
)

How to Avoid the Dreaded Dead Lock when Pessimistic Locking – And some Awesome Java 8 Usage!

Sometimes you simply cannot avoid it: Pessimistic locking via SQL. In fact, it’s an awesome tool when you want to synchronise several applications on a shared, global lock.

Some may think this is abusing the database. We think use the tools you have if they can solve the problem you have. For instance, the RDBMS can be the perfect implementation for a message queue.

Let’s assume you do have that pessimistic locking use-case and you do want to choose the RDBMS. Now, how to get it right? Because it is really easy to produce a deadlock. Imagine the following setup (and I’m using Oracle for this):

CREATE TABLE locks (v NUMBER(18));

INSERT INTO locks
SELECT level
FROM dual
CONNECT BY level <= 10;

This generates 10 records, which we’ll use as 10 distinct row-level locks.

Now, let’s connect to the database from two sqlplus clients:

Instance 1

SQL> SELECT *
  2  FROM locks
  3  WHERE v = 1
  4  FOR UPDATE;

         V
----------
         1

Instance 2

SQL> SELECT *
  2  FROM locks
  3  WHERE v = 2
  4  FOR UPDATE;

         V
----------
         2

We’ve now acquired two different locks from two different sessions.

And then, let’s inverse things:

Instance 1

SQL> SELECT *
  2  FROM locks
  3  WHERE v = 2
  4  FOR UPDATE;

Instance 2

SQL> SELECT *
  2  FROM locks
  3  WHERE v = 1
  4  FOR UPDATE;

Both sessions are now locked and luckily, Oracle will detect this and fail one of the sessions:

ORA-00060: deadlock detected while waiting for resource

Avoiding deadlocks

This is a very explicit example where it is easy to see why it happens, and potentially, how to avoid it. A simple way to avoid deadlocks is to establish a rule that all locks will always have to be acquired in ascending order. If you know you need lock number 1 and 2, you must acquire them in that order. This way, you will still produce locking and thus contention, but at least the contention will eventually (probably) get resolved once load decreases. Here’s an example that shows what happens when you have more clients. This time, written as Java threads.

In the example, we’re using jOOλ for simpler lambda expressions (e.g. lambdas throwing checked exceptions). And of course, we’ll be abusing Java 8, heavily!

Class.forName("oracle.jdbc.OracleDriver");

// We want a collection of 4 threads and their
// associated execution counters
List<Tuple2<Thread, AtomicLong>> list =
IntStream
    .range(0, 4)

    // Let's use jOOλ here to wrap checked exceptions
    // we'll map the thread index to the actual tuple
    .mapToObj(Unchecked.intFunction(i -> {
        final Connection con = DriverManager.getConnection(
            "jdbc:oracle:thin:@localhost:1521:xe", 
            "TEST", "TEST");

        final AtomicLong counter = new AtomicLong();
        final Random rnd = new Random();

        return Tuple.tuple(

            // Each thread acquires a random number of
            // locks in ascending order
            new Thread(Unchecked.runnable(() -> {
                for (;;) {
                    String sql =
                      " SELECT *"
                    + " FROM locks"
                    + " WHERE v BETWEEN ? AND ?"
                    + " ORDER BY v"
                    + " FOR UPDATE";

                    try (PreparedStatement stmt = 
                             con.prepareStatement(sql)) {
                        stmt.setInt(1, rnd.nextInt(10));
                        stmt.setInt(2, rnd.nextInt(10));
                        stmt.executeUpdate();

                        counter.incrementAndGet();
                        con.commit();
                    }
                }
            })),
            counter
        );
    }))
    .collect(Collectors.toList());

// Starting each thread
list.forEach(tuple -> tuple.v1.start());

// Printing execution counts
for (;;) {
    list.forEach(tuple -> {
        System.out.print(String.format(
            "%1s:%2$-10s",
            tuple.v1.getName(),
            tuple.v2.get()
        ));
    });

    System.out.println();
    Thread.sleep(1000);
}

As the program runs, you can see that it continues progressively, with each thread taking approximately the same load as the other threads:

Thread-1:0         Thread-2:0         Thread-3:0         Thread-4:0
Thread-1:941       Thread-2:966       Thread-3:978       Thread-4:979
Thread-1:2215      Thread-2:2206      Thread-3:2244      Thread-4:2253
Thread-1:3422      Thread-2:3400      Thread-3:3466      Thread-4:3418
Thread-1:4756      Thread-2:4720      Thread-3:4855      Thread-4:4847
Thread-1:6095      Thread-2:5987      Thread-3:6250      Thread-4:6173
Thread-1:7537      Thread-2:7377      Thread-3:7644      Thread-4:7503
Thread-1:9122      Thread-2:8884      Thread-3:9176      Thread-4:9155

Now, for the sake of the argument, let’s do the forbidden thing and ORDER BY DBMS_RANDOM.VALUE

String sql =
  " SELECT *"
+ " FROM locks"
+ " WHERE v BETWEEN ? AND ?"
+ " ORDER BY DBMS_RANDOM.VALUE"
+ " FOR UPDATE";

It won’t take long and your application explodes:

Thread-1:0         Thread-2:0         Thread-3:0         Thread-4:0         
Thread-1:72        Thread-2:79        Thread-3:79        Thread-4:90        
Thread-1:72        Thread-2:79        Thread-3:79        Thread-4:90        
Thread-1:72        Thread-2:79        Thread-3:79        Thread-4:90        
Exception in thread "Thread-3" org.jooq.lambda.UncheckedException: 
java.sql.SQLException: ORA-00060: deadlock detected while waiting for resource

Thread-1:72        Thread-2:79        Thread-3:79        Thread-4:93        
Thread-1:72        Thread-2:79        Thread-3:79        Thread-4:93        
Thread-1:72        Thread-2:79        Thread-3:79        Thread-4:93        
Exception in thread "Thread-1" org.jooq.lambda.UncheckedException: 
java.sql.SQLException: ORA-00060: deadlock detected while waiting for resource

Thread-1:72        Thread-2:1268      Thread-3:79        Thread-4:1330      
Thread-1:72        Thread-2:3332      Thread-3:79        Thread-4:3455      
Thread-1:72        Thread-2:5691      Thread-3:79        Thread-4:5841      
Thread-1:72        Thread-2:8663      Thread-3:79        Thread-4:8811      
Thread-1:72        Thread-2:11307     Thread-3:79        Thread-4:11426     
Thread-1:72        Thread-2:12231     Thread-3:79        Thread-4:12348     
Thread-1:72        Thread-2:12231     Thread-3:79        Thread-4:12348     
Thread-1:72        Thread-2:12231     Thread-3:79        Thread-4:12348     
Exception in thread "Thread-4" org.jooq.lambda.UncheckedException: 
java.sql.SQLException: ORA-00060: deadlock detected while waiting for resource

Thread-1:72        Thread-2:13888     Thread-3:79        Thread-4:12348     
Thread-1:72        Thread-2:17037     Thread-3:79        Thread-4:12348     
Thread-1:72        Thread-2:20234     Thread-3:79        Thread-4:12348     
Thread-1:72        Thread-2:23495     Thread-3:79        Thread-4:12348     

And in the end, all but one of your threads have been killed (at least in our example) because of deadlock exceptions.

Beware of execution plans

The above example has worked, because in the given example, the execution plan applied locking AFTER ordering as can be seen here:

SQL_ID  bcyyxqyubp4v8, child number 0
-------------------------------------
SELECT * FROM locks WHERE v BETWEEN :v1 AND :v2 ORDER BY v FOR UPDATE
 
Plan hash value: 2944215640
 
--------------------------------------
| Id  | Operation            | Name  |
--------------------------------------
|   0 | SELECT STATEMENT     |       |
|   1 |  FOR UPDATE          |       |
|   2 |   SORT ORDER BY      |       | <-- happens before FOR UPDATE
|*  3 |    FILTER            |       |
|*  4 |     TABLE ACCESS FULL| LOCKS |
--------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - filter(TO_NUMBER(:V1)<=TO_NUMBER(:V2))
   4 - filter(("V"=TO_NUMBER(:V1)))

(see this article to learn how to get Oracle execution plans like the above)

You should obviously not rely on this in a more real world scenario.

Beware of contention, though

The above examples have also been impressive in terms of displaying the other negative side-effects of pessimistic locking (or locking in general): Contention. The single thread that continued executing in the “bad example” was almost as fast as the four threads before. Our silly example where we used random lock ranges led to the fact that on average, almost every attempt to acquire locks did at least some blocking. How can you figure this out? By looking out for enq: TX – row lock contention events in your sessions. For instance:

SELECT blocking_session, event
FROM v$session
WHERE username = 'TEST'

The above query returns the catastrophic result, here:

BLOCKING_SESSION   EVENT
-------------------------------------
48                 enq: TX - row lock contention
54                 enq: TX - row lock contention
11                 enq: TX - row lock contention
11                 enq: TX - row lock contention

Conclusion

The conclusion can only be: Use pessimistic locking sparingly and always expect the unexpected. When doing pessimistic locking, both deadlocks and heavy contention are quite possible problems that you can run into. As a general rule of thumb, follow these rules (in order):

  • Avoid pessimistic locking if you can
  • Avoid locking more than one row per session if you can
  • Avoid locking rows in random order if you can
  • Avoid going to work to see what happened

How to Use Java 8 Streams to Swiftly Replace Elements in a List

Imagine you have a list of items:

List<String> books = Arrays.asList(
    "The Holy Cow: The Bovine Testament",
    "True Hip Hop",
    "Truth and Existence",
    "The Big Book of Green Design"
);

(Don’t judge me. Books from this random book generator)

Now you’d like to create a new list where the third item only is replaced by some new value:

List<String> books = Arrays.asList(
    "The Holy Cow: The Bovine Testament",
    "True Hip Hop",
    "Pregnancy For Dummies", // New book at index 2
    "The Big Book of Green Design"
);

Of course, you could go and either modify the original list:

books.set(2, "Pregnancy For Dummies");

… or create a copy of the original list and then modify that copy:

List<String> copy = new ArrayList<>(books);
copy.set(2, "Pregnancy For Dummies");

But if you want to write a one-liner to do the same in a functional style, you’ll write the following, using jOOλ

seq(books)
    .zipWithIndex()
    .map(t -> t.v2 == 2 
            ? "Pregnancy For Dummies"
            : t.v1)
    .toList();

With the JDK standard Streams API, things get a bit harder. You could write:

Stream.concat(
    Stream.concat(
        books.stream().limit(2),
        Stream.of("Pregnancy For Dummies")
    ),
    books.stream.skip(3)
).collect(Collectors.toList());

That would be a bit unfortunate, though, as the first part of the stream would need to be traversed twice – once for the limit and once for the skipping (see also our post about the caveats of OFFSET pagination in SQL)

Swift or not?

Clearly, the JDK APIs won’t help you to write concise functional logic, as can be seen above and the “imperative” style is more straight-forward. We’ve written about this before. This has also been our main motivation to create jOOλ.

If you’re looking for even more functional bliss, do also have a look at the following libraries:

Don’t be Fooled by Generics and Backwards-Compatibility. Use Generic Generic Types

I’ve recently had a very interesting discussion with Sebastian Gruber from Ergon, a very early jOOQ customer, whom we’re in close touch with. Talking to Sebastian has lead our engineering team to the conclusion that we should completely rewrite the jOOQ API. Right now, we already have lots of generics for various purposes, e.g.

  • Generics for column types, such as
    interface Field<T> { ... }
    Field<String> field = BOOK.TITLE;
    
  • Generics for table types, such as
    interface Table<R extends Record> { ... }
    Table<BookRecord> books = BOOK;
    
  • Combined generics where both <T> and <R> are used
  • … and much more

Sometimes, you just cannot anticipate how many different generic types you’ll need on your classes and interfaces two years down the line, and the problem with Java is: You can generify your classes only exactly once. Let’s assume that you’ve always had a type like this:

class Foo {}

Now you happen to know that you need two generic type parameters right now:

// Still compatible
class Foo<Bar, Baz> {}

That’ll work and all the existing client code will still compile, with a rawtype warning. But once you’ve published Foo<Bar, Baz>, you can no longer add more type variables to it, or remove them. Every modification will break client code!

// Breaking change
class Foo<Bar, Baz, Fizz> {}

The solution: Generic generic types

We don’t want to place that burden upon our customers, the heavy burden of backwards-incompatibility. This is why we’re now publishing our next release of jOOQ with a new feature that we call generic generic types. How does it work? It’s easy. We’ve learned from the best database designers who have already been using generic column types all along. In SQL, if you run into this kind of problem, you’d simply write:

CREATE TABLE foo (
    bar int,
    baz int,
    fizz int,

    generic_1 varchar(4000),
    generic_2 varchar(4000),
    generic_3 varchar(4000),
    generic_4 varchar(4000),
    -- [...]
);

Now your SQL schema is safe for ages to come. We’ll do the same in Java:

class Foo<
    Bar, 
    Baz, 
    Fizz,

    Generic1,
    Generic2,
    Generic3,
    Generic4,
    // [...]
> {}

We’ll thus generify all our types to have exactly 256 generic type parameters. 256 was the sensible limit that MS Access chose for the number of possible columns. That way, our customers will only have to upgrade to the new version of jOOQ once and from then on, generic type backwards-compatibility will be guaranteed forever.

Happy coding!