Scala exercise 1: template method design pattern

Introduction

As part of my studying with Scala, I have tried to find some exercises to do but most are either too simple or too "academic". So, I decided to create a series of Scala exercises to implement the well known design patterns in an industrial context. Here is the first one: template method.

Problem: Create a Scala class JdbcTemplate that has two template methods: execute() and load(). They will execute a SQL statement and load the query results using JDBC respectively. The methods should open and close the connection, create and close the statement and etc. so that the caller doesn't need to worry about those. For example, the client could use your code like this:

object Test {
  def main(args: Array[String]) {
    Class.forName("org.h2.Driver")
    val t = new JdbcTemplate
    val ds = () => DriverManager.getConnection("jdbc:h2:~/test")
    t.execute(ds, "drop table products if exists")
    t.execute(ds, "create table products (id long primary key, name varchar(128))")
    val insertSql = "insert into products values(?, ?)"
    t.execute(ds, insertSql, List(100, "p1")) //the list is the parameters
    t.execute(ds, insertSql, List(101, "p2")) //ditto
    val ps = t.load[Product](ds, "select * from products", Nil,
      { //this function maps a record into an object
        (rs) => new Product(rs.getLong("id"), rs.getString("name"))
      })
    println(ps)
  }
}

//make it a case class so that it can be printed to the console nicely
case class Product(val id: Long, val name: String) {
}

To make it run, you need to include the h2 database into your classpath. If you use Maven, just add the following to pom.xml:

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

Note that I am not suggesting that you do this in production code; you'd probably want to use an ORM framework.

Click here to see the answer.

import java.sql.{DriverManager, ResultSet, PreparedStatement, Connection}

class JdbcTemplate {
  def execute(ds: () => Connection, sql: String) {
    execute(ds, sql, Nil)
  }
  //Use List[Any] instead of List[Object] so that elements can be primitives
  def execute(ds: () => Connection, sql: String, params: List[Any]) {
    execute(ds, sql, params, (st: PreparedStatement) => st.executeUpdate)
  }

  def execute(ds: () => Connection, sql: String, params: List[Any], executor: PreparedStatement => Unit) {
    val con = ds()
    try {
      var st = con.prepareStatement(sql)
      try {
        //zip (a, b, c) with (0, 1, 2) to get ((a, 0), (b, 1), (c, 2))
        params.zipWithIndex.foreach {
          //case p1 => e1 can be seen as an anonymous function: x => x matches { case p1 => e1 }
          case (value, idx) => st.setObject(idx+1, value)
        }
        executor(st)
      } finally {
        st.close
      }
    } finally {
      con.close
    }

  }

  def load[A](ds: () => Connection, sql: String, params: List[Any], objMapper: ResultSet => A): List[A] = {
    var result = List[A]()
    execute(ds, sql, params, (st: PreparedStatement) => {
      val rs = st.executeQuery
      try {
        while (rs.next) {
          result = objMapper(rs) :: result
        }
      } finally {
        rs.close
      }
    })
    result.reverse
  }

}