METAMODEL-1107: Fixed
authorkaspersorensen <i.am.kasper.sorensen@gmail.com>
Mon, 25 Jul 2016 16:16:59 +0000 (09:16 -0700)
committerkaspersorensen <i.am.kasper.sorensen@gmail.com>
Mon, 25 Jul 2016 16:16:59 +0000 (09:16 -0700)
Closes #112

14 files changed:
CHANGES.md
jdbc/pom.xml
jdbc/src/main/java/org/apache/metamodel/jdbc/JdbcDataSet.java
jdbc/src/main/java/org/apache/metamodel/jdbc/JdbcDeleteBuilder.java
jdbc/src/main/java/org/apache/metamodel/jdbc/JdbcInsertBuilder.java
jdbc/src/main/java/org/apache/metamodel/jdbc/JdbcUpdateBuilder.java
jdbc/src/main/java/org/apache/metamodel/jdbc/JdbcUtils.java
jdbc/src/main/java/org/apache/metamodel/jdbc/dialects/AbstractQueryRewriter.java
jdbc/src/main/java/org/apache/metamodel/jdbc/dialects/DB2QueryRewriter.java
jdbc/src/main/java/org/apache/metamodel/jdbc/dialects/HiveQueryRewriter.java
jdbc/src/main/java/org/apache/metamodel/jdbc/dialects/IQueryRewriter.java
jdbc/src/main/java/org/apache/metamodel/jdbc/dialects/MysqlQueryRewriter.java
jdbc/src/main/java/org/apache/metamodel/jdbc/dialects/PostgresqlQueryRewriter.java
jdbc/src/test/java/org/apache/metamodel/jdbc/integrationtests/PostgresqlTest.java

index 0b2b49d..4f61177 100644 (file)
@@ -7,6 +7,7 @@
  * [METAMODEL-1094] - Added support for Apache Cassandra version 3.x.
  * [METAMODEL-1093] - Close compiled ResultSets.
  * [METAMODEL-1102] - Separated FixedWidthLineParser.
+ * [METAMODEL-1107] - Added support for PostgreSQL's "json" and "jsonb" data types.
  
 ### Apache MetaModel 4.5.3
 
@@ -57,8 +58,8 @@
 
 ### Apache MetaModel 4.4.0
 
- * [METAMODEL-192] - Added support for Scalar functions. We have a basic set of datatype conversion functions as well as support for UDF via implementing the ScalarFunction interface.
- * [METAMODEL-194] - Added support for setting the "Max rows" flag of a query to 0. This will always return an empty dataset.
+ * [METAMODEL-192] - Added support for Scalar functions. We have a basic set of data type conversion functions as well as support for UDF via implementing the ScalarFunction interface.
+ * [METAMODEL-194] - Added support for setting the "Max rows" flag of a query to 0. This will always return an empty data set.
  * [METAMODEL-173] - Improved CSV writing to non-file destinations. Added .write() and .append() methods to Resource interface.
  * [METAMODEL-170] - Dropped support for Java 6.
  * [METAMODEL-176] - Trimmed the transient dependencies of the JDBC module.
index fb332f7..3969b1c 100644 (file)
                        <groupId>org.postgresql</groupId>
                        <artifactId>postgresql</artifactId>
                        <version>9.3-1104-jdbc4</version>
-                       <scope>test</scope>
+                       <!-- optional instead of test-scoped because we code against it in the rewriter class -->
+                       <optional>true</optional>
+               </dependency>
+               <dependency>
+                       <groupId>com.fasterxml.jackson.core</groupId>
+                       <artifactId>jackson-core</artifactId>
+                       <optional>true</optional>
+               </dependency>
+               <dependency>
+                       <groupId>com.fasterxml.jackson.core</groupId>
+                       <artifactId>jackson-databind</artifactId>
+                       <optional>true</optional>
                </dependency>
                <dependency>
                        <groupId>mysql</groupId>
index 4142ab7..cbcdb7a 100644 (file)
  */
 package org.apache.metamodel.jdbc;
 
-import java.io.InputStream;
-import java.io.Reader;
-import java.sql.Blob;
-import java.sql.Clob;
 import java.sql.Connection;
 import java.sql.ResultSet;
 import java.sql.SQLException;
@@ -31,10 +27,11 @@ import org.apache.metamodel.MetaModelException;
 import org.apache.metamodel.data.AbstractDataSet;
 import org.apache.metamodel.data.DefaultRow;
 import org.apache.metamodel.data.Row;
+import org.apache.metamodel.jdbc.dialects.DefaultQueryRewriter;
+import org.apache.metamodel.jdbc.dialects.IQueryRewriter;
 import org.apache.metamodel.query.Query;
 import org.apache.metamodel.query.SelectItem;
 import org.apache.metamodel.schema.Column;
-import org.apache.metamodel.schema.ColumnType;
 import org.apache.metamodel.util.FileHelper;
 import org.slf4j.Logger;
 import org.slf4j.LoggerFactory;
@@ -131,8 +128,8 @@ final class JdbcDataSet extends AbstractDataSet {
                             values[i] = null;
                         }
                     } catch (Exception e) {
-                        logger.debug("Could not invoke wasNull() method on resultset, error message: {}",
-                                e.getMessage());
+                        logger.debug("Could not invoke wasNull() method on resultset, error message: {}", e
+                                .getMessage());
                     }
                 }
                 _row = new DefaultRow(getHeader(), values);
@@ -149,44 +146,18 @@ final class JdbcDataSet extends AbstractDataSet {
         final SelectItem selectItem = getHeader().getSelectItem(i);
         final int columnIndex = i + 1;
         if (selectItem.getAggregateFunction() == null) {
-            Column column = selectItem.getColumn();
+            final Column column = selectItem.getColumn();
             if (column != null) {
-                ColumnType type = column.getType();
-                try {
-                    if (type == ColumnType.TIME) {
-                        return _resultSet.getTime(columnIndex);
-                    } else if (type == ColumnType.DATE) {
-                        return _resultSet.getDate(columnIndex);
-                    } else if (type == ColumnType.TIMESTAMP) {
-                        return _resultSet.getTimestamp(columnIndex);
-                    } else if (type == ColumnType.BLOB) {
-                        final Blob blob = _resultSet.getBlob(columnIndex);
-                        return blob;
-                    } else if (type == JdbcDataContext.COLUMN_TYPE_BLOB_AS_BYTES) {
-                        final Blob blob = _resultSet.getBlob(columnIndex);
-                        final InputStream inputStream = blob.getBinaryStream();
-                        final byte[] bytes = FileHelper.readAsBytes(inputStream);
-                        return bytes;
-                    } else if (type.isBinary()) {
-                        return _resultSet.getBytes(columnIndex);
-                    } else if (type == ColumnType.CLOB || type == ColumnType.NCLOB) {
-                        final Clob clob = _resultSet.getClob(columnIndex);
-                        return clob;
-                    } else if (type == JdbcDataContext.COLUMN_TYPE_CLOB_AS_STRING) {
-                        final Clob clob = _resultSet.getClob(columnIndex);
-                        final Reader reader = clob.getCharacterStream();
-                        final String result = FileHelper.readAsString(reader);
-                        return result;
-                    } else if (type.isBoolean()) {
-                        return _resultSet.getBoolean(columnIndex);
-                    }
-                } catch (Exception e) {
-                    logger.warn("Failed to retrieve " + type
-                            + " value using type-specific getter, retrying with generic getObject(...) method", e);
+                final IQueryRewriter queryRewriter;
+                if (_jdbcDataContext == null) {
+                    queryRewriter = new DefaultQueryRewriter(null);
+                } else {
+                    queryRewriter = _jdbcDataContext.getQueryRewriter();
                 }
+                return queryRewriter.getResultSetValue(resultSet, columnIndex, column);
             }
         }
-        return _resultSet.getObject(columnIndex);
+        return resultSet.getObject(columnIndex);
     }
 
     /**
index bf6aaf2..e46cd19 100644 (file)
@@ -70,7 +70,7 @@ final class JdbcDeleteBuilder extends AbstractRowDeletionBuilder {
                 for (FilterItem whereItem : whereItems) {
                     if (JdbcUtils.isPreparedParameterCandidate(whereItem)) {
                         Object operand = whereItem.getOperand();
-                        JdbcUtils.setStatementValue(st, valueCounter, whereItem.getSelectItem().getColumn(), operand);
+                        _queryRewriter.setStatementParameter(st, valueCounter, whereItem.getSelectItem().getColumn(), operand);
                         valueCounter++;
                     }
                 }
index 66cdbe7..d9c7a5e 100644 (file)
@@ -76,7 +76,7 @@ final class JdbcInsertBuilder extends AbstractRowInsertionBuilder<JdbcUpdateCall
                                for (int i = 0; i < columns.length; i++) {
                                        boolean explicitNull = explicitNulls[i];
                                        if (values[i] != null || explicitNull) {
-                                               JdbcUtils.setStatementValue(st, valueCounter, columns[i], values[i]);
+                                           _queryRewriter.setStatementParameter(st, valueCounter, columns[i], values[i]);
                                                valueCounter++;
                                        }
                                }
index 6610a7e..590a23d 100644 (file)
@@ -72,7 +72,7 @@ final class JdbcUpdateBuilder extends AbstractRowUpdationBuilder {
                 for (int i = 0; i < columns.length; i++) {
                     boolean explicitNull = explicitNulls[i];
                     if (values[i] != null || explicitNull) {
-                        JdbcUtils.setStatementValue(st, valueCounter, columns[i], values[i]);
+                        _queryRewriter.setStatementParameter(st, valueCounter, columns[i], values[i]);
 
                         valueCounter++;
                     }
@@ -84,7 +84,7 @@ final class JdbcUpdateBuilder extends AbstractRowUpdationBuilder {
                         final Object operand = whereItem.getOperand();
                         final Column column = whereItem.getSelectItem().getColumn();
 
-                        JdbcUtils.setStatementValue(st, valueCounter, column, operand);
+                        _queryRewriter.setStatementParameter(st, valueCounter, column, operand);
 
                         valueCounter++;
                     }
index 1073d6f..2d66790 100644 (file)
  */
 package org.apache.metamodel.jdbc;
 
-import java.io.InputStream;
-import java.io.Reader;
-import java.sql.Blob;
-import java.sql.Clob;
-import java.sql.NClob;
 import java.sql.PreparedStatement;
 import java.sql.SQLException;
-import java.sql.Time;
-import java.sql.Timestamp;
-import java.util.Calendar;
-import java.util.Date;
 import java.util.List;
 
 import org.apache.metamodel.MetaModelException;
+import org.apache.metamodel.jdbc.dialects.DefaultQueryRewriter;
 import org.apache.metamodel.jdbc.dialects.IQueryRewriter;
 import org.apache.metamodel.query.FilterItem;
 import org.apache.metamodel.query.OperatorType;
@@ -39,7 +31,6 @@ import org.apache.metamodel.query.QueryParameter;
 import org.apache.metamodel.schema.Column;
 import org.apache.metamodel.schema.ColumnType;
 import org.apache.metamodel.schema.TableType;
-import org.apache.metamodel.util.FileHelper;
 import org.apache.metamodel.util.FormatHelper;
 import org.slf4j.Logger;
 import org.slf4j.LoggerFactory;
@@ -71,133 +62,12 @@ public final class JdbcUtils {
     }
 
     /**
-     * Method which handles the action of setting a parameterized value on a
-     * statement. Traditionally this is done using the
-     * {@link PreparedStatement#setObject(int, Object)} method but for some
-     * types we use more specific setter methods.
-     * 
-     * @param st
-     * @param valueIndex
-     * @param column
-     * @param value
-     * @throws SQLException
+     * @deprecated use {@link IQueryRewriter#setStatementParameter(PreparedStatement, int, Column, Object)}
      */
+    @Deprecated
     public static void setStatementValue(final PreparedStatement st, final int valueIndex, final Column column,
             Object value) throws SQLException {
-        final ColumnType type = (column == null ? null : column.getType());
-
-        if (type == null || type == ColumnType.OTHER) {
-            // type is not known - nothing more we can do to narrow the type
-            st.setObject(valueIndex, value);
-            return;
-        }
-
-        if (value == null && type != null) {
-            try {
-                final int jdbcType = type.getJdbcType();
-                st.setNull(valueIndex, jdbcType);
-                return;
-            } catch (Exception e) {
-                logger.warn("Exception occurred while calling setNull(...) for value index " + valueIndex
-                        + ". Attempting value-based setter method instead.", e);
-            }
-        }
-
-        if (type == ColumnType.VARCHAR && value instanceof Date) {
-            // some drivers (SQLite and JTDS for MS SQL server) treat dates as
-            // VARCHARS. In that case we need to convert the dates to the
-            // correct format
-            String nativeType = column.getNativeType();
-            Date date = (Date) value;
-            if ("DATE".equalsIgnoreCase(nativeType)) {
-                value = FormatHelper.formatSqlTime(ColumnType.DATE, date, false);
-            } else if ("TIME".equalsIgnoreCase(nativeType)) {
-                value = FormatHelper.formatSqlTime(ColumnType.TIME, date, false);
-            } else if ("TIMESTAMP".equalsIgnoreCase(nativeType) || "DATETIME".equalsIgnoreCase(nativeType)) {
-                value = FormatHelper.formatSqlTime(ColumnType.TIMESTAMP, date, false);
-            }
-        }
-
-        if (type != null && type.isTimeBased() && value instanceof String) {
-            value = FormatHelper.parseSqlTime(type, (String) value);
-        }
-
-        try {
-            if (type == ColumnType.DATE && value instanceof Date) {
-                Calendar cal = Calendar.getInstance();
-                cal.setTime((Date) value);
-                st.setDate(valueIndex, new java.sql.Date(cal.getTimeInMillis()), cal);
-            } else if (type == ColumnType.TIME && value instanceof Date) {
-                final Time time = toTime((Date) value);
-                st.setTime(valueIndex, time);
-            } else if (type == ColumnType.TIMESTAMP && value instanceof Date) {
-                final Timestamp ts = toTimestamp((Date) value);
-                st.setTimestamp(valueIndex, ts);
-            } else if (type == ColumnType.CLOB || type == ColumnType.NCLOB) {
-                if (value instanceof InputStream) {
-                    InputStream inputStream = (InputStream) value;
-                    st.setAsciiStream(valueIndex, inputStream);
-                } else if (value instanceof Reader) {
-                    Reader reader = (Reader) value;
-                    st.setCharacterStream(valueIndex, reader);
-                } else if (value instanceof NClob) {
-                    NClob nclob = (NClob) value;
-                    st.setNClob(valueIndex, nclob);
-                } else if (value instanceof Clob) {
-                    Clob clob = (Clob) value;
-                    st.setClob(valueIndex, clob);
-                } else if (value instanceof String) {
-                    st.setString(valueIndex, (String) value);
-                } else {
-                    st.setObject(valueIndex, value);
-                }
-            } else if (type == ColumnType.BLOB || type == ColumnType.BINARY) {
-                if (value instanceof byte[]) {
-                    byte[] bytes = (byte[]) value;
-                    st.setBytes(valueIndex, bytes);
-                } else if (value instanceof InputStream) {
-                    InputStream inputStream = (InputStream) value;
-                    st.setBinaryStream(valueIndex, inputStream);
-                } else if (value instanceof Blob) {
-                    Blob blob = (Blob) value;
-                    st.setBlob(valueIndex, blob);
-                } else {
-                    st.setObject(valueIndex, value);
-                }
-            } else if (type.isLiteral()) {
-                final String str;
-                if (value instanceof Reader) {
-                    Reader reader = (Reader) value;
-                    str = FileHelper.readAsString(reader);
-                } else {
-                    str = value.toString();
-                }
-                st.setString(valueIndex, str);
-            } else {
-                st.setObject(valueIndex, value);
-            }
-        } catch (SQLException e) {
-            logger.error("Failed to set parameter {} to value: {}", valueIndex, value);
-            throw e;
-        }
-    }
-
-    private static Time toTime(Date value) {
-        if (value instanceof Time) {
-            return (Time) value;
-        }
-        final Calendar cal = Calendar.getInstance();
-        cal.setTime((Date) value);
-        return new java.sql.Time(cal.getTimeInMillis());
-    }
-
-    private static Timestamp toTimestamp(Date value) {
-        if (value instanceof Timestamp) {
-            return (Timestamp) value;
-        }
-        final Calendar cal = Calendar.getInstance();
-        cal.setTime((Date) value);
-        return new Timestamp(cal.getTimeInMillis());
+        new DefaultQueryRewriter(null).setStatementParameter(st, valueIndex, column, value);
     }
 
     public static String getValueAsSql(Column column, Object value, IQueryRewriter queryRewriter) {
@@ -208,7 +78,7 @@ public final class JdbcUtils {
         if (columnType.isLiteral() && value instanceof String) {
             value = queryRewriter.escapeQuotes((String) value);
         }
-        String formatSqlValue = FormatHelper.formatSqlValue(columnType, value);
+        final String formatSqlValue = FormatHelper.formatSqlValue(columnType, value);
         return formatSqlValue;
     }
 
index 087bf2f..5ce4445 100644 (file)
  */
 package org.apache.metamodel.jdbc.dialects;
 
+import java.io.InputStream;
+import java.io.Reader;
+import java.sql.Blob;
+import java.sql.Clob;
+import java.sql.NClob;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Time;
+import java.sql.Timestamp;
+import java.util.Calendar;
+import java.util.Date;
 import java.util.List;
 
 import org.apache.metamodel.jdbc.JdbcDataContext;
@@ -35,8 +47,11 @@ import org.apache.metamodel.query.Query;
 import org.apache.metamodel.query.ScalarFunction;
 import org.apache.metamodel.query.SelectClause;
 import org.apache.metamodel.query.SelectItem;
+import org.apache.metamodel.schema.Column;
 import org.apache.metamodel.schema.ColumnType;
 import org.apache.metamodel.schema.ColumnTypeImpl;
+import org.apache.metamodel.util.FileHelper;
+import org.apache.metamodel.util.FormatHelper;
 import org.slf4j.Logger;
 import org.slf4j.LoggerFactory;
 
@@ -287,4 +302,162 @@ public abstract class AbstractQueryRewriter implements IQueryRewriter {
         }
         return item.toSql(isSchemaIncludedInColumnPaths());
     }
+    
+    @Override
+    public void setStatementParameter(PreparedStatement st, int valueIndex, Column column, Object value)
+            throws SQLException {
+
+        final ColumnType type = (column == null ? null : column.getType());
+
+        if (type == null || type == ColumnType.OTHER) {
+            // type is not known - nothing more we can do to narrow the type
+            st.setObject(valueIndex, value);
+            return;
+        }
+
+        if (value == null && type != null) {
+            try {
+                final int jdbcType = type.getJdbcType();
+                st.setNull(valueIndex, jdbcType);
+                return;
+            } catch (Exception e) {
+                logger.warn("Exception occurred while calling setNull(...) for value index " + valueIndex
+                        + ". Attempting value-based setter method instead.", e);
+            }
+        }
+
+        if (type == ColumnType.VARCHAR && value instanceof Date) {
+            // some drivers (SQLite and JTDS for MS SQL server) treat dates as
+            // VARCHARS. In that case we need to convert the dates to the
+            // correct format
+            String nativeType = column.getNativeType();
+            Date date = (Date) value;
+            if ("DATE".equalsIgnoreCase(nativeType)) {
+                value = FormatHelper.formatSqlTime(ColumnType.DATE, date, false);
+            } else if ("TIME".equalsIgnoreCase(nativeType)) {
+                value = FormatHelper.formatSqlTime(ColumnType.TIME, date, false);
+            } else if ("TIMESTAMP".equalsIgnoreCase(nativeType) || "DATETIME".equalsIgnoreCase(nativeType)) {
+                value = FormatHelper.formatSqlTime(ColumnType.TIMESTAMP, date, false);
+            }
+        }
+
+        if (type != null && type.isTimeBased() && value instanceof String) {
+            value = FormatHelper.parseSqlTime(type, (String) value);
+        }
+
+        try {
+            if (type == ColumnType.DATE && value instanceof Date) {
+                Calendar cal = Calendar.getInstance();
+                cal.setTime((Date) value);
+                st.setDate(valueIndex, new java.sql.Date(cal.getTimeInMillis()), cal);
+            } else if (type == ColumnType.TIME && value instanceof Date) {
+                final Time time = toTime((Date) value);
+                st.setTime(valueIndex, time);
+            } else if (type == ColumnType.TIMESTAMP && value instanceof Date) {
+                final Timestamp ts = toTimestamp((Date) value);
+                st.setTimestamp(valueIndex, ts);
+            } else if (type == ColumnType.CLOB || type == ColumnType.NCLOB) {
+                if (value instanceof InputStream) {
+                    InputStream inputStream = (InputStream) value;
+                    st.setAsciiStream(valueIndex, inputStream);
+                } else if (value instanceof Reader) {
+                    Reader reader = (Reader) value;
+                    st.setCharacterStream(valueIndex, reader);
+                } else if (value instanceof NClob) {
+                    NClob nclob = (NClob) value;
+                    st.setNClob(valueIndex, nclob);
+                } else if (value instanceof Clob) {
+                    Clob clob = (Clob) value;
+                    st.setClob(valueIndex, clob);
+                } else if (value instanceof String) {
+                    st.setString(valueIndex, (String) value);
+                } else {
+                    st.setObject(valueIndex, value);
+                }
+            } else if (type == ColumnType.BLOB || type == ColumnType.BINARY) {
+                if (value instanceof byte[]) {
+                    byte[] bytes = (byte[]) value;
+                    st.setBytes(valueIndex, bytes);
+                } else if (value instanceof InputStream) {
+                    InputStream inputStream = (InputStream) value;
+                    st.setBinaryStream(valueIndex, inputStream);
+                } else if (value instanceof Blob) {
+                    Blob blob = (Blob) value;
+                    st.setBlob(valueIndex, blob);
+                } else {
+                    st.setObject(valueIndex, value);
+                }
+            } else if (type.isLiteral()) {
+                final String str;
+                if (value instanceof Reader) {
+                    Reader reader = (Reader) value;
+                    str = FileHelper.readAsString(reader);
+                } else {
+                    str = value.toString();
+                }
+                st.setString(valueIndex, str);
+            } else {
+                st.setObject(valueIndex, value);
+            }
+        } catch (SQLException e) {
+            logger.error("Failed to set parameter {} to value: {}", valueIndex, value);
+            throw e;
+        }
+    }
+    
+    protected Time toTime(Date value) {
+        if (value instanceof Time) {
+            return (Time) value;
+        }
+        final Calendar cal = Calendar.getInstance();
+        cal.setTime((Date) value);
+        return new java.sql.Time(cal.getTimeInMillis());
+    }
+
+    protected Timestamp toTimestamp(Date value) {
+        if (value instanceof Timestamp) {
+            return (Timestamp) value;
+        }
+        final Calendar cal = Calendar.getInstance();
+        cal.setTime((Date) value);
+        return new Timestamp(cal.getTimeInMillis());
+    }
+    
+    @Override
+    public Object getResultSetValue(ResultSet resultSet, int columnIndex, Column column) throws SQLException {
+        final ColumnType type = column.getType();
+        try {
+            if (type == ColumnType.TIME) {
+                return resultSet.getTime(columnIndex);
+            } else if (type == ColumnType.DATE) {
+                return resultSet.getDate(columnIndex);
+            } else if (type == ColumnType.TIMESTAMP) {
+                return resultSet.getTimestamp(columnIndex);
+            } else if (type == ColumnType.BLOB) {
+                final Blob blob = resultSet.getBlob(columnIndex);
+                return blob;
+            } else if (type == JdbcDataContext.COLUMN_TYPE_BLOB_AS_BYTES) {
+                final Blob blob = resultSet.getBlob(columnIndex);
+                final InputStream inputStream = blob.getBinaryStream();
+                final byte[] bytes = FileHelper.readAsBytes(inputStream);
+                return bytes;
+            } else if (type.isBinary()) {
+                return resultSet.getBytes(columnIndex);
+            } else if (type == ColumnType.CLOB || type == ColumnType.NCLOB) {
+                final Clob clob = resultSet.getClob(columnIndex);
+                return clob;
+            } else if (type == JdbcDataContext.COLUMN_TYPE_CLOB_AS_STRING) {
+                final Clob clob = resultSet.getClob(columnIndex);
+                final Reader reader = clob.getCharacterStream();
+                final String result = FileHelper.readAsString(reader);
+                return result;
+            } else if (type.isBoolean()) {
+                return resultSet.getBoolean(columnIndex);
+            }
+        } catch (Exception e) {
+            logger.warn("Failed to retrieve " + type
+                    + " value using type-specific getter, retrying with generic getObject(...) method", e);
+        }
+        return resultSet.getObject(columnIndex);
+    }
 }
\ No newline at end of file
index d75bf54..3b0d144 100644 (file)
@@ -34,7 +34,7 @@ import org.apache.metamodel.util.TimeComparator;
 /**
  * Query rewriter for IBM DB2
  */
-public class DB2QueryRewriter extends DefaultQueryRewriter implements IQueryRewriter {
+public class DB2QueryRewriter extends DefaultQueryRewriter {
 
     public DB2QueryRewriter(JdbcDataContext dataContext) {
         super(dataContext);
index ec63f7b..b18b9aa 100644 (file)
@@ -24,7 +24,7 @@ import org.apache.metamodel.schema.ColumnType;
 /**
  * Query rewriter for Apache Hive
  */
-public class HiveQueryRewriter extends DefaultQueryRewriter implements IQueryRewriter {
+public class HiveQueryRewriter extends DefaultQueryRewriter {
 
     public HiveQueryRewriter(JdbcDataContext dataContext) {
         super(dataContext);
index f867cb1..3ab24a7 100644 (file)
@@ -18,6 +18,9 @@
  */
 package org.apache.metamodel.jdbc.dialects;
 
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
 import java.sql.Types;
 
 import org.apache.metamodel.jdbc.JdbcDataContext;
@@ -26,6 +29,7 @@ import org.apache.metamodel.query.FilterItem;
 import org.apache.metamodel.query.FromItem;
 import org.apache.metamodel.query.Query;
 import org.apache.metamodel.query.ScalarFunction;
+import org.apache.metamodel.schema.Column;
 import org.apache.metamodel.schema.ColumnType;
 
 /**
@@ -46,6 +50,32 @@ public interface IQueryRewriter {
     public String rewriteFilterItem(FilterItem whereItem);
 
     /**
+     * Method which handles the action of setting a parameterized value on a
+     * statement. Traditionally this is done using the
+     * {@link PreparedStatement#setObject(int, Object)} method but for some
+     * types we use more specific setter methods.
+     * 
+     * @param st
+     * @param valueIndex
+     * @param column
+     * @param value
+     * @throws SQLException
+     */
+    public void setStatementParameter(final PreparedStatement st, final int valueIndex, final Column column,
+            final Object value) throws SQLException;
+
+    /**
+     * Retrieves a value from a JDBC {@link ResultSet} when the anticipated value is mapped to a particular column.
+     * 
+     * @param resultSet
+     * @param columnIndex
+     * @param column
+     * @throws SQLException
+     * @return
+     */
+    public Object getResultSetValue(ResultSet resultSet, int columnIndex, Column column) throws SQLException;
+
+    /**
      * Gets whether this query rewriter is able to write the "Max rows" query
      * property to the query string.
      * 
index 4ea8f78..68647cc 100644 (file)
@@ -24,7 +24,7 @@ import org.apache.metamodel.schema.ColumnType;
 /**
  * Query rewriter for MySQL
  */
-public class MysqlQueryRewriter extends LimitOffsetQueryRewriter implements IQueryRewriter {
+public class MysqlQueryRewriter extends LimitOffsetQueryRewriter {
 
     public MysqlQueryRewriter(JdbcDataContext dataContext) {
         super(dataContext);
index eebb116..531306f 100644 (file)
  */
 package org.apache.metamodel.jdbc.dialects;
 
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.util.Map;
+
 import org.apache.metamodel.jdbc.JdbcDataContext;
 import org.apache.metamodel.query.FromItem;
 import org.apache.metamodel.query.Query;
+import org.apache.metamodel.schema.Column;
 import org.apache.metamodel.schema.ColumnType;
 import org.apache.metamodel.schema.Schema;
 import org.apache.metamodel.schema.Table;
+import org.postgresql.util.PGobject;
+
+import com.fasterxml.jackson.databind.ObjectMapper;
 
 /**
  * Query rewriter for PostgreSQL
  */
-public class PostgresqlQueryRewriter extends LimitOffsetQueryRewriter implements IQueryRewriter {
+public class PostgresqlQueryRewriter extends LimitOffsetQueryRewriter {
+
+    private final ObjectMapper jsonObjectMapper = new ObjectMapper();
 
     public PostgresqlQueryRewriter(JdbcDataContext dataContext) {
         super(dataContext);
@@ -36,10 +47,14 @@ public class PostgresqlQueryRewriter extends LimitOffsetQueryRewriter implements
 
     @Override
     public ColumnType getColumnType(int jdbcType, String nativeType, Integer columnSize) {
-        if ("bool".equals(nativeType)) {
+        switch (nativeType) {
+        case "bool":
             // override the normal behaviour of postgresql which maps "bool" to
             // a BIT.
             return ColumnType.BOOLEAN;
+        case "json":
+        case "jsonb":
+            return ColumnType.MAP;
         }
         return super.getColumnType(jdbcType, nativeType, columnSize);
     }
@@ -55,10 +70,58 @@ public class PostgresqlQueryRewriter extends LimitOffsetQueryRewriter implements
         if (columnType == ColumnType.DOUBLE) {
             return "double precision";
         }
+        if (columnType == ColumnType.MAP) {
+            return "jsonb";
+        }
         return super.rewriteColumnType(columnType, columnSize);
     }
 
     @Override
+    public void setStatementParameter(PreparedStatement st, int valueIndex, Column column, Object value)
+            throws SQLException {
+        switch (column.getNativeType()) {
+        case "json":
+        case "jsonb":
+            assert column.getType() == ColumnType.MAP;
+            if (value != null) {
+                final PGobject pgo = new PGobject();
+                pgo.setType(column.getNativeType());
+                if (value instanceof Map) {
+                    try {
+                        pgo.setValue(jsonObjectMapper.writeValueAsString(value));
+                    } catch (Exception e) {
+                        throw new IllegalArgumentException("Unable to write value as JSON string: " + value);
+                    }
+                } else {
+                    pgo.setValue(value.toString());
+                }
+                st.setObject(valueIndex, pgo);
+                return;
+            }
+        }
+        super.setStatementParameter(st, valueIndex, column, value);
+    }
+
+    @Override
+    public Object getResultSetValue(ResultSet resultSet, int columnIndex, Column column) throws SQLException {
+        switch (column.getNativeType()) {
+        case "json":
+        case "jsonb":
+            assert column.getType() == ColumnType.MAP;
+            final String stringValue = resultSet.getString(columnIndex);
+            if (stringValue == null) {
+                return null;
+            }
+            try {
+                return jsonObjectMapper.readValue(stringValue, Map.class);
+            } catch (Exception e) {
+                throw new IllegalArgumentException("Unable to read string as JSON: " + stringValue);
+            }
+        }
+        return super.getResultSetValue(resultSet, columnIndex, column);
+    }
+
+    @Override
     protected String rewriteFromItem(Query query, FromItem item) {
         String result = super.rewriteFromItem(query, item);
         Table table = item.getTable();
index 5cf6822..2668df9 100644 (file)
@@ -22,7 +22,9 @@ import java.lang.reflect.Method;
 import java.sql.Connection;
 import java.sql.DatabaseMetaData;
 import java.util.Arrays;
+import java.util.HashMap;
 import java.util.List;
+import java.util.Map;
 import java.util.concurrent.TimeUnit;
 
 import javax.swing.table.TableModel;
@@ -65,12 +67,12 @@ public class PostgresqlTest extends AbstractJdbIntegrationTest {
     protected String getPropertyPrefix() {
         return "postgresql";
     }
-    
+
     public void testTimestampValueInsertSelect() throws Exception {
         if (!isConfigured()) {
             return;
         }
-        
+
         final Connection connection = getConnection();
         JdbcTestTemplates.timestampValueInsertSelect(connection, TimeUnit.MICROSECONDS);
     }
@@ -113,8 +115,8 @@ public class PostgresqlTest extends AbstractJdbIntegrationTest {
         dc.executeUpdate(new UpdateScript() {
             @Override
             public void run(UpdateCallback callback) {
-                Table table = callback.createTable(dc.getDefaultSchema(), "test_table").withColumn("foo")
-                        .ofType(ColumnType.INTEGER).withColumn("bar").ofType(ColumnType.VARCHAR).execute();
+                Table table = callback.createTable(dc.getDefaultSchema(), "test_table").withColumn("foo").ofType(
+                        ColumnType.INTEGER).withColumn("bar").ofType(ColumnType.VARCHAR).execute();
                 callback.insertInto(table).value("foo", 1).value("bar", "hello").execute();
                 callback.insertInto(table).value("foo", 2).value("bar", "there").execute();
                 callback.insertInto(table).value("foo", 3).value("bar", "world").execute();
@@ -197,10 +199,10 @@ public class PostgresqlTest extends AbstractJdbIntegrationTest {
         dc.executeUpdate(new UpdateScript() {
             @Override
             public void run(UpdateCallback cb) {
-                Table table = cb.createTable(schema, "my_table").withColumn("id").asPrimaryKey()
-                        .ofType(ColumnType.INTEGER).ofNativeType("SERIAL").nullable(false).withColumn("name")
-                        .ofType(ColumnType.VARCHAR).ofSize(10).withColumn("foo").ofType(ColumnType.BOOLEAN)
-                        .nullable(true).withColumn("bar").ofType(ColumnType.BOOLEAN).nullable(true).execute();
+                Table table = cb.createTable(schema, "my_table").withColumn("id").asPrimaryKey().ofType(
+                        ColumnType.INTEGER).ofNativeType("SERIAL").nullable(false).withColumn("name").ofType(
+                                ColumnType.VARCHAR).ofSize(10).withColumn("foo").ofType(ColumnType.BOOLEAN).nullable(
+                                        true).withColumn("bar").ofType(ColumnType.BOOLEAN).nullable(true).execute();
 
                 assertEquals("my_table", table.getName());
             }
@@ -267,9 +269,9 @@ public class PostgresqlTest extends AbstractJdbIntegrationTest {
             @Override
             public void run(UpdateCallback cb) {
                 Table table = cb.createTable(schema, "my_table").withColumn("id").ofType(ColumnType.INTEGER)
-                        .ofNativeType("SERIAL").nullable(false).withColumn("name").ofType(ColumnType.VARCHAR)
-                        .ofSize(10).withColumn("foo").ofType(ColumnType.BOOLEAN).nullable(true).withColumn("bar")
-                        .ofType(ColumnType.BOOLEAN).nullable(true).execute();
+                        .ofNativeType("SERIAL").nullable(false).withColumn("name").ofType(ColumnType.VARCHAR).ofSize(10)
+                        .withColumn("foo").ofType(ColumnType.BOOLEAN).nullable(true).withColumn("bar").ofType(
+                                ColumnType.BOOLEAN).nullable(true).execute();
 
                 assertEquals("my_table", table.getName());
             }
@@ -329,6 +331,51 @@ public class PostgresqlTest extends AbstractJdbIntegrationTest {
         }
     }
 
+    public void testJsonAndJsonbDatatypes() throws Exception {
+        if (!isConfigured()) {
+            return;
+        }
+
+        final JdbcDataContext dc = new JdbcDataContext(getConnection());
+
+        final Schema schema = dc.getDefaultSchema();
+
+        dc.executeUpdate(new UpdateScript() {
+            @Override
+            public void run(UpdateCallback cb) {
+                final Table table = cb.createTable(schema, "json_datatypes_table").withColumn("id").ofType(ColumnType.INTEGER)
+                        .ofNativeType("SERIAL").asPrimaryKey().nullable(false).withColumn("col_json").ofNativeType(
+                                "json").withColumn("col_jsonb").ofNativeType("jsonb").execute();
+                assertEquals("json_datatypes_table", table.getName());
+
+                final Map<String, Object> map = new HashMap<>();
+                map.put("foo", "bar");
+                cb.insertInto(table).value("id", 1).value("col_json", map).execute();
+                cb.insertInto(table).value("id", 2).value("col_jsonb", "{'foo':'baz'}".replace('\'', '"')).execute();
+            }
+        });
+
+        try {
+            final DataSet ds = dc.query().from("json_datatypes_table").select("col_json", "col_jsonb").execute();
+            
+            assertTrue(ds.next());
+            assertEquals("Row[values=[{foo=bar}, null]]", ds.getRow().toString());
+            assertTrue(ds.getRow().getValue(0) instanceof Map);
+            assertTrue(ds.next());
+            assertEquals("Row[values=[null, {foo=baz}]]", ds.getRow().toString());
+            assertTrue(ds.getRow().getValue(1) instanceof Map);
+            assertFalse(ds.next());
+        } finally {
+            dc.executeUpdate(new UpdateScript() {
+                @Override
+                public void run(UpdateCallback cb) {
+                    cb.dropTable("json_datatypes_table").execute();
+                }
+            });
+        }
+
+    }
+
     /**
      * Tests some inconsistencies dealing with booleans.
      * 
@@ -415,7 +462,7 @@ public class PostgresqlTest extends AbstractJdbIntegrationTest {
             assertTrue(ds.next());
             Double nAn = (Double) ds.getRow().getValue(ds.getSelectItems()[0]);
             assertFalse(ds.next());
-    
+
             assertEquals(Double.MIN_VALUE, minVal, DELTA);
             assertEquals(Double.MAX_VALUE, maxVal, DELTA);
             assertTrue(Double.isInfinite(negInf));
@@ -430,7 +477,7 @@ public class PostgresqlTest extends AbstractJdbIntegrationTest {
             });
         }
     }
-    
+
     public void testBlob() throws Exception {
         if (!isConfigured()) {
             return;
@@ -516,8 +563,8 @@ public class PostgresqlTest extends AbstractJdbIntegrationTest {
                 @Override
                 public void run(UpdateCallback cb) {
                     Table table = cb.createTable(schema, "my_table").withColumn("id").ofType(ColumnType.INTEGER)
-                            .ofNativeType("SERIAL").nullable(false).withColumn("person name").ofSize(255)
-                            .withColumn("age").ofType(ColumnType.INTEGER).execute();
+                            .ofNativeType("SERIAL").nullable(false).withColumn("person name").ofSize(255).withColumn(
+                                    "age").ofType(ColumnType.INTEGER).execute();
                     assertEquals("[id, person name, age]", Arrays.toString(table.getColumnNames()));
                     assertEquals(
                             "Column[name=id,columnNumber=0,type=INTEGER,nullable=false,nativeType=serial,columnSize=10]",
@@ -582,8 +629,8 @@ public class PostgresqlTest extends AbstractJdbIntegrationTest {
                 @Override
                 public void run(UpdateCallback cb) {
                     Table table = cb.createTable(schema, "my_table").withColumn("id").ofType(ColumnType.INTEGER)
-                            .ofNativeType("SERIAL").nullable(false).withColumn("person name").ofSize(255)
-                            .withColumn("age").ofType(ColumnType.INTEGER).execute();
+                            .ofNativeType("SERIAL").nullable(false).withColumn("person name").ofSize(255).withColumn(
+                                    "age").ofType(ColumnType.INTEGER).execute();
                     assertEquals("[id, person name, age]", Arrays.toString(table.getColumnNames()));
                     assertEquals(
                             "Column[name=id,columnNumber=0,type=INTEGER,nullable=false,nativeType=serial,columnSize=10]",
@@ -635,8 +682,8 @@ public class PostgresqlTest extends AbstractJdbIntegrationTest {
                 @Override
                 public void run(UpdateCallback cb) {
                     Table table = cb.createTable(schema, "my_table").withColumn("id").ofType(ColumnType.INTEGER)
-                            .ofNativeType("SERIAL").nullable(false).withColumn("person name").ofSize(255)
-                            .withColumn("age").ofType(ColumnType.INTEGER).execute();
+                            .ofNativeType("SERIAL").nullable(false).withColumn("person name").ofSize(255).withColumn(
+                                    "age").ofType(ColumnType.INTEGER).execute();
                     assertEquals("[id, person name, age]", Arrays.toString(table.getColumnNames()));
                     assertEquals(
                             "Column[name=id,columnNumber=0,type=INTEGER,nullable=false,nativeType=serial,columnSize=10]",
@@ -706,10 +753,9 @@ public class PostgresqlTest extends AbstractJdbIntegrationTest {
 
         assertEquals("[Table[name=categories,type=TABLE,remarks=null], "
                 + "Table[name=cust_hist,type=TABLE,remarks=null], " + "Table[name=customers,type=TABLE,remarks=null], "
-                + "Table[name=inventory,type=TABLE,remarks=null], "
-                + "Table[name=orderlines,type=TABLE,remarks=null], " + "Table[name=orders,type=TABLE,remarks=null], "
-                + "Table[name=products,type=TABLE,remarks=null], " + "Table[name=reorder,type=TABLE,remarks=null]]",
-                Arrays.toString(schema.getTables()));
+                + "Table[name=inventory,type=TABLE,remarks=null], " + "Table[name=orderlines,type=TABLE,remarks=null], "
+                + "Table[name=orders,type=TABLE,remarks=null], " + "Table[name=products,type=TABLE,remarks=null], "
+                + "Table[name=reorder,type=TABLE,remarks=null]]", Arrays.toString(schema.getTables()));
 
         Table productsTable = schema.getTableByName("products");
         assertEquals(
@@ -804,7 +850,8 @@ public class PostgresqlTest extends AbstractJdbIntegrationTest {
         Column quantityColumn = orderlinesTable.getColumnByName("quantity");
 
         q.from(orderlinesTable);
-        q.where(new FilterItem(new SelectItem(prodIdColumn), OperatorType.EQUALS_TO, new SelectItem(commonProdIdColumn)));
+        q.where(new FilterItem(new SelectItem(prodIdColumn), OperatorType.EQUALS_TO, new SelectItem(
+                commonProdIdColumn)));
         q.groupBy(titleColumn);
         q.getSelectClause().removeItem(q.getSelectClause().getSelectItem(productPriceColumn));
         SelectItem quantitySum = new SelectItem(FunctionType.SUM, quantityColumn).setAlias("orderAmount");
@@ -815,7 +862,8 @@ public class PostgresqlTest extends AbstractJdbIntegrationTest {
         assertEquals("SELECT \"products\".\"title\" AS product-title, SUM(\"orderlines\".\"quantity\") AS orderAmount "
                 + "FROM public.\"products\", public.\"orderlines\" "
                 + "WHERE \"products\".\"prod_id\" = \"orderlines\".\"prod_id\" " + "GROUP BY \"products\".\"title\" "
-                + "HAVING SUM(\"orderlines\".\"quantity\") > 25 " + "ORDER BY \"products\".\"title\" ASC", q.toString());
+                + "HAVING SUM(\"orderlines\".\"quantity\") > 25 " + "ORDER BY \"products\".\"title\" ASC", q
+                        .toString());
         data = dc.executeQuery(q);
         tableModel = new DataSetTableModel(data);
         assertEquals(2, tableModel.getColumnCount());
@@ -856,8 +904,8 @@ public class PostgresqlTest extends AbstractJdbIntegrationTest {
                 @Override
                 public void run(UpdateCallback cb) {
                     Table table = cb.createTable(schema, "my_table").withColumn("id").ofType(ColumnType.INTEGER)
-                            .ofNativeType("SERIAL").nullable(false).withColumn("person name").ofSize(255)
-                            .withColumn("age").ofType(ColumnType.INTEGER).execute();
+                            .ofNativeType("SERIAL").nullable(false).withColumn("person name").ofSize(255).withColumn(
+                                    "age").ofType(ColumnType.INTEGER).execute();
                     assertEquals("[id, person name, age]", Arrays.toString(table.getColumnNames()));
                     assertEquals(
                             "Column[name=id,columnNumber=0,type=INTEGER,nullable=false,nativeType=serial,columnSize=10]",