Mapping column types Slick 3.1.1

Question

I am new to Slick and having a really hard time getting mapping of java.sql.date/time/timestamp mapped into jodatime.

trait ColumnTypeMappings {

  val profile: JdbcProfile
  import profile.api._

  val localTimeFormatter = DateTimeFormat.forPattern("HH:mm:ss")
  val javaTimeFormatter = new SimpleDateFormat("HH:mm:ss")

  implicit val myDateColumnType = MappedColumnType.base[LocalDate, Date](
    ld => new        java.sql.Date(ld.toDateTimeAtStartOfDay(DateTimeZone.UTC).getMillis),
    d  => new LocalDateTime(d.getTime).toLocalDate
  )

  implicit val myTimeColumnType = MappedColumnType.base[LocalTime, Time](
    lt => new java.sql.Time(javaTimeFormatter.parse(lt.toString(localTimeFormatter)).getTime),
    t  => new LocalTime(t.getTime)
  )

  implicit val myTimestampColumnType = MappedColumnType.base[DateTime, Timestamp](
    dt => new java.sql.Timestamp(dt.getMillis),
    ts => new DateTime(ts.getTime, DateTimeZone.UTC)
  )

}

In the auto generated Tables.scala I include the mapping like this:

trait Tables extends ColumnTypeMappings {
  val profile: slick.driver.JdbcDriver
  import profile.api._
  import scala.language.implicitConversions
  // + rest of the auto generated code by slick codegen
}

And to wrap it all up I use this like this:

object TestTables extends Tables {
  val profile = slick.driver.MySQLDriver
}

import Tables._
import profile.api._

val db = Database.forURL("url", "user", "password", driver = "com.mysql.jdbc.Driver")
val q = Company.filter(_.companyid === 1).map(._name)
val action = q.result
val future = db.run(action)
val result = Await.result(future, Duration.Inf)

I get an NullPointerException on: implicit val myDateColumnType.... when running this. I've verified that this last block of code works if I remove the mapping.

Answer 1

So i think the issue may be that you are extending ColumnTypeMappings in your Tables.scala. The documentation doesn't make it clear but I think the auto generated code relating to the database should not be touched, as this is used by slick to map the rows in the DB, and then extend TestTables by ColumnTypeMappings to do the implicit conversion when you get the result back from the database.

I haven't particularly delved into slick 3.x yet so I may be wrong, but I think that makes sense.

Edit: No, i was wrong :(. Apologies

Answer 2

Try changing implicit val to implicit def in your definitions of the MappedColumnTypes. The reason why is related to the answer given by Maksym Chernenko to this question. Generally, the JdbcProfile driver (that defines api.MappedColumnType) has not been injected yet, and:

that causes NPE. You can either make your "mapper" val lazy, or change it from val to def (as shown below)

implicit def myDateColumnType = MappedColumnType.base[LocalDate, Date](
  ld => new java.sql.Date(ld.toDateTimeAtStartOfDay(DateTimeZone.UTC).getMillis),
  d  => new LocalDateTime(d.getTime).toLocalDate
)

implicit def myTimeColumnType = MappedColumnType.base[LocalTime, Time](
  lt => new java.sql.Time(javaTimeFormatter.parse(lt.toString(localTimeFormatter)).getTime),
  t  => new LocalTime(t.getTime)
)

implicit def myTimestampColumnType = MappedColumnType.base[DateTime,  Timestamp](
  dt => new java.sql.Timestamp(dt.getMillis),
  ts => new DateTime(ts.getTime, DateTimeZone.UTC)
)