插入&从/或从SQL数据库（如H2）获取java.time.LocalDate对象 [英] Insert & fetch java.time.LocalDate objects to/from an SQL database such as H2

问题描述

如何插入和获取 java。时间类型，例如 LocalDate 通过 JDBC 到SQL数据库例如 H2数据库引擎？

How to insert and fetch java.time types such as LocalDate via JDBC to an SQL database such as the H2 Database Engine?

使用 PreparedStatement :: setDate 和 ResultSet :: getDate 适用于旧版 java.sql.Date 类型。我想避免使用这些麻烦的旧日期时间类。

The old way using PreparedStatement::setDate and ResultSet::getDate works for the legacy java.sql.Date type. I want to avoid using these troublesome old date-time classes.

通过 JDBC驱动程序？

推荐答案

我们有两条通过JDBC交换java.time对象的路径：

We have two routes to exchanging java.time objects through JDBC:

• JDBC 4.2兼容驱动程序
  如果您的JDBC驱动程序符合 JDBC 4.2规范或之后，您可以直接处理java.time对象。


• 旧驱动程序，在JDBC 4.2之前
  如果您的JDBC驱动程序尚未符合JDBC 4.2或更高版本，然后您将您的java.time对象简要地转换为它们的等效java.sql类型，反之亦然。查看添加到旧类的新转换方法。

• JDBC 4.2 compliant drivers
  If your JDBC driver complies with the JDBC 4.2 specification or later, you can deal directly with the java.time objects.
• Older drivers, before JDBC 4.2
  If your JDBC driver does not yet comply with JDBC 4.2 or later, then you briefly convert your java.time objects to their equivalent java.sql type or vice-versa. Look to new conversion methods added to the old classes.

旧的日期时间类，例如 java。 util.Date ， java.util.Calendar ，以及相关的 java.sql 类例如 java.sql.Date 是一个可怕的混乱。它们采用设计糟糕的黑客攻击方式构建，已被证明存在缺陷，麻烦和混乱。尽可能避免使用它们。现在取代了java.time类。

The legacy date-time classes such as java.util.Date, java.util.Calendar, and the related java.sql classes such as java.sql.Date are an awful mess. Built with a poorly-designed hacked approach, they have proven to be flawed, troublesome, and confusing. Avoid them whenever possible. Now supplanted by the java.time classes.

内置的JDBC驱动程序H2（截至2017-03）似乎符合JDBC 4.2。

The built-in JDBC driver for H2 (as of 2017-03) appears to comply with JDBC 4.2.

合规驱动程序现在知道java.time类型。但是，不是添加 setLocalDate / getLocalDate 各种方法，JDBC委员会添加了 setObject / getObject 方法。

Compliant drivers are now aware of the java.time types. But rather than adding setLocalDate/getLocalDate sorts of methods, the JDBC committee added setObject/getObject methods.

要将数据发送到数据库，只需将java.time对象传递给 PreparedStatement :: setObject 。传递的参数的Java类型由驱动程序检测并转换为适当的SQL类型。 Java LocalDate 将转换为SQL DATE 类型。请参阅 JDBC Maintenance Release 4.2 第22节PDF文档获取这些映射的列表。

To send data to the database, simply pass your java.time object to PreparedStatement::setObject. The Java type of your passed argument is detected by the driver and converted to the appropriate SQL type. A Java LocalDate is converted to a SQL DATE type. See section 22 of JDBC Maintenance Release 4.2 PDF document for a list of these mappings.

myPreparedStatement.setObject ( 1 , myLocalDate ); // Automatic detection and conversion of data type.

要从数据库中检索数据，请调用 ResultSet :: getObject 。我们可以传递一个额外的参数，而不是强制生成 Object 对象， Class 。通过指定预期的类，我们获得类型安全检查并通过您的 IDE 和编译器。

To retrieve data from the database, call ResultSet::getObject. Rather than casting the resulting Object object, we can pass an extra argument, the Class of the data type we expect to receive. By specifying the expected class, we gain type-safety checked and verified by your IDE and compiler.

LocalDate localDate = myResultSet.getObject ( "my_date_column_" , LocalDate.class ); 

这是一个完整的工作示例应用程序，显示如何插入和选择 LocalDate 进入H2数据库的值。

Here is an entire working example app showing how to insert and select LocalDate values into an H2 database.

package com.example.h2localdate;

import java.sql.*;
import java.time.LocalDate;
import java.time.ZoneId;
import java.util.UUID;

/**
 * Hello world!
 */
public class App {
    public static void main ( String[] args ) {
        App app = new App ( );
        app.doIt ( );
    }

    private void doIt ( ) {
        try {
            Class.forName ( "org.h2.Driver" );
        } catch ( ClassNotFoundException e ) {
            e.printStackTrace ( );
        }

        try (
            Connection conn = DriverManager.getConnection ( "jdbc:h2:mem:trash_me_db_" ) ;
            Statement stmt = conn.createStatement ( ) ;
        ) {
            String tableName = "test_";
            String sql = "CREATE TABLE " + tableName + " (\n" +
                "  id_ UUID DEFAULT random_uuid() PRIMARY KEY ,\n" +
                "  date_ DATE NOT NULL\n" +
                ");";
            stmt.execute ( sql );

            // Insert row.
            sql = "INSERT INTO test_ ( date_ ) " + "VALUES (?) ;";
            try ( PreparedStatement preparedStatement = conn.prepareStatement ( sql ) ; ) {
                LocalDate today = LocalDate.now ( ZoneId.of ( "America/Montreal" ) );
                preparedStatement.setObject ( 1, today.minusDays ( 1 ) );  // Yesterday.
                preparedStatement.executeUpdate ( );
                preparedStatement.setObject ( 1, today );                  // Today.
                preparedStatement.executeUpdate ( );
                preparedStatement.setObject ( 1, today.plusDays ( 1 ) );   // Tomorrow.
                preparedStatement.executeUpdate ( );
            }

            // Query all.
            sql = "SELECT * FROM test_";
            try ( ResultSet rs = stmt.executeQuery ( sql ) ; ) {
                while ( rs.next ( ) ) {
                    //Retrieve by column name
                    UUID id = rs.getObject ( "id_", UUID.class );  // Pass the class to be type-safe, rather than casting returned value.
                    LocalDate localDate = rs.getObject ( "date_", LocalDate.class );  // Ditto, pass class for type-safety.

                    //Display values
                    System.out.println ( "id_: " + id + " | date_: " + localDate );
                }
            }

        } catch ( SQLException e ) {
            e.printStackTrace ( );
        }
    }
}

运行时。

id_：e856a305-41a1-45fa-ab69-cfa676285461 | date_：2017-03-26

id_: e856a305-41a1-45fa-ab69-cfa676285461 | date_: 2017-03-26

id_：a4474e79-3e1f-4395-bbba-044423b37b9f | date_：2017-03-27

id_: a4474e79-3e1f-4395-bbba-044423b37b9f | date_: 2017-03-27

id_：5d47bc3d-ebfa-43ab-bbc2-7bb2313b33b0 | date_：2017-03-28

id_: 5d47bc3d-ebfa-43ab-bbc2-7bb2313b33b0 | date_: 2017-03-28

不合规的司机

H2，上面显示的代码是我建议你采取的道路。但是，对于其他不符合JDBC 4.2的数据库，我可以向您展示如何在java.time和java.sql类型之间进行简单转换。这种转换代码肯定会在H2上运行，如下所示，但这样做很愚蠢，因为我们现在有更简单的方法。

Non-compliant drivers

For H2, the code shown above is the road I recommend you take. But FYI, for other databases that do not comply yet with JDBC 4.2, I can show you how to briefly convert between java.time and java.sql types. This kind of conversion code certainly runs on H2 as I show below, but doing so is silly now that we have the simpler approach shown above.

将数据发送到数据库，将 LocalDate 转换为 java.sql.Date 使用添加到旧类的新方法的对象。

To send data to the database, convert your LocalDate to a java.sql.Date object using new methods added to that old class.

java.sql.Date mySqlDate = java.sql.Date.valueOf( myLocalDate );

然后转到 PreparedStatement :: setDate 方法。

preparedStatement.setDate ( 1, mySqlDate );

要从数据库中检索，请调用 ResultSet :: getDate 获取 java.sql.Date 对象。

To retrieve from the database, call ResultSet::getDate to obtain a java.sql.Date object.

java.sql.Date mySqlDate = myResultSet.getDate( 1 );

然后立即转换为 LocalDate 。您应该尽可能简短地处理java.sql对象。使用java.time类型完成所有业务逻辑和其他工作。

Then immediately convert to a LocalDate. You should handle the java.sql objects as briefly as possible. Do all your business logic and other work using only the java.time types.

LocalDate myLocalDate = mySqlDate.toLocalDate();

这是一个完整的示例应用程序，显示了java.time类型的java.sql类型的使用H2数据库。

Here is an entire example app showing this use of java.sql types with java.time types in an H2 database.

package com.example.h2localdate;

import java.sql.*;
import java.time.LocalDate;
import java.time.ZoneId;
import java.util.UUID;

/**
 * Hello world!
 */
public class App {
    public static void main ( String[] args ) {
        App app = new App ( );
        app.doIt ( );
    }

    private void doIt ( ) {
        try {
            Class.forName ( "org.h2.Driver" );
        } catch ( ClassNotFoundException e ) {
            e.printStackTrace ( );
        }

        try (
            Connection conn = DriverManager.getConnection ( "jdbc:h2:mem:trash_me_db_" ) ;
            Statement stmt = conn.createStatement ( ) ;
        ) {
            String tableName = "test_";
            String sql = "CREATE TABLE " + tableName + " (\n" +
                "  id_ UUID DEFAULT random_uuid() PRIMARY KEY ,\n" +
                "  date_ DATE NOT NULL\n" +
                ");";
            stmt.execute ( sql );

            // Insert row.
            sql = "INSERT INTO test_ ( date_ ) " + "VALUES (?) ;";
            try ( PreparedStatement preparedStatement = conn.prepareStatement ( sql ) ; ) {
                LocalDate today = LocalDate.now ( ZoneId.of ( "America/Montreal" ) );
                preparedStatement.setDate ( 1, java.sql.Date.valueOf ( today.minusDays ( 1 ) ) );  // Yesterday.
                preparedStatement.executeUpdate ( );
                preparedStatement.setDate ( 1, java.sql.Date.valueOf ( today ) );  // Today.
                preparedStatement.executeUpdate ( );
                preparedStatement.setDate ( 1, java.sql.Date.valueOf ( today.plusDays ( 1 ) ) );  // Tomorrow.
                preparedStatement.executeUpdate ( );
            }

            // Query all.
            sql = "SELECT * FROM test_";
            try ( ResultSet rs = stmt.executeQuery ( sql ) ; ) {
                while ( rs.next ( ) ) {
                    //Retrieve by column name
                    UUID id = ( UUID ) rs.getObject ( "id_" );  // Cast the `Object` object to UUID if your driver does not support JDBC 4.2 and its ability to pass the expected return type for type-safety.
                    java.sql.Date sqlDate = rs.getDate ( "date_" );
                    LocalDate localDate = sqlDate.toLocalDate ();  // Immediately convert into java.time. Mimimize use of java.sql types.

                    //Display values
                    System.out.println ( "id_: " + id + " | date_: " + localDate );
                }
            }

        } catch ( SQLException e ) {
            e.printStackTrace ( );
        }
    }
}

---

关于java.time

java.time 框架内置于Java 8及更高版本中。这些类取代了麻烦的旧遗留日期时间类，例如 java.util.Date ， 日历 ，& SimpleDateFormat 。

---

About java.time

The java.time framework is built into Java 8 and later. These classes supplant the troublesome old legacy date-time classes such as java.util.Date, Calendar, & SimpleDateFormat.

Joda-Time 项目，现在在维护模式，建议迁移到 java.time 类。

The Joda-Time project, now in maintenance mode, advises migration to the java.time classes.

要了解更多信息，请参阅 Oracle教程。并搜索Stack Overflow以获取许多示例和解释。规范是 JSR 310 。

To learn more, see the Oracle Tutorial. And search Stack Overflow for many examples and explanations. Specification is JSR 310.

从哪里获取java.time类？

Where to obtain the java.time classes?

• Java SE 8 和 SE 9 及以后
  
  
  
  
  • 内置。
  
  
  • 带有捆绑实现的标准Java API的一部分。
  
  
  • Java 9添加了一些小功能和修复。
  
  
  • Java SE 8 and SE 9 and later
    • Built-in.
    • Part of the standard Java API with a bundled implementation.
    • Java 9 adds some minor features and fixes.
    
    
    • 大部分java.time功能都被反向移植到Java 6& 7 ThreeTen-Backport 。
    
    
    • Much of the java.time functionality is back-ported to Java 6 & 7 in ThreeTen-Backport.
    
    
    • ThreeTenABP 项目专门针对Android调整 ThreeTen-Backport （如上所述）。
    
    
    • 请参阅 如何使用ThreeTenABP ...... 。
    
    
    • The ThreeTenABP project adapts ThreeTen-Backport (mentioned above) for Android specifically.
    • See How to use ThreeTenABP….

    ThreeTen-Extra 项目使用其他类扩展java.time。该项目是未来可能添加到java.time的试验场。您可以在这里找到一些有用的课程，例如 Interval ， YearWeek ， YearQuarter ，更多。

    这篇关于插入&从/或从SQL数据库（如H2）获取java.time.LocalDate对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！