Using JDBC in Scala

This article is showing very simple usage of Java JDBC driver in Scala.

Currently, I am working on graduate level Network Security Course project. Briefly my project is cloud based secure password storage.

For a while I am using Heroku-Scala/Java on Play Framework-MongoDB(Reactive Mongo) stack in different projects. In this project, I decided to use an SQL database instead of NoSql. Since project focus on security rather than excellent database or webserver, It would not matter which database I am using. However, my data schema in this project is more appropriate for SQL.

I already discovered how to send query and map result to object in Scala when I use MongoDB. This time, I need a library that will send query to SQL db and hopefully map result to object. So, I googled. I found out couple of cool libraries. One of them is Slick. This is function relational mapping(ORM in Java) for Scala. I think this library make whole process much more faster once you get used to it, even if it brings it’s own problems. Another cool library was ScalikeJDBC. It looks quite nice when you check their github samples page. These are nice libraries and I am planning to use them in bigger projects. In this project, I just have 3–4 tables and I just need couple of queries to database. So I decided to use directly JDBC. I coded query sender and mapper method using Scala generics and implicit so that I do not have to write create statement, close statement, and all boilerplate code, again and again.

I have user table in database, and its class correspondence in Scala:

case class User(id: Int, email: String, name: String, phone: String, password: String, role: String) {

  def this(registerRequest: RegisterRequest, passwordHash: String, role: String) = this(-1, registerRequest.email, registerRequest.name, registerRequest.phone, passwordHash, role)

  def toJson(): JsValue = {
    JsObject(Seq("id" -> Json.toJson(id),
      "email" -> Json.toJson(email),
      "name" -> Json.toJson(name),
      "phone" -> Json.toJson(phone)
    ))
  }
}

Like this User table, I have other tables. I want to have only one method that can execute a query and map results to these objects.

Following method achieve this. Method takes SqlMappable type as generic, jdbc connection, and query string. when I call implicity[SqlMappable[T]].map, scala looking for a implicit defined in object of T.

trait SQLOps {
    
    def executeQueryForList[T : SqlMappable](connection: java.sql.Connection, query: String): List[T] = {
        var stmt: Statement = null
        val result: ListBuffer[T] = new ListBuffer[T]
        try {
          stmt = connection.createStatement()
          val resultSet = stmt.executeQuery(query)
          while(resultSet.next()) {
            var mappedObject = implicitly[SqlMappable[T]].map(resultSet)
            if(mappedObject != null)
              result += mappedObject
          }
          result.toList
        } catch {
          case e:Exception =>
            e.printStackTrace()
            result.toList
        } finally {
            //closing statement looks enough
            //closing result set can be a good practice too
          if(stmt != null)
            stmt.close()
        }
  }
}

Following code shows definition of implicit value. We need to define this for each object type that we have.

object User {
  implicit val map = new SqlMappable[User] {
    def map(r: ResultSet) = new User(r.getInt("id"), r.getString("email"), r.getString("name"), r.getString("phone"), r.getString("password"), r.getString("role"))
  }
}

After having all these, all I need to do is to have a Dao class with SqlOps trait. I use this method like that:

val users = executeQueryForList[User](connection, “SELECT * FROM USER”)

For queries that are not returning object result, I have another function that tells whether query is successful or not.

Using an async driver would be much more better in terms of performance. I was using ReactiveMongo which returns Future[Result] instead of result and you carry this Future until REST end point which make application scalable.

This is just a simple usage for beginner Scala users. It is not a mature way to use in large project. I would love to hear your suggestions too.

Sources:

Call a "static" method belonging to a generic type in scala