/*
* Copyright (c) 2004, PostgreSQL Global Development Group
* See the LICENSE file in the project root for more information.
*/
package org.postgresql.test.jdbc42;
import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertNotNull;
import static org.junit.Assert.assertTrue;
import org.postgresql.test.TestUtil;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.sql.Types;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.time.OffsetDateTime;
import java.time.ZoneId;
import java.time.ZoneOffset;
import java.time.chrono.IsoEra;
import java.time.temporal.ChronoField;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.TimeZone;
public class SetObject310Test {
private static final TimeZone saveTZ = TimeZone.getDefault();
private Connection con;
@Before
public void setUp() throws Exception {
con = TestUtil.openDB();
TestUtil.createTable(con, "table1", "timestamp_without_time_zone_column timestamp without time zone,"
+ "timestamp_with_time_zone_column timestamp with time zone,"
+ "date_column date,"
+ "time_without_time_zone_column time without time zone,"
+ "time_with_time_zone_column time with time zone"
);
}
@After
public void tearDown() throws SQLException {
TimeZone.setDefault(saveTZ);
TestUtil.dropTable(con, "table1");
TestUtil.closeDB(con);
}
private void insert(Object data, String columnName, Integer type) throws SQLException {
PreparedStatement ps = con.prepareStatement(TestUtil.insertSQL("table1", columnName, "?"));
try {
if (type != null) {
ps.setObject(1, data, type);
} else {
ps.setObject(1, data);
}
assertEquals(1, ps.executeUpdate());
} finally {
ps.close();
}
}
private String readString(String columnName) throws SQLException {
Statement st = con.createStatement();
try {
ResultSet rs = st.executeQuery(TestUtil.selectSQL("table1", columnName));
try {
assertNotNull(rs);
assertTrue(rs.next());
return rs.getString(1);
} finally {
rs.close();
}
} finally {
st.close();
}
}
private String insertThenReadStringWithoutType(LocalDateTime data, String columnName) throws SQLException {
insert(data, columnName, null);
return readString(columnName);
}
private String insertThenReadStringWithType(LocalDateTime data, String columnName) throws SQLException {
insert(data, columnName, Types.TIMESTAMP);
return readString(columnName);
}
private void insertWithoutType(Object data, String columnName) throws SQLException {
insert(data, columnName, null);
}
private void insertWithType(OffsetDateTime data, String columnName) throws SQLException {
insert(data, columnName, Types.TIMESTAMP_WITH_TIMEZONE);
}
private <T> T insertThenReadWithoutType(Object data, String columnName, Class<T> expectedType) throws SQLException {
PreparedStatement ps = con.prepareStatement(TestUtil.insertSQL("table1", columnName, "?"));
try {
ps.setObject(1, data);
assertEquals(1, ps.executeUpdate());
} finally {
ps.close();
}
Statement st = con.createStatement();
try {
ResultSet rs = st.executeQuery(TestUtil.selectSQL("table1", columnName));
try {
assertNotNull(rs);
assertTrue(rs.next());
return expectedType.cast(rs.getObject(1));
} finally {
rs.close();
}
} finally {
st.close();
}
}
private <T> T insertThenReadWithType(Object data, int sqlType, String columnName, Class<T> expectedType) throws SQLException {
PreparedStatement ps = con.prepareStatement(TestUtil.insertSQL("table1", columnName, "?"));
try {
ps.setObject(1, data, sqlType);
assertEquals(1, ps.executeUpdate());
} finally {
ps.close();
}
Statement st = con.createStatement();
try {
ResultSet rs = st.executeQuery(TestUtil.selectSQL("table1", columnName));
try {
assertNotNull(rs);
assertTrue(rs.next());
return expectedType.cast(rs.getObject(1));
} finally {
rs.close();
}
} finally {
st.close();
}
}
private void deleteRows() throws SQLException {
Statement st = con.createStatement();
try {
st.executeUpdate("DELETE FROM table1");
} finally {
st.close();
}
}
/**
* Test the behavior of setObject for timestamp columns.
*/
@Test
public void testSetLocalDateTime() throws SQLException {
List<String> zoneIdsToTest = getZoneIdsToTest();
List<String> datesToTest = getDatesToTest();
for (String zoneId : zoneIdsToTest) {
ZoneId zone = ZoneId.of(zoneId);
for (String date : datesToTest) {
LocalDateTime localDateTime = LocalDateTime.parse(date);
String expected = date.replace('T', ' ');
localTimestamps(zone, localDateTime, expected);
}
}
}
/**
* Test the behavior of setObject for timestamp columns.
*/
@Test
public void testSetOffsetDateTime() throws SQLException {
List<String> zoneIdsToTest = getZoneIdsToTest();
List<TimeZone> storeZones = new ArrayList<TimeZone>();
for (String zoneId : zoneIdsToTest) {
storeZones.add(TimeZone.getTimeZone(zoneId));
}
List<String> datesToTest = getDatesToTest();
for (TimeZone timeZone : storeZones) {
ZoneId zoneId = timeZone.toZoneId();
for (String date : datesToTest) {
LocalDateTime localDateTime = LocalDateTime.parse(date);
String expected = date.replace('T', ' ');
offsetTimestamps(zoneId, localDateTime, expected, storeZones);
}
}
}
private List<String> getDatesToTest() {
return Arrays.asList("2015-09-03T12:00:00", "2015-06-30T23:59:58",
"1997-06-30T23:59:59", "1997-07-01T00:00:00", "2012-06-30T23:59:59", "2012-07-01T00:00:00",
"2015-06-30T23:59:59", "2015-07-01T00:00:00", "2005-12-31T23:59:59", "2006-01-01T00:00:00",
"2008-12-31T23:59:59", "2009-01-01T00:00:00", /* "2015-06-30T23:59:60", */ "2015-07-31T00:00:00",
"2015-07-31T00:00:01", "2015-07-31T00:00:00.000001",
// On 2000-03-26 02:00:00 Moscow went to DST, thus local time became 03:00:00
"2000-03-26T01:59:59", "2000-03-26T02:00:00", "2000-03-26T02:00:01", "2000-03-26T02:59:59",
"2000-03-26T03:00:00", "2000-03-26T03:00:01", "2000-03-26T03:59:59", "2000-03-26T04:00:00",
"2000-03-26T04:00:01", "2000-03-26T04:00:00.000001",
// On 2000-10-29 03:00:00 Moscow went to regular time, thus local time became 02:00:00
"2000-10-29T01:59:59", "2000-10-29T02:00:00", "2000-10-29T02:00:01", "2000-10-29T02:59:59",
"2000-10-29T03:00:00", "2000-10-29T03:00:01", "2000-10-29T03:59:59", "2000-10-29T04:00:00",
"2000-10-29T04:00:01", "2000-10-29T04:00:00.000001");
}
private List<String> getZoneIdsToTest() {
List<String> zoneIdsToTest = new ArrayList<String>();
zoneIdsToTest.add("Africa/Casablanca"); // It is something like GMT+0..GMT+1
zoneIdsToTest.add("America/Adak"); // It is something like GMT-10..GMT-9
zoneIdsToTest.add("Atlantic/Azores"); // It is something like GMT-1..GMT+0
zoneIdsToTest.add("Europe/Moscow"); // It is something like GMT+3..GMT+4 for 2000s
zoneIdsToTest.add("Pacific/Apia"); // It is something like GMT+13..GMT+14
zoneIdsToTest.add("Pacific/Niue"); // It is something like GMT-11..GMT-11
for (int i = -12; i <= 13; i++) {
zoneIdsToTest.add(String.format("GMT%+02d", i));
}
return zoneIdsToTest;
}
private void localTimestamps(ZoneId zoneId, LocalDateTime localDateTime, String expected) throws SQLException {
TimeZone.setDefault(TimeZone.getTimeZone(zoneId));
String readBack = insertThenReadStringWithoutType(localDateTime, "timestamp_without_time_zone_column");
assertEquals(expected, readBack);
deleteRows();
readBack = insertThenReadStringWithType(localDateTime, "timestamp_without_time_zone_column");
assertEquals(expected, readBack);
deleteRows();
}
private void offsetTimestamps(ZoneId dataZone, LocalDateTime localDateTime, String expected, List<TimeZone> storeZones) throws SQLException {
OffsetDateTime data = localDateTime.atZone(dataZone).toOffsetDateTime();
for (TimeZone storeZone : storeZones) {
TimeZone.setDefault(storeZone);
insertWithoutType(data, "timestamp_with_time_zone_column");
String readBack = readString("timestamp_with_time_zone_column");
OffsetDateTime o = OffsetDateTime.parse(readBack.replace(' ', 'T') + ":00");
assertEquals(data.toInstant(), o.toInstant());
deleteRows();
}
for (TimeZone storeZone : storeZones) {
TimeZone.setDefault(storeZone);
insertWithType(data, "timestamp_with_time_zone_column");
String readBack = readString("timestamp_with_time_zone_column");
OffsetDateTime o = OffsetDateTime.parse(readBack.replace(' ', 'T') + ":00");
assertEquals(data.toInstant(), o.toInstant());
deleteRows();
}
}
/**
* Test the behavior of setObject for timestamp columns.
*/
@Test
public void testSetLocalDateTimeBc() throws SQLException {
// use BC for funsies
List<LocalDateTime> bcDates = new ArrayList<LocalDateTime>();
bcDates.add(LocalDateTime.parse("1997-06-30T23:59:59.999999").with(ChronoField.ERA, IsoEra.BCE.getValue()));
bcDates.add(LocalDateTime.parse("0997-06-30T23:59:59.999999").with(ChronoField.ERA, IsoEra.BCE.getValue()));
for (LocalDateTime bcDate : bcDates) {
// -1997-06-30T23:59:59.999999 -> 1997-06-30 23:59:59.999999 BC
String expected = bcDate.toString().substring(1).replace('T', ' ') + " BC";
localTimestamps(ZoneOffset.UTC, bcDate, expected);
}
}
/**
* Test the behavior setObject for date columns.
*/
@Test
public void testSetLocalDateWithType() throws SQLException {
LocalDate data = LocalDate.parse("1971-12-15");
java.sql.Date actual = insertThenReadWithType(data, Types.DATE, "date_column", java.sql.Date.class);
java.sql.Date expected = java.sql.Date.valueOf("1971-12-15");
assertEquals(expected, actual);
}
/**
* Test the behavior setObject for date columns.
*/
@Test
public void testSetLocalDateWithoutType() throws SQLException {
LocalDate data = LocalDate.parse("1971-12-15");
java.sql.Date actual = insertThenReadWithoutType(data, "date_column", java.sql.Date.class);
java.sql.Date expected = java.sql.Date.valueOf("1971-12-15");
assertEquals(expected, actual);
}
/**
* Test the behavior setObject for time columns.
*/
@Test
public void testSetLocalTimeAndReadBack() throws SQLException {
LocalTime data = LocalTime.parse("16:21:51.123456");
insertWithoutType(data, "time_without_time_zone_column");
String readBack = readString("time_without_time_zone_column");
assertEquals("16:21:51.123456", readBack);
}
/**
* Test the behavior setObject for time columns.
*/
@Test
public void testSetLocalTimeWithType() throws SQLException {
LocalTime data = LocalTime.parse("16:21:51");
Time actual = insertThenReadWithType(data, Types.TIME, "time_without_time_zone_column", Time.class);
Time expected = Time.valueOf("16:21:51");
assertEquals(expected, actual);
}
/**
* Test the behavior setObject for time columns.
*/
@Test
public void testSetLocalTimeWithoutType() throws SQLException {
LocalTime data = LocalTime.parse("16:21:51");
Time actual = insertThenReadWithoutType(data, "time_without_time_zone_column", Time.class);
Time expected = Time.valueOf("16:21:51");
assertEquals(expected, actual);
}
}