List of usage examples for java.sql Types BOOLEAN
int BOOLEAN
To view the source code for java.sql Types BOOLEAN.
Click Source Link
BOOLEAN. From source file:rapture.repo.jdbc.JDBCStructuredStore.java
protected Map<String, Integer> getInputParamTypes(Map<String, Object> inParams) { Map<String, Integer> retMap = new HashMap<>(); for (Map.Entry<String, Object> entry : inParams.entrySet()) { String clazz = entry.getValue().getClass().getSimpleName(); switch (clazz) { case "String": retMap.put(entry.getKey(), Types.VARCHAR); break; case "Integer": retMap.put(entry.getKey(), Types.INTEGER); break; case "Float": retMap.put(entry.getKey(), Types.FLOAT); break; case "Double": retMap.put(entry.getKey(), Types.DOUBLE); break; case "Character": retMap.put(entry.getKey(), Types.CHAR); break; case "Boolean": retMap.put(entry.getKey(), Types.BOOLEAN); break; default:/*from w w w . j ava2s . c o m*/ throw RaptureExceptionFactory.create("Unsupported class for param type"); } } return retMap; }
From source file:com.manydesigns.portofino.persistence.hibernate.HibernateConfig.java
public boolean setHibernateType(@Nullable SimpleValue value, com.manydesigns.portofino.model.database.Column column, Class javaType, final int jdbcType) { String typeName;/*from www . j av a 2 s.com*/ Properties typeParams = null; if (javaType == null) { return false; } if (javaType == Long.class) { typeName = LongType.INSTANCE.getName(); } else if (javaType == Short.class) { typeName = ShortType.INSTANCE.getName(); } else if (javaType == Integer.class) { typeName = IntegerType.INSTANCE.getName(); } else if (javaType == Byte.class) { typeName = ByteType.INSTANCE.getName(); } else if (javaType == Float.class) { typeName = FloatType.INSTANCE.getName(); } else if (javaType == Double.class) { typeName = DoubleType.INSTANCE.getName(); } else if (javaType == Character.class) { typeName = CharacterType.INSTANCE.getName(); } else if (javaType == String.class) { typeName = StringType.INSTANCE.getName(); } else if (java.util.Date.class.isAssignableFrom(javaType)) { switch (jdbcType) { case Types.DATE: typeName = DateType.INSTANCE.getName(); break; case Types.TIME: typeName = TimeType.INSTANCE.getName(); break; case Types.TIMESTAMP: typeName = TimestampType.INSTANCE.getName(); break; default: typeName = null; } } else if (javaType == Boolean.class) { if (jdbcType == Types.BIT || jdbcType == Types.BOOLEAN) { typeName = BooleanType.INSTANCE.getName(); } else if (jdbcType == Types.NUMERIC || jdbcType == Types.DECIMAL || jdbcType == Types.INTEGER || jdbcType == Types.SMALLINT || jdbcType == Types.TINYINT || jdbcType == Types.BIGINT) { typeName = NumericBooleanType.INSTANCE.getName(); } else if (jdbcType == Types.CHAR || jdbcType == Types.VARCHAR) { typeName = StringBooleanType.class.getName(); typeParams = new Properties(); typeParams.setProperty("true", trueString != null ? trueString : StringBooleanType.NULL); typeParams.setProperty("false", falseString != null ? falseString : StringBooleanType.NULL); typeParams.setProperty("sqlType", String.valueOf(jdbcType)); } else { typeName = null; } } else if (javaType == BigDecimal.class) { typeName = BigDecimalType.INSTANCE.getName(); } else if (javaType == BigInteger.class) { typeName = BigIntegerType.INSTANCE.getName(); } else if (javaType == byte[].class) { typeName = BlobType.INSTANCE.getName(); } else { typeName = null; } if (typeName == null) { logger.error("Unsupported type (java type: {}, jdbc type: {}) " + "for column '{}'.", new Object[] { javaType, jdbcType, column.getColumnName() }); return false; } if (value != null) { value.setTypeName(typeName); if (typeParams != null) { value.setTypeParameters(typeParams); } } return true; }
From source file:org.wso2.carbon.dataservices.core.odata.RDBMSDataHandler.java
private String getValueFromResultSet(int columnType, String column, ResultSet resultSet) throws SQLException { String paramValue;// w w w .j a v a 2s . c o m switch (columnType) { case Types.INTEGER: /* fall through */ case Types.TINYINT: /* fall through */ case Types.SMALLINT: paramValue = ConverterUtil.convertToString(resultSet.getInt(column)); paramValue = resultSet.wasNull() ? null : paramValue; break; case Types.DOUBLE: paramValue = ConverterUtil.convertToString(resultSet.getDouble(column)); paramValue = resultSet.wasNull() ? null : paramValue; break; case Types.VARCHAR: /* fall through */ case Types.CHAR: /* fall through */ case Types.CLOB: /* fall through */ case Types.LONGVARCHAR: paramValue = resultSet.getString(column); break; case Types.BOOLEAN: /* fall through */ case Types.BIT: paramValue = ConverterUtil.convertToString(resultSet.getBoolean(column)); paramValue = resultSet.wasNull() ? null : paramValue; break; case Types.BLOB: Blob sqlBlob = resultSet.getBlob(column); if (sqlBlob != null) { paramValue = this.getBase64StringFromInputStream(sqlBlob.getBinaryStream()); } else { paramValue = null; } paramValue = resultSet.wasNull() ? null : paramValue; break; case Types.BINARY: /* fall through */ case Types.LONGVARBINARY: /* fall through */ case Types.VARBINARY: InputStream binInStream = resultSet.getBinaryStream(column); if (binInStream != null) { paramValue = this.getBase64StringFromInputStream(binInStream); } else { paramValue = null; } break; case Types.DATE: Date sqlDate = resultSet.getDate(column); if (sqlDate != null) { paramValue = ConverterUtil.convertToString(sqlDate); } else { paramValue = null; } break; case Types.DECIMAL: /* fall through */ case Types.NUMERIC: BigDecimal bigDecimal = resultSet.getBigDecimal(column); if (bigDecimal != null) { paramValue = ConverterUtil.convertToString(bigDecimal); } else { paramValue = null; } paramValue = resultSet.wasNull() ? null : paramValue; break; case Types.FLOAT: paramValue = ConverterUtil.convertToString(resultSet.getFloat(column)); paramValue = resultSet.wasNull() ? null : paramValue; break; case Types.TIME: Time sqlTime = resultSet.getTime(column); if (sqlTime != null) { paramValue = this.convertToTimeString(sqlTime); } else { paramValue = null; } break; case Types.LONGNVARCHAR: /* fall through */ case Types.NCHAR: /* fall through */ case Types.NCLOB: /* fall through */ case Types.NVARCHAR: paramValue = resultSet.getNString(column); break; case Types.BIGINT: paramValue = ConverterUtil.convertToString(resultSet.getLong(column)); paramValue = resultSet.wasNull() ? null : paramValue; break; case Types.TIMESTAMP: Timestamp sqlTimestamp = resultSet.getTimestamp(column); if (sqlTimestamp != null) { paramValue = this.convertToTimestampString(sqlTimestamp); } else { paramValue = null; } paramValue = resultSet.wasNull() ? null : paramValue; break; /* handle all other types as strings */ default: paramValue = resultSet.getString(column); paramValue = resultSet.wasNull() ? null : paramValue; break; } return paramValue; }
From source file:org.dspace.storage.rdbms.MockDatabaseManager.java
/** * Convert the current row in a ResultSet into a TableRow object. * * @param results/* w ww . ja v a 2 s. c om*/ * A ResultSet to process * @param table * The name of the table * @param pColumnNames * The name of the columns in this resultset * @return A TableRow object with the data from the ResultSet * @exception SQLException * If a database error occurs */ @Mock static TableRow process(ResultSet results, String table, List<String> pColumnNames) throws SQLException { String dbName = ConfigurationManager.getProperty("db.name"); ResultSetMetaData meta = results.getMetaData(); int columns = meta.getColumnCount() + 1; // If we haven't been passed the column names try to generate them from the metadata / table List<String> columnNames = pColumnNames != null ? pColumnNames : ((table == null) ? getColumnNames(meta) : getColumnNames(table)); TableRow row = new TableRow(canonicalize(table), columnNames); // Process the columns in order // (This ensures maximum backwards compatibility with // old JDBC drivers) for (int i = 1; i < columns; i++) { String name = meta.getColumnName(i); int jdbctype = meta.getColumnType(i); if (jdbctype == Types.BIT || jdbctype == Types.BOOLEAN) { row.setColumn(name, results.getBoolean(i)); } else if ((jdbctype == Types.INTEGER) || (jdbctype == Types.NUMERIC) || (jdbctype == Types.DECIMAL)) { // If we are using oracle if ("oracle".equals(dbName)) { // Test the value from the record set. If it can be represented using an int, do so. // Otherwise, store it as long long longValue = results.getLong(i); if (longValue <= (long) Integer.MAX_VALUE) row.setColumn(name, (int) longValue); else row.setColumn(name, longValue); } else row.setColumn(name, results.getInt(i)); } else if (jdbctype == Types.BIGINT) { row.setColumn(name, results.getLong(i)); } else if (jdbctype == Types.DOUBLE) { row.setColumn(name, results.getDouble(i)); } else if (jdbctype == Types.CLOB && "oracle".equals(dbName)) { // Support CLOBs in place of TEXT columns in Oracle row.setColumn(name, results.getString(i)); } else if (jdbctype == Types.VARCHAR) { /*try { byte[] bytes = results.getBytes(i); if (bytes != null) { String mystring = new String(results.getBytes(i), "UTF-8"); row.setColumn(name, mystring); } else { row.setColumn(name, results.getString(i)); } } catch (UnsupportedEncodingException e) { // do nothing, UTF-8 is built in! }*/ //removing issue with H2 and getBytes row.setColumn(name, results.getString(i)); } else if (jdbctype == Types.DATE) { row.setColumn(name, results.getDate(i)); } else if (jdbctype == Types.TIME) { row.setColumn(name, results.getTime(i)); } else if (jdbctype == Types.TIMESTAMP) { row.setColumn(name, results.getTimestamp(i)); } else { throw new IllegalArgumentException("Unsupported JDBC type: " + jdbctype + " (" + name + ")"); } if (results.wasNull()) { row.setColumnNull(name); } } // Now that we've prepped the TableRow, reset the flags so that we can detect which columns have changed row.resetChanged(); return row; }
From source file:org.apache.hadoop.hive.jdbc.TestJdbcDriver.java
public void testResultSetMetaData() throws SQLException { Statement stmt = con.createStatement(); ResultSet res = stmt.executeQuery("select c1, c2, c3, c4, c5 as a, c6, c7, c8, c9, c10, c11, c12, " + "c1*2, sentences(null, null, null) as b, c17, c18, c20 from " + dataTypeTableName + " limit 1"); ResultSetMetaData meta = res.getMetaData(); ResultSet colRS = con.getMetaData().getColumns(null, null, dataTypeTableName.toLowerCase(), null); assertEquals(17, meta.getColumnCount()); assertTrue(colRS.next());// w ww .ja v a 2s . com assertEquals("c1", meta.getColumnName(1)); assertEquals(Types.INTEGER, meta.getColumnType(1)); assertEquals("int", meta.getColumnTypeName(1)); assertEquals(11, meta.getColumnDisplaySize(1)); assertEquals(10, meta.getPrecision(1)); assertEquals(0, meta.getScale(1)); assertEquals("c1", colRS.getString("COLUMN_NAME")); assertEquals(Types.INTEGER, colRS.getInt("DATA_TYPE")); assertEquals("int", colRS.getString("TYPE_NAME").toLowerCase()); assertEquals(meta.getPrecision(1), colRS.getInt("COLUMN_SIZE")); assertEquals(meta.getScale(1), colRS.getInt("DECIMAL_DIGITS")); assertTrue(colRS.next()); assertEquals("c2", meta.getColumnName(2)); assertEquals("boolean", meta.getColumnTypeName(2)); assertEquals(Types.BOOLEAN, meta.getColumnType(2)); assertEquals(1, meta.getColumnDisplaySize(2)); assertEquals(1, meta.getPrecision(2)); assertEquals(0, meta.getScale(2)); assertEquals("c2", colRS.getString("COLUMN_NAME")); assertEquals(Types.BOOLEAN, colRS.getInt("DATA_TYPE")); assertEquals("boolean", colRS.getString("TYPE_NAME").toLowerCase()); assertEquals(meta.getPrecision(2), colRS.getInt("COLUMN_SIZE")); assertEquals(meta.getScale(2), colRS.getInt("DECIMAL_DIGITS")); assertTrue(colRS.next()); assertEquals("c3", meta.getColumnName(3)); assertEquals(Types.DOUBLE, meta.getColumnType(3)); assertEquals("double", meta.getColumnTypeName(3)); assertEquals(25, meta.getColumnDisplaySize(3)); assertEquals(15, meta.getPrecision(3)); assertEquals(15, meta.getScale(3)); assertEquals("c3", colRS.getString("COLUMN_NAME")); assertEquals(Types.DOUBLE, colRS.getInt("DATA_TYPE")); assertEquals("double", colRS.getString("TYPE_NAME").toLowerCase()); assertEquals(meta.getPrecision(3), colRS.getInt("COLUMN_SIZE")); assertEquals(meta.getScale(3), colRS.getInt("DECIMAL_DIGITS")); assertTrue(colRS.next()); assertEquals("c4", meta.getColumnName(4)); assertEquals(Types.VARCHAR, meta.getColumnType(4)); assertEquals("string", meta.getColumnTypeName(4)); assertEquals(Integer.MAX_VALUE, meta.getColumnDisplaySize(4)); assertEquals(Integer.MAX_VALUE, meta.getPrecision(4)); assertEquals(0, meta.getScale(4)); assertEquals("c4", colRS.getString("COLUMN_NAME")); assertEquals(Types.VARCHAR, colRS.getInt("DATA_TYPE")); assertEquals("string", colRS.getString("TYPE_NAME").toLowerCase()); assertEquals(meta.getPrecision(4), colRS.getInt("COLUMN_SIZE")); assertEquals(meta.getScale(4), colRS.getInt("DECIMAL_DIGITS")); assertTrue(colRS.next()); assertEquals("a", meta.getColumnName(5)); assertEquals(Types.VARCHAR, meta.getColumnType(5)); assertEquals("string", meta.getColumnTypeName(5)); assertEquals(Integer.MAX_VALUE, meta.getColumnDisplaySize(5)); assertEquals(Integer.MAX_VALUE, meta.getPrecision(5)); assertEquals(0, meta.getScale(5)); assertEquals("c5", colRS.getString("COLUMN_NAME")); assertEquals(Types.VARCHAR, colRS.getInt("DATA_TYPE")); assertEquals("array<int>", colRS.getString("TYPE_NAME").toLowerCase()); assertEquals(meta.getPrecision(5), colRS.getInt("COLUMN_SIZE")); assertEquals(meta.getScale(5), colRS.getInt("DECIMAL_DIGITS")); assertTrue(colRS.next()); assertEquals("c6", meta.getColumnName(6)); assertEquals(Types.VARCHAR, meta.getColumnType(6)); assertEquals("string", meta.getColumnTypeName(6)); assertEquals(Integer.MAX_VALUE, meta.getColumnDisplaySize(6)); assertEquals(Integer.MAX_VALUE, meta.getPrecision(6)); assertEquals(0, meta.getScale(6)); assertEquals("c6", colRS.getString("COLUMN_NAME")); assertEquals(Types.VARCHAR, colRS.getInt("DATA_TYPE")); assertEquals("map<int,string>", colRS.getString("TYPE_NAME").toLowerCase()); assertEquals(meta.getPrecision(6), colRS.getInt("COLUMN_SIZE")); assertEquals(meta.getScale(6), colRS.getInt("DECIMAL_DIGITS")); assertTrue(colRS.next()); assertEquals("c7", meta.getColumnName(7)); assertEquals(Types.VARCHAR, meta.getColumnType(7)); assertEquals("string", meta.getColumnTypeName(7)); assertEquals(Integer.MAX_VALUE, meta.getColumnDisplaySize(7)); assertEquals(Integer.MAX_VALUE, meta.getPrecision(7)); assertEquals(0, meta.getScale(7)); assertEquals("c7", colRS.getString("COLUMN_NAME")); assertEquals(Types.VARCHAR, colRS.getInt("DATA_TYPE")); assertEquals("map<string,string>", colRS.getString("TYPE_NAME").toLowerCase()); assertEquals(meta.getPrecision(7), colRS.getInt("COLUMN_SIZE")); assertEquals(meta.getScale(7), colRS.getInt("DECIMAL_DIGITS")); assertTrue(colRS.next()); assertEquals("c8", meta.getColumnName(8)); assertEquals(Types.VARCHAR, meta.getColumnType(8)); assertEquals("string", meta.getColumnTypeName(8)); assertEquals(Integer.MAX_VALUE, meta.getColumnDisplaySize(8)); assertEquals(Integer.MAX_VALUE, meta.getPrecision(8)); assertEquals(0, meta.getScale(8)); assertEquals("c8", colRS.getString("COLUMN_NAME")); assertEquals(Types.VARCHAR, colRS.getInt("DATA_TYPE")); assertEquals("struct<r:string,s:int,t:double>", colRS.getString("TYPE_NAME").toLowerCase()); assertEquals(meta.getPrecision(8), colRS.getInt("COLUMN_SIZE")); assertEquals(meta.getScale(8), colRS.getInt("DECIMAL_DIGITS")); assertTrue(colRS.next()); assertEquals("c9", meta.getColumnName(9)); assertEquals(Types.TINYINT, meta.getColumnType(9)); assertEquals("tinyint", meta.getColumnTypeName(9)); assertEquals(4, meta.getColumnDisplaySize(9)); assertEquals(3, meta.getPrecision(9)); assertEquals(0, meta.getScale(9)); assertEquals("c9", colRS.getString("COLUMN_NAME")); assertEquals(Types.TINYINT, colRS.getInt("DATA_TYPE")); assertEquals("tinyint", colRS.getString("TYPE_NAME").toLowerCase()); assertEquals(meta.getPrecision(9), colRS.getInt("COLUMN_SIZE")); assertEquals(meta.getScale(9), colRS.getInt("DECIMAL_DIGITS")); assertTrue(colRS.next()); assertEquals("c10", meta.getColumnName(10)); assertEquals(Types.SMALLINT, meta.getColumnType(10)); assertEquals("smallint", meta.getColumnTypeName(10)); assertEquals(6, meta.getColumnDisplaySize(10)); assertEquals(5, meta.getPrecision(10)); assertEquals(0, meta.getScale(10)); assertEquals("c10", colRS.getString("COLUMN_NAME")); assertEquals(Types.SMALLINT, colRS.getInt("DATA_TYPE")); assertEquals("smallint", colRS.getString("TYPE_NAME").toLowerCase()); assertEquals(meta.getPrecision(10), colRS.getInt("COLUMN_SIZE")); assertEquals(meta.getScale(10), colRS.getInt("DECIMAL_DIGITS")); assertTrue(colRS.next()); assertEquals("c11", meta.getColumnName(11)); assertEquals(Types.FLOAT, meta.getColumnType(11)); assertEquals("float", meta.getColumnTypeName(11)); assertEquals(24, meta.getColumnDisplaySize(11)); assertEquals(7, meta.getPrecision(11)); assertEquals(7, meta.getScale(11)); assertEquals("c11", colRS.getString("COLUMN_NAME")); assertEquals(Types.FLOAT, colRS.getInt("DATA_TYPE")); assertEquals("float", colRS.getString("TYPE_NAME").toLowerCase()); assertEquals(meta.getPrecision(11), colRS.getInt("COLUMN_SIZE")); assertEquals(meta.getScale(11), colRS.getInt("DECIMAL_DIGITS")); assertTrue(colRS.next()); assertEquals("c12", meta.getColumnName(12)); assertEquals(Types.BIGINT, meta.getColumnType(12)); assertEquals("bigint", meta.getColumnTypeName(12)); assertEquals(20, meta.getColumnDisplaySize(12)); assertEquals(19, meta.getPrecision(12)); assertEquals(0, meta.getScale(12)); assertEquals("c12", colRS.getString("COLUMN_NAME")); assertEquals(Types.BIGINT, colRS.getInt("DATA_TYPE")); assertEquals("bigint", colRS.getString("TYPE_NAME").toLowerCase()); assertEquals(meta.getPrecision(12), colRS.getInt("COLUMN_SIZE")); assertEquals(meta.getScale(12), colRS.getInt("DECIMAL_DIGITS")); assertEquals("_c12", meta.getColumnName(13)); assertEquals(Types.INTEGER, meta.getColumnType(13)); assertEquals("int", meta.getColumnTypeName(13)); assertEquals(11, meta.getColumnDisplaySize(13)); assertEquals(10, meta.getPrecision(13)); assertEquals(0, meta.getScale(13)); assertEquals("b", meta.getColumnName(14)); assertEquals(Types.VARCHAR, meta.getColumnType(14)); assertEquals("string", meta.getColumnTypeName(14)); assertEquals(Integer.MAX_VALUE, meta.getColumnDisplaySize(14)); assertEquals(Integer.MAX_VALUE, meta.getPrecision(14)); assertEquals(0, meta.getScale(14)); assertEquals("c17", meta.getColumnName(15)); assertEquals(Types.TIMESTAMP, meta.getColumnType(15)); assertEquals("timestamp", meta.getColumnTypeName(15)); assertEquals(29, meta.getColumnDisplaySize(15)); assertEquals(29, meta.getPrecision(15)); assertEquals(9, meta.getScale(15)); assertEquals("c18", meta.getColumnName(16)); assertEquals(Types.DECIMAL, meta.getColumnType(16)); assertEquals("decimal", meta.getColumnTypeName(16)); assertEquals(Integer.MAX_VALUE, meta.getColumnDisplaySize(16)); assertEquals(Integer.MAX_VALUE, meta.getPrecision(16)); assertEquals(Integer.MAX_VALUE, meta.getScale(16)); assertEquals("c20", meta.getColumnName(17)); assertEquals(Types.DATE, meta.getColumnType(17)); assertEquals("date", meta.getColumnTypeName(17)); assertEquals(10, meta.getColumnDisplaySize(17)); assertEquals(10, meta.getPrecision(17)); assertEquals(0, meta.getScale(17)); for (int i = 1; i <= meta.getColumnCount(); i++) { assertFalse(meta.isAutoIncrement(i)); assertFalse(meta.isCurrency(i)); assertEquals(ResultSetMetaData.columnNullable, meta.isNullable(i)); } }
From source file:org.jumpmind.symmetric.db.AbstractTriggerTemplate.java
protected ColumnString fillOutColumnTemplate(String origTableAlias, String tableAlias, String columnPrefix, Column column, DataEventType dml, boolean isOld, Channel channel, Trigger trigger) { boolean isLob = symmetricDialect.getPlatform().isLob(column.getMappedTypeCode()); String templateToUse = null;//from w w w. j a v a 2 s.c om if (column.getJdbcTypeName() != null && (column.getJdbcTypeName().toUpperCase().contains(TypeMap.GEOMETRY)) && StringUtils.isNotBlank(geometryColumnTemplate)) { templateToUse = geometryColumnTemplate; } else if (column.getJdbcTypeName() != null && (column.getJdbcTypeName().toUpperCase().contains(TypeMap.GEOGRAPHY)) && StringUtils.isNotBlank(geographyColumnTemplate)) { templateToUse = geographyColumnTemplate; } else { switch (column.getMappedTypeCode()) { case Types.TINYINT: case Types.SMALLINT: case Types.INTEGER: case Types.BIGINT: case Types.FLOAT: case Types.REAL: case Types.DOUBLE: case Types.NUMERIC: case Types.DECIMAL: templateToUse = numberColumnTemplate; break; case Types.CHAR: case Types.NCHAR: case Types.VARCHAR: case ColumnTypes.NVARCHAR: templateToUse = stringColumnTemplate; break; case ColumnTypes.SQLXML: templateToUse = xmlColumnTemplate; break; case Types.ARRAY: templateToUse = arrayColumnTemplate; break; case Types.LONGVARCHAR: case ColumnTypes.LONGNVARCHAR: if (!isLob) { templateToUse = stringColumnTemplate; break; } case Types.CLOB: if (isOld && symmetricDialect.needsToSelectLobData()) { templateToUse = emptyColumnTemplate; } else { templateToUse = clobColumnTemplate; } break; case Types.BINARY: case Types.VARBINARY: if (isNotBlank(binaryColumnTemplate)) { templateToUse = binaryColumnTemplate; break; } case Types.BLOB: if (requiresWrappedBlobTemplateForBlobType()) { templateToUse = wrappedBlobColumnTemplate; break; } case Types.LONGVARBINARY: case -10: // SQL-Server ntext binary type if (column.getJdbcTypeName() != null && (column.getJdbcTypeName().toUpperCase().contains(TypeMap.IMAGE)) && StringUtils.isNotBlank(imageColumnTemplate)) { if (isOld) { templateToUse = emptyColumnTemplate; } else { templateToUse = imageColumnTemplate; } } else if (isOld && symmetricDialect.needsToSelectLobData()) { templateToUse = emptyColumnTemplate; } else { templateToUse = blobColumnTemplate; } break; case Types.DATE: if (noDateColumnTemplate()) { templateToUse = datetimeColumnTemplate; break; } templateToUse = dateColumnTemplate; break; case Types.TIME: if (noTimeColumnTemplate()) { templateToUse = datetimeColumnTemplate; break; } templateToUse = timeColumnTemplate; break; case Types.TIMESTAMP: templateToUse = datetimeColumnTemplate; break; case Types.BOOLEAN: case Types.BIT: templateToUse = booleanColumnTemplate; break; default: if (column.getJdbcTypeName() != null) { if (column.getJdbcTypeName().toUpperCase().equals(TypeMap.INTERVAL)) { templateToUse = numberColumnTemplate; break; } else if (column.getMappedType().equals(TypeMap.TIMESTAMPTZ) && StringUtils.isNotBlank(this.dateTimeWithTimeZoneColumnTemplate)) { templateToUse = this.dateTimeWithTimeZoneColumnTemplate; break; } else if (column.getMappedType().equals(TypeMap.TIMESTAMPLTZ) && StringUtils.isNotBlank(this.dateTimeWithLocalTimeZoneColumnTemplate)) { templateToUse = this.dateTimeWithLocalTimeZoneColumnTemplate; break; } } if (StringUtils.isBlank(templateToUse) && StringUtils.isNotBlank(this.otherColumnTemplate)) { templateToUse = this.otherColumnTemplate; break; } throw new NotImplementedException(column.getName() + " is of type " + column.getMappedType() + " with JDBC type of " + column.getJdbcTypeName()); } } if (dml == DataEventType.DELETE && isLob && requiresEmptyLobTemplateForDeletes()) { templateToUse = emptyColumnTemplate; } else if (isLob && trigger.isUseStreamLobs()) { templateToUse = emptyColumnTemplate; } if (templateToUse != null) { templateToUse = templateToUse.trim(); } else { throw new NotImplementedException(); } String formattedColumnText = FormatUtils.replace("columnName", String.format("%s%s", columnPrefix, column.getName()), templateToUse); formattedColumnText = FormatUtils.replace("columnSize", column.getSize(), formattedColumnText); formattedColumnText = FormatUtils.replace("masterCollation", symmetricDialect.getMasterCollation(), formattedColumnText); if (isLob) { formattedColumnText = symmetricDialect.massageForLob(formattedColumnText, channel); } formattedColumnText = FormatUtils.replace("origTableAlias", origTableAlias, formattedColumnText); formattedColumnText = FormatUtils.replace("tableAlias", tableAlias, formattedColumnText); formattedColumnText = FormatUtils.replace("prefixName", symmetricDialect.getTablePrefix(), formattedColumnText); return new ColumnString(formattedColumnText, isLob); }
From source file:com.flexive.core.storage.GenericDivisionImporter.java
/** * Import data from a zip archive to a database table * * @param stmt statement to use * @param zip zip archive containing the zip entry * @param ze zip entry within the archive * @param xpath xpath containing the entries to import * @param table name of the table * @param executeInsertPhase execute the insert phase? * @param executeUpdatePhase execute the update phase? * @param updateColumns columns that should be set to <code>null</code> in a first pass (insert) * and updated to the provided values in a second pass (update), * columns that should be used in the where clause have to be prefixed * with "KEY:", to assign a default value use the expression "columnname:default value", * if the default value is "@", it will be a negative counter starting at 0, decreasing. * If the default value starts with "%", it will be set to the column following the "%" * character in the first pass * @throws Exception on errors/*w w w. j a v a 2 s. c o m*/ */ protected void importTable(Statement stmt, final ZipFile zip, final ZipEntry ze, final String xpath, final String table, final boolean executeInsertPhase, final boolean executeUpdatePhase, final String... updateColumns) throws Exception { //analyze the table final ResultSet rs = stmt.executeQuery("SELECT * FROM " + table + " WHERE 1=2"); StringBuilder sbInsert = new StringBuilder(500); StringBuilder sbUpdate = updateColumns.length > 0 ? new StringBuilder(500) : null; if (rs == null) throw new IllegalArgumentException("Can not analyze table [" + table + "]!"); sbInsert.append("INSERT INTO ").append(table).append(" ("); final ResultSetMetaData md = rs.getMetaData(); final Map<String, ColumnInfo> updateClauseColumns = updateColumns.length > 0 ? new HashMap<String, ColumnInfo>(md.getColumnCount()) : null; final Map<String, ColumnInfo> updateSetColumns = updateColumns.length > 0 ? new LinkedHashMap<String, ColumnInfo>(md.getColumnCount()) : null; final Map<String, String> presetColumns = updateColumns.length > 0 ? new HashMap<String, String>(10) : null; //preset to a referenced column (%column syntax) final Map<String, String> presetRefColumns = updateColumns.length > 0 ? new HashMap<String, String>(10) : null; final Map<String, Integer> counters = updateColumns.length > 0 ? new HashMap<String, Integer>(10) : null; final Map<String, ColumnInfo> insertColumns = new HashMap<String, ColumnInfo>( md.getColumnCount() + (counters != null ? counters.size() : 0)); int insertIndex = 1; int updateSetIndex = 1; int updateClauseIndex = 1; boolean first = true; for (int i = 0; i < md.getColumnCount(); i++) { final String currCol = md.getColumnName(i + 1).toLowerCase(); if (updateColumns.length > 0) { boolean abort = false; for (String col : updateColumns) { if (col.indexOf(':') > 0 && !col.startsWith("KEY:")) { String value = col.substring(col.indexOf(':') + 1); col = col.substring(0, col.indexOf(':')); if ("@".equals(value)) { if (currCol.equalsIgnoreCase(col)) { counters.put(col, 0); insertColumns.put(col, new ColumnInfo(md.getColumnType(i + 1), insertIndex++)); sbInsert.append(',').append(currCol); } } else if (value.startsWith("%")) { if (currCol.equalsIgnoreCase(col)) { presetRefColumns.put(col, value.substring(1)); insertColumns.put(col, new ColumnInfo(md.getColumnType(i + 1), insertIndex++)); sbInsert.append(',').append(currCol); // System.out.println("==> adding presetRefColumn "+col+" with value of "+value.substring(1)); } } else if (!presetColumns.containsKey(col)) presetColumns.put(col, value); } if (currCol.equalsIgnoreCase(col)) { abort = true; updateSetColumns.put(currCol, new ColumnInfo(md.getColumnType(i + 1), updateSetIndex++)); break; } } if (abort) continue; } if (first) { first = false; } else sbInsert.append(','); sbInsert.append(currCol); insertColumns.put(currCol, new ColumnInfo(md.getColumnType(i + 1), insertIndex++)); } if (updateColumns.length > 0 && executeUpdatePhase) { sbUpdate.append("UPDATE ").append(table).append(" SET "); int counter = 0; for (String updateColumn : updateSetColumns.keySet()) { if (counter++ > 0) sbUpdate.append(','); sbUpdate.append(updateColumn).append("=?"); } sbUpdate.append(" WHERE "); boolean hasKeyColumn = false; for (String col : updateColumns) { if (!col.startsWith("KEY:")) continue; hasKeyColumn = true; String keyCol = col.substring(4); for (int i = 0; i < md.getColumnCount(); i++) { if (!md.getColumnName(i + 1).equalsIgnoreCase(keyCol)) continue; updateClauseColumns.put(keyCol, new ColumnInfo(md.getColumnType(i + 1), updateClauseIndex++)); sbUpdate.append(keyCol).append("=? AND "); break; } } if (!hasKeyColumn) throw new IllegalArgumentException("Update columns require a KEY!"); sbUpdate.delete(sbUpdate.length() - 5, sbUpdate.length()); //remove trailing " AND " //"shift" clause indices for (String col : updateClauseColumns.keySet()) { GenericDivisionImporter.ColumnInfo ci = updateClauseColumns.get(col); ci.index += (updateSetIndex - 1); } } if (presetColumns != null) { for (String key : presetColumns.keySet()) sbInsert.append(',').append(key); } sbInsert.append(")VALUES("); for (int i = 0; i < insertColumns.size(); i++) { if (i > 0) sbInsert.append(','); sbInsert.append('?'); } if (presetColumns != null) { for (String key : presetColumns.keySet()) sbInsert.append(',').append(presetColumns.get(key)); } sbInsert.append(')'); if (DBG) { LOG.info("Insert statement:\n" + sbInsert.toString()); if (updateColumns.length > 0) LOG.info("Update statement:\n" + sbUpdate.toString()); } //build a map containing all nodes that require attributes //this allows for matching simple xpath queries like "flatstorages/storage[@name='FX_FLAT_STORAGE']/data" final Map<String, List<String>> queryAttributes = new HashMap<String, List<String>>(5); for (String pElem : xpath.split("/")) { if (!(pElem.indexOf('@') > 0 && pElem.indexOf('[') > 0)) continue; List<String> att = new ArrayList<String>(5); for (String pAtt : pElem.split("@")) { if (!(pAtt.indexOf('=') > 0)) continue; att.add(pAtt.substring(0, pAtt.indexOf('='))); } queryAttributes.put(pElem.substring(0, pElem.indexOf('[')), att); } final PreparedStatement psInsert = stmt.getConnection().prepareStatement(sbInsert.toString()); final PreparedStatement psUpdate = updateColumns.length > 0 && executeUpdatePhase ? stmt.getConnection().prepareStatement(sbUpdate.toString()) : null; try { final SAXParser parser = SAXParserFactory.newInstance().newSAXParser(); final DefaultHandler handler = new DefaultHandler() { private String currentElement = null; private Map<String, String> data = new HashMap<String, String>(10); private StringBuilder sbData = new StringBuilder(10000); boolean inTag = false; boolean inElement = false; int counter; List<String> path = new ArrayList<String>(10); StringBuilder currPath = new StringBuilder(100); boolean insertMode = true; /** * {@inheritDoc} */ @Override public void startDocument() throws SAXException { counter = 0; inTag = false; inElement = false; path.clear(); currPath.setLength(0); sbData.setLength(0); data.clear(); currentElement = null; } /** * {@inheritDoc} */ @Override public void processingInstruction(String target, String data) throws SAXException { if (target != null && target.startsWith("fx_")) { if (target.equals("fx_mode")) insertMode = "insert".equals(data); } else super.processingInstruction(target, data); } /** * {@inheritDoc} */ @Override public void endDocument() throws SAXException { if (insertMode) LOG.info("Imported [" + counter + "] entries into [" + table + "] for xpath [" + xpath + "]"); else LOG.info("Updated [" + counter + "] entries in [" + table + "] for xpath [" + xpath + "]"); } /** * {@inheritDoc} */ @Override public void startElement(String uri, String localName, String qName, Attributes attributes) throws SAXException { pushPath(qName, attributes); if (currPath.toString().equals(xpath)) { inTag = true; data.clear(); for (int i = 0; i < attributes.getLength(); i++) { String name = attributes.getLocalName(i); if (StringUtils.isEmpty(name)) name = attributes.getQName(i); data.put(name, attributes.getValue(i)); } } else { currentElement = qName; } inElement = true; sbData.setLength(0); } /** * Push a path element from the stack * * @param qName element name to push * @param att attributes */ private void pushPath(String qName, Attributes att) { if (att.getLength() > 0 && queryAttributes.containsKey(qName)) { String curr = qName + "["; boolean first = true; final List<String> attList = queryAttributes.get(qName); for (int i = 0; i < att.getLength(); i++) { if (!attList.contains(att.getQName(i))) continue; if (first) first = false; else curr += ','; curr += "@" + att.getQName(i) + "='" + att.getValue(i) + "'"; } curr += ']'; path.add(curr); } else path.add(qName); buildPath(); } /** * Pop the top path element from the stack */ private void popPath() { path.remove(path.size() - 1); buildPath(); } /** * Rebuild the current path */ private synchronized void buildPath() { currPath.setLength(0); for (String s : path) currPath.append(s).append('/'); if (currPath.length() > 1) currPath.delete(currPath.length() - 1, currPath.length()); // System.out.println("currPath: " + currPath); } /** * {@inheritDoc} */ @Override public void endElement(String uri, String localName, String qName) throws SAXException { if (currPath.toString().equals(xpath)) { if (DBG) LOG.info("Insert [" + xpath + "]: [" + data + "]"); inTag = false; try { if (insertMode) { if (executeInsertPhase) { processColumnSet(insertColumns, psInsert); counter += psInsert.executeUpdate(); } } else { if (executeUpdatePhase) { if (processColumnSet(updateSetColumns, psUpdate)) { processColumnSet(updateClauseColumns, psUpdate); counter += psUpdate.executeUpdate(); } } } } catch (SQLException e) { throw new SAXException(e); } catch (ParseException e) { throw new SAXException(e); } } else { if (inTag) { data.put(currentElement, sbData.toString()); } currentElement = null; } popPath(); inElement = false; sbData.setLength(0); } /** * Process a column set * * @param columns the columns to process * @param ps prepared statement to use * @return if data other than <code>null</code> has been set * @throws SQLException on errors * @throws ParseException on date/time conversion errors */ private boolean processColumnSet(Map<String, ColumnInfo> columns, PreparedStatement ps) throws SQLException, ParseException { boolean dataSet = false; for (String col : columns.keySet()) { ColumnInfo ci = columns.get(col); String value = data.get(col); if (insertMode && counters != null && counters.get(col) != null) { final int newVal = counters.get(col) - 1; value = String.valueOf(newVal); counters.put(col, newVal); // System.out.println("new value for " + col + ": " + newVal); } if (insertMode && presetRefColumns != null && presetRefColumns.get(col) != null) { value = data.get(presetRefColumns.get(col)); // System.out.println("Set presetRefColumn for "+col+" to ["+value+"] from column ["+presetRefColumns.get(col)+"]"); } if (value == null) ps.setNull(ci.index, ci.columnType); else { dataSet = true; switch (ci.columnType) { case Types.BIGINT: case Types.NUMERIC: if (DBG) LOG.info("BigInt " + ci.index + "->" + new BigDecimal(value)); ps.setBigDecimal(ci.index, new BigDecimal(value)); break; case java.sql.Types.DOUBLE: if (DBG) LOG.info("Double " + ci.index + "->" + Double.parseDouble(value)); ps.setDouble(ci.index, Double.parseDouble(value)); break; case java.sql.Types.FLOAT: case java.sql.Types.REAL: if (DBG) LOG.info("Float " + ci.index + "->" + Float.parseFloat(value)); ps.setFloat(ci.index, Float.parseFloat(value)); break; case java.sql.Types.TIMESTAMP: case java.sql.Types.DATE: if (DBG) LOG.info("Timestamp/Date " + ci.index + "->" + FxFormatUtils.getDateTimeFormat().parse(value)); ps.setTimestamp(ci.index, new Timestamp(FxFormatUtils.getDateTimeFormat().parse(value).getTime())); break; case Types.TINYINT: case Types.SMALLINT: if (DBG) LOG.info("Integer " + ci.index + "->" + Integer.valueOf(value)); ps.setInt(ci.index, Integer.valueOf(value)); break; case Types.INTEGER: case Types.DECIMAL: try { if (DBG) LOG.info("Long " + ci.index + "->" + Long.valueOf(value)); ps.setLong(ci.index, Long.valueOf(value)); } catch (NumberFormatException e) { //Fallback (temporary) for H2 if the reported long is a big decimal (tree...) ps.setBigDecimal(ci.index, new BigDecimal(value)); } break; case Types.BIT: case Types.CHAR: case Types.BOOLEAN: if (DBG) LOG.info("Boolean " + ci.index + "->" + value); if ("1".equals(value) || "true".equals(value)) ps.setBoolean(ci.index, true); else ps.setBoolean(ci.index, false); break; case Types.LONGVARBINARY: case Types.VARBINARY: case Types.BLOB: case Types.BINARY: ZipEntry bin = zip.getEntry(value); if (bin == null) { LOG.error("Failed to lookup binary [" + value + "]!"); ps.setNull(ci.index, ci.columnType); break; } try { ps.setBinaryStream(ci.index, zip.getInputStream(bin), (int) bin.getSize()); } catch (IOException e) { LOG.error("IOException importing binary [" + value + "]: " + e.getMessage(), e); } break; case Types.CLOB: case Types.LONGVARCHAR: case Types.VARCHAR: case SQL_LONGNVARCHAR: case SQL_NCHAR: case SQL_NCLOB: case SQL_NVARCHAR: if (DBG) LOG.info("String " + ci.index + "->" + value); ps.setString(ci.index, value); break; default: LOG.warn("Unhandled type [" + ci.columnType + "] for column [" + col + "]"); } } } return dataSet; } /** * {@inheritDoc} */ @Override public void characters(char[] ch, int start, int length) throws SAXException { if (inElement) sbData.append(ch, start, length); } }; handler.processingInstruction("fx_mode", "insert"); parser.parse(zip.getInputStream(ze), handler); if (updateColumns.length > 0 && executeUpdatePhase) { handler.processingInstruction("fx_mode", "update"); parser.parse(zip.getInputStream(ze), handler); } } finally { Database.closeObjects(GenericDivisionImporter.class, psInsert, psUpdate); } }
From source file:com.mmnaseri.dragonfly.metadata.impl.AnnotationTableMetadataResolver.java
private static int getColumnType(Class<?> javaType, Method method, ColumnMetadata foreignReference) { final int dimensions = ReflectionUtils.getArrayDimensions(javaType); javaType = ReflectionUtils.mapType(ReflectionUtils.getComponentType(javaType)); if (dimensions > 1) { throw new UnsupportedColumnTypeError("Arrays of dimension > 1 are not supported"); }/* www. j a va 2 s .c o m*/ if (Byte.class.equals(javaType) && dimensions == 0) { return Types.TINYINT; } else if (Short.class.equals(javaType)) { return Types.SMALLINT; } else if (Integer.class.equals(javaType)) { return Types.INTEGER; } else if (Long.class.equals(javaType)) { return Types.BIGINT; } else if (Float.class.equals(javaType)) { return Types.FLOAT; } else if (Double.class.equals(javaType)) { return Types.DOUBLE; } else if (BigDecimal.class.equals(javaType)) { return Types.DECIMAL; } else if (BigInteger.class.equals(javaType)) { return Types.NUMERIC; } else if (Character.class.equals(javaType)) { return Types.CHAR; } else if (String.class.equals(javaType) || Class.class.equals(javaType)) { if (method != null && method.isAnnotationPresent(Column.class) && method.getAnnotation(Column.class).length() > 0) { return Types.VARCHAR; } else { return Types.LONGVARCHAR; } } else if (Date.class.isAssignableFrom(javaType)) { if (javaType.equals(java.sql.Date.class)) { return Types.DATE; } else if (javaType.equals(Time.class)) { return Types.TIME; } else if (javaType.equals(java.sql.Timestamp.class)) { return Types.TIMESTAMP; } final TemporalType temporalType = method != null && method.isAnnotationPresent(Temporal.class) ? method.getAnnotation(Temporal.class).value() : null; return (temporalType == null || temporalType.equals(TemporalType.TIMESTAMP)) ? Types.TIMESTAMP : (temporalType.equals(TemporalType.DATE) ? Types.DATE : Types.TIME); } else if (Byte.class.equals(javaType) && dimensions > 0) { return Types.VARBINARY; } else if (Enum.class.isAssignableFrom(javaType)) { return Types.VARCHAR; } else if (Boolean.class.equals(javaType)) { return Types.BOOLEAN; } else if (Collection.class.isAssignableFrom(javaType) && method.isAnnotationPresent(BasicCollection.class)) { return Types.LONGVARCHAR; } if (foreignReference != null) { return Integer.MIN_VALUE; } return Types.LONGVARCHAR; }
From source file:ca.sqlpower.matchmaker.address.AddressPool.java
/** * Inserts and updates the contents of the result table with the * {@link AddressResult} contents in this {@link AddressPool}. * AddressResults that are marked as {@link StorageState#DIRTY} are assumed * to be already in the database and are updated. AddressResults that are * marked as {@link StorageState#NEW} are assumed to be new entries that do * no yet exist in the database and are inserted. * /*w ww . ja v a 2 s . c o m*/ * It is worth noting that at the moment, new Address results won't have an * output street number yet (since they have not been validated yet) but a * {@link NullPointerException} gets thrown if we try to insert a null * Integer, so for the time being, I've set the 'null' steet number to be * -1, since I don't believe there's anyone with a negative street number, * but if I'm wrong, this will have to be changed. * * @throws SQLException * @throws SQLObjectException */ public void store(Logger engineLogger, boolean useBatchExecute, boolean debug) throws SQLException, SQLObjectException { setStarted(true); setFinished(false); setCancelled(false); setProgress(0); List<AddressResult> dirtyAddresses = new ArrayList<AddressResult>(); List<AddressResult> deleteAddresses = new ArrayList<AddressResult>(); List<AddressResult> newAddresses = new ArrayList<AddressResult>(); for (List<Object> key : addresses.keySet()) { AddressResult result = addresses.get(key); if (result.getStorageState() == StorageState.DELETE) { deleteAddresses.add(result); } else if (result.getStorageState() == StorageState.DIRTY) { dirtyAddresses.add(result); } else if (result.getStorageState() == StorageState.NEW) { newAddresses.add(result); } } setJobSize(deleteAddresses.size() + dirtyAddresses.size() + newAddresses.size()); engineLogger.debug("# of Delete Address Records:" + deleteAddresses.size()); engineLogger.debug("# of Dirty Address Records:" + dirtyAddresses.size()); engineLogger.debug("# of New Address Records:" + newAddresses.size()); Connection con = null; PreparedStatement ps = null; Statement stmt = null; StringBuilder sql = null; AddressResult result = null; try { con = project.createResultTableConnection(); con.setAutoCommit(false); boolean useBatchUpdates = useBatchExecute && con.getMetaData().supportsBatchUpdates(); SQLTable resultTable = project.getResultTable(); int keySize = project.getSourceTableIndex().getChildCount(); if (deleteAddresses.size() > 0) { stmt = con.createStatement(); for (AddressResult currentResult : deleteAddresses) { sql = new StringBuilder("DELETE FROM "); appendFullyQualifiedTableName(sql, resultTable); sql.append(" WHERE "); int j = 0; for (Object keyValue : currentResult.getKeyValues()) { if (j > 0) { sql.append("AND "); } if (keyValue == null) { sql.append(SOURCE_ADDRESS_KEY_COLUMN_BASE).append(j).append(" is null "); } else if (keyValue instanceof String || keyValue instanceof Character) { sql.append(SOURCE_ADDRESS_KEY_COLUMN_BASE).append(j) .append("=" + SQL.quote(keyValue.toString()) + " "); } else { sql.append(SOURCE_ADDRESS_KEY_COLUMN_BASE).append(j).append("=" + keyValue + " "); } j++; } engineLogger.debug("Preparing the following address result to be deleted: " + currentResult); engineLogger.debug("Executing statement " + sql); stmt.execute(sql.toString()); incrementProgress(); } if (stmt != null) stmt.close(); stmt = null; } Map<String, Integer> columnMetaData = this.getColumnMetaData(engineLogger, resultTable); /* For backward compatibility, see if old column names are being used. * NOTE: the database may return column names as upper case. */ boolean usingNewNames = true; if (columnMetaData.containsKey(OLD_OUTPUT_DELIVERY_INSTALLATION_NAME)) { usingNewNames = false; } engineLogger.debug("Using new shorter names? " + usingNewNames); if (dirtyAddresses.size() > 0) { //First, create and UPDATE PreparedStatement to update dirty records sql = new StringBuilder(); sql.append("UPDATE "); appendFullyQualifiedTableName(sql, resultTable); sql.append(" SET "); sql.append(INPUT_ADDRESS_LINE1).append("=?, "); // 1 sql.append(INPUT_ADDRESS_LINE2).append("=?, "); // 2 sql.append(INPUT_MUNICIPALITY).append("=?, "); // 3 sql.append(INPUT_PROVINCE).append("=?, "); // 4 sql.append(INPUT_COUNTRY).append("=?, "); // 5 sql.append(INPUT_POSTAL_CODE).append("=?, "); // 6 sql.append(OUTPUT_COUNTRY).append("=?, "); // 7 if (usingNewNames) { sql.append(OUTPUT_DELIVERY_INSTALLATION_NAME).append("=?, "); // 8 sql.append(OUTPUT_DELIVERY_INSTALLATION_TYPE).append("=?, "); // 9 } else { sql.append(OLD_OUTPUT_DELIVERY_INSTALLATION_NAME).append("=?, "); // 8 sql.append(OLD_OUTPUT_DELIVERY_INSTALLATION_TYPE).append("=?, "); // 9 } sql.append(OUTPUT_DIRECTION_PREFIX).append("=?, "); // 10 sql.append(OUTPUT_FAILED_PARSING_STRING).append("=?, "); // 11 sql.append(OUTPUT_GENERAL_DELIVERY_NAME).append("=?, "); // 12 sql.append(OUTPUT_LOCK_BOX_NUMBER).append("=?, "); // 13 sql.append(OUTPUT_LOCK_BOX_TYPE).append("=?, "); // 14 sql.append(OUTPUT_MUNICIPALITY).append("=?, "); // 15 sql.append(OUTPUT_POSTAL_CODE).append("=?, "); // 16 sql.append(OUTPUT_PROVINCE).append("=?, "); // 17 sql.append(OUTPUT_RURAL_ROUTE_NUMBER).append("=?, "); // 18 sql.append(OUTPUT_RURAL_ROUTE_TYPE).append("=?, "); // 19 sql.append(OUTPUT_STREET_DIRECTION).append("=?, "); // 20 sql.append(OUTPUT_STREET_NAME).append("=?, "); // 21 sql.append(OUTPUT_STREET_NUMBER).append("=?, "); // 22 sql.append(OUTPUT_STREET_NUMBER_SUFFIX).append("=?, "); // 23 if (usingNewNames) { sql.append(OUTPUT_STREET_NUMBER_SUFFIX_SEPARATE).append("=?, "); // 23.5 } else { sql.append(OLD_OUTPUT_STREET_NUMBER_SUFFIX_SEPARATE).append("=?, "); // 23.5 } sql.append(OUTPUT_STREET_TYPE).append("=?, "); // 24 sql.append(OUTPUT_STREET_TYPE_PREFIX).append("=?, "); // 25 sql.append(OUTPUT_SUITE).append("=?, "); // 26 sql.append(OUTPUT_SUITE_PREFIX).append("=?, "); // 27 sql.append(OUTPUT_SUITE_TYPE).append("=?, "); // 28 sql.append(OUTPUT_TYPE).append("=?, "); // 29 sql.append(OUTPUT_UNPARSED_ADDRESS).append("=?, "); // 30 sql.append(OUTPUT_URBAN_BEFORE_RURAL).append("=?, "); // 31 sql.append(OUTPUT_VALID).append("=? "); // 32 sql.append("WHERE "); String baseStatement = sql.toString(); int batchCount = 0; for (int i = 0; i < dirtyAddresses.size(); i++) { sql = new StringBuilder(baseStatement); result = dirtyAddresses.get(i); int j = 0; // I really wish there was a better way to handle this, // but unfortunately in SQL, <column> = null and <column> is // null are not the same thing, and you usually want 'is // null' Why they couldn't just use '= null' is beyond me. // Otherwise, we could just use a single prepared statement // for all the records. The main reason we had to switch // back to using prepared statements is because different RDBMS // platforms handle Booleans differently (some support // boolean explicitly, others use an integer (1 or 0) for (Object keyValue : result.getKeyValues()) { if (j > 0) { sql.append("AND "); } if (keyValue == null) { sql.append(SOURCE_ADDRESS_KEY_COLUMN_BASE).append(j).append(" is null "); // 18+ } else if (keyValue instanceof String || keyValue instanceof Character) { sql.append(SOURCE_ADDRESS_KEY_COLUMN_BASE).append(j) .append("=" + SQL.quote(keyValue.toString()) + " "); // 18+ } else { sql.append(SOURCE_ADDRESS_KEY_COLUMN_BASE).append(j).append("=" + keyValue + " "); // 18+ } j++; } ps = con.prepareStatement(sql.toString()); Address inputAddress = result.getInputAddress(); this.adjustInputAddress(inputAddress, columnMetaData); engineLogger.debug( "Setting input unparsed address line 1 to " + inputAddress.getUnparsedAddressLine1()); ps.setString(1, inputAddress.getUnparsedAddressLine1()); engineLogger.debug( "Setting input unparsed address line 2 to " + inputAddress.getUnparsedAddressLine2()); ps.setString(2, inputAddress.getUnparsedAddressLine2()); engineLogger.debug("Setting input municipality to " + inputAddress.getMunicipality()); ps.setString(3, inputAddress.getMunicipality()); engineLogger.debug("Setting input province to " + inputAddress.getProvince()); ps.setString(4, inputAddress.getProvince()); engineLogger.debug("Setting input country to " + inputAddress.getCountry()); ps.setString(5, inputAddress.getCountry()); engineLogger.debug("Setting input postal code to " + inputAddress.getPostalCode()); ps.setString(6, inputAddress.getPostalCode()); Address outputAddress = result.getOutputAddress(); this.adjustOutputAddress(outputAddress, columnMetaData, usingNewNames); engineLogger.debug("Setting output suite to " + outputAddress.getSuite()); ps.setString(7, outputAddress.getSuite()); engineLogger.debug("Setting output delivery installation name to " + outputAddress.getDeliveryInstallationName()); ps.setString(8, outputAddress.getDeliveryInstallationName()); engineLogger.debug("Setting output delivery nstallation type to " + outputAddress.getDeliveryInstallationType()); ps.setString(9, outputAddress.getDeliveryInstallationType()); engineLogger.debug("Setting output direction prefix to " + outputAddress.isDirectionPrefix()); ps.setBoolean(10, outputAddress.isDirectionPrefix()); engineLogger.debug( "Setting output failed parsing string to " + outputAddress.getFailedParsingString()); ps.setString(11, outputAddress.getFailedParsingString()); engineLogger.debug( "Setting output general delivery name to " + outputAddress.getGeneralDeliveryName()); ps.setString(12, outputAddress.getGeneralDeliveryName()); engineLogger.debug("Setting output lock box number to " + outputAddress.getLockBoxNumber()); ps.setString(13, outputAddress.getLockBoxNumber()); engineLogger.debug("Setting output lock box type to " + outputAddress.getLockBoxType()); ps.setString(14, outputAddress.getLockBoxType()); engineLogger.debug("Setting output municipality to " + outputAddress.getMunicipality()); ps.setString(15, outputAddress.getMunicipality()); engineLogger.debug("Setting output postal code to " + outputAddress.getPostalCode()); ps.setString(16, outputAddress.getPostalCode()); engineLogger.debug("Setting output province to " + outputAddress.getProvince()); ps.setString(17, outputAddress.getProvince()); engineLogger .debug("Setting output rural route number to " + outputAddress.getRuralRouteNumber()); ps.setString(18, outputAddress.getRuralRouteNumber()); engineLogger.debug("Setting output rural route type to " + outputAddress.getRuralRouteType()); ps.setString(19, outputAddress.getRuralRouteType()); engineLogger.debug("Setting output street direciton to " + outputAddress.getStreetDirection()); ps.setString(20, outputAddress.getStreetDirection()); engineLogger.debug("Setting output street to " + outputAddress.getStreet()); ps.setString(21, outputAddress.getStreet()); engineLogger.debug("Setting output street number to " + outputAddress.getStreetNumber()); Integer streetNumber = outputAddress.getStreetNumber(); if (streetNumber == null) { ps.setNull(22, Types.INTEGER); } else { ps.setInt(22, streetNumber); } engineLogger.debug( "Setting output street number suffix to " + outputAddress.getStreetNumberSuffix()); ps.setString(23, outputAddress.getStreetNumberSuffix()); engineLogger.debug("Setting output street number suffix separate to " + outputAddress.isStreetNumberSuffixSeparate()); Boolean isStreetNumberSuffixSeparate = outputAddress.isStreetNumberSuffixSeparate(); if (isStreetNumberSuffixSeparate == null) { ps.setNull(24, Types.BOOLEAN); } else { ps.setBoolean(24, outputAddress.isStreetNumberSuffixSeparate()); } engineLogger.debug("Setting output suite to " + outputAddress.getSuite()); ps.setString(25, outputAddress.getStreetType()); engineLogger.debug("Setting output streetTypePrefix to " + outputAddress.isStreetTypePrefix()); ps.setBoolean(26, outputAddress.isStreetTypePrefix()); engineLogger.debug("Setting output suite to " + outputAddress.getSuite()); ps.setString(27, outputAddress.getSuite()); engineLogger.debug("Setting output suitePrefix to " + outputAddress.isSuitePrefix()); ps.setBoolean(28, outputAddress.isSuitePrefix()); engineLogger.debug("Setting output suiteType to " + outputAddress.getSuiteType()); ps.setString(29, outputAddress.getSuiteType()); engineLogger.debug("Setting output type to " + outputAddress.getType()); RecordType type = outputAddress.getType(); ps.setString(30, type == null ? null : type.toString()); engineLogger.debug( "Setting output unparsedAddressLine1 to " + outputAddress.getUnparsedAddressLine1()); ps.setString(31, outputAddress.getUnparsedAddressLine1()); engineLogger.debug("Setting output urbanBeforeRural to " + outputAddress.isUrbanBeforeRural()); Boolean urbanBeforeRural = outputAddress.isUrbanBeforeRural(); if (urbanBeforeRural == null) { ps.setNull(32, Types.BOOLEAN); } else { ps.setBoolean(32, outputAddress.isUrbanBeforeRural()); } engineLogger.debug("Setting valid to " + result.isValid()); ps.setBoolean(33, result.isValid()); if (useBatchUpdates) { engineLogger.debug("Adding to batch"); ps.addBatch(); batchCount++; // TODO: The batchCount should be user setable if (batchCount > 1000) { engineLogger.debug("Executing batch"); ps.executeBatch(); batchCount = 0; } } else { engineLogger.debug("Executing statement"); ps.execute(); } incrementProgress(); } // Execute remaining batch statements if (batchCount > 0 && useBatchUpdates) { ps.executeBatch(); } if (ps != null) ps.close(); ps = null; } if (newAddresses.size() > 0) { //Next, let's meke an INSERT PreparedStatement to insert new records sql = new StringBuilder(); sql.append("INSERT INTO "); appendFullyQualifiedTableName(sql, resultTable); sql.append("("); for (int i = 0; i < keySize; i++) { sql.append(SOURCE_ADDRESS_KEY_COLUMN_BASE).append(i).append(", "); } sql.append(INPUT_ADDRESS_LINE1).append(", "); sql.append(INPUT_ADDRESS_LINE2).append(", "); sql.append(INPUT_MUNICIPALITY).append(", "); sql.append(INPUT_PROVINCE).append(", "); sql.append(INPUT_COUNTRY).append(", "); sql.append(INPUT_POSTAL_CODE).append(", "); sql.append(OUTPUT_COUNTRY).append(", "); if (usingNewNames) { sql.append(OUTPUT_DELIVERY_INSTALLATION_NAME).append(", "); sql.append(OUTPUT_DELIVERY_INSTALLATION_TYPE).append(", "); } else { sql.append(OLD_OUTPUT_DELIVERY_INSTALLATION_NAME).append(", "); sql.append(OLD_OUTPUT_DELIVERY_INSTALLATION_TYPE).append(", "); } sql.append(OUTPUT_DIRECTION_PREFIX).append(", "); sql.append(OUTPUT_FAILED_PARSING_STRING).append(", "); sql.append(OUTPUT_GENERAL_DELIVERY_NAME).append(", "); sql.append(OUTPUT_LOCK_BOX_NUMBER).append(", "); sql.append(OUTPUT_LOCK_BOX_TYPE).append(", "); sql.append(OUTPUT_MUNICIPALITY).append(", "); sql.append(OUTPUT_POSTAL_CODE).append(", "); sql.append(OUTPUT_PROVINCE).append(", "); sql.append(OUTPUT_RURAL_ROUTE_NUMBER).append(", "); sql.append(OUTPUT_RURAL_ROUTE_TYPE).append(", "); sql.append(OUTPUT_STREET_DIRECTION).append(", "); sql.append(OUTPUT_STREET_NAME).append(", "); sql.append(OUTPUT_STREET_NUMBER).append(", "); sql.append(OUTPUT_STREET_NUMBER_SUFFIX).append(", "); if (usingNewNames) { sql.append(OUTPUT_STREET_NUMBER_SUFFIX_SEPARATE).append(", "); } else { sql.append(OLD_OUTPUT_STREET_NUMBER_SUFFIX_SEPARATE).append(", "); } sql.append(OUTPUT_STREET_TYPE).append(", "); sql.append(OUTPUT_STREET_TYPE_PREFIX).append(", "); sql.append(OUTPUT_SUITE).append(", "); sql.append(OUTPUT_SUITE_PREFIX).append(", "); sql.append(OUTPUT_SUITE_TYPE).append(", "); sql.append(OUTPUT_TYPE).append(", "); sql.append(OUTPUT_UNPARSED_ADDRESS).append(", "); sql.append(OUTPUT_URBAN_BEFORE_RURAL).append(", "); sql.append(OUTPUT_VALID).append(")"); sql.append("VALUES("); for (int i = 0; i < keySize; i++) { sql.append("?, "); } sql.append( "?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"); ps = con.prepareStatement(sql.toString()); int batchCount = 0; for (int i = 0; i < newAddresses.size(); i++) { result = newAddresses.get(i); int j = 1; for (Object keyValue : result.getKeyValues()) { ps.setObject(j, keyValue); j++; engineLogger.debug("Setting key value " + j + " to " + keyValue); } Address inputAddress = result.getInputAddress(); this.adjustInputAddress(inputAddress, columnMetaData); engineLogger.debug( "Setting input unparsed address line 1 to " + inputAddress.getUnparsedAddressLine1()); ps.setString(j, inputAddress.getUnparsedAddressLine1()); engineLogger.debug( "Setting input unparsed address line 2 to " + inputAddress.getUnparsedAddressLine2()); ps.setString(j + 1, inputAddress.getUnparsedAddressLine2()); engineLogger.debug("Setting input municipality to " + inputAddress.getMunicipality()); ps.setString(j + 2, inputAddress.getMunicipality()); engineLogger.debug("Setting input province to " + inputAddress.getProvince()); ps.setString(j + 3, inputAddress.getProvince()); engineLogger.debug("Setting input country to " + inputAddress.getCountry()); ps.setString(j + 4, inputAddress.getCountry()); engineLogger.debug("Setting input postal code to " + inputAddress.getPostalCode()); ps.setString(j + 5, inputAddress.getPostalCode()); Address outputAddress = result.getOutputAddress(); this.adjustOutputAddress(outputAddress, columnMetaData, usingNewNames); engineLogger.debug("Setting output suite to " + outputAddress.getSuite()); ps.setString(j + 6, outputAddress.getSuite()); engineLogger.debug("Setting output delivery installation name to " + outputAddress.getDeliveryInstallationName()); ps.setString(j + 7, outputAddress.getDeliveryInstallationName()); engineLogger.debug("Setting output delivery nstallation type to " + outputAddress.getDeliveryInstallationType()); ps.setString(j + 8, outputAddress.getDeliveryInstallationType()); engineLogger.debug("Setting output direction prefix to " + outputAddress.isDirectionPrefix()); ps.setBoolean(j + 9, outputAddress.isDirectionPrefix()); engineLogger.debug( "Setting output failed parsing string to " + outputAddress.getFailedParsingString()); ps.setString(j + 10, outputAddress.getFailedParsingString()); engineLogger.debug( "Setting output general delivery name to " + outputAddress.getGeneralDeliveryName()); ps.setString(j + 11, outputAddress.getGeneralDeliveryName()); engineLogger.debug("Setting output lock box number to " + outputAddress.getLockBoxNumber()); ps.setString(j + 12, outputAddress.getLockBoxNumber()); engineLogger.debug("Setting output lock box type to " + outputAddress.getLockBoxType()); ps.setString(j + 13, outputAddress.getLockBoxType()); engineLogger.debug("Setting output municipality to " + outputAddress.getMunicipality()); ps.setString(j + 14, outputAddress.getMunicipality()); engineLogger.debug("Setting output postal code to " + outputAddress.getPostalCode()); ps.setString(j + 15, outputAddress.getPostalCode()); engineLogger.debug("Setting output province to " + outputAddress.getProvince()); ps.setString(j + 16, outputAddress.getProvince()); engineLogger .debug("Setting output rural route number to " + outputAddress.getRuralRouteNumber()); ps.setString(j + 17, outputAddress.getRuralRouteNumber()); engineLogger.debug("Setting output rural route type to " + outputAddress.getRuralRouteType()); ps.setString(j + 18, outputAddress.getRuralRouteType()); engineLogger.debug("Setting output street direciton to " + outputAddress.getStreetDirection()); ps.setString(j + 19, outputAddress.getStreetDirection()); engineLogger.debug("Setting output street to " + outputAddress.getStreet()); ps.setString(j + 20, outputAddress.getStreet()); engineLogger.debug("Setting output street number to " + outputAddress.getStreetNumber()); Integer streetNumber = outputAddress.getStreetNumber(); if (streetNumber == null) { ps.setNull(j + 21, Types.INTEGER); } else { ps.setInt(j + 21, streetNumber); } engineLogger.debug( "Setting output street number suffix to " + outputAddress.getStreetNumberSuffix()); ps.setString(j + 22, outputAddress.getStreetNumberSuffix()); engineLogger.debug("Setting output street number suffix separate to " + outputAddress.isStreetNumberSuffixSeparate()); Boolean isStreetNumberSuffixSeparate = outputAddress.isStreetNumberSuffixSeparate(); if (isStreetNumberSuffixSeparate == null) { ps.setNull(j + 23, Types.BOOLEAN); } else { ps.setBoolean(j + 23, outputAddress.isStreetNumberSuffixSeparate()); } engineLogger.debug("Setting output suite to " + outputAddress.getSuite()); ps.setString(j + 24, outputAddress.getStreetType()); engineLogger.debug("Setting output streetTypePrefix to " + outputAddress.isStreetTypePrefix()); ps.setBoolean(j + 25, outputAddress.isStreetTypePrefix()); engineLogger.debug("Setting output suite to " + outputAddress.getSuite()); ps.setString(j + 26, outputAddress.getSuite()); engineLogger.debug("Setting output suitePrefix to " + outputAddress.isSuitePrefix()); ps.setBoolean(j + 27, outputAddress.isSuitePrefix()); engineLogger.debug("Setting output suiteType to " + outputAddress.getSuiteType()); ps.setString(j + 28, outputAddress.getSuiteType()); engineLogger.debug("Setting output type to " + outputAddress.getType()); RecordType type = outputAddress.getType(); ps.setString(j + 29, type == null ? null : type.toString()); engineLogger.debug( "Setting output unparsedAddressLine1 to " + outputAddress.getUnparsedAddressLine1()); ps.setString(j + 30, outputAddress.getUnparsedAddressLine1()); engineLogger.debug("Setting output urbanBeforeRural to " + outputAddress.isUrbanBeforeRural()); Boolean urbanBeforeRural = outputAddress.isUrbanBeforeRural(); if (urbanBeforeRural == null) { ps.setNull(j + 31, Types.BOOLEAN); } else { ps.setBoolean(j + 31, outputAddress.isUrbanBeforeRural()); } engineLogger.debug("Setting valid to " + result.isValid()); ps.setBoolean(j + 32, result.isValid()); engineLogger.debug("Preparing the following address to be inserted: " + result); if (useBatchUpdates) { engineLogger.debug("Adding to batch"); ps.addBatch(); batchCount++; // TODO: The batchCount should be user setable if (batchCount > 1000) { engineLogger.debug("Executing batch"); ps.executeBatch(); batchCount = 0; } } else { engineLogger.debug("Executing statement"); ps.execute(); } incrementProgress(); } // Execute remaining batch statements if (batchCount > 0 && useBatchUpdates) { ps.executeBatch(); } if (ps != null) ps.close(); ps = null; } if (debug) { engineLogger.debug("Rolling back changes"); con.rollback(); } else { engineLogger.debug("Committing changes"); con.commit(); } for (AddressResult ar : addresses.values()) { ar.markClean(); } } catch (Exception ex) { try { con.rollback(); } catch (SQLException sqlEx) { engineLogger.error("Error while rolling back. " + "Suppressing this exception to prevent it from overshadowing the orginal exception.", sqlEx); } throw new RuntimeException("Unexpected exception while storing address validation results.\n" + "SQL statement: " + ((sql == null) ? "null" : sql.toString()) + "\n" + "Current result: " + ((result == null) ? "null" : "Input Address:\n" + result.getInputAddress() + "\n" + "Output Address:\n" + result.getOutputAddress()), ex); } finally { setFinished(true); if (ps != null) try { ps.close(); } catch (SQLException e) { engineLogger.error("Error while closing PreparedStatement", e); } if (stmt != null) try { stmt.close(); } catch (SQLException e) { engineLogger.error("Error while closing Statement", e); } if (con != null) try { con.close(); } catch (SQLException e) { engineLogger.error("Error while closing Connection", e); } } }
From source file:gobblin.source.extractor.extract.jdbc.JdbcExtractor.java
private String parseColumnAsString(final ResultSet resultset, final ResultSetMetaData resultsetMetadata, int i) throws SQLException { if (isBlob(resultsetMetadata.getColumnType(i))) { return readBlobAsString(resultset.getBlob(i)); }// w ww .j a v a 2 s .c o m if ((resultsetMetadata.getColumnType(i) == Types.BIT || resultsetMetadata.getColumnType(i) == Types.BOOLEAN) && convertBitToBoolean()) { return Boolean.toString(resultset.getBoolean(i)); } return resultset.getString(i); }