You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@openjpa.apache.org by st...@apache.org on 2019/01/25 18:11:58 UTC
[openjpa] 03/04: OPENJPA-2713 properly handle WITH TIME ZONE if
supported by db
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 <st...@apache.org>
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
+