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)
)
Like this:
Like Loading...
Published by lukaseder
I made jOOQ
View all posts by lukaseder