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:
1 2 3 4 5 6 7 | +------+------+------+ | col1 | col2 | col3 | +------+------+------+ | A | B | C | row 1 | D | E | F | row 2 | G | H | I | row 3 +------+------+------+ |
to be “flat mapped” into this:
01 02 03 04 05 06 07 08 09 10 11 12 13 | +------+ | 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:
1 2 3 4 5 6 7 8 9 | 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!
1 2 3 4 5 6 7 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:
1 | [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:
01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 | 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:
01 02 03 04 05 06 07 08 09 10 | 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:
1 2 3 4 | 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):
1 2 3 4 5 | SELECT c FROM t UNPIVOT ( c FOR col in (col1, col2, col3) ) |
Reference: | How to FlatMap a JDBC ResultSet with Java 8? from our JCG partner Lukas Eder at the JAVA, SQL, AND JOOQ blog. |