This is an automated email from the ASF dual-hosted git repository. struberg pushed a commit to branch master in repository https://gitbox.apache.org/repos/asf/openjpa.git
commit 6d2544f390a53b8e7a8891ca5c025961dda948cc Author: Mark Struberg <[email protected]> AuthorDate: Fri Jan 25 16:42:15 2019 +0100 OPENJPA-2713 properly handle WITH TIME ZONE if supported by db This includes handling the new java.sql.Types.TIME_WITH_ZONE and DATE_WITH_ZONE. --- .../org/apache/openjpa/jdbc/sql/DBDictionary.java | 50 ++++----- .../apache/openjpa/jdbc/sql/DerbyDictionary.java | 18 ++++ .../openjpa/jdbc/sql/PostgresDictionary.java | 69 ++++++++++-- .../java/org/apache/openjpa/kernel/BrokerImpl.java | 2 +- .../openjpa/lib/jdbc/DelegatingResultSet.java | 4 +- .../persistence/simple/TestJava8TimeTypes.java | 18 +++- openjpa-project/supportedJava8TimeTypes.adoc | 118 +++++++++++++++++++++ 7 files changed, 236 insertions(+), 43 deletions(-) diff --git a/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/DBDictionary.java b/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/DBDictionary.java index 0fe52d5..f9bc575 100644 --- a/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/DBDictionary.java +++ b/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/DBDictionary.java @@ -51,7 +51,6 @@ import java.time.LocalTime; import java.time.OffsetDateTime; import java.time.OffsetTime; import java.time.ZoneId; -import java.time.ZoneOffset; import java.util.ArrayList; import java.util.Arrays; import java.util.Calendar; @@ -370,6 +369,8 @@ public class DBDictionary public String structTypeName = "STRUCT"; public String timeTypeName = "TIME"; public String timestampTypeName = "TIMESTAMP"; + public String timeWithZoneTypeName = "TIME WITH TIME ZONE"; + public String timestampWithZoneTypeName = "TIMESTAMP WITH TIME ZONE"; public String tinyintTypeName = "TINYINT"; public String varbinaryTypeName = "VARBINARY"; public String varcharTypeName = "VARCHAR"; @@ -1222,10 +1223,12 @@ public class DBDictionary */ public void setDate(PreparedStatement stmnt, int idx, java.sql.Date val, Calendar cal, Column col) throws SQLException { - if (cal == null) + if (cal == null) { stmnt.setDate(idx, val); - else + } + else { stmnt.setDate(idx, val, cal); + } } /** @@ -1292,8 +1295,7 @@ public class DBDictionary /** * Set the given value as a parameter to the statement. */ - public void setDouble(PreparedStatement stmnt, int idx, double val, - Column col) + public void setDouble(PreparedStatement stmnt, int idx, double val, Column col) throws SQLException { stmnt.setDouble(idx, val); } @@ -1301,8 +1303,7 @@ public class DBDictionary /** * Set the given value as a parameter to the statement. */ - public void setFloat(PreparedStatement stmnt, int idx, float val, - Column col) + public void setFloat(PreparedStatement stmnt, int idx, float val, Column col) throws SQLException { stmnt.setFloat(idx, val); } @@ -1326,8 +1327,7 @@ public class DBDictionary /** * Set the given value as a parameter to the statement. */ - public void setLocale(PreparedStatement stmnt, int idx, Locale val, - Column col) + public void setLocale(PreparedStatement stmnt, int idx, Locale val, Column col) throws SQLException { setString(stmnt, idx, val.getLanguage() + "_" + val.getCountry() + "_" + val.getVariant(), col); @@ -1337,8 +1337,7 @@ public class DBDictionary * Set null as a parameter to the statement. The column * type will come from {@link Types}. */ - public void setNull(PreparedStatement stmnt, int idx, int colType, - Column col) + public void setNull(PreparedStatement stmnt, int idx, int colType, Column col) throws SQLException { stmnt.setNull(idx, colType); } @@ -1346,8 +1345,7 @@ public class DBDictionary /** * Set the given value as a parameter to the statement. */ - public void setNumber(PreparedStatement stmnt, int idx, Number num, - Column col) + public void setNumber(PreparedStatement stmnt, int idx, Number num, Column col) throws SQLException { // check for known floating point types to give driver a chance to // handle special numbers like NaN and infinity; bug #1053 @@ -1363,8 +1361,7 @@ public class DBDictionary * Set the given value as a parameter to the statement. The column * type will come from {@link Types}. */ - public void setObject(PreparedStatement stmnt, int idx, Object val, - int colType, Column col) + public void setObject(PreparedStatement stmnt, int idx, Object val, int colType, Column col) throws SQLException { if (colType == -1 || colType == Types.OTHER) stmnt.setObject(idx, val); @@ -1383,8 +1380,7 @@ public class DBDictionary /** * Set the given value as a parameter to the statement. */ - public void setShort(PreparedStatement stmnt, int idx, short val, - Column col) + public void setShort(PreparedStatement stmnt, int idx, short val, Column col) throws SQLException { stmnt.setShort(idx, val); } @@ -1392,8 +1388,7 @@ public class DBDictionary /** * Set the given value as a parameter to the statement. */ - public void setString(PreparedStatement stmnt, int idx, String val, - Column col) + public void setString(PreparedStatement stmnt, int idx, String val, Column col) throws SQLException { stmnt.setString(idx, val); } @@ -1401,8 +1396,7 @@ public class DBDictionary /** * Set the given value as a parameter to the statement. */ - public void setTime(PreparedStatement stmnt, int idx, Time val, - Calendar cal, Column col) + public void setTime(PreparedStatement stmnt, int idx, Time val, Calendar cal, Column col) throws SQLException { if (cal == null) stmnt.setTime(idx, val); @@ -1413,8 +1407,7 @@ public class DBDictionary /** * Set the given value as a parameter to the statement. */ - public void setTimestamp(PreparedStatement stmnt, int idx, - Timestamp val, Calendar cal, Column col) + public void setTimestamp(PreparedStatement stmnt, int idx, Timestamp val, Calendar cal, Column col) throws SQLException { val = StateManagerImpl.roundTimestamp(val, datePrecision); @@ -1435,8 +1428,7 @@ public class DBDictionary * @param type the field mapping type code for the value * @param store the store manager for the current context */ - public void setTyped(PreparedStatement stmnt, int idx, Object val, - Column col, int type, JDBCStore store) + public void setTyped(PreparedStatement stmnt, int idx, Object val, Column col, int type, JDBCStore store) throws SQLException { if (val == null) { setNull(stmnt, idx, (col == null) ? Types.OTHER : col.getType(), @@ -1857,9 +1849,9 @@ public class DBDictionary case JavaTypes.LOCAL_DATETIME: return getPreferredType(Types.TIMESTAMP); case JavaTypes.OFFSET_TIME: - return getPreferredType(Types.TIME); + return getPreferredType(Types.TIME_WITH_TIMEZONE); case JavaTypes.OFFSET_DATETIME: - return getPreferredType(Types.TIMESTAMP); + return getPreferredType(Types.TIMESTAMP_WITH_TIMEZONE); case JavaSQLTypes.SQL_ARRAY: return getPreferredType(Types.ARRAY); case JavaSQLTypes.BINARY_STREAM: @@ -1962,6 +1954,10 @@ public class DBDictionary return timeTypeName; case Types.TIMESTAMP: return timestampTypeName; + case Types.TIME_WITH_TIMEZONE: + return timeWithZoneTypeName; + case Types.TIMESTAMP_WITH_TIMEZONE: + return timestampWithZoneTypeName; case Types.TINYINT: return tinyintTypeName; case Types.VARBINARY: diff --git a/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/DerbyDictionary.java b/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/DerbyDictionary.java index 5c139de..63b996e 100644 --- a/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/DerbyDictionary.java +++ b/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/DerbyDictionary.java @@ -21,6 +21,7 @@ package org.apache.openjpa.jdbc.sql; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; +import java.sql.Types; import java.util.Arrays; import javax.sql.DataSource; @@ -69,6 +70,8 @@ public class DerbyDictionary supportsComments = true; + // Derby does still not support 'WITH TIMEZONE' from the SQL92 standard + fixedSizeTypeNameSet.addAll(Arrays.asList(new String[]{ "BIGINT", "INTEGER", "TEXT" })); @@ -223,4 +226,19 @@ public class DerbyDictionary } return count; } + + /** + * Derby doesn't support SQL-2003 'WITH TIMEZONE' nor the respective JDBC types. + */ + @Override + public int getPreferredType(int type) { + switch (type) { + case Types.TIME_WITH_TIMEZONE: + return Types.TIME; + case Types.TIMESTAMP_WITH_TIMEZONE: + return Types.TIMESTAMP; + default: + return type; + } + } } diff --git a/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/PostgresDictionary.java b/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/PostgresDictionary.java index 2ba03e0..e64d1de 100644 --- a/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/PostgresDictionary.java +++ b/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/PostgresDictionary.java @@ -34,6 +34,10 @@ import java.sql.Statement; import java.sql.Types; import java.text.ParseException; import java.text.SimpleDateFormat; +import java.time.LocalDate; +import java.time.LocalDateTime; +import java.time.LocalTime; +import java.time.OffsetDateTime; import java.util.Arrays; import java.util.Date; import java.util.HashSet; @@ -64,11 +68,9 @@ import org.postgresql.largeobject.LargeObjectManager; /** * Dictionary for PostgreSQL. */ -public class PostgresDictionary - extends DBDictionary { +public class PostgresDictionary extends DBDictionary { - private static final Localizer _loc = Localizer.forPackage - (PostgresDictionary.class); + private static final Localizer _loc = Localizer.forPackage(PostgresDictionary.class); private Method dbcpGetDelegate; @@ -122,6 +124,13 @@ public class PostgresDictionary */ public String isOwnedSequenceSQL = "SELECT pg_get_serial_sequence(?, ?)"; + + /** + * Since PostgreSQL + */ + private boolean supportsTimezone; + + public PostgresDictionary() { platform = "PostgreSQL"; validationSQL = "SELECT NOW()"; @@ -411,14 +420,15 @@ public class PostgresDictionary } } } catch (Throwable t) { - if (log.isWarnEnabled()) + if (log.isWarnEnabled()) { log.warn(_loc.get("psql-owned-seq-warning"), t); + } return isOwnedSequence(strName); } } else { if(log.isTraceEnabled()) { log.trace(String.format("Unable to query ownership for sequence %s using the connection. " + - "Falling back to simpler detection based on the name", + "Falling back to simpler detection based on the name", name.getName())); } @@ -723,6 +733,47 @@ public class PostgresDictionary } } + + @Override + public LocalDate getLocalDate(ResultSet rs, int column) throws SQLException { + return rs.getObject(column, LocalDate.class); + } + + @Override + public LocalTime getLocalTime(ResultSet rs, int column) throws SQLException { + return rs.getObject(column, LocalTime.class); + } + + @Override + public LocalDateTime getLocalDateTime(ResultSet rs, int column) throws SQLException { + return rs.getObject(column, LocalDateTime.class); + } + + @Override + public OffsetDateTime getOffsetDateTime(ResultSet rs, int column) throws SQLException { + return rs.getObject(column, OffsetDateTime.class); + } + + @Override + public void setLocalDate(PreparedStatement stmnt, int idx, LocalDate val, Column col) throws SQLException { + stmnt.setObject(idx, val); + } + + @Override + public void setLocalTime(PreparedStatement stmnt, int idx, LocalTime val, Column col) throws SQLException { + stmnt.setObject(idx, val); + } + + @Override + public void setLocalDateTime(PreparedStatement stmnt, int idx, LocalDateTime val, Column col) throws SQLException { + stmnt.setObject(idx, val); + } + + @Override + public void setOffsetDateTime(PreparedStatement stmnt, int idx, OffsetDateTime val, Column col) throws SQLException { + stmnt.setObject(idx, val); + } + /** * Determine XML column support and backslash handling. */ @@ -967,8 +1018,7 @@ public class PostgresDictionary /** * Connection wrapper to work around the postgres empty result set bug. */ - protected static class PostgresConnection - extends DelegatingConnection { + protected static class PostgresConnection extends DelegatingConnection { private final PostgresDictionary _dict; @@ -996,8 +1046,7 @@ public class PostgresDictionary /** * Statement wrapper to work around the postgres empty result set bug. */ - protected static class PostgresPreparedStatement - extends DelegatingPreparedStatement { + protected static class PostgresPreparedStatement extends DelegatingPreparedStatement { private final PostgresDictionary _dict; diff --git a/openjpa-kernel/src/main/java/org/apache/openjpa/kernel/BrokerImpl.java b/openjpa-kernel/src/main/java/org/apache/openjpa/kernel/BrokerImpl.java index bfa1ef9..a1a7680 100644 --- a/openjpa-kernel/src/main/java/org/apache/openjpa/kernel/BrokerImpl.java +++ b/openjpa-kernel/src/main/java/org/apache/openjpa/kernel/BrokerImpl.java @@ -2464,7 +2464,7 @@ public class BrokerImpl implements Broker, FindCallbacks, Cloneable, Serializabl Object failedObject = null; if (t[0] instanceof OpenJPAException){ - failedObject = ((OpenJPAException)t[0]).getFailedObject(); + failedObject = ((OpenJPAException)t[0]).getFailedObject(); } return new StoreException(_loc.get("rolled-back")). diff --git a/openjpa-lib/src/main/java/org/apache/openjpa/lib/jdbc/DelegatingResultSet.java b/openjpa-lib/src/main/java/org/apache/openjpa/lib/jdbc/DelegatingResultSet.java index b12422c..63172d9 100644 --- a/openjpa-lib/src/main/java/org/apache/openjpa/lib/jdbc/DelegatingResultSet.java +++ b/openjpa-lib/src/main/java/org/apache/openjpa/lib/jdbc/DelegatingResultSet.java @@ -1077,12 +1077,12 @@ public class DelegatingResultSet implements ResultSet, Closeable { @Override public <T>T getObject(String columnLabel, Class<T> type) throws SQLException{ - throw new UnsupportedOperationException(); + return _rs.getObject(columnLabel, type); } @Override public <T>T getObject(int columnIndex, Class<T> type) throws SQLException{ - throw new UnsupportedOperationException(); + return _rs.getObject(columnIndex, type); } } diff --git a/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/simple/TestJava8TimeTypes.java b/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/simple/TestJava8TimeTypes.java index 8175036..f0c5637 100644 --- a/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/simple/TestJava8TimeTypes.java +++ b/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/simple/TestJava8TimeTypes.java @@ -21,6 +21,8 @@ package org.apache.openjpa.persistence.simple; import org.apache.openjpa.persistence.test.SingleEMFTestCase; import javax.persistence.EntityManager; + +import java.time.Instant; import java.time.LocalDate; import java.time.LocalDateTime; import java.time.LocalTime; @@ -35,12 +37,13 @@ public class TestJava8TimeTypes extends SingleEMFTestCase { private static String VAL_LOCAL_TIME = "04:57:15"; private static String VAL_LOCAL_DATETIME = "2019-01-01T01:00:00"; + @Override public void setUp() { setUp(CLEAR_TABLES, Java8TimeTypes.class); } - public void testJava8Types() throws Exception { + public void testJava8Types() { EntityManager em = emf.createEntityManager(); em.getTransaction().begin(); Java8TimeTypes e = new Java8TimeTypes(); @@ -63,8 +66,17 @@ public class TestJava8TimeTypes extends SingleEMFTestCase { assertEquals(LocalTime.parse(VAL_LOCAL_TIME), eRead.getLocalTimeField()); assertEquals(LocalDate.parse(VAL_LOCAL_DATE), eRead.getLocalDateField()); assertEquals(LocalDateTime.parse(VAL_LOCAL_DATETIME), eRead.getLocalDateTimeField()); - assertEquals(e.getOffsetTimeField(), eRead.getOffsetTimeField()); - assertEquals(e.getOffsetDateTimeField(), eRead.getOffsetDateTimeField()); + + + // Many databases do not support WITH TIMEZONE syntax. + // Thus we can only portably ensure tha the same instant is used at least. + assertEquals(Instant.from(e.getOffsetDateTimeField()), + Instant.from(eRead.getOffsetDateTimeField())); + + assertEquals(e.getOffsetTimeField().withOffsetSameInstant(eRead.getOffsetTimeField().getOffset()), + eRead.getOffsetTimeField()); } + + } diff --git a/openjpa-project/supportedJava8TimeTypes.adoc b/openjpa-project/supportedJava8TimeTypes.adoc new file mode 100644 index 0000000..0a18a07 --- /dev/null +++ b/openjpa-project/supportedJava8TimeTypes.adoc @@ -0,0 +1,118 @@ += Supported Java8 Time types + +This is a temporary document to describe the state of the java.time.* integration in Apache OpenJPA. + +== JPA-2.2 required types + +The following java.time types have to be supported mandatorily in JPA-2.2: + +* java.time.LocalDate +* java.time.LocalTime +* java.time.LocalDateTime +* java.time.OffsetTime +* java.time.OffsetDateTime + +Apache OpenJPA additionally supports the following types: + +* TODO java.time.Instant etc + +Not every database supports all those types natively though. +Some cannot store them at all, others have a mode which e.g. doesn't store the Offset part. + +For our example we assume we live in Europe (+1 timezone). +If you store 04:12-9 then you might actually end up with 14:12+1 when retrieving the value from the database. +That means that OpenJPA tries to at least keep the instant correct if the database doesn't support zones natively. + +== Database Support matrix: + + +[cols=6*,options=header] +|=== +| DBName +| LocalDate +| LocalTime +| LocalDateTime +| OffsetTime +| OffsetDateTime + +| Derby +| DATE +| TIME +| TIMESTAMP +| not natively supported +| not natively supported + +| PostgreSQL +| DATE +| TIME +| TIMESTAMP +| TIME WITH TIME ZONE +| TIMESTAMP WITH TIME ZONE + +| MySQL +| DATE +| TIME +| DATETIME +| not natively supported, fallback to TIME +| not natively supported, fallback to DATETIME + +| MariaDB +| DATE +| TIME +| DATETIME +| not natively supported, fallback to TIME +| not natively supported, fallback to DATETIME + +| Microsoft SQLServer +| DATE +| TIME +| DATETIME2 +| +| + +| Oracle +| DATE +| TIME +| DATE +| TIME WITH TIME ZONE +| TIMESTAMP WITH TIME ZONE + +| H2 +| DATE +| TIME +| DATE +| +| + + +... +|=== + + +=== Notes + +==== PostgreSQL + +PostgreSQL supports some of the types natively in the JDBC driver: +* LocalDate +* LocalTime +* LocalDateTime +* OffsetDateTime + +Note that `OffsetTime` is not supported in `setObject/getObject`. + +Also note that PostgreSQL always stores DateTime values internally as UTC. +Thus when retrieving the date back from the Database again you will get the same Instant representation, but in a the local TimeZone! + + + +==== MySQL & MariaDB + +MySQL supports LocalDate, LocalTime and LocalDateTime in `setObject/getObject`. +It also supports OffsetTime and OffsetDateTime in `setObject/getObject`, but only via conversion. +So the MySQL JDBC driver will effectively convert them to the local timezone and keep the 'Instant'. + +MariaDB does basically the same. + +==== Oracle +
