CODEWITHSUNDEEP
scalaplayframeworkanorm
Blackbird
Blackbird

Reputation: 2378

Using the Parser API with nullable columns in Anorm 2.4

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:

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

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.

Upvotes: 4

Views: 649

Answers (2)

Blackbird
Blackbird

Reputation: 2378

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.*)
    }
  }

Upvotes: 0

Jonny Coombes
Jonny Coombes

Reputation: 575

Not sure if this helps, but using Anorm 2.4, I have a case class which looks like this:

 final case class Role(id: Int,
                      label: String,
                      roletype: Int,
                      lid: Option[Int],
                      aid: Option[Int],
                      created: DateTime,
                      modified: DateTime)

and then just have parser combinator for it which looks like this: 

 val roleOptionRowParser = int("id") ~ str("label") ~ int("roletype") ~ (int("lid")?) ~ (int("vid")?) ~ get[DateTime]("created") ~
    get[DateTime]("modified") map {
    case id~label~roletype~lid~vid~created~modified ⇒ Some(Role(id, label, roletype, lid, vid, created, modified))
    case _ ⇒ None
}

so you basically just parse out using the ? combinator for optional fields, then match based on what you extract from the SQL result row. You can then apply this to queries in the following way:

SQL(s"""
            | select * from $source
            |    where $clause
            """.stripMargin).on(params : _*).as(rowParser.single).get

where 'rowParser' in this case is just a reference to the roleOptionRowParser defined in the last lump of code.

If you have multiple rows returned from your query (or expect to have multiple rows) then you can apply the same combinators (such as ? or *) before passing them through to the 'as' function like this:

SQL(s"""
            | select * from $source
            |    where $clause
            """.stripMargin).on(params : _*).as(rowParser *).flatten

or 

SQL(s"""
            | select * from $source
            |    where $clause
            """.stripMargin).on(params : _*).as(rowParser ?).flatten

Ah - forgot to mention that the 'flatten' on the end is there because my parser in this example returns an Option[Role], depending on whether all the necessary column values are present in the returned row (this bit):

case id~label~roletype~lid~vid~created~modified ⇒ Some(Role(id, label, roletype, lid, vid, created, modified))

So, when multiple rows are returned, I just apply 'flatten' to uplift out of the Option type so that I end up with a list of actual 'Role' instances.

Cheers,

HTH.

Upvotes: 2

Related Questions