[Java][JDBC]PostgreSQLの日時型をJava8のDate and Timeクラスで扱う

PostgreSQL の JDBC Driver では Java8 対応がされています。

PostgreSQL と Java8 での型の対応

サンプル

timestamp with time zone 型を扱ってみます。

テストデータベースの準備

my_database=# create table table1 (column1 timestamp with time zone);
CREATE TABLE
my_database=# \d table1
                        Table "public.table1"
 Column  |           Type           | Collation | Nullable | Default
---------+--------------------------+-----------+----------+---------
 column1 | timestamp with time zone |           |          |

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.example</groupId>
    <artifactId>example</artifactId>
    <version>1.0.0-SNAPSHOT</version>
    <packaging>jar</packaging>
    <name>example</name>

    <properties>
        <java.version>1.8</java.version>
        <maven.compiler.target>${java.version}</maven.compiler.target>
        <maven.compiler.source>${java.version}</maven.compiler.source>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <version>42.2.16</version>
        </dependency>
    </dependencies>

</project>

src/main/java/com/example/Example.java

timestamp with time zone 型は OffsetDateTime クラスで扱います

package com.example;

import org.postgresql.util.PGTimestamp;

import java.sql.*;
import java.time.OffsetDateTime;
import java.time.ZoneId;

public class Example {

    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Class.forName("org.postgresql.Driver");

        String url = "jdbc:postgresql://127.0.0.1/my_database";
        String username = "my_username";
        String password = "my_secretpassword";

        try (Connection connection = DriverManager.getConnection(url, username, password)) {
            PreparedStatement stmt = connection.prepareStatement("insert into table1(column1) values (?)");
            stmt.setObject(1, OffsetDateTime.now(ZoneId.of("Asia/Tokyo")));
            stmt.execute();
        }

        try (Connection connection = DriverManager.getConnection(url, username, password)) {
            PreparedStatement stmt = connection.prepareStatement("select column1 from table1");
            ResultSet resultSet = stmt.executeQuery();
            while (resultSet.next()) {
                OffsetDateTime offsetDateTime = resultSet.getObject("column1", OffsetDateTime.class);
                System.out.println(offsetDateTime.atZoneSameInstant(ZoneId.of("Asia/Tokyo")));
            }
        }
    }

}

実行

$ mvn -q exec:java -Dexec.mainClass=com.example.Example
2020-09-18T22:13:46.927+09:00[Asia/Tokyo]