Using the Parser API with nullable columns in Anorm 2.4

Question

I'm really struggling to get rid of deprecation warnings now that I've upgraded to Anorm 2.4. I've had a look at How to handle null in Anorm but it didn't help me enough.

Let's take a simple example: the account database table:

• id (bigint not null)
• email_address (varchar not null)
• first_name (varchar)
• last_name (varchar)

I could have 2 functions in my Scala code: getAccountOfId and getAccountsOfLastName.

• getAccountOfId returns 0 or 1 account, therefore Option[(Long, String, Option[String], Option[String])] to keep our example simple
• getAccountsOfLastName returns a list of accounts (which could potentially have a size of 0), therefore List[(Long, String, Option[String], String)] to keep our example simple

Part of the code of these 2 functions:

def getAccountOfId(id: Long): Option[(Long, String, Option[String], Option[String])] = {
  DB.withConnection { implicit c =>
    val query = """select email_address, first_name, last_name
        from account
        where id = {id};"""

    /* Rest of the code that I struggle with unless I use deprecated functions */
  }
}

def getAccountsOfLastName(lastName: String): List[(Long, String, Option[String], String)] = {
  DB.withConnection { implicit c =>
    val query = """select id, email_address, first_name
        from account
        where last_name = {lastName};"""

    /* Rest of the code that I struggle with unless I use deprecated functions */
  }
}

I want the "rest of the code" in these 2 functions to be based on Anorm's Parser API.

Answer 1

Turns out it was easy:

  def getAccountOfId(id: Long): Option[(Long, String, Option[String], Option[String])] = {
    DB.withConnection { implicit c =>
      val query = """select email_address, first_name, last_name
        from account
        where id = {id};"""

      val rowParser = str("email_address") ~ (str("first_name") ?) ~ (str("last_name") ?) map {
        case emailAddress ~ firstNameOpt ~ lastNameOpt => (id, emailAddress, firstNameOpt, lastNameOpt)
      }

      SQL(query).on("id" -> id).as(rowParser.singleOpt)
    }
  }

  def getAccountsOfLastName(lastName: String): List[(Long, String, Option[String], String)] = {
    DB.withConnection { implicit c =>
      val query = """select id, email_address, first_name
        from account
        where last_name = {lastName};"""

      val rowParser = long("id") ~ str("email_address") ~ (str("first_name") ?) map {
        case id ~ emailAddress ~ firstNameOpt => (id, emailAddress, firstNameOpt, lastName)
      }

      SQL(query).on("lastName" -> lastName).as(rowParser.*)
    }
  }