Java 未将准确日期存储到 mysql 数据库

Question

我正在编写一个程序来将字符串转换为 java.util.Date 对象，然后将其存储在数据库中，该程序运行正确，没有错误。但是在 MySql 中，日期没有正确存储，java 正在存储日期，这是相对于写入字符串的日期的昨天日期。示例 - 如果我写“2018-11-10”，它会存储“2018-11-09”。我的代码-

import java.sql.Connection;

import java.sql.Date;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.SQLException;

public class Demo {

    public static void main(String[] args) throws ClassNotFoundException, SQLException {

        Connection con;

        Class.forName("com.mysql.cj.jdbc.Driver");

        Date date = Date.valueOf("2018-11-20"); 

        con = DriverManager.getConnection("jdbc:mysql://localhost:9090/ProjectScheduler", "root", "tiger");

        PreparedStatement prepare = con.prepareStatement("insert into SchedulerDB(Date) values(?)");

        prepare.setDate(1, date);

        prepare.executeUpdate();

    }

}

在 MySql 工作台中，我将表创建为

create table SchedulerDB(Date date)

当我运行选择查询以查看表中的数据时

select * from SchedulerDB

然后在表中的“日期”列-“SchedulerDB”下，我得到了 2018-11-19。但我想要 2018-11-20，因为这是我用 java 代码写的。请帮我解决这个问题。

Answer 1

myPreparedStatement.setObject( &nbsp;&nbsp;&nbsp;&nbsp;…&nbsp;, &nbsp;&nbsp;&nbsp;&nbsp;LocalDate.parse(&nbsp;"2018-11-20"))&nbsp;;细节您正在使用设计非常糟糕的java.sql.Date课程。不幸的是，这个类是一个子类，java.util.Date尽管文档奇怪地告诉我们忽略继承这一事实。因此，虽然java.sql.Date&nbsp;假装仅代表一个日期，但它实际上是一个时刻，在某个区域将一天中的时间设置为午夜，然后调整为 UTC。这是一个糟糕的设计，也是从不使用遗留日期时间类的众多原因之一。对于DATESQL 中的列，请改用现代LocalDate类。LocalDate在LocalDate类真正代表一个日期，只值，没有时间的天，没有时区或偏移从-UTC。对于类型类似于 SQL 标准DATE类型的数据库列，请使用LocalDateJava 中的&nbsp;类。LocalDate&nbsp;ld&nbsp;=&nbsp;LocalDate.parse(&nbsp;"2018-11-20"&nbsp;);JDBC 4.2从 JDBC 4.2 开始，我们可以直接与数据库交换java.time对象。myPreparedStatement.setObject(&nbsp;…&nbsp;,&nbsp;ld&nbsp;)&nbsp;;恢复：LocalDate&nbsp;ld&nbsp;=&nbsp;myResultSet.getObject(&nbsp;…&nbsp;,&nbsp;LocalDate.class&nbsp;)&nbsp;;由于不涉及时区，您检索到的值将与2018-11-20您存储的值相同。示例应用这是一个使用H2 数据库引擎的完整示例应用程序。作为演示，我将它设置为写入内存数据库，而不是持久存储到实际存储中。我们创建一个只有一个表的数据库，有两列：一个名为的序号主键&nbsp;pkey_DATE名为的列when_我们插入几个LocalDate对象，然后检索要转储到控制台的行。package com.basilbourque.example;import java.sql.*;import java.time.LocalDate;import java.util.List;public class EventDate {&nbsp; &nbsp; public static void main ( String[] args ) {&nbsp; &nbsp; &nbsp; &nbsp; EventDate app = new EventDate();&nbsp; &nbsp; &nbsp; &nbsp; app.doIt();&nbsp; &nbsp; }&nbsp; &nbsp; private void doIt ( ) {&nbsp; &nbsp; &nbsp; &nbsp; final String driverName = "org.h2.Driver";&nbsp; &nbsp; &nbsp; &nbsp; final String catalogName = "event_demo_db";&nbsp; &nbsp; &nbsp; &nbsp; final String jdbcPath = "jdbc:h2:mem:" + catalogName + ";DB_CLOSE_DELAY=-1";&nbsp; // Set delay to keep in-memory database even after last connection closed.&nbsp; &nbsp; &nbsp; &nbsp; // Verify JDBC driver.&nbsp; &nbsp; &nbsp; &nbsp; try {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Class.forName( driverName );&nbsp; &nbsp; &nbsp; &nbsp; } catch ( ClassNotFoundException e ) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; e.printStackTrace();&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; // Connect, and create database.&nbsp; &nbsp; &nbsp; &nbsp; try (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Connection conn = DriverManager.getConnection( jdbcPath ) ;&nbsp; &nbsp; &nbsp; &nbsp; ) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; String sql = null;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; // Create table.&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; try ( Statement stmt = conn.createStatement() ; ) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; sql = "CREATE TABLE " + "event_" + " ( \n" +&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; " pkey_ IDENTITY PRIMARY KEY , \n" +&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; " when_ DATE NOT NULL \n" +&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ") ; \n";&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; System.out.println( "TRACE - SQL:\n" + sql );&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; stmt.execute( sql );&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; System.out.println( "TRACE - Created table `event_`." );&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; // Add rows&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; sql = "INSERT INTO event_ ( when_ ) \n" +&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "VALUES ( ? ) " +&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "; ";&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; List < LocalDate > dates = List.of( LocalDate.parse( "2018-11-10" ) , LocalDate.parse( "2018-12-31" ) );&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; System.out.println( "Inserting list of LocalDate objects: " + dates );&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; try (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; PreparedStatement ps = conn.prepareStatement( sql ) ;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; for ( LocalDate localDate : dates ) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ps.setObject( 1 , localDate );&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ps.executeUpdate();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; // Retrieve rows&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;sql = "SELECT * FROM " + "event_" + " ;";&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; try (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Statement stmt = conn.createStatement() ;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ResultSet rs = stmt.executeQuery( sql ) ;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; while ( rs.next() ) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; int pkey = rs.getInt( "pkey_" );&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; LocalDate when = rs.getObject( "when_" , LocalDate.class );&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; System.out.println( "Row pkey_: " + pkey + " when_: " + when );&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; } catch ( SQLException e ) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; e.printStackTrace();&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; }}跑的时候。插入 LocalDate 对象列表：[2018-11-10, 2018-12-31]行 pkey_: 1 when_: 2018-11-10行 pkey_: 2 when_: 2018-12-31