通过 java.time LocalDateTime 比较/搜索 Anorm [英] Anorm compare/search by java.time LocalDateTime

问题描述

Anorm 2.5.2 和 java.time LocalDateTime 作为日期

Anorm 2.5.2 and java.time LocalDateTime as date

val users = SQL(
       s"SELECT * FROM user WHERE name={name} AND registered_date={registeredDate}").on(
        "name" -> user.name,
        "registeredDate" -> user.registeredDate
      ).executeQuery().as(userParser.*)

我发现即使我在数据库中有相同的 name1 和相同的 datetime(moment)，查询也会返回空的 users - 找不到/匹配它.

I found that even though I have a same name1 and same datetime(moment) in the database, the query returns empty users - can not find/match it.

所以它似乎无法按日期时间进行比较.为什么会这样?

我能够正确插入本地日期时间(请参阅:相关) 到数据库中(我的意思是 db 和 scala 类型之间的转换似乎已开启)-即我使用:

I was able to insert local-date-time correctly (see: related) into database (i mean It seems the conversion between db and scala type is on) - I.e. I use:

import anorm.ParameterMetaData._(扩展JavaTimeParameterMetaData)

import anorm.ParameterMetaData._ (that extends JavaTimeParameterMetaData)

我的用户解析器:

 val userParser: RowParser[User] = {
    get[Option[Long]]("user.id") ~
      get[String]("user.name") ~
      get[LocalDateTime]("user.registered_date") map {
      case id ~ name ~ registeredDate =>
        User(
          id  = id ,
          name = name,
          registeredDate = registeredDate
        )
    }
  }

我的用户:

case class User(id: Option[Long] = None,
                name: String,
                registeredDate: LocalDateTime
               )

推荐答案

好的.我的错.

关于MYSQL和TIMESTAMP的精度http://dev.mysql.com/doc/refman/5.5/zh/datetime.html

It is about MYSQL and precision of TIMESTAMP http://dev.mysql.com/doc/refman/5.5/en/datetime.html

所以，就我而言:

CREATE TABLE `user` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `registered_date` TIMESTAMP(6) NOT NULL,
  PRIMARY KEY (`id`));

此处:毫秒精度的时间戳: 如何在 MySQL 中保存它们

我只需用 TIMESTAMP(6) 声明它 - 这也将包括毫秒.

I had just to declare it with TIMESTAMP(6) - that would include milliseconds as well.

因为在我的测试中:

  "The User Repository" should "not create new user if it already exists" in {

    val someTimeAgo = LocalDateTime.now
    val userWithoultId1 = User(name = "name1", registeredDate = someTimeAgo)
    val maybeUserWithId1 = userRepository.findOrCreate(userWithoultId1)
    val maybeUserWithId2 = userRepository.findOrCreate(userWithoultId1)
    maybeUserWithId1.get.id should === (maybeUserWithId2.get.id)

LocalDateTime.now 显然也包括毫秒

LocalDateTime.now includes milieseconds as well, obviously

所以我仍然喜欢 Anorm.(只有我..):)

So I still love Anorm. (it is just me..) :)

这篇关于通过 java.time LocalDateTime 比较/搜索 Anorm的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！