Convert ResultSet Into Map – 将结果集转换为地图

1. Introduction

1.导言

Java applications widely use the Java Database Connectivity (JDBC) API to connect and execute queries on a database. ResultSet is a tabular representation of the data extracted by these queries.

Java 应用程序广泛使用 Java Database Connectivity (JDBC) API 连接数据库并执行查询。ResultSet 是这些查询提取的数据的表格表示。

In this tutorial, we’ll learn how to convert the data of a JDBC ResultSet into a Map.

在本教程中，我们将学习如何将 JDBC ResultSet 的数据转换为 Map 。

2. Setup

2.设置

We’ll write a few test cases to achieve our goal. Our data source will be an H2 database. H2 is a fast, open-source, in-memory database that supports the JDBC API. Let’s add the relevant Maven dependency:

我们将编写一些测试用例来实现我们的目标。我们的数据源将是 H2 数据库。H2 是一个快速、开源、支持 JDBC API 的内存数据库。让我们添加相关的 Maven 依赖关系：

<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
</dependency>

Once the database connection is ready, we’ll write a method to do the initial data setup for our test cases. To achieve this, we first create a JDBC Statement, and subsequently create a database table named employee using the same. The employee table consists of columns named empId, empName, and empCity that will hold information about the ID, name, and city of the employee. We now can insert sample data in the table using the Statement.execute() method:

数据库连接准备就绪后，我们将编写一个方法来为测试用例进行初始数据设置。为此，我们首先创建一个 JDBC Statement ，然后使用它创建一个名为 employee 的数据库表。employee 表由名为 empId、empName、 和 empCity 的列组成，这些列将保存雇员的 ID、姓名和城市信息。现在，我们可以使用 Statement.execute() 方法在表中插入示例数据：

void initialDataSetup() throws SQLException {
    Statement statement = connection.createStatement();

    String sql = "CREATE TABLE employee ( " +
      "empId INTEGER not null, " +
      "empName VARCHAR(50), " +
      "empCity VARCHAR(50), " +
      "PRIMARY KEY (empId))";

    statement.execute(sql);

    List<String> sqlQueryList = Arrays.asList(
      "INSERT INTO employee VALUES (1, 'Steve','London')", 
      "INSERT INTO employee VALUES (2, 'John','London')", 
      "INSERT INTO employee VALUES (3, 'David', 'Sydney')",
      "INSERT INTO employee VALUES (4, 'Kevin','London')", 
      "INSERT INTO employee VALUES (5, 'Jade', 'Sydney')");
    
    for (String query: sqlQueryList) {
        statement.execute(query);
    }
}

3. ResultSet to Map

3.将 ResultSet 转换为 Map</em

Now that the sample data is present in the database, we can query it for extraction. Querying the database gives the output in the form of a ResultSet. Our goal is to transform the data from this ResultSet into a Map where the key is the city name, and the value is the list of employee names in that city.

既然样本数据已经存在于数据库中，我们就可以对其进行查询提取。查询数据库可获得 ResultSet 形式的输出。我们的目标是将ResultSet中的数据转换为Map，其中键为城市名称，值为该城市的员工姓名列表。

3.1. Using Java 7

3.1.使用 Java 7

We’ll first create a PreparedStatement from the database connection and provide an SQL query to it. Then, we can use the PreparedStatement.executeQuery() method to get the ResultSet.

首先，我们将从数据库连接创建一个 PreparedStatement 并为其提供一个 SQL 查询。然后，我们可以使用 PreparedStatement.executeQuery() 方法来获取 ResultSet.

We can now iterate over the ResultSet data and fetch the column data individually. In order to do this, we can use the ResultSet.getString() method by passing the column name of the employee table into it. After that, we can use the Map.containsKey() method to check if the map already contains an entry for that city name. If there’s no key found for that city, we’ll add an entry with the city name as the key and an empty ArrayList as the value. Then, we add the employee’s name to the list of employee names for that city:

现在，我们可以遍历 ResultSet 数据并单独获取列数据。为此，我们可以使用 ResultSet.getString() 方法，向其中传递 employee 表的列名。然后，我们可以使用 Map.containsKey() 方法来检查地图是否已包含该城市名称的条目。如果没有找到该城市的键，我们将添加一个以城市名称为键、空 ArrayList 为值的条目。然后，我们将该员工的姓名添加到该城市的员工姓名列表中：

@Test
void whenUsingContainsKey_thenConvertResultSetToMap() throws SQLException {
    ResultSet resultSet = connection.prepareStatement(
        "SELECT * FROM employee").executeQuery();
    Map<String, List<String>> valueMap = new HashMap<>();

    while (resultSet.next()) {
        String empCity = resultSet.getString("empCity");
        String empName = resultSet.getString("empName");
        if (!valueMap.containsKey(empCity)) {
            valueMap.put(empCity, new ArrayList<>());
        }
        valueMap.get(empCity).add(empName);
    }
    assertEquals(3, valueMap.get("London").size());
}

3.2. Using Java 8

3.2.使用 Java 8

Java 8 introduced the concept of lambda expressions and default methods. We can leverage them in our implementation to simplify the entry of new keys in the output map. We can use the method named computeIfAbsent() of the Map class, which takes two parameters: a key and a mapping function. If the key is found, then it returns the relevant value; otherwise, it will use the mapping function to create the default value and store it in the map as a new key-value pair. We can add the employee’s name to the list afterward.

Java 8 引入了 lambda 表达式和默认方法的概念。我们可以在实现中利用它们来简化输出映射中新键的输入。我们可以使用 Map class 中名为 computeIfAbsent() 的方法，该方法需要两个参数：键和映射函数。如果键被找到，它将返回相关的值；否则，它将使用映射函数创建默认值，并将其作为新的键值对存储在 map 中。之后，我们可以将员工姓名添加到列表中。

Here’s the modified version of the previous test case using Java 8:

下面是使用 Java 8 的前一个测试用例的修改版：

@Test
void whenUsingComputeIfAbsent_thenConvertResultSetToMap() throws SQLException {
    ResultSet resultSet = connection.prepareStatement(
        "SELECT * FROM employee").executeQuery();
    Map<String, List<String>> valueMap = new HashMap<>();

    while (resultSet.next()) {
        String empCity = resultSet.getString("empCity");
        String empName = resultSet.getString("empName");
        valueMap.computeIfAbsent(empCity, data -> new ArrayList<>()).add(empName);
    }
    assertEquals(3, valueMap.get("London").size());
}

3.3. Using Apache Commons DbUtils

3.3.使用 Apache Commons DbUtils

Apache Commons DbUtils is a third-party library that provides additional and simplified functionalities for JDBC operations. It provides an interesting interface named ResultSetHandler that consumes JDBC ResultSet as input and allows us to transform it into the desired object that the application expects. Moreover, this library uses the QueryRunner class to run SQL queries on the database table. The QueryRunner.query() method takes the database connection, SQL query, and ResultSetHandler as input and directly returns the expected format.

Apache Commons DbUtils 是一个第三方库，可为 JDBC 操作提供额外的简化功能。它提供了一个名为 ResultSetHandler 的有趣接口，该接口将 JDBC ResultSet 作为输入，并允许我们将其转换为应用程序所期望的对象。此外，该库使用 QueryRunner 类在数据库表上运行 SQL 查询。QueryRunner.query()方法将数据库连接、SQL 查询和 ResultSetHandler 作为输入，并直接返回预期格式。

Let’s look at an example of how to create a Map from a ResultSet using ResultSetHandler:

让我们看看如何使用 ResultSetHandler 从 ResultSet 创建 Map 的示例：

@Test
void whenUsingDbUtils_thenConvertResultSetToMap() throws SQLException {

    ResultSetHandler <Map<String, List<String>>> handler = new ResultSetHandler <Map <String, List<String>>>() {
        public Map<String, List<String>> handle(ResultSet resultSet) throws SQLException {
            Map<String, List<String>> result = new HashMap<>();
            while (resultSet.next()) {
                String empCity = resultSet.getString("empCity");
                String empName = resultSet.getString("empName");
                result.computeIfAbsent(empCity, data -> new ArrayList<>()).add(empName);
            }
            return result;
        }
    };
    QueryRunner run = new QueryRunner();
    Map<String, List<String>> valueMap = run.query(connection, "SELECT * FROM employee", handler);
    assertEquals(3, valueMap.get("London").size());
}

4. Conclusion

4.结论

To summarize, we took a look at several ways we can aggregate data from ResultSet and convert it into a Map using Java 7, Java 8, and the Apache DbUtils library.

总而言之，我们使用 Java 7、Java 8 和 Apache DbUtils 库了解了从 ResultSet 中聚合数据并将其转换为 Map 的几种方法。

As always, the full code for this article can be found over on GitHub.

与往常一样，本文的完整代码可在 GitHub 上找到。