@Test
public void test_writeRead_timestamp() {
// create test table
String drop = _elSqlBundle.getSql("DropTstTimes"); // "DROP TABLE IF EXISTS tst_times";
getDbConnector().getJdbcTemplate().update(drop);
String create =
_elSqlBundle.getSql(
"CreateTstTimes"); // "CREATE TABLE tst_times ( id bigint not null, ver timestamp
// without time zone not null )";
getDbConnector().getJdbcTemplate().update(create);
// insert data
String insert = _elSqlBundle.getSql("InsertTstTimes"); // "INSERT INTO tst_times VALUES (?,?)";
final Timestamp tsOut1 = DbDateUtils.toSqlTimestamp(INSTANT1);
final Timestamp tsOut2 = DbDateUtils.toSqlTimestamp(INSTANT2);
final Timestamp tsOut3 = DbDateUtils.toSqlTimestamp(INSTANT3);
getDbConnector().getJdbcTemplate().update(insert, 1, tsOut1);
getDbConnector().getJdbcTemplate().update(insert, 2, tsOut2);
getDbConnector().getJdbcTemplate().update(insert, 3, tsOut3);
// pull back to check roundtripping
String select1 =
_elSqlBundle.getSql("SelectTstTimes"); // "SELECT ver FROM tst_times WHERE id = ?";
Map<String, Object> result1 = getDbConnector().getJdbcTemplate().queryForMap(select1, 1);
Map<String, Object> result2 = getDbConnector().getJdbcTemplate().queryForMap(select1, 2);
Map<String, Object> result3 = getDbConnector().getJdbcTemplate().queryForMap(select1, 3);
Timestamp tsIn1 = (Timestamp) result1.get("ver");
Timestamp tsIn2 = (Timestamp) result2.get("ver");
Timestamp tsIn3 = (Timestamp) result3.get("ver");
Instant retrieved1 = DbDateUtils.fromSqlTimestamp(tsIn1);
Instant retrieved2 = DbDateUtils.fromSqlTimestamp(tsIn2);
Instant retrieved3 = DbDateUtils.fromSqlTimestamp(tsIn3);
assertEquals(super.toString() + " Instant " + retrieved1, INSTANT1, retrieved1);
assertEquals(super.toString() + " Instant " + retrieved2, INSTANT2, retrieved2);
assertEquals(super.toString() + " Instant " + retrieved3, INSTANT3, retrieved3);
// pull back the raw DB string form to ensure it actually stored UTC field values
String retrievedText1 =
getDbConnector()
.getJdbcTemplate()
.queryForObject(
select1,
new RowMapper<String>() {
@Override
public String mapRow(ResultSet rs, int rowNum) throws SQLException {
return rs.getString("ver");
}
},
1);
String retrievedText2 =
getDbConnector()
.getJdbcTemplate()
.queryForObject(
select1,
new RowMapper<String>() {
@Override
public String mapRow(ResultSet rs, int rowNum) throws SQLException {
return rs.getString("ver");
}
},
2);
String retrievedText3 =
getDbConnector()
.getJdbcTemplate()
.queryForObject(
select1,
new RowMapper<String>() {
@Override
public String mapRow(ResultSet rs, int rowNum) throws SQLException {
return rs.getString("ver");
}
},
3);
assertEquals(
super.toString() + " Instant " + retrieved1,
OffsetDateTime.ofInstant(INSTANT1, ZoneOffset.UTC).toString(FORMAT),
retrievedText1);
assertEquals(
super.toString() + " Instant " + retrieved2,
OffsetDateTime.ofInstant(INSTANT2, ZoneOffset.UTC).toString(FORMAT),
retrievedText2);
assertEquals(
super.toString() + " Instant " + retrieved2,
OffsetDateTime.ofInstant(INSTANT3, ZoneOffset.UTC).toString(FORMAT),
retrievedText3);
// tidy up
getDbConnector().getJdbcTemplate().update(drop);
}
