How to return a single record from a Play Framework Anorm SELECT query

I don’t know if there is a better way to do this yet, but if you want to select one record from a database table using the Play Framework and Anorm, I do know that this code works:

/**
 * Look up the `uid` for the given username and password.
 */
def getUserId(username: String, password: String): Option[Long] = DB.withConnection { implicit c =>
    val rowOption = SQL("select id from users where username = {username} and password = {password} limit 1")
        .on('username -> username, 'password -> password)
        .apply
        .headOption
    rowOption match {
      case Some(row) => Some(row[Long]("id"))  // the uid
      case None => None
    }
}

The key to the solution is using the apply and headOption method calls after the usual SQL constructor. I also use a LIMIT 1 in the SQL SELECT query, though that isn’t really needed if there’s a proper index on the users database table.

Another approach

Instead of using match, you can use code like this, if you prefer:

// returns a Long instead of Option[Long]
def getUserId(username: String, password: String): Long = DB.withConnection { implicit c =>
    val rowOption = SQL("select id from users where username = {username} and password = {password} limit 1")
        .on('username -> username, 'password -> password)
        .apply
        .headOption
    rowOption.map(row => row[Long]("id")).getOrElse(0L)
}

Personally I prefer the first approach, where I return an Option from this method and let the caller handle the result as they wish, but I just wanted to show another possible way to deal with the Option result in this method, i.e., using map and getOrElse.

Post new comment

The content of this field is kept private and will not be shown publicly.