自定义LocalDateTime解析器在Slick 3

Question

我正在将一堆java.sql.Timestamp列从我的Slick 3模型转换为LocalDateTime。我的数据库后端是MySQL 8，我要转换的列是TIMESTAMP或DATETIME。

我遇到MySQL以格式yyyy-MM-dd HH:mm:ss返回日期的问题，而LocalDateTime.parse期望yyyy-MM-dd'T'HH:mm:ss。这会导致运行时错误，如java.time.format.DateTimeParseException: Text '2022-12-05 08:01:08' could not be parsed at index 10。

它发现可以通过使用自定义格式化器来解决这个问题，像这样:

private val formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")
val localDateTimeMapper: BaseColumnType[LocalDateTime] = MappedJdbcType.base[LocalDateTime, String](
ldt => ldt.format(formatter),
s => LocalDateTime.parse(s, formatter)
)

通常我会将格式化器定义为implicit，但它会在模型中创建一个编译错误:No implicits found for parameter tt: TypedType[LocalDateTime]。显式应用格式化器对column[LocalDateTime]工作得很好，但对column[Option[LocalDateTime]]不起作用(导致Type mismatch, required TypedType[Option[LocalDateTime]])。

class Users(tag: Tag) extends Table[User](tag, "users") {
def uuid           = column[UUID]("uuid", O.PrimaryKey)
def name           = column[String]("name")
def email          = column[String]("email")
def lastSignedInAt = column[Option[LocalDateTime]]("last_signed_in_at")(localDateTimeMapper)
def createdAt      = column[LocalDateTime]("created_at")(localDateTimeMapper)
override def * = (uuid, name, email, lastSignedInAt, createdAt) <> (User.tupled, User.unapply)
}

其他自定义类型(如enum)使用隐式格式化器方法工作没有问题，但我怀疑这里的问题是Slick有一个我试图覆盖的LocalDateTime-映射器。据我所知，Slick希望将LocalDateTime对象存储为VARCHAR而不是日期类型，但我不想转换数据库列。

关于如何使我的自定义格式化器工作(或使用在Slick内置的功能)，以允许LocalDateTime与MySQL的日期类型工作的任何建议?

Answer 1

我最终找到了一种方法，通过扩展Slick的MySQLProfile:

package lib
import slick.jdbc.JdbcProfile
import java.sql.PreparedStatement
import java.sql.ResultSet
import java.time.LocalDateTime
import java.time.format.DateTimeFormatter
trait ExMySQLProfile extends JdbcProfile with slick.jdbc.MySQLProfile { driver =>
private val localDateTimeFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")
override val columnTypes = new ExJdbcTypes
class ExJdbcTypes extends super.JdbcTypes {
@inline
private[this] def stringToMySqlString(value: String): String = {
value match {
case null => "NULL"
case _ =>
val sb = new StringBuilder
sb.append(''')
for (c <- value) c match {
case ''' => sb.append("\'")
case '"'  => sb.append("\"")
case 0    => sb.append("\0")
case 26   => sb.append("\Z")
case 'b' => sb.append("\b")
case 'n' => sb.append("\n")
case 'r' => sb.append("\r")
case 't' => sb.append("\t")
case '\' => sb.append("\\")
case _    => sb.append(c)
}
sb.append(''')
sb.toString
}
}
/**
* Override LocalDateTime handler, to parse values as we expect them.
*
* The default implementation in Slick does not support TIMESTAMP or DATETIME
* columns, but expects timestamps to be stored as VARCHAR
*/
override val localDateTimeType: LocalDateTimeJdbcType = new LocalDateTimeJdbcType {
override def sqlType: Int = java.sql.Types.TIMESTAMP
override def setValue(v: LocalDateTime, p: PreparedStatement, idx: Int): Unit = {
p.setString(idx, if (v == null) null else v.toString)
}
override def getValue(r: ResultSet, idx: Int): LocalDateTime = {
r.getString(idx) match {
case null          => null
case iso8601String => LocalDateTime.parse(iso8601String, localDateTimeFormatter)
}
}
override def updateValue(v: LocalDateTime, r: ResultSet, idx: Int) = {
r.updateString(idx, if (v == null) null else v.format(localDateTimeFormatter))
}
override def valueToSQLLiteral(value: LocalDateTime): String = {
stringToMySqlString(value.format(localDateTimeFormatter))
}
}
}
}
trait MySQLProfile extends ExMySQLProfile {}
object MySQLProfile extends MySQLProfile

在我的application.conf中，我已经配置了配置文件:

slick.dbs.default {
profile = "lib.MySQLProfile$"
}