How to handle null in Anorm

Question

I have a table with nullable column, and when query the null column, it threw error

 val row: List[(String,String)] = SQL("select top 10 Spare_Part part,Pricing_Category cat from Price_Point_Base")
                .as((str("part"))~ str("cat") map(flatten) *)

I checked the link https://www.playframework.com/documentation/2.0/ScalaAnorm .

It only gives away to handle nullable column using something like

SQL("Select name,indepYear from Country")().map { row =>
  row[String]("name") -> row[Option[Int]]("indepYear")
}

But since str("part") is more compact than row[String]("name"), so I'd like to try using str("part"), but how to make str("part") works with nullable column?

You should consider updating. There was major updates since 2.0 and latest release 2.3 (2.3.4). Bugs fixed since cause the exception. — cchantep

Michael Zajac Michael Zajac · Accepted Answer · 2014-09-03T13:21:49

If you're reading a nullable column, you really should be binding it to an Option[String] to represent that it could be missing a value. Anorm will not convert it to null, rather it will throw an error.

val row: List[(Option[String], String)] = 
    SQL("select top 10 Spare_Part part,Pricing_Category cat from Price_Point_Base")
       .as( get[Option[String]("part") ~ str("cat") map(flatten) *)

This is messy however, and would get messier if you wanted to supply a default value for the String. We could make a re-usable parser that defaults NULL values to empty strings:

val parser: RowParser[(String, String)] = {
    get[Option[String]]("part") ~ get[Option[String]]("cat") map {
       case part~cat => (part.getOrElse(""), cat.getOrElse(""))
    }
}

And then apply it:

val row: List[(String, String)] = 
    SQL("select top 10 Spare_Part part,Pricing_Category cat from Price_Point_Base")
       .as(parser *)

How to handle null in Anorm

2 Answers