Enterprise Java

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)
)
Do you want to know how to develop your skillset to become a Java Rockstar?
Subscribe to our newsletter to start Rocking right now!
To get you started we give you our best selling eBooks for FREE!
1. JPA Mini Book
2. JVM Troubleshooting Guide
3. JUnit Tutorial for Unit Testing
4. Java Annotations Tutorial
5. Java Interview Questions
6. Spring Interview Questions
7. Android UI Design
and many more ....
I agree to the Terms and Privacy Policy

Lukas Eder

Lukas is a Java and SQL enthusiast developer. He created the Data Geekery GmbH. He is the creator of jOOQ, a comprehensive SQL library for Java, and he is blogging mostly about these three topics: Java, SQL and jOOQ.
Subscribe
Notify of
guest


This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Back to top button