Wednesday 12 April 2017

[Scala] How to convert dates implicitly ?

If you read any book or tutorial about scala you probably know what implicit keyword means. Basically you can create a method that will be called when compiler thinks it's a good idea to do so. Although I'm not a fan of implicit conversions (when you overuse it the code becomes less readable and clear) it's very useful to get rid of method calls in some cases.

When you create a DAO you probably extend some abstract data access object that provides entity manager, jdbc template or other object that connects your application with a database.

I've been using Jooq for 16 months now to construct SQL queries. It's quite nice tool that prevents from typical sql typos. See example below:
def findForUpdate(searchKey: DateTime, productId: String): Option[SearchHistoryReport] = {
    val sql = dslContext.select(SEARCH_HISTORY_REPORT.SEARCH_DATE_AND_HOUR,
      SEARCH_HISTORY_REPORT.PRODUCT_ID,
      SEARCH_HISTORY_REPORT.SEARCH_SCORE)
      .from(SEARCH_HISTORY_REPORT)
      .where(SEARCH_HISTORY_REPORT.SEARCH_DATE_AND_HOUR.equal(new Timestamp(searchKey.getMillis)))
      .and(SEARCH_HISTORY_REPORT.PRODUCT_ID.equal(productId))
      .forUpdate().getSQL(INLINED)
    Try({
      npjt.queryForObject(sql, noParams(), (rs: ResultSet, rowNum: Int) => SearchHistoryReport.builder()
        .searchDateAndHour(fromTimestamp(rs.getTimestamp(SEARCH_HISTORY_REPORT.SEARCH_DATE_AND_HOUR.toString)))
        .productId(rs.getString(SEARCH_HISTORY_REPORT.PRODUCT_ID.toString))
        .searchScore(rs.getBigDecimal(SEARCH_HISTORY_REPORT.SEARCH_SCORE.toString)).build())
    }).toOption
  }
Search date and hour is a datetime column in the database:
MariaDB [_censored]> describe search_history_report;
+----------------------+---------------+------+-----+---------+-------+
| Field                | Type          | Null | Key | Default | Extra |
+----------------------+---------------+------+-----+---------+-------+
| product_id           | varchar(255)  | NO   | PRI | NULL    |       |
| search_date_and_hour | datetime      | NO   | PRI | NULL    |       |
| search_score         | decimal(19,2) | NO   |     | NULL    |       |
+----------------------+---------------+------+-----+---------+-------+
Datetime can be compared to java.sql.Timestamp but all dates in my domain objects are Joda's DateTime.

Let's see another example:
val sql = dslContext.select(sum(SEARCH_HISTORY_REPORT.SEARCH_SCORE).as(SCORE_SUM_ALIAS), SEARCH_HISTORY_REPORT.PRODUCT_ID)
      .from(SEARCH_HISTORY_REPORT)
      .where(SEARCH_HISTORY_REPORT.PRODUCT_ID.in(productIds))
      .and(SEARCH_HISTORY_REPORT.SEARCH_DATE_AND_HOUR.between(new Timestamp(startDate.getMillis)).and(new Timestamp(endDate.getMillis)))
      .groupBy(SEARCH_HISTORY_REPORT.PRODUCT_ID).getSQL(INLINED)
I simply want to get rid of DateTime => Timestamp conversion.

Let's assume that my abstract dao looks like that:
trait Dao {
  def dslContext(): DSLContext = ???

  def jdbcTemplate(): NamedParameterJdbcTemplate = ???
}
It provides methods that return dslContext (jooq) and jdbcTemplate (spring).

I'd be really happy if all my daos could automatically convert org.joda.time.DateTime to java.sql.Timestamp.

Let's create implicit conversion between those types.
trait Dao {
  implicit def asTimestamp(date: DateTime): Timestamp = new Timestamp(date.getMillis)

  def dslContext(): DSLContext = ???

  def jdbcTemplate(): NamedParameterJdbcTemplate = ???
}
That's all. Now when I pass DateTime to method that takes Timestamp the compiler knows that implicit conversion can be used. It calls asTimestamp method that returns Timestamp behind the hood. Programmer doesn't have to remember that jooq likes timestamps only.

Query that uses implicit conversion looks like that:
val sql = dslContext.select(sum(SEARCH_HISTORY_REPORT.SEARCH_SCORE).as(SCORE_SUM_ALIAS), SEARCH_HISTORY_REPORT.PRODUCT_ID)
      .from(SEARCH_HISTORY_REPORT)
      .where(SEARCH_HISTORY_REPORT.PRODUCT_ID.in(productIds))
      .and(SEARCH_HISTORY_REPORT.SEARCH_DATE_AND_HOUR.between(startDate).and(endDate))
      .groupBy(SEARCH_HISTORY_REPORT.PRODUCT_ID).getSQL(INLINED)
I guess it's perfect use case when implicit conversion can be used. The code is very consise and readable and it's natural to pass DateTime so noone has to remember about this conversion.