Convert ResultSet Into Map

1. Introduction

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.

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

2. Setup

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:

<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:

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

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.

3.1. Using 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.

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:

@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

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.

Here’s the modified version of the previous test case using 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

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.

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

@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

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.