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

查看:22
本文介绍了插入 &从 SQL 数据库(如 H2)获取 java.time.LocalDate 对象的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

如何插入和获取

符合 JDBC 4.2 的驱动程序

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

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

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

myPreparedStatement.setObject ( 1 , myLocalDate );//自动检测和转换数据类型.

要从数据库中检索数据,请调用 ResultSet::getObject.我们可以传递一个额外的参数,而不是转换生成的 Object 对象,Class 我们期望接收的数据类型.通过指定预期的类别,我们获得了由您的 IDE 和编译器.

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

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

package com.example.h2localdate;导入 java.sql.*;导入 java.time.LocalDate;导入 java.time.ZoneId;导入 java.util.UUID;/*** 你好,世界!*/公共类应用{公共静态无效主(字符串[] args){应用程序=新应用程序();app.doIt();}私有无效 doIt ( ) {尝试 {Class.forName(org.h2.Driver");} catch ( ClassNotFoundException e ) {e.printStackTrace();}尝试 (Connection conn = DriverManager.getConnection ( "jdbc:h2:mem:trash_me_db_") ;语句stmt = conn.createStatement();){String tableName = "test_";String sql =创建表";+ 表名 + "(\n" +"id_ UUID DEFAULT random_uuid() PRIMARY KEY ,\n"+"日期_日期非空\n"+");";stmt.execute ( sql );//插入行.sql = "INSERT INTO test_ (date_) "+ "值 (?) ;";尝试 ( PreparedStatement PreparedStatement = conn.prepareStatement ( sql ) ; ) {LocalDate today = LocalDate.now ( ZoneId.of ( "America/Montreal") );PreparedStatement.setObject ( 1, today.minusDays ( 1 ) );//昨天.prepareStatement.executeUpdate();PreparedStatement.setObject(1,今天);//今天.prepareStatement.executeUpdate();PreparedStatement.setObject ( 1, today.plusDays ( 1 ) );//明天.prepareStatement.executeUpdate();}//查询所有.sql = "SELECT * FROM test_";尝试(结果集 rs = stmt.executeQuery(sql);){while ( rs.next ( ) ) {//按列名检索UUID id = rs.getObject (id_", UUID.class);//传递类是类型安全的,而不是转换返回值.LocalDate localDate = rs.getObject ( "date_", LocalDate.class );//同上,为类型安全传递类.//显示值System.out.println ( "id_: " + id + " | date_: " + localDate );}}} catch ( SQLException e ) {e.printStackTrace();}}}

运行时.

<块引用>

id_: e856a305-41a1-45fa-ab69-cfa676285461 |日期_:2017-03-26

<块引用>

id_:a4474e79-3e1f-4395-bbba-044423b37b9f |日期_:2017-03-27

<块引用>

id_: 5d47bc3d-ebfa-43ab-bbc2-7bb2313b33b0 |日期_: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.sql 类型和 java.time 类型.

package com.example.h2localdate;导入 java.sql.*;导入 java.time.LocalDate;导入 java.time.ZoneId;导入 java.util.UUID;/*** 你好,世界!*/公共类应用{公共静态无效主(字符串[] args){应用程序=新应用程序();app.doIt();}私有无效 doIt ( ) {尝试 {Class.forName(org.h2.Driver");} catch ( ClassNotFoundException e ) {e.printStackTrace();}尝试 (Connection conn = DriverManager.getConnection ( "jdbc:h2:mem:trash_me_db_") ;语句stmt = conn.createStatement();){String tableName = "test_";String sql =创建表";+ 表名 + "(\n" +"id_ UUID DEFAULT random_uuid() PRIMARY KEY ,\n"+"日期_日期非空\n"+");";stmt.execute ( sql );//插入行.sql = "INSERT INTO test_ (date_) "+ "值 (?) ;";尝试 ( PreparedStatement PreparedStatement = conn.prepareStatement ( sql ) ; ) {LocalDate today = LocalDate.now ( ZoneId.of ( "America/Montreal") );PreparedStatement.setDate ( 1, java.sql.Date.valueOf ( today.minusDays ( 1 ) ) );//昨天.prepareStatement.executeUpdate();PreparedStatement.setDate ( 1, java.sql.Date.valueOf ( today ) );//今天.prepareStatement.executeUpdate();PreparedStatement.setDate ( 1, java.sql.Date.valueOf ( today.plusDays ( 1 ) ) );//明天.prepareStatement.executeUpdate();}//查询所有.sql = "SELECT * FROM test_";尝试(结果集 rs = stmt.executeQuery(sql);){while ( rs.next ( ) ) {//按列名检索UUID id = (UUID) rs.getObject (id_");//如果您的驱动程序不支持 JDBC 4.2 及其为类型安全传递预期返回类型的能力,则将 `Object` 对象强制转换为 UUID.java.sql.Date sqlDate = rs.getDate (date_");LocalDate localDate = sqlDate.toLocalDate();//立即转换成java.time.尽量减少 java.sql 类型的使用.//显示值System.out.println ( "id_: " + id + " | date_: " + localDate );}}} catch ( SQLException e ) {e.printStackTrace();}}}

为了好玩,让我们尝试另一个.这次使用DataSource实现 从中获取连接.这次尝试 LocalDate.MIN 在 ISO 8601 中是大约 10 亿年前的常数,-999999999-01-01.

package work.basil.example;导入 java.sql.*;导入 java.time.LocalDate;导入 java.time.ZoneId;导入 java.util.UUID;公共类 LocalDateMin{public static void main ( String[] args ){LocalDateMin app = new LocalDateMin();app.doIt();}私有无效 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(斯科特");ds.setPassword(老虎");尝试 (连接 conn = ds.getConnection() ;语句 stmt = conn.createStatement() ;){String tableName = "test_";String sql =创建表";+ 表名 + "(\n" +"id_ UUID DEFAULT random_uuid() PRIMARY KEY ,\n"+"日期_日期非空\n"+");";stmt.execute(sql);//插入行.sql = "INSERT INTO test_ (date_) "+ "值 (?) ;";尝试 ( PreparedStatement PreparedStatement = conn.prepareStatement( sql ) ; ){LocalDate today = LocalDate.now( ZoneId.of( "America/Montreal") );prepareStatement.setObject( 1 , LocalDate.MIN );//最小值 =prepareStatement.executeUpdate();}//查询所有.sql = "SELECT * FROM test_";尝试 ( 结果集 rs = stmt.executeQuery( sql ) ; ){而 ( rs.next() ){//按列名检索UUID id = rs.getObject( "id_", UUID.class);//传递类是类型安全的,而不是转换返回值.LocalDate localDate = rs.getObject( "date_", LocalDate.class );//同上,为类型安全传递类.//显示值System.out.println( "id_: " + id + " | date_: " + localDate );}}} catch ( SQLException e ){e.printStackTrace();}}}

<块引用>

id_: 4b0ba138-d7ae-469b-854f-5cbe7430026f |日期_:-999999999-01-01


关于java.time

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

要了解更多信息,请参阅 Oracle 教程.并在 Stack Overflow 上搜索许多示例和解释.规范是 JSR 310.

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

您可以直接与您的数据库交换 java.time 对象.使用符合 JDBC 驱动程序/jeps/170" rel="nofollow noreferrer">JDBC 4.2 或更高版本.不需要字符串,不需要 java.sql.* 类.休眠 5 &JPA 2.2 支持 java.time.

从哪里获取 java.time 类?

ThreeTen-Extra 项目扩展了 java.time与额外的课程.该项目是未来可能添加到 java.time 的试验场.您可能会在这里找到一些有用的类,例如 间隔YearWeek, YearQuarter更多.

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

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.

What is the modern way for sending java.time types through a JDBC driver?

解决方案

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

  • 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.

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 4.2 compliant drivers

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

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.

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.

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

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

When run.

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

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.

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

Then pass to the PreparedStatement::setDate method.

preparedStatement.setDate ( 1, mySqlDate );

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

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

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

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

For fun let's try another. This time using a DataSource implementation from which to get a connection. And this time trying LocalDate.MIN which is a constant for about a billion years ago in ISO 8601, -999999999-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


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.

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

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

You may exchange java.time objects directly with your database. Use a JDBC driver compliant with JDBC 4.2 or later. No need for strings, no need for java.sql.* classes. Hibernate 5 & JPA 2.2 support java.time.

Where to obtain the java.time classes?

The ThreeTen-Extra project extends java.time with additional classes. This project is a proving ground for possible future additions to java.time. You may find some useful classes here such as Interval, YearWeek, YearQuarter, and more.

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

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆