METAMODEL-1107: Fixed Closes #112
Project: http://git-wip-us.apache.org/repos/asf/metamodel/repo Commit: http://git-wip-us.apache.org/repos/asf/metamodel/commit/a5235c11 Tree: http://git-wip-us.apache.org/repos/asf/metamodel/tree/a5235c11 Diff: http://git-wip-us.apache.org/repos/asf/metamodel/diff/a5235c11 Branch: refs/heads/5.x Commit: a5235c11ff2f409798d143023fba1c10b7e1a6fd Parents: 1910d56 Author: kaspersorensen <[email protected]> Authored: Mon Jul 25 09:16:59 2016 -0700 Committer: kaspersorensen <[email protected]> Committed: Mon Jul 25 09:16:59 2016 -0700 ---------------------------------------------------------------------- CHANGES.md | 5 +- jdbc/pom.xml | 13 +- .../org/apache/metamodel/jdbc/JdbcDataSet.java | 53 ++---- .../metamodel/jdbc/JdbcDeleteBuilder.java | 2 +- .../metamodel/jdbc/JdbcInsertBuilder.java | 2 +- .../metamodel/jdbc/JdbcUpdateBuilder.java | 4 +- .../org/apache/metamodel/jdbc/JdbcUtils.java | 140 +-------------- .../jdbc/dialects/AbstractQueryRewriter.java | 173 +++++++++++++++++++ .../jdbc/dialects/DB2QueryRewriter.java | 2 +- .../jdbc/dialects/HiveQueryRewriter.java | 2 +- .../metamodel/jdbc/dialects/IQueryRewriter.java | 30 ++++ .../jdbc/dialects/MysqlQueryRewriter.java | 2 +- .../jdbc/dialects/PostgresqlQueryRewriter.java | 67 ++++++- .../jdbc/integrationtests/PostgresqlTest.java | 102 ++++++++--- 14 files changed, 382 insertions(+), 215 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/metamodel/blob/a5235c11/CHANGES.md ---------------------------------------------------------------------- diff --git a/CHANGES.md b/CHANGES.md index 0b2b49d..4f61177 100644 --- a/CHANGES.md +++ b/CHANGES.md @@ -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. http://git-wip-us.apache.org/repos/asf/metamodel/blob/a5235c11/jdbc/pom.xml ---------------------------------------------------------------------- diff --git a/jdbc/pom.xml b/jdbc/pom.xml index fb332f7..3969b1c 100644 --- a/jdbc/pom.xml +++ b/jdbc/pom.xml @@ -75,7 +75,18 @@ <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> http://git-wip-us.apache.org/repos/asf/metamodel/blob/a5235c11/jdbc/src/main/java/org/apache/metamodel/jdbc/JdbcDataSet.java ---------------------------------------------------------------------- diff --git a/jdbc/src/main/java/org/apache/metamodel/jdbc/JdbcDataSet.java b/jdbc/src/main/java/org/apache/metamodel/jdbc/JdbcDataSet.java index 4142ab7..cbcdb7a 100644 --- a/jdbc/src/main/java/org/apache/metamodel/jdbc/JdbcDataSet.java +++ b/jdbc/src/main/java/org/apache/metamodel/jdbc/JdbcDataSet.java @@ -18,10 +18,6 @@ */ 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); } /** http://git-wip-us.apache.org/repos/asf/metamodel/blob/a5235c11/jdbc/src/main/java/org/apache/metamodel/jdbc/JdbcDeleteBuilder.java ---------------------------------------------------------------------- diff --git a/jdbc/src/main/java/org/apache/metamodel/jdbc/JdbcDeleteBuilder.java b/jdbc/src/main/java/org/apache/metamodel/jdbc/JdbcDeleteBuilder.java index bf6aaf2..e46cd19 100644 --- a/jdbc/src/main/java/org/apache/metamodel/jdbc/JdbcDeleteBuilder.java +++ b/jdbc/src/main/java/org/apache/metamodel/jdbc/JdbcDeleteBuilder.java @@ -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++; } } http://git-wip-us.apache.org/repos/asf/metamodel/blob/a5235c11/jdbc/src/main/java/org/apache/metamodel/jdbc/JdbcInsertBuilder.java ---------------------------------------------------------------------- diff --git a/jdbc/src/main/java/org/apache/metamodel/jdbc/JdbcInsertBuilder.java b/jdbc/src/main/java/org/apache/metamodel/jdbc/JdbcInsertBuilder.java index 66cdbe7..d9c7a5e 100644 --- a/jdbc/src/main/java/org/apache/metamodel/jdbc/JdbcInsertBuilder.java +++ b/jdbc/src/main/java/org/apache/metamodel/jdbc/JdbcInsertBuilder.java @@ -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++; } } http://git-wip-us.apache.org/repos/asf/metamodel/blob/a5235c11/jdbc/src/main/java/org/apache/metamodel/jdbc/JdbcUpdateBuilder.java ---------------------------------------------------------------------- diff --git a/jdbc/src/main/java/org/apache/metamodel/jdbc/JdbcUpdateBuilder.java b/jdbc/src/main/java/org/apache/metamodel/jdbc/JdbcUpdateBuilder.java index 6610a7e..590a23d 100644 --- a/jdbc/src/main/java/org/apache/metamodel/jdbc/JdbcUpdateBuilder.java +++ b/jdbc/src/main/java/org/apache/metamodel/jdbc/JdbcUpdateBuilder.java @@ -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++; } http://git-wip-us.apache.org/repos/asf/metamodel/blob/a5235c11/jdbc/src/main/java/org/apache/metamodel/jdbc/JdbcUtils.java ---------------------------------------------------------------------- diff --git a/jdbc/src/main/java/org/apache/metamodel/jdbc/JdbcUtils.java b/jdbc/src/main/java/org/apache/metamodel/jdbc/JdbcUtils.java index 1073d6f..2d66790 100644 --- a/jdbc/src/main/java/org/apache/metamodel/jdbc/JdbcUtils.java +++ b/jdbc/src/main/java/org/apache/metamodel/jdbc/JdbcUtils.java @@ -18,20 +18,12 @@ */ 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; } http://git-wip-us.apache.org/repos/asf/metamodel/blob/a5235c11/jdbc/src/main/java/org/apache/metamodel/jdbc/dialects/AbstractQueryRewriter.java ---------------------------------------------------------------------- diff --git a/jdbc/src/main/java/org/apache/metamodel/jdbc/dialects/AbstractQueryRewriter.java b/jdbc/src/main/java/org/apache/metamodel/jdbc/dialects/AbstractQueryRewriter.java index 087bf2f..5ce4445 100644 --- a/jdbc/src/main/java/org/apache/metamodel/jdbc/dialects/AbstractQueryRewriter.java +++ b/jdbc/src/main/java/org/apache/metamodel/jdbc/dialects/AbstractQueryRewriter.java @@ -18,6 +18,18 @@ */ 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 http://git-wip-us.apache.org/repos/asf/metamodel/blob/a5235c11/jdbc/src/main/java/org/apache/metamodel/jdbc/dialects/DB2QueryRewriter.java ---------------------------------------------------------------------- diff --git a/jdbc/src/main/java/org/apache/metamodel/jdbc/dialects/DB2QueryRewriter.java b/jdbc/src/main/java/org/apache/metamodel/jdbc/dialects/DB2QueryRewriter.java index d75bf54..3b0d144 100644 --- a/jdbc/src/main/java/org/apache/metamodel/jdbc/dialects/DB2QueryRewriter.java +++ b/jdbc/src/main/java/org/apache/metamodel/jdbc/dialects/DB2QueryRewriter.java @@ -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); http://git-wip-us.apache.org/repos/asf/metamodel/blob/a5235c11/jdbc/src/main/java/org/apache/metamodel/jdbc/dialects/HiveQueryRewriter.java ---------------------------------------------------------------------- diff --git a/jdbc/src/main/java/org/apache/metamodel/jdbc/dialects/HiveQueryRewriter.java b/jdbc/src/main/java/org/apache/metamodel/jdbc/dialects/HiveQueryRewriter.java index ec63f7b..b18b9aa 100644 --- a/jdbc/src/main/java/org/apache/metamodel/jdbc/dialects/HiveQueryRewriter.java +++ b/jdbc/src/main/java/org/apache/metamodel/jdbc/dialects/HiveQueryRewriter.java @@ -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); http://git-wip-us.apache.org/repos/asf/metamodel/blob/a5235c11/jdbc/src/main/java/org/apache/metamodel/jdbc/dialects/IQueryRewriter.java ---------------------------------------------------------------------- diff --git a/jdbc/src/main/java/org/apache/metamodel/jdbc/dialects/IQueryRewriter.java b/jdbc/src/main/java/org/apache/metamodel/jdbc/dialects/IQueryRewriter.java index f867cb1..3ab24a7 100644 --- a/jdbc/src/main/java/org/apache/metamodel/jdbc/dialects/IQueryRewriter.java +++ b/jdbc/src/main/java/org/apache/metamodel/jdbc/dialects/IQueryRewriter.java @@ -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. * http://git-wip-us.apache.org/repos/asf/metamodel/blob/a5235c11/jdbc/src/main/java/org/apache/metamodel/jdbc/dialects/MysqlQueryRewriter.java ---------------------------------------------------------------------- diff --git a/jdbc/src/main/java/org/apache/metamodel/jdbc/dialects/MysqlQueryRewriter.java b/jdbc/src/main/java/org/apache/metamodel/jdbc/dialects/MysqlQueryRewriter.java index 4ea8f78..68647cc 100644 --- a/jdbc/src/main/java/org/apache/metamodel/jdbc/dialects/MysqlQueryRewriter.java +++ b/jdbc/src/main/java/org/apache/metamodel/jdbc/dialects/MysqlQueryRewriter.java @@ -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); http://git-wip-us.apache.org/repos/asf/metamodel/blob/a5235c11/jdbc/src/main/java/org/apache/metamodel/jdbc/dialects/PostgresqlQueryRewriter.java ---------------------------------------------------------------------- diff --git a/jdbc/src/main/java/org/apache/metamodel/jdbc/dialects/PostgresqlQueryRewriter.java b/jdbc/src/main/java/org/apache/metamodel/jdbc/dialects/PostgresqlQueryRewriter.java index eebb116..531306f 100644 --- a/jdbc/src/main/java/org/apache/metamodel/jdbc/dialects/PostgresqlQueryRewriter.java +++ b/jdbc/src/main/java/org/apache/metamodel/jdbc/dialects/PostgresqlQueryRewriter.java @@ -18,17 +18,28 @@ */ 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(); http://git-wip-us.apache.org/repos/asf/metamodel/blob/a5235c11/jdbc/src/test/java/org/apache/metamodel/jdbc/integrationtests/PostgresqlTest.java ---------------------------------------------------------------------- diff --git a/jdbc/src/test/java/org/apache/metamodel/jdbc/integrationtests/PostgresqlTest.java b/jdbc/src/test/java/org/apache/metamodel/jdbc/integrationtests/PostgresqlTest.java index 5cf6822..2668df9 100644 --- a/jdbc/src/test/java/org/apache/metamodel/jdbc/integrationtests/PostgresqlTest.java +++ b/jdbc/src/test/java/org/apache/metamodel/jdbc/integrationtests/PostgresqlTest.java @@ -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]",
