向/从SQL数据库(如H2)插入和获取java.time.LocalDate对象

如何通过JDBC将java.time等java.time类型插入到H2 Database Engine等SQL数据库中？使用PreparedStatement::setDate和ResultSet::getDate的旧方法适用于旧版java.sql.Date.我想避免使用这些麻烦的旧日期时间类.通过...

如何通过JDBC将java.time等java.time类型插入到H2 Database Engine等SQL数据库中？

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

通过JDBC driver发送java.time类型的现代方法是什么？

解决方法:

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

>符合JDBC 4.2的驱动程序如果JDBC驱动程序符合JDBC 4.2 specification或更高版本,则可以直接处理java.time对象.
> JDBC之前的旧驱动程序4.2如果您的JDBC驱动程序尚未符合JDBC 4.2或更高版本,那么您可以将java.time对象简要地转换为其等效的java.sql类型,反之亦然.查看添加到旧类的新转换方法.

旧的日期时间类(如java.util.Date,java.util.Calendar)和相关的java.sql类(如java.sql.Date)非常混乱.它们采用设计糟糕的黑客攻击方式构建,已证明存在缺陷,麻烦和混乱.尽可能避免使用它们.现在取代了java.time类.

符合JDBC 4.2的驱动程序

H2的内置JDBC驱动程序(截至2017-03)似乎符合JDBC 4.2.

兼容的驱动程序现在知道java.time类型.但是,不是添加setLocalDate / getLocalDate方法,JDBC委员会添加了setObject/getObject方法.

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

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

要从数据库中检索数据,请调用ResultSet :: getObject.我们可以传递一个额外的参数,即我们期望接收的数据类型的Class,而不是转换生成的Object对象.通过指定期望的类,我们通过IDE和编译器检查并验证type-safety.

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

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

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_: a4474e79-3e1f-4395-bbba-044423b37b9f | date_: 2017-03-27

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

不合规的司机

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

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

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

然后传递给PreparedStatement :: setDate方法.

preparedStatement.setDate ( 1, mySqlDate );

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

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

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

LocalDate myLocalDate = mySqlDate.toLocalDate();

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

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 ( );
        }
    }
}

为了好玩,让我们尝试另一个.这次using a DataSource implementation从中获得连接.而这一次尝试LocalDate.MIN,这是大约10亿年前的常数,-999,999,999-01-01.

package work.basil.example;

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

public class LocalDateMin
{
    public static void main ( String[] args )
    {
        LocalDateMin app = new LocalDateMin();
        app.doIt();
    }

    private void doIt ()
    {
        org.h2.jdbcx.JdbcDataSource ds = new org.h2.jdbcx.JdbcDataSource();
        ds.setURL( "jdbc:h2:mem:localdate_min_example_db_;DB_CLOSE_DELAY=-1" );
        ds.setUser( "scott" );
        ds.setPassword( "tiger" );

        try (
                Connection conn = ds.getConnection() ;
                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 , LocalDate.MIN );  // MIN =
                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_: 4b0ba138-d7ae-469b-854f-5cbe7430026f | date_: -999999999-01-01

关于java.time

java.time框架内置于Java 8及更高版本中.这些类取代了麻烦的旧legacy日期时间类,如java.util.Date,Calendar和& SimpleDateFormat.

Joda-Time项目现在是maintenance mode,建议迁移到java.time课程.

要了解更多信息,请参阅Oracle Tutorial.并搜索Stack Overflow以获取许多示例和说明.规格是JSR 310.

从哪里获取java.time类？

> Java SE 8和SE 9及之后

>内置.
>部分标准Java API,带有捆绑实现.
> Java 9增加了一些小功能和修复.

> Java SE 6和SE 7

>大部分java.time功能都被反向移植到Java 6& 7月在ThreeTen-Backport.

> Android

> ThreeTenABP项目专门针对Android调整了ThreeTen-Backport(如上所述).
>见How to use ThreeTenABP….

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