Anorm null values

Question

I'm writing server that executes any select query on my db and returns json. I've done most of that task but I stuck with parsing nullable column to string.

val result = SQL("SELECT * FROM Table limit 5;")().map(_.asList.map({_.toString})).toList
val jsonResp = Json.toJson(result)

And that generates if the column could have null value string Some(123) instead of 123. I tried with match but I failed with compose that command. Maybe you had some similar problem and you know how to deal with that kind of response?

Edit: I made some progress by adding pattern matching:

    val result = SQL(query)()
      .map(_.asList.map(
    {
      case Some(s) => s.toString
      case None => ""
      case v => v.toString
    }
    )).toList

but I'm not sure is it good way to solve that problem. Still waiting for ideas

What you are doing is rendering null column values as empty strings. An empty string is not the same as null, even though some databases map it that way. Similarly, a nullable integer column will also render as an empty string. This may not meet your needs for type safety in JSON. — Bob Dalgleish

cchantep cchantep · Accepted Answer · 2014-10-22T23:51:40

Anorm is supporting nullable column as optional value.

There you return row as a list of raw value. It would be better to use the parser API to indicate how to extract properly values. E.g.

SQL("SELECT a, b, c ...").as(get[Option[String]]("a") ~ int("b") ~ str("c) map { case a ~ b ~ c => MyClass(a, b, c) }.*)

Returns SQL results as list of MyClass, with properties being in order Option[String], Int and String.

Anorm documentation has plenty of other examples.

Anorm null values

2 Answers