Scala: How to use Iterator.continually to loop over a ResultSet iterator (and handling iterating in a functional way to create a list)

Sometimes when you’re working with Scala and want to do things in a functional way, the solution isn’t always clear. For instance, I wanted to write a database query using plain old SQL and JDBC, so to do that, I needed to work with iterating over a ResultSet.

Specifically, I’m writing a little “password manager” application, and for one function I just wanted to return list of all the “app names” stored in the database, where an “app” is something like Gmail, Facebook, Twitter, or any other application or service that requires a username and password.

Scala, functional programming, and working with an iterator

The short story is that I found a way to work with a ResultSet with this SQL SELECT query by using Scala’s Iterator.continually function. Not accounting for errors, the entire function looks like this:

def listAppNames(url: String): Try[List[String]] = Try {
    val connection: Connection = DriverManager.getConnection(url)
    val statement: Statement = connection.createStatement()
    val resultSet: ResultSet = statement.executeQuery("SELECT app_name FROM passwords ORDER BY app_name")
    val appNames: List[String] = Iterator.continually((resultSet.next(), resultSet))
        .takeWhile(_._1)
        .map(_._2.getString("app_name"))
        .toList
    resultSet.close()
    statement.close()
    connection.close()
    appNames
}

To help keep you from getting distracted, one thing to note is that this application uses a SQLite database on my local computer, so the odds of getting an error here are extremely low, and even if I get one, it’s just me using this application.

The Iterator.continually code

The part I do want to focus on is that this code is what I’m interested in:

val appNames: List[String] = Iterator.continually((resultSet.next(), resultSet))
    .takeWhile(_._1)
    .map(_._2.getString("app_name"))
    .toList
}

It shows a good way to iterate over something like a ResultSet, specifically using the ResultSet and the resultSet.next() method.

How that iterator code works

If you’re not familiar with Iterator.continually, here’s a description of how that code works:

The first thing to know is that the Iterator.continually method creates an infinite iterator that repeatedly applies a given expression. In this case, it is used to repeatedly call resultSet.next() and resultSet.

Next, this code:

Iterator.continually((resultSet.next(), resultSet))

creates an infinite iterator that repeatedly calls resultSet.next() and returns a tuple (Boolean, ResultSet). In that code, resultSet.next() moves the cursor to the next row and returns true if there is a next row, false otherwise. Then the tuple (resultSet.next(), resultSet) contains the boolean result from resultSet.next() and the resultSet itself.

After that, .takeWhile(_._1) filters the elements of the iterator, taking (retaining) only those for which the first element of the tuple (_._1, which is the result of resultSet.next()) is true. This effectively stops the iteration once resultSet.next() returns false, which indicates that there are no more rows. (Note that _._1 is the Scala 2 way to access the first field of a tuple.)

Then, .map(_._2.getString("app_name")) transforms the remaining elements of the iterator --- those where resultSet.next() returned true. For each remaining element, it calls getString("app_name") on the second element of the tuple (i.e., _._2, which is the resultSet). This extracts the app_name value from the current row of the ResultSet.

Finally, .toList converts the resulting app_name values into a List, and the function returns a List[String].

The steps: Initialization, Filtering, Mapping, and Collecting

If you’re interested in a little bit of theory and formal language about how this works, I put that code into ChatGPT, and it organized it into steps of Initialization, Filtering, Mapping, and Collecting:

• Initialization: Iterator.continually((resultSet.next(), resultSet)) starts an infinite loop, where each iteration tries to move to the next row in the ResultSet and returns a tuple containing the result (true or false) and the ResultSet itself.
• Filter: .takeWhile(_._1) continues taking elements from the iterator as long as resultSet.next() returns true, i.e., there are more rows.
• Mapping: .map(_._2.getString("app_name")) extracts the app_name from each valid row in the ResultSet.
• Collecting: .toList collects all the extracted app_name values into a List.

If you’ve ever heard the term Map/Reduce or MapReduce, that’s essentially what’s happening here, where the Filtering and Collecting are part of the reducing and Mapping is, well, mapping (transformation) of the data.

A Scala Iterator.continually example

As an example of how this works with real data, suppose the passwords table has the following data:

| id | app_name  | encrypted_password | salt | iv |
|----|-----------|--------------------|------|----|
| 1  | gmail     | ...                | ...  | ...|
| 2  | facebook  | ...                | ...  | ...|
| 3  | twitter   | ...                | ...  | ...|

Now, when listAppNames() is called, the steps are:

1. Iterator.continually((resultSet.next(), resultSet)) creates tuples (true, resultSet), (true, resultSet), (true, resultSet), (false, resultSet) as resultSet.next() returns true for the first three rows, and then false on the fourth call.
2. takeWhile(_._1) keeps only the first three tuples.
3. map(_._2.getString("app_name")) extracts "gmail", "facebook", and "twitter" from the corresponding rows.
4. toList collects these into the list List("gmail", "facebook", "twitter").

This process lets you safely iterate over the ResultSet and extract the desired values, without running into common pitfalls like off-by-one errors or handling null values incorrectly.