JDBC test program to check if conversion between java.sql.OffsetTime and TIMESTAMP WITH TIME ZONE is supported

JdbcConnection.java

/**
 *    Copyright 2019 the original author or authors.
 *
 *    Licensed under the Apache License, Version 2.0 (the "License");
 *    you may not use this file except in compliance with the License.
 *    You may obtain a copy of the License at
 *
 *       http://www.apache.org/licenses/LICENSE-2.0
 *
 *    Unless required by applicable law or agreed to in writing, software
 *    distributed under the License is distributed on an "AS IS" BASIS,
 *    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 *    See the License for the specific language governing permissions and
 *    limitations under the License.
 */
package net.harawata.jdbc.connection;

import java.io.File;
import java.net.MalformedURLException;
import java.net.URL;
import java.net.URLClassLoader;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.Driver;
import java.sql.SQLException;
import java.util.Locale;
import java.util.Properties;
import java.util.ResourceBundle;

public class JdbcConnection {

  private static final String CONFIG_DIR = "path to the dir contains .properties files";
  private final String db;

  public JdbcConnection(String db) {
    this.db = db;
  }

  public Connection getConnection() throws ClassNotFoundException, SQLException, MalformedURLException,
      InstantiationException, IllegalAccessException {
    final URLClassLoader loader = new URLClassLoader(new URL[] { new File(CONFIG_DIR).toURI().toURL() });
    final ResourceBundle bundle = ResourceBundle.getBundle(db, Locale.getDefault(), loader);
    final String driver = bundle.getString("driver");
    final String url = bundle.getString("url");
    final String username = bundle.getString("username");
    final String password = bundle.getString("password");
    Properties info = new Properties();
    info.put("user", username);
    info.put("password", password);
    Connection con = ((Driver) ClassLoader.getSystemClassLoader().loadClass(driver).newInstance()).connect(url, info);
    DatabaseMetaData dbmd = con.getMetaData();
    System.out.println(">>> DB version : " + dbmd.getDatabaseProductName() + " " + dbmd.getDatabaseProductVersion());
    System.out.println(">>> Driver version : " + dbmd.getDriverVersion());
    return con;
  }

}

OffsetTimeTest.java

/**
 *    Copyright 2019 the original author or authors.
 *
 *    Licensed under the Apache License, Version 2.0 (the "License");
 *    you may not use this file except in compliance with the License.
 *    You may obtain a copy of the License at
 *
 *       http://www.apache.org/licenses/LICENSE-2.0
 *
 *    Unless required by applicable law or agreed to in writing, software
 *    distributed under the License is distributed on an "AS IS" BASIS,
 *    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 *    See the License for the specific language governing permissions and
 *    limitations under the License.
 */
package test;

import static org.junit.jupiter.api.Assertions.*;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.time.OffsetTime;
import java.time.ZoneOffset;
import java.util.TimeZone;

import org.junit.jupiter.api.Test;

import net.harawata.jdbc.connection.JdbcConnection;

public class OffsetTimeTest {

  private static final String PREPARED_OFFSET_TIME = "1970-01-01 11:22:33.123456000+10:20";

  private static final OffsetTime OFFSET_TIME = OffsetTime.of(11, 22, 33, 123456000, ZoneOffset.ofHoursMinutes(10, 20));

  @Test
  void hsqldb_select() throws Exception {
    select("hsqldb", "timestamp with time zone", PREPARED_OFFSET_TIME, OFFSET_TIME);
  }

  @Test
  void hsqldb_insert() throws Exception {
    insert("hsqldb", "timestamp with time zone", OFFSET_TIME);
  }

  @Test
  void oracle_select() throws Exception {
    select("oracle12", "timestamp with time zone", PREPARED_OFFSET_TIME, OFFSET_TIME);
  }

  @Test
  void oracle_insert() throws Exception {
    insert("oracle12", "timestamp with time zone", OFFSET_TIME);
  }

  @Test
  void mssql_select() throws Exception {
    select("mssql", "datetimeoffset", PREPARED_OFFSET_TIME, OFFSET_TIME);
  }

  @Test
  void mssql_insert() throws Exception {
    insert("mssql", "datetimeoffset", OFFSET_TIME);
  }

  @Test
  void postgres_select() throws Exception {
    select("postgres", "timestamp with time zone", PREPARED_OFFSET_TIME, OFFSET_TIME);
  }

  @Test
  void postgres_insert() throws Exception {
    insert("postgres", "timestamp with time zone", OFFSET_TIME);
  }

  @Test
  void h2_select() throws Exception {
    select("h2", "timestamp with time zone", PREPARED_OFFSET_TIME, OFFSET_TIME);
  }

  @Test
  void h2_insert() throws Exception {
    insert("h2", "timestamp with time zone", OFFSET_TIME);
  }

  @Test
  void mariadb_timestamp_select() throws Exception {
    String[] arr = PREPARED_OFFSET_TIME.split("\\+");
    TimeZone tz = TimeZone.getDefault();
    TimeZone.setDefault(TimeZone.getTimeZone("GMT+" + arr[1]));
    try {
      select("mariadb", "timestamp(6)", arr[0], OFFSET_TIME);
    } finally {
      TimeZone.setDefault(tz);
    }
  }

  @Test
  void mariadb_timestamp_insert() throws Exception {
    insert("mariadb", "timestamp(6)", OFFSET_TIME);
  }

  @Test
  void mariadb_datetime_select() throws Exception {
    String[] arr = PREPARED_OFFSET_TIME.split("\\+");
    TimeZone tz = TimeZone.getDefault();
    TimeZone.setDefault(TimeZone.getTimeZone("GMT+" + arr[1]));
    try {
      select("mariadb", "datetime(6)", arr[0], OFFSET_TIME);
    } finally {
      TimeZone.setDefault(tz);
    }
  }

  @Test
  void mariadb_datetime_insert() throws Exception {
    insert("mariadb", "datetime(6)", OFFSET_TIME);
  }

  private void select(String dbId, String columnType, String testValue, OffsetTime expected) throws Exception {
    // Get connection via DriverManager.
    try (Connection con = new JdbcConnection(dbId).getConnection()) {
      try (Statement stmt = con.createStatement()) {
        try {
          stmt.execute("drop table test");
        } catch (Exception e) {
          // expected
        }
        System.out.println("Create test table...");
        stmt.execute("create table test (id integer, t " + columnType + ")");
        System.out.println("Insert test data...");
        stmt.execute("insert into test (id, t) values (1, '" + testValue + "')");
      }
      execSelect(con, 1, expected);
    }
  }

  private void insert(String dbId, String columnType, OffsetTime testValue) throws Exception {
    // Get connection via DriverManager.
    try (Connection con = new JdbcConnection(dbId).getConnection()) {
      try (Statement stmt = con.createStatement()) {
        try {
          stmt.execute("drop table test");
        } catch (Exception e) {
          // expected
        }
        System.out.println("Create test table...");
        stmt.execute("create table test (id integer, t " + columnType + ")");
      }
      execInsert(con, 2, testValue);
      execSelect(con, 2, testValue);
    }
  }

  private static void execSelect(Connection con, final Integer id, OffsetTime expected) throws Exception {
    try (PreparedStatement stmt = con.prepareStatement("select id, t from test where id = ?")) {
      stmt.setInt(1, id);
      try (ResultSet rs = stmt.executeQuery()) {
        while (rs.next()) {
          assertEquals(id.intValue(), rs.getInt(1));
          assertEquals(expected, rs.getObject(2, OffsetTime.class));
        }
      }
    }
  }

  private static void execInsert(Connection con, Integer id, OffsetTime t) throws Exception {
    try (PreparedStatement stmt = con.prepareStatement("insert into test (id, t) values (?, ?)")) {
      stmt.setInt(1, 2);
      stmt.setObject(2, t);
      stmt.executeUpdate();
    }
  }
}

ZonedDateTimeTest.java

/**
 *    Copyright 2019 the original author or authors.
 *
 *    Licensed under the Apache License, Version 2.0 (the "License");
 *    you may not use this file except in compliance with the License.
 *    You may obtain a copy of the License at
 *
 *       http://www.apache.org/licenses/LICENSE-2.0
 *
 *    Unless required by applicable law or agreed to in writing, software
 *    distributed under the License is distributed on an "AS IS" BASIS,
 *    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 *    See the License for the specific language governing permissions and
 *    limitations under the License.
 */
package test;

import static org.junit.jupiter.api.Assertions.*;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.time.ZoneId;
import java.time.ZoneOffset;
import java.time.ZonedDateTime;

import org.junit.jupiter.api.Test;

import net.harawata.jdbc.connection.JdbcConnection;

public class ZonedDateTimeTest {

  private static final ZonedDateTime ZONED_DATE_TIME_OFFSET = ZonedDateTime.of(2019, 1, 10, 11, 22, 33, 123456000,
      ZoneId.ofOffset("", ZoneOffset.ofHours(-8)));

  private static final ZonedDateTime ZONED_DATE_TIME_REGION = ZonedDateTime.of(2019, 1, 10, 11, 22, 33, 123456000,
      ZoneId.of("America/Los_Angeles"));

  @Test
  void oracle_select_region() throws Exception {
    select("oracle12", "timestamp with time zone", "TIMESTAMP '2019-01-10 11:22:33.123456000 America/Los_Angeles'",
        ZONED_DATE_TIME_REGION);
  }

  @Test
  void oracle_select_offset() throws Exception {
    select("oracle12", "timestamp with time zone", "TIMESTAMP '2019-01-10 11:22:33.123456000 -08:00'",
        ZONED_DATE_TIME_OFFSET);
  }

  @Test
  void oracle_insert_region() throws Exception {
    insert("oracle12", "timestamp with time zone", ZONED_DATE_TIME_REGION);
  }

  @Test
  void oracle_insert_offset() throws Exception {
    insert("oracle12", "timestamp with time zone", ZONED_DATE_TIME_OFFSET);
  }

  private void select(String dbId, String columnType, String testValue, ZonedDateTime expected) throws Exception {
    // Get connection via DriverManager.
    try (Connection con = new JdbcConnection(dbId).getConnection()) {
      try (Statement stmt = con.createStatement()) {
        try {
          stmt.execute("drop table test");
        } catch (Exception e) {
          // expected
        }
        System.out.println("Create test table...");
        stmt.execute("create table test (id integer, t " + columnType + ")");
        System.out.println("Insert test data...");
        stmt.execute("insert into test (id, t) values (1, " + testValue + ")");
      }
      execSelect(con, 1, expected);
    }
  }

  private void insert(String dbId, String columnType, ZonedDateTime testValue) throws Exception {
    // Get connection via DriverManager.
    try (Connection con = new JdbcConnection(dbId).getConnection()) {
      try (Statement stmt = con.createStatement()) {
        try {
          stmt.execute("drop table test");
        } catch (Exception e) {
          // expected
        }
        System.out.println("Create test table...");
        stmt.execute("create table test (id integer, t " + columnType + ")");
      }
      execInsert(con, 2, testValue);
      execSelect(con, 2, testValue);
    }
  }

  private static void execSelect(Connection con, final Integer id, ZonedDateTime expected) throws Exception {
    try (PreparedStatement stmt = con.prepareStatement("select id, t from test where id = ?")) {
      stmt.setInt(1, id);
      try (ResultSet rs = stmt.executeQuery()) {
        while (rs.next()) {
          assertEquals(id.intValue(), rs.getInt(1));
          System.out.println(rs.getString(2));
          assertEquals(expected, rs.getObject(2, ZonedDateTime.class));
        }
      }
    }
  }

  private static void execInsert(Connection con, Integer id, ZonedDateTime t) throws Exception {
    try (PreparedStatement stmt = con.prepareStatement("insert into test (id, t) values (?, ?)")) {
      stmt.setInt(1, 2);
      stmt.setObject(2, t);
      stmt.executeUpdate();
    }
  }
}