openjpa-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From strub...@apache.org
Subject [openjpa] 03/04: OPENJPA-2713 properly handle WITH TIME ZONE if supported by db
Date Fri, 25 Jan 2019 18:11:58 GMT
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 <struberg@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
+


Mime
View raw message