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)
)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s