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) )
| Reference: | How to FlatMap a JDBC ResultSet with Java 8? from our JCG partner Lukas Eder at the JAVA, SQL, AND JOOQ blog. |
