Support for java.time (JSR-310) types with JDBC 4.2

[Sean Flanigan]

Has anyone written support for mapping directly between JSR-310 types, like java.time.LocalDate and java.time.LocalDateTime, and the corresponding JDBC types? (Section B.4 in the JDBC 4.2 spec.)

I've seen examples of code for mapping Joda Time types with Squeryl, but the code I've seen uses java.sql.Timestamp as an intermediate format.

With JDBC 4.2, it is possible to use PreparedStatement.setObject and ResultSet.getObject with java.time types instead of get/setTime and get/setTimestamp. (Sections B.5 and B.6 in the JDBC spec.) I'm not sure that sort of change is within the scope of Squeryl's type mapper support.

But perhaps there's another way of bypassing the java.sql.Timestamp conversion? Any ideas out there?

JDBC spec: https://download.oracle.com/otndocs/jcp/jdbc-4_2-mrel2-spec/index.html

Postgres ref: https://jdbc.postgresql.org/documentation/head/8-date-time.html

Thanks

Sean.

[Sean Flanigan]

To answer my own question: I'm pretty sure this isn't possible at present, because there doesn't seem to be a way to register your own instances of PrimitiveJdbcMapper without modifying Squeryl.

For now, I'm making do with a NonPrimitiveJdbcMapper which relies on SquerylEntryPoint.sqlDateTEF (and thus on java.sql.Date, unfortunately).

I don't have time to change Squeryl right now, but if someone wants to try it sometime, you could try adding this (warning: completely untested) code to org.squeryl.internals.FieldMapper:

  implicit val localDateTEF =
//    new TypedExpressionFactory[LocalDate,TDate] with PrimitiveJdbcMapper[LocalDate] {
    new TypedExpressionFactory[LocalDate,LocalDate] with PrimitiveJdbcMapper[LocalDate] {
      val sample = LocalDate.now()
      val defaultColumnLength = -1
      def extractNativeJdbcValue(rs: ResultSet, i: Int) = rs.getObject(i, classOf[LocalDate])
    }
  implicit val optionLocalDateTEF =
//    new TypedExpressionFactory[Option[LocalDate],TOptionDate] with DeOptionizer[LocalDate, LocalDate, TDate, Option[LocalDate], TOptionDate] {
    new TypedExpressionFactory[Option[LocalDate],TOptionDate] with DeOptionizer[LocalDate, LocalDate, LocalDate, Option[LocalDate], TOptionDate] {
      val deOptionizer = localDateTEF
  }

  implicit val localDateTimeTEF =
    new TypedExpressionFactory[LocalDateTime,TTimestamp] with PrimitiveJdbcMapper[LocalDateTime] {
      val sample = LocalDateTime.now()
      val defaultColumnLength = -1
      def extractNativeJdbcValue(rs: ResultSet, i: Int) = rs.getObject(i, classOf[LocalDateTime])
    }
  implicit val optionLocalDateTimeTEF =
    new TypedExpressionFactory[Option[LocalDateTime],TOptionTimestamp] with DeOptionizer[LocalDateTime, LocalDateTime, TTimestamp, Option[LocalDateTime], TOptionTimestamp] {
      val deOptionizer = localDateTimeTEF
    }

  implicit val zonedDateTimeTEF =
    new TypedExpressionFactory[ZonedDateTime,TTimestamp] with PrimitiveJdbcMapper[ZonedDateTime] {
      val sample = ZonedDateTime.now(ZoneOffset.UTC)
      val defaultColumnLength = -1
      def extractNativeJdbcValue(rs: ResultSet, i: Int) = rs.getObject(i, classOf[ZonedDateTime])
    }
  implicit val optionZonedDateTimeTEF =
    new TypedExpressionFactory[Option[ZonedDateTime],TOptionTimestamp] with DeOptionizer[ZonedDateTime, ZonedDateTime, TTimestamp, Option[ZonedDateTime], TOptionTimestamp] {
      val deOptionizer = zonedDateTimeTEF
    }

You could add the TEFs above to org.squeryl.internals.FieldMapper.PrimitiveTypeSupport, and change FieldMapper.initialize method to call "register" for each of the TEF instances above. Come to think of it, for consistency the implicit vals and defs above should probably not be implicits, but should be redeclared as implicits in PrimitiveTypeMode, similar to implicit val sqlDateTEF = PrimitiveTypeSupport.sqlDateTEF and implicit def dateToTE(s: Date) = dateTEF.create(s).

--
You received this message because you are subscribed to the Google Groups "Squeryl" group.
To unsubscribe from this group and stop receiving emails from it, send an email to squeryl+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/squeryl/8e77e337-486d-4246-8bb3-6f0caf1b20ce%40googlegroups.com.

[Sean Flanigan]

I have put my unfinished and untested attempt on GitHub here: [1]  As the TODO comment says, the column-type mappings for the new date/time types are bound to be wrong in some or all cases.

I was able to use `+ publishM2` from the sbt prompt to make the modified Squeryl available to my Maven project, but then I ran into the problem that Derby's JDBC component doesn't actually support the JSR-310 classes [1], so I won't be able to use it for my use case unless I switch from Derby to H2.

Anyway, it's on GitHub if someone wants to use it as a starting point for another attempt.

[1]: https://github.com/seanf/squeryl/commits/jsr310 

[2]: https://issues.apache.org/jira/browse/DERBY-6445

[Kiran Pathak]

Very nice! Thanks. I hope this project gets more eyes. I really like Squeryl.

To unsubscribe from this group and stop receiving emails from it, send an email to squ...@googlegroups.com.