resultset用法
您还不喜欢机能吗? 这样标题可能不会引起您的共鸣-但文章会! 相信我。
本质上,我们想要这样:
+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
| A | B | C | row 1
| D | E | F | row 2
| G | H | I | row 3
+------+------+------+
被“平面映射”到:
+------+
| cols |
+------+
| A |\
| B | | row 1
| C |/
| D |\
| E | | row 2
| F |/
| G |\
| H | | row 3
| I |/
+------+
如何使用Java 8?
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');
现在,让我们添加一些jOOQ和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);
…就这样! 输出为:
[A, B, C, D, E, F, G, H, I]
您如何阅读以上内容? 就像这样:
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());
请注意,如果您不使用jOOQ来呈现和执行查询,则仍然可以使用jOOQ将JDBC ResultSet
转换为jOOQ Result
来产生相同的输出:
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);
}
奖励:SQL方式
产生相同结果SQL方法很简单:
SELECT col1 FROM t UNION ALL
SELECT col2 FROM t UNION ALL
SELECT col3 FROM t
ORDER BY 1
或者,当然,如果您使用的是Oracle或SQL Server,则可以使用神奇的UNPIVOT子句( 与PIVOT子句相反):
SELECT c
FROM t
UNPIVOT (
c FOR col in (col1, col2, col3)
)
翻译自: https://www.javacodegeeks.com/2015/04/how-to-flatmap-a-jdbc-resultset-with-java-8.html
resultset用法