代码之家  ›  专栏  ›  技术社区  ›  Steven Chong

最佳实践:如何通过JDBC在sql.date中检查特定的java.util.calendar/date?

  •  4
  • Steven Chong  · 技术社区  · 16 年前

    这是我昨天以来一直在努力的事情。

    我有预约要保存在数据库中。它们由日期和时间组成,比如:

    01.02.1970 14:00
    

    (德语格式,在美国,我想应该是1970年1月2日下午2:00)。

    第一个想法:将其保存为sql.date!

    所以我创建了一个表:

    CREATE TABLE appointments (id NUMBER(10) NOT NULL, datum DATE NOT NULL, PRIMARY KEY id)
    

    到现在为止,一直都还不错。

    现在我写了一个DAO来保存我通过网络表单输入的约会。之后我想写一个单元测试,检查预约是否保存正确。

    相关试验部分如下:

    JdbcDao myDao = new JdbcDao();
    myDao.setDataSource(jdbcTemplate.getDataSource());      
    myDao.saveAppointment(appointmentModel);
    
    // Not needed but I saw, the appointment is saved in the database
    setComplete();
    
    // And now for the (sorry for the harsh words) pain in the *** part
    
    String sql = "SELECT id, datum FROM appointments WHERE datum ... // <--
    

    <--:这只是其中的一部分,我不知道输入什么来查看特定日期是否已经在数据库中。

    我试过:

    datum = ?
    

    以下呼叫

    jdbcTemplate.query(sql, args, rowMapper);
    

    args数组中有java.util.date、java.util.calendar或java.lang.string(“dd.mm.yyyy”),它保存的参数替换了?在准备好的声明中。

    当然,这是个坏主意,因为数据库

    DD.MM.YYYY HH:MI 
    

    在表格行中(dd=day,mm=month,yy=year,hh=hour,mi=minute)。

    因此,我找到了在SQL命令之间重构(并尝试在args数组中传递所有类型的格式、输入、字符串、对象)的select命令:

    String sql = "SELECT id, datum FROM appointments WHERE datum BETWEEN to_date( ?, 'DD.MM.YYYY HH24:MI:SS') AND to_date( ?, 'DD.MM.YYYY HH24:MI:SS')
    

    如果我直接通过一个SQL工具输入它,它会像其他许多尝试一样工作,例如。

    SELECT * FROM appointments WHERE datum BETWEEN to_date('01.02.1970 00:00:00', 'DD.MM.YYYY HH24:MI:SS') AND to_date('01.02.1970 23:59:59', 'DD.MM.YYYY HH24:MI:SS')
    

    输出,例如:

    ID                      DATUM                   
    ----------------------  -------------------
    70                      01.02.1970 11:11:11
    

    但是Java中的JDBC调用总是导致一个空结果集。

    长话短说:

    如果一个Java对象表示的日期存在于数据库中的一个SQL Load列中,那么从给定的时间起,查询数据库的最佳实践是什么?

    2 回复  |  直到 8 年前
        1
  •  1
  •   Community CDub    8 年前

    这个 Answer by Sacre 是正确的,但过时了。现代的方法是使用java.time类。

    DR

    StringBuild sql = new StringBuilder()
    sql.append( "SELECT id_, datum_ " );
    sql.append( "FROM appointments_ " );
    sql.append( "WHERE datum_ >= ? " );  // Start is *inclusive*…
    sql.append( "AND datum_ < ? ; " );   // …while end is *exclusive*. (Half-Open approach)
    

    通过一对 Instant 物体。

    pstmt.setObject( 1 , start ) ;  // Pass `java.time.Instant` object directly via `PreparedStatement::setObject` method.
    pstmt.setObject( 2 , stop ) ;
    

    使用对象而不是字符串

    避免使用字符串在数据库中获取/存储日期时间值。使用日期时间对象作为日期时间值。Java和JDBC对日期时间对象有着丰富的支持。

    使用JavaTimes

    这个 java.sql.Date 类的目的是表示一个没有一天中的时间和没有时区的仅日期值。事实上,它携带了这两种额外的东西,但也不装作。现代的替代品是 LocalDate . 这个 LocalDate 类表示没有时间和时区的仅日期值。

    但是 Java.Q.L.DATE (和) 本地日期 是否使用了错误的类,因为您的列似乎未定义为 仅日期 类型,但作为 日期时间 类型。

    这里的关键问题被你的问题忽略了:时区。一 时区对于确定日期至关重要 . 在任何一个特定的时刻,日期在全球各地各不相同。例如,在 Paris France 是新的一天,而昨天仍然是 Montréal Québec .

    指定一个 proper time zone name 格式为 continent/region ,如 America/Montreal , Africa/Casablanca Pacific/Auckland . 切勿使用3-4字母缩写,如 EST IST 因为它们是 真正的时区,不标准,甚至不独特!.

    ZoneId z = ZoneId.of( "America/Montreal" );
    LocalDate today = LocalDate.now( z );
    

    关于数据库中的时区

    • 您的数据库几乎可以肯定地将日期时间值存储为包含时区或从UTC信息偏移的日期时间值,这些日期时间值的类型如SQL标准 TIMESTAMP WITH TIME ZONE .
    • 如果使用 TIMESTAMP WITHOUT TIME ZONE ,则这些值没有时区或与UTC信息的偏移量,也不表示时间线上的实际时刻,但可能适用于未来足够远的约会,将其存储为分区值会带来与政治家们冲突的风险,从而改变时区定义,如调整/采用/放弃夏令时时间。

    如果存储为分区值,则需要确定所需时区中日期的开始和结束时刻,然后将该对点转换为UTC值,并查询数据库中该对UTC值。

    通常,定义时间跨度的日期时间工作的最佳实践(如此处所需的一天的持续时间)是使用半开放方法,其中跨度的开始是 包容的 当结尾是 排他性 .所以一天从一天中的第一个时刻开始,一直到但不包括 下列的 一天。

    不要假定一天的第一个时刻是00:00:00。异常情况,例如夏令时(DST)意味着一天可能从01:00:00开始。让java.time确定一天中的第一个时刻。

    ZonedDateTime startZdt = localDate.atStartOfDay( z );
    ZonedDateTime stopZdt = localDate.plusDays( 1 ).atStartOfDay( z );
    

    还要记住,从开始到停止的时间间隔不一定是24小时。异常,如DST,意味着一天可能运行23或25小时,或其他一些时间长度。

    通过提取将其转换为UTC 瞬间 对象。这个 Instant 类表示时间线上的某一时刻 UTC 决议为 nanoseconds (小数点后最多九(9)位)。

    Instant start = startZdt.toInstant();  // Convert to UTC value.
    Instant stop = stopZdt.toInstant();
    

    要执行SQL查询,请执行 使用 BETWEEN .该命令不使用半开放式方法,而是使用封闭式方法,其中开始和结束都包含在内。

    StringBuild sql = new StringBuilder()
    sql.append( "SELECT id_, datum_ " );
    sql.append( "FROM appointments_ " );
    sql.append( "WHERE datum_ >= ? " );  // Start is *inclusive*…
    sql.append( "AND datum_ < ? ; " );   // …while end is *exclusive*. (Half-Open approach)
    PreparedStatement pstmt = conn.prepareStatement( sql.toString() );
    

    指定您的一对 瞬间 对象作为SQL语句要使用的参数。

    pstmt.setObject( 1 , start ) ;  // Pass java.time.Instant object directly via `setObject` method.
    pstmt.setObject( 2 , stop ) ;
    

    提要栏:根据SQL规范的承诺,在所有SQL标识符后面加上一个下划线,以避免与1000个保留字发生任何名称冲突。

    注意我们是怎么打电话的 setObject 直接使用jdbc中的java.time类型。

    如果你 JDBC driver 还不支持 JDBC 4.2 或者稍后,您必须返回到使用传统java.sql类型。在这种情况下,我们需要 java.sql.Timestamp . 但这样做只需简单地“尽量留在java.time中”。

    pstmt.setTimestamp( 1 , java.sql.Timestamp.from( start ) ) ;
    pstmt.setTimestamp( 2 , java.sql.Timestamp.from( stop ) ) ;
    

    关于JavaTimes

    这个 java.time 框架是在Java 8和之后构建的。这些阶级取代了麻烦的旧阶级。 legacy 日期时间类,如 java.util.Date , Calendar 和; SimpleDateFormat .

    这个 Joda-Time 项目,现在 maintenance mode ,建议迁移到 Java.时间 类。

    要了解更多信息,请参阅 Oracle Tutorial . 以及搜索堆栈溢出以获得许多示例和解释。规格为 JSR 310 .

    在哪里获取java.time类?

    这个 ThreeTen-Extra Project使用其他类扩展java.time。这个项目是将来可能添加到java.time的一个试验场。您可以在这里找到一些有用的类,例如 Interval , YearWeek , YearQuarter more .

        2
  •  5
  •   user7094    16 年前

    像这样:

    PreparedStatement ps = conn.prepareCall("SELECT * FROM table WHERE someDate = ?");
    ps.setDate(1, javaDate)
    

    (从内存中,所以语法可能不太正确)

    但是,您必须将java.util.date对象转换为java.sql.date对象。

    这相当简单:

    java.sql.Date myDate = new java.sql.Date(oldDate.getTime());
    

    在哪里? oldDate 是java.util.date对象。