Avoiding mutable variables in Scala + JDBC

Question

I have the following code in Scala to access a database with JDBC. It works fine, however it uses several mutable variables (var declarations) because these need to be available in the finally clause for the JDBC elements to be closed. Can this code be changed to use only immutable variables (val declarations)?

var connection:Connection = null
var statement:Statement = null
var resultSet:ResultSet = null

try {
  val url = "someUrl"
  val user = "someUser"
  val password = "somePwd"
  Class.forName("mySql")
  connection = DriverManager.getConnection(url, user, password)
  statement = connection.createStatement()
  resultSet = statement.executeQuery("SELECT column FROM table")
  while ( resultSet.next() ) {
    val col = resultSet.getString(1)
    println(col)
  }

} 
catch {
  case e:Exception => e.printStackTrace
}
finally {
  if (resultSet != null) resultSet.close
  if (statement != null) statement.close
  if (connection != null) connection.close
}

Answer 1

• You can create small function to return connection.

• You don't have to declare variables at the top(eg. connection, statement, resultSet) when you don't have values for them. Instead you can create functions to return actual value as Option[T]

• Or since you need to have reference to connection, statement etc to close them, this answer has nice explanation on closing resources. I'm stealing the same code to use here. see the function autoClean which takes the resource you want to work on and the code to operate after which resource will be cleaned.

• Also see immutable way of collecting resultSet data.

So, your would look like,

import java.sql.{Connection, DriverManager}

object AutoCleanJdbcConnection {

  val url = "someUrl"
  val user = "someUser"
  val password = "somePwd"

  def queryDb(): Option[Seq[String]] = {

    autoClean(dbConnection) { connection =>

      autoClean(connection.createStatement()) { statement =>

        autoClean(statement.executeQuery("SELECT column FROM table")) { result =>

          new Iterator[String] {
            def hasNext: Boolean = result.next()

            def next(): String = result.getString(1)
          }.toSeq

        }
      }
    }.flatten.flatten

  }

  def dbConnection: Connection = {
    Class.forName("mySql")
    DriverManager.getConnection(url, user, password)
  }

  def main(args: Array[String]): Unit = {
    queryDb().foreach { data =>
      println(data)
    }
  }

  def autoClean[A <: AutoCloseable, B](resource: A)(ops: A => B): Option[B] = cleanly(resource)(_.close())(ops)

  /**
    * can be returning Either[B, Throwable]
    */
  def cleanly[A, B](resource: => A)(resourceCleanupCode: A => Unit)(operationCode: A => B): Option[B] = {
    try {
      val res = resource
      try {
        Some(operationCode(res))
      }
      finally {
        resourceCleanupCode(res)
      }
    } catch {
      case e: Exception => None
    }
  }
}

NOTE: The code is compiled but I did not run this against database.

Answer 2

Here's a relatively straight translation of what you've got into something a little more FP. Type annotations added for clarity. (I haven't run this, but it does compile.)

import scala.util.Try

val connection:Try[Connection]= Try(DriverManager.getConnection(url, user, password))
val statement: Try[Statement] = connection.map(_.createStatement())
val resultSet: Try[ResultSet] = statement.map(_.executeQuery("SELECT column FROM table"))

resultSet.map(rs => while (rs.next()) println(rs.getString(1)))  
         .recover{case e => e.printStackTrace()}

resultSet.foreach(_.close())
statement.foreach(_.close())
connection.foreach(_.close())

The idea is to avoid vars by making the failure condition part of the variable's type.

In this case you won't attempt to createStatement() if the getConnection() failed, you won't attempt to executeQuery() if the createStatement() failed, and you'll close() only those resources that didn't fail.

Answer 3

Here is an approach that is based on Scala's Try and Stream classes. It is built on top of jwvh's answer by adding exception handling to ensure that all resources get closed even in an error scenario.

This approach avoids var's by capturing the exceptions into Scala's Try abstraction and it avoids the while loop by converting the JDBC row handling into a Scala collection.

Try can hold two states, a successful value or an exception. tryInstance.map will only be invoked if the value of tryInstance holds a successful value.

Scala Stream's are lazy, that is they calculate one value at a time and delay calculation of the next value. This lets us move the while loop into the Scala collection libraries at the cost of having a little extra logic to detect the end of the stream and thus close the resources at the correct time.

NB this code has not been tested, however I have successfully used this strategy in production code. I include the following code here for illustrative purposes.

import Stream._

Class.forName("mySql")

def doQuery( url:String, user:String, password:String ): List[String] = {
  val conn      = Try(DriverManager.getConnection(url, user, password))
  val statement = conn.map( _.createStatement() )
  val rs        = statement.map( _.executeQuery("SQL") )

  toStream(rs,statement,conn).toList
}

private def toStream(rs:Try[ResultSet], s:Try[Statement], c:Try[Connection]) : Stream[String] = {
  try {
    val resultSet = rs.get // will throw an exception if any previous problems had occurred while opening the connection and parsing the sql

    if ( resultSet.next() ) {
      resultSet.getString(1) #:: toStream(rs,s,c)
    } else {
      close(rs,s,c)

      Stream.empty
    }
  } catch {
    case ex =>
      close(rs,s,c)
      throw ex
  }
}

Answer 4

I see that there is a lot of boiler plate code like getting a connection, closing a connection, handling failures! This is how Java should be done, but you want to write Scala. So if not using JDBC library directly is an option, you could try using some mapping libraries which does this boiler plate for you.

For example., you could have a look at Slick which helps you writing database abstractions with keeping functional paradigm intact!