ResultSet.updateTime()导致值错误。

huangapple
未分类评论50阅读模式
英文:

ResultSet.updateTime() results in a wrong value

问题

所以，我在我的机器上有一个MySQL数据库的本地服务器，我正在尝试从一个Java程序直接更改数据库中一张表中的TIME列值。

我尝试的操作是从名为“Flights”的表中提取所有源为“Amsterdam”的行，存入一个名为rs的结果集，然后将结果行中的第二列时间更新为用户输入的值，然后更新数据库中的行。

这些是我想要更新其第二列时间的行：

35 11:38:08 9W8915 Amsterdam Cancelled 4
36 11:38:08 DL9644 Amsterdam Cancelled 4
37 11:38:08 G35516 Amsterdam Cancelled 4
...

代码部分：

System.out.println("输入时间：（HH:MM:SS）");
SimpleDateFormat sdf = new SimpleDateFormat("hh:mm:ss");
try {
    Time tme = new Time(sdf.parse(in.nextLine()).getTime());
    System.out.println(tme);
    while(rs.next()) {
        System.out.println(rs.getString(2));
        System.out.println("新时间：" + tme);
        rs.updateTime(2, tme);
        rs.updateRow();
    }
}

我面临的问题是更新后的时间是错误的。
我插入了“22:22:22”，似乎tme（Time对象）接收到了正确的时间（因为System.out.println(tme)打印出“22:22:22”），但数据库中的结果是错误的。
这是在使用tme = '22:22:22' 执行查询后的结果：

35 01:52:22 9W8915 Amsterdam Cancelled 4
36 01:52:22 DL9644 Amsterdam Cancelled 4
37 01:52:22 G35516 Amsterdam Cancelled 4
...

控制台输出：

输入时间：（HH:MM:SS）
22:22:22
22:22:22
11:38:08
新时间：22:22:22
11:38:08
新时间：22:22:22
11:38:08
新时间：22:22:22
...

正如你所看到的，第二列的时间已经被更新为“01:52:22”而不是“22:22:22”。
我不明白为什么第二列的时间已经变成了01:52:22，而tme却包含了22:22:22。
任何想法都会非常受欢迎！

英文:

So, I have a local server of MySQL database on my machine and I'm trying to change a TIME column values in one of the database's tables, straight from a Java program.

What I'm trying to do is to fetch all the lines from a table called "Flights" where the source is "Amsterdam" into a ResultSet called rs, then update the time inside the result row's 2nd column to a value the user inputs, and then update the row inside the DB.

Those are the rows I want to update their 2'nd column TIME:

35	11:38:08	9W8915	Amsterdam	Cancelled	4
36	11:38:08	DL9644	Amsterdam	Cancelled	4
37	11:38:08	G35516	Amsterdam	Cancelled	4
38	11:38:08	GA9081	Amsterdam	Cancelled	4
39	11:38:08	KL1017	Amsterdam	Cancelled	4
40	11:38:08	MF9651	Amsterdam	Cancelled	4
113	11:38:08	9W8916	Amsterdam	On time 14:25	4
114	11:38:08	G35524	Amsterdam	On time 14:25	4
115	11:38:08	KL1019	Amsterdam	On time 14:25	4
116	11:38:08	MF9653	Amsterdam	On time 14:25	4
144	11:38:08	AA6505	Amsterdam	On time 14:35	5
145	11:38:08	BA435	Amsterdam	On time 14:35	5
279	11:38:08	G35530	Amsterdam	On time 16:05	4
280	11:38:08	KL1021	Amsterdam	On time 16:05	4
281	11:38:08	MF9929	Amsterdam	On time 16:05	4

The code:

System.out.println("Enter time: (HH:MM:SS)"); 
   SimpleDateFormat sdf = new SimpleDateFormat("hh:mm:ss");
    try {
    	Time tme = new Time(sdf.parse(in.nextLine()).getTime());
    	System.out.println(tme);
    	while(rs.next()) {	
    		System.out.println(rs.getString(2));
    		System.out.println("New Time: " + tme);
    		rs.updateTime(2, tme);
    		rs.updateRow();
    	}
    }

The problem I'm facing is that the updated time is wrong.
I insert "22:22:22" and it seems that tme (Time object) recieves the correct time (because System.out.println(tme) prints "22:22:22") but the results inside the database are wrong.
That's the result AFTER executing the query with tme='22:22:22':

35	01:52:22	9W8915	Amsterdam	Cancelled	4
36	01:52:22	DL9644	Amsterdam	Cancelled	4
37	01:52:22	G35516	Amsterdam	Cancelled	4
38	01:52:22	GA9081	Amsterdam	Cancelled	4
39	01:52:22	KL1017	Amsterdam	Cancelled	4
40	01:52:22	MF9651	Amsterdam	Cancelled	4
113	01:52:22	9W8916	Amsterdam	On time 14:25	4
114	01:52:22	G35524	Amsterdam	On time 14:25	4
115	01:52:22	KL1019	Amsterdam	On time 14:25	4
116	01:52:22	MF9653	Amsterdam	On time 14:25	4
144	01:52:22	AA6505	Amsterdam	On time 14:35	5
145	01:52:22	BA435	Amsterdam	On time 14:35	5
279	01:52:22	G35530	Amsterdam	On time 16:05	4
280	01:52:22	KL1021	Amsterdam	On time 16:05	4
281	01:52:22	MF9929	Amsterdam	On time 16:05	4

The output from the console:

Enter time: (HH:MM:SS)
22:22:22
22:22:22
11:38:08
New Time: 22:22:22
11:38:08
New Time: 22:22:22
11:38:08
New Time: 22:22:22
11:38:08
New Time: 22:22:22
11:38:08
New Time: 22:22:22
11:38:08
New Time: 22:22:22
11:38:08
New Time: 22:22:22
11:38:08
New Time: 22:22:22
11:38:08
New Time: 22:22:22
11:38:08
New Time: 22:22:22
11:38:08
New Time: 22:22:22
11:38:08
New Time: 22:22:22
11:38:08
New Time: 22:22:22
11:38:08
New Time: 22:22:22
11:38:08
New Time: 22:22:22

As you can see, the time in the second column has been updated to "01:52:22" insted of "22:22:22".
I don't understand why the time in 2nd column has changed to 01:52:22 if tme contains 22:22:22.
Any ideas will be very appreciated!

答案1

得分: 0

java.time

使用现代的 java.time 类库来替代这些糟糕的传统日期时间类，可能可以避免你在这里遇到的时区问题。

使用与 JDBC 4.2 或更高版本兼容的 JDBC 驱动程序。这些较新版本的 JDBC 要求支持通过调用 setObject、updateObject 和 getObject 方法与数据库交换 java.time 对象。

你的输入字符串符合标准的 ISO 8601 格式。这些标准格式在 java.time 中用于解析/生成文本。因此无需指定格式化模式。

LocalTime lt = LocalTime.parse("22:22:22");
myResultSet.updateObject(…, lt);

检索数据。

LocalTime lt = myResultSet.getObject(…, LocalTime.class);

示例应用程序

以下是一个完整的示例应用程序，演示如何使用 LocalTime 对象来更新 TIME WITHOUT TIME ZONE 列的行。

该示例使用 H2 数据库引擎。H2 对于这样的演示非常方便。由于它是用 Java 编写的，可以通过 Maven 或类似的工具在演示应用程序中安装它。

首先，我们建立一个包含数据库连接信息的 DataSource 对象。我们使用 mem 来配置一个内存数据库，因为没有必要将此示例数据库持久化到存储中。

接下来，我们创建一个包含两列的表，一个类型为 UUID 的主键，以及一个用于存储时刻值的 TIME WITHOUT TIME ZONE 列。然后我们插入几行数据，时间分别为 10:10:10 和 11:11:11。我们将这些行输出到控制台以进行验证。然后我们更新这些行，都将值更新为 22:22:22。再次输出这些行以进行验证。

// 建立实现 `DataSource` 接口的对象。
JdbcDataSource ds = new JdbcDataSource();
ds.setURL("jdbc:h2:mem:localtime_example_db;DB_CLOSE_DELAY=-1");
// 将 `DB_CLOSE_DELAY` 设置为 `-1`，以在连接关闭后保持内存数据库的存在。
ds.setUser("scott");
ds.setPassword("tiger");

try (
        Connection conn = ds.getConnection();
)
{
    // 创建数据库表。
    String sql = "CREATE TABLE event_ ( \n" +
            "  pkey_ UUID NOT NULL DEFAULT RANDOM_UUID() PRIMARY KEY , \n" +
            "  when_ TIME WITHOUT TIME ZONE NOT NULL \n" +
            ");";
    try (
            Statement stmt = conn.createStatement();
    )
    {
        stmt.execute(sql);
    }

    // 插入行。
    sql = "INSERT INTO event_ ( when_ ) \n";
    sql += "VALUES ( ? ) \n";
    sql += ";";
    try (
            PreparedStatement pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
    )
    {

        // 插入第一行。
        LocalTime lt10 = LocalTime.parse("10:10:10");
        pstmt.setObject(1, lt10);
        pstmt.executeUpdate();

        ResultSet rs = pstmt.getGeneratedKeys();
        System.out.println("INFO - Reporting generated keys.");
        while (rs.next())
        {
            UUID uuid = rs.getObject(1, UUID.class);
            System.out.println("generated keys: " + uuid);
        }

        // 插入另一行。
        LocalTime lt11 = LocalTime.parse("11:11:11");
        pstmt.setObject(1, lt11);
        pstmt.executeUpdate();
    }

    // 输出所有行。
    System.out.println("INFO - Reporting all rows in table `event_`.");
    sql = "SELECT * FROM event_";
    try (
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
    )
    {
        while (rs.next())
        {
            UUID pkey = rs.getObject("pkey_", UUID.class);
            LocalTime lt = rs.getObject("when_", LocalTime.class);
            System.out.println("Event: " + pkey + " | " + lt);
        }
    }

    // 更新行。
    sql = "SELECT pkey_, when_  FROM event_ ;";
    try (
            Statement stmt = conn.createStatement(
                    ResultSet.TYPE_FORWARD_ONLY,
                    ResultSet.CONCUR_UPDATABLE
            );
            ResultSet rs = stmt.executeQuery(sql);
    )
    {
        LocalTime lt = LocalTime.parse("22:22:22");

        while (rs.next())
        {
            rs.updateObject("when_", lt);
            rs.updateRow();
        }
    }

    // 输出所有行。
    System.out.println("INFO - Reporting all rows in table `event_`.");
    sql = "SELECT * FROM event_";
    try (
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
    )
    {
        while (rs.next())
        {
            UUID pkey = rs.getObject("pkey_", UUID.class);
            LocalTime lt = rs.getObject("when_", LocalTime.class);
            System.out.println("Event: " + pkey + " | " + lt);
        }
    }
}
catch (SQLException e)
{
    e.printStackTrace();
}

运行后输出：

INFO - Reporting generated keys.

generated keys: 7554110f-a856-4945-9926-2d03ca819c93

INFO - Reporting all rows in table `event_`.

Event: 7554110f-a856-4945-9926-2d03ca819c93 | 10:10:10

Event: c05a7be2-3c09-4e57-8861-c05161138199 | 11:11:11

INFO - Reporting all rows in table `event_`.

Event: 7554110f-a856-4945-9926-2d03ca819c93 | 22:22:22

Event: c05a7be2-3c09-4e57-8861-c05161138199 | 22:22:22
英文:

java.time

Using the modern java.time classes instead of these terrible legacy date-time classes may avoid the time zone problem you seem to encounter here.

Use a JDBC driver compliant with JDBC 4.2 or later. These later versions of JDBC require support for exchanging java.time objects with the database by calling setObject, updateObject, and getObject.

Your input strings comply with the standard ISO 8601 format. These standard formats are used by default in java.time for parsing/generating text. So no need to specify a formatting pattern.

LocalTime lt = LocalTime.parse( "22:22:22" ) ;
myResultSet.updateObject( … , lt ) ;

Retrieval.

LocalTime lt = myResultSet.getObject( … , LocalTime.class ) ;

Example app

Here is an entire example app to demonstrate using a LocalTime object to update rows of a TIME WITHOUT TIME ZONE column.

This example uses the H2 Database Engine. H2 is handy for such demos. Being written in Java, it can be installed in your demo app via Maven or similar tool.

First we establish a DataSource object, containing our database connection information. We configure an in-memory database using mem, as there is no need to persist this example database to storage.

Next we create a table of two columns, a primary key of type UUID, and a TIME WITHOUT TIME ZONE column for the time-of-day value. Then we write a couple of rows, with times 10:10:10 and 11:11:11. We dump those rows to the console to verify. Then we update those rows, both getting the value 22:22:22. Again, we dump those rows to verify.

// Establish an object implementing `DataSource` interface.
JdbcDataSource ds = new JdbcDataSource();
ds.setURL( "jdbc:h2:mem:localtime_example_db;DB_CLOSE_DELAY=-1" );
// Set `DB_CLOSE_DELAY` to `-1` to keep in-memory database in existence after connection closes.
ds.setUser( "scott" );
ds.setPassword( "tiger" );

try (
        Connection conn = ds.getConnection() ;
)
{
    // Create database.
    String sql = "CREATE TABLE event_ ( \n" +
            "  pkey_ UUID NOT NULL DEFAULT RANDOM_UUID() PRIMARY KEY , \n" +
            "  when_ TIME WITHOUT TIME ZONE NOT NULL \n" +
            ");";
    try (
            Statement stmt = conn.createStatement() ;
    )
    {
        stmt.execute( sql );
    }

    // Insert row.
    sql = "INSERT INTO event_ ( when_ ) \n";
    sql += "VALUES ( ? ) \n";
    sql += ";";
    try (
            PreparedStatement pstmt = conn.prepareStatement( sql , Statement.RETURN_GENERATED_KEYS ) ;
    )
    {

        // Insert row.
        LocalTime lt10 = LocalTime.parse( "10:10:10" );
        pstmt.setObject( 1 , lt10 );
        pstmt.executeUpdate();

        ResultSet rs = pstmt.getGeneratedKeys();
        System.out.println( "INFO - Reporting generated keys." );
        while ( rs.next() )
        {
            UUID uuid = rs.getObject( 1 , UUID.class );
            System.out.println( "generated keys: " + uuid );
        }

        // Insert another row.
        LocalTime lt11 = LocalTime.parse( "11:11:11" );
        pstmt.setObject( 1 , lt11 );
        pstmt.executeUpdate();
    }


    // Dump all rows.
    System.out.println( "INFO - Reporting all rows in table `event_`." );
    sql = "SELECT * FROM event_";
    try (
            Statement stmt = conn.createStatement() ;
            ResultSet rs = stmt.executeQuery( sql ) ;
    )
    {
        while ( rs.next() )
        {
            UUID pkey = rs.getObject( "pkey_" , UUID.class );
            LocalTime lt = rs.getObject( "when_" , LocalTime.class );
            System.out.println( "Event: " + pkey + " | " + lt );
        }
    }

    // Update rows.
    sql = "SELECT pkey_ , when_  FROM event_ ;";
    try (
            Statement stmt = conn.createStatement(
                    ResultSet.TYPE_FORWARD_ONLY ,
                    ResultSet.CONCUR_UPDATABLE
            ) ;
            ResultSet rs = stmt.executeQuery( sql ) ;
    )
    {
        LocalTime lt = LocalTime.parse( "22:22:22" );

        while ( rs.next() )
        {
            rs.updateObject( "when_" , lt );
            rs.updateRow();
        }
    }

    // Dump all rows.
    System.out.println( "INFO - Reporting all rows in table `event_`." );
    sql = "SELECT * FROM event_";
    try (
            Statement stmt = conn.createStatement() ;
            ResultSet rs = stmt.executeQuery( sql ) ;
    )
    {
        while ( rs.next() )
        {
            UUID pkey = rs.getObject( "pkey_" , UUID.class );
            LocalTime lt = rs.getObject( "when_" , LocalTime.class );
            System.out.println( "Event: " + pkey + " | " + lt );
        }
    }
}
catch ( SQLException e )
{
    e.printStackTrace();
}

When run.

>INFO - Reporting generated keys.
>
>generated keys: 7554110f-a856-4945-9926-2d03ca819c93
>
>INFO - Reporting all rows in table event_.
>
>Event: 7554110f-a856-4945-9926-2d03ca819c93 | 10:10:10
>
>Event: c05a7be2-3c09-4e57-8861-c05161138199 | 11:11:11
>
>INFO - Reporting all rows in table event_.
>
>Event: 7554110f-a856-4945-9926-2d03ca819c93 | 22:22:22
>
>Event: c05a7be2-3c09-4e57-8861-c05161138199 | 22:22:22

huangapple
  • 本文由 发表于 2020年4月9日 22:10:23
  • 转载请务必保留本文链接：https://java.coder-hub.com/61123142.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定