Fork me on GitHub

Saturday, February 1, 2014

Simple enhancements on accessing Android built-in Sqlite

Functional programming rocks. However, legacy Java libraries prevent us to stay in such an ideal world. The job objective of Scaloid is to help write Android code in more elegant style. Although Scaloid doing good job on layout building, I haven't do much things about databases yet.

There are many DB frameworks for Scala. If you writing an app relying on DB extensively, I recommend to use one of them. But when you are using the built-in Sqlite just for storing some casual data, it is not a bad idea that using Android APIs for database access. The biggest problem in this case is that the code is not neat, because of its API architecture. Let's see a common example:

def readDailyPractice(query: String, params: Array[String]) = {
  var dailyTime: List[DailyPractice] = List()
  val c = getReadableDatabase.rawQuery(query, params)
  try {
    while (c.moveToNext()) {
      dailyTime = dailyTime :+ 
        new DailyPractice(c.getString(0), c.getString(1), c.getLong(2))
  } finally c.close()

We have to use variable for the return object because the android.database.Cursor does not provide Scala-compatible iterator. Another clutter is close() call in try-finally section. This can be reduced in more functional-style:

def readDailyPractice(query: String, params: Array[String]) = 
  getReadableDatabase.rawQuery(query, params).closeAfter( => 
    new DailyPractice(c.getString(0), c.getString(1), c.getLong(2)).toList)

By declaring implicit def cursor2RichCursor(c: Cursor) = new RichCursor(c), or extending the trait org.scaloid.common.DatabaseImplicits to your class, The android.database.Cursor implicitly converted into RichCursor, which implements Iterable[Cursor]. Now, we can use many useful iterator methods such as map, foreach, and foldLeft.

I also introduce a function closeAfter[T](body: RichCursor => T):T that closes the cursor after evaluating its parameter.

One of the important thing that can be missed is to convert the mapped result into a List. returns another Iterator that evaluate the original iterator(RichCursor in this case) on demand. The implementation of looks like this:

def map[B](f: A => B): Iterator[B] = new AbstractIterator[B] {
  def hasNext = self.hasNext
  def next() = f(

So if we missed to convert the iterator to List or other form, newly created Iterator generated from the mapping will evaluate the Cursor that is already closed, and vomit the exception. This issue is too detailed, and very easy to miss at first. What we just need is iterate each rows and map a row with a domain object, and forget about cursor. So I created a wrapper function that does this mission simply:

def readDailyPractice(query: String, params: Array[String]) = 
  getReadableDatabase.rawQuery(query, params).orm(c => 
    new DailyPractice(c.getString(0), c.getString(1), c.getLong(2)))

As the name of the function implies, it does very simple form of Object-Relation-Mapping.

In other cases, we often need just one record from the query result. For example, getting count of something, or retrieving some column about a particular user. Even such a simple requirement, we have to write a verbose code as:

def readOneRecord(query: String, params: Array[String], default: String) = 
  try {
    val c = getReadableDatabase.rawQuery(query, params)
    if (c.moveToFirst()) c.getString(0) else default
  } finally c.close()

I created a helper method toString(defaultVal) for this purpose. We can rewrite the code above as:

def readOneRecord(query: String, params: Array[String], default: String) = 
  getReadableDatabase.rawQuery(query, params).toString(default)

Other accessors, toShort, toLong, toInt, toFloat, and toDouble is available as well.

All of these magic can be done by the implicitly converted class RichCursor. The source code of RichCursor is very simple.

class RichCursor(c: Cursor) extends Iterable[Cursor] {
  def iterator = new CursorIterator

  class CursorIterator extends Iterator[Cursor] {
    def hasNext = c.getPosition < c.getCount - 1

    def next() = {

  def closeAfter[T](body: RichCursor => T) = try body(this) finally c.close()

  def orm[T](body: Cursor => T) = closeAfter(

  def toLong(default: Long): Long = closeAfter(csr => if (c.moveToFirst()) c.getLong(0) else default)

  // definitions of toString, toShort... is straightforward

This class is also available on Scaloid 3.2.1

1 comment:

  1. Thanks. It eases many issues.

    I use this generic function for closing resources not just cursors:

    def use[T <: { def close() }, R](resource: T)(cod: T => R) =