ScalaKnol : Loan Pattern Revisited

Here I am going to modify the loan pattern described in this article “Scala Knol : Understanding Load Pattern” .

Let us take an example from earlier blog, we have all used connections to get to the database and then we need to destroy the connection so that there are no leaks. OK, we can use the connection factory but let us assume that we do not have that for now. The general code would look like this

def doLoanSqlCall(f:Connection=>Int) = {
var conn: Connection = null
try {
val url = "jdbc:mysql://localhost:3306/"
Class.forName("com.mysql.jdbc.Driver").newInstance()
conn = DriverManager.getConnection(url + "bob", "userName", "password")

f(conn)

} catch {
case ex: Exception => {
println("Exception: " + ex.getMessage)
}
} finally {
conn.close
}
}

Here this is a higher order function which takes a function f as an argument. function f takes connection as an argument and returns Int.
f: Connection => Int

def executeMySqlFunction(conn:Connection):Int = {
val stmt = conn.prepareStatement("insert into employee (id,age) values (?,?) ")
stmt.setString(1, "1001")
stmt.setInt(2, "21")
stmt.executeUpdate
}

User just need to pass the above function in doLoanSqlCall factory method. Function executeMySqlFunction do not need to worry about the creating and deleting the connection. All these stuff would be handled by the method doLoanSqlCall.

Hence the call becomes

doLoanSqlCall(executeMySqlFunction)

Example given in this article is excellent if function is not expected to return any other value but Integer. It is working fine in case of most of the insert or update operation where Sql operation returns Integer values.

What is a problem is this approach ?

There is a obvious scenario where data is fetched from database then a custom object is created and returned by the function.

Suppose you have to execute following jdbc code and return an object instead of Integer then above described loan pattern will not help you.

def executeMySqlFunction(conn:Connection):Option[List[Int]] = {
val query = "select id from employee"
val stmt = conn.createStatement
val resultSet = stmt.executeQuery(query)
val ids = ListBuffer[Int]()
while (resultSet.next) {
ids.append(resultSet.getInt("id"))
}
Some(ids.toList)
}

Here mysql function is taking Connection as an argument and returning any generic type. ( In this case it is returning Optiont[List[Int]] but it can return any generic type)

So, Function defination is as follows
Connection => R

Now we need a factory method which need to accept a function f: Connection => R , instead of f: Connection => Int. The modified version of doLoanSqlCall is as follows

def doLoanSqlCall[R](f:Connection=>Option[R]) = {
var conn: Connection = null
try {
val url = "jdbc:mysql://localhost:3306/"
Class.forName("com.mysql.jdbc.Driver").newInstance()
conn = DriverManager.getConnection(url + "bob", "userName", "password")

f(conn)

} catch {
case ex: Exception => {
println("Exception: " + ex.getMessage)
None
}
} finally {
conn.close
}
}

This version of doLoanSqlCall method will take a function which takes connection as an argument and return Option of any generic type.

Use executeMySqlFunction as closure

Following example explains the practical way of using the loan pattern with the help of closure.

def getUserName(id: Int): Option[String] = {
val query = "select name from employee where id = " + id
def executeMySqlFunction(conn: Connection): Option[String] = {
val statement = conn.createStatement()
val resultSet = statement.executeQuery(query)
var name: String = ""
while (resultSet.next())
name = resultSet.getString("name")
Some(name)
}
doLoanSqlCall(executeMySqlFunction)
}