List of usage examples for java.sql Types DECIMAL
int DECIMAL
To view the source code for java.sql Types DECIMAL.
Click Source Link
The constant in the Java programming language, sometimes referred to as a type code, that identifies the generic SQL type DECIMAL.
From source file:mondrian.spi.impl.JdbcDialectImpl.java
public SqlStatement.Type getType(ResultSetMetaData metaData, int columnIndex) throws SQLException { final int columnType = metaData.getColumnType(columnIndex + 1); SqlStatement.Type internalType = null; if (columnType != Types.NUMERIC && columnType != Types.DECIMAL) { internalType = DEFAULT_TYPE_MAP.get(columnType); } else {/* w w w .j ava2s . c o m*/ final int precision = metaData.getPrecision(columnIndex + 1); final int scale = metaData.getScale(columnIndex + 1); if (scale == 0 && precision <= 9) { // An int (up to 2^31 = 2.1B) can hold any NUMBER(10, 0) value // (up to 10^9 = 1B). internalType = SqlStatement.Type.INT; } else { internalType = SqlStatement.Type.DOUBLE; } } internalType = internalType == null ? SqlStatement.Type.OBJECT : internalType; logTypeInfo(metaData, columnIndex, internalType); return internalType; }
From source file:org.dspace.storage.rdbms.DatabaseManager.java
/** * Convert the current row in a ResultSet into a TableRow object. * * @param results// w ww . java 2 s. co m * 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 */ static TableRow process(ResultSet results, String table, List<String> pColumnNames) throws SQLException { 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); switch (jdbctype) { case Types.BIT: row.setColumn(name, results.getBoolean(i)); break; case Types.INTEGER: case Types.NUMERIC: if (isOracle) { 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)); } break; case Types.DECIMAL: case Types.BIGINT: row.setColumn(name, results.getLong(i)); break; case Types.DOUBLE: row.setColumn(name, results.getDouble(i)); break; case Types.CLOB: if (isOracle) { row.setColumn(name, results.getString(i)); } else { throw new IllegalArgumentException("Unsupported JDBC type: " + jdbctype); } break; case 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) { log.error("Unable to parse text from database", e); } break; case Types.DATE: row.setColumn(name, results.getDate(i)); break; case Types.TIME: row.setColumn(name, results.getTime(i)); break; case Types.TIMESTAMP: row.setColumn(name, results.getTimestamp(i)); break; default: throw new IllegalArgumentException("Unsupported JDBC type: " + jdbctype); } // Determines if the last column was null, and sets the tablerow accordingly 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.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;/*w w w .j av a 2 s .c o m*/ 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.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;// ww w . jav a 2 s.c o m 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.jumpmind.vaadin.ui.sqlexplorer.TabularResultLayout.java
private void setEditor(Grid.Column gridColumn, Column tableColumn, List<TextField> primaryKeyEditors) { TextField editor = new TextField(); int typeCode = tableColumn.getMappedTypeCode(); switch (typeCode) { case Types.DATE: editor.setConverter(new ObjectConverter(Date.class, typeCode)); break;//w ww . java 2 s .c om case Types.TIME: editor.setConverter(new ObjectConverter(Time.class, typeCode)); break; case Types.TIMESTAMP: editor.setConverter(new ObjectConverter(Timestamp.class, typeCode)); break; case Types.BIT: editor.setConverter(new StringToBooleanConverter()); break; case Types.TINYINT: case Types.SMALLINT: case Types.BIGINT: case Types.INTEGER: editor.setConverter(new StringToLongConverter() { private static final long serialVersionUID = 1L; public NumberFormat getFormat(Locale locale) { NumberFormat format = super.getFormat(locale); format.setGroupingUsed(false); return format; } }); break; case Types.FLOAT: case Types.DOUBLE: case Types.REAL: case Types.NUMERIC: case Types.DECIMAL: editor.setConverter(new StringToBigDecimalConverter() { private static final long serialVersionUID = 1L; public NumberFormat getFormat(Locale locale) { NumberFormat format = super.getFormat(locale); format.setGroupingUsed(false); return format; } }); break; default: break; } editor.addValidator(new TableChangeValidator(editor, tableColumn)); editor.setNullRepresentation(""); if (!tableColumn.isRequired()) { editor.setNullSettingAllowed(true); } if (tableColumn.isPrimaryKey()) { primaryKeyEditors.add(editor); } gridColumn.setEditorField(editor); }
From source file:org.dspace.storage.rdbms.MockDatabaseManager.java
/** * Convert the current row in a ResultSet into a TableRow object. * * @param results//www .j a v a 2 s. co m * 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.wso2.carbon.dataservices.core.odata.RDBMSDataHandler.java
private String getValueFromResultSet(int columnType, String column, ResultSet resultSet) throws SQLException { String paramValue;// w ww.j a v a 2 s . 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: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"); }/*w ww . j av a 2s . co 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: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//from w w w.j av 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:org.openmrs.module.sync.api.db.hibernate.HibernateSyncDAO.java
public void exportChildDB(String uuidForChild, OutputStream os) throws DAOException { PrintStream out = new PrintStream(os); Set<String> tablesToSkip = new HashSet<String>(); {/*from ww w . ja va 2 s. c om*/ tablesToSkip.add("hl7_in_archive"); tablesToSkip.add("hl7_in_queue"); tablesToSkip.add("hl7_in_error"); tablesToSkip.add("formentry_archive"); tablesToSkip.add("formentry_queue"); tablesToSkip.add("formentry_error"); tablesToSkip.add("sync_class"); tablesToSkip.add("sync_import"); tablesToSkip.add("sync_record"); tablesToSkip.add("sync_server"); tablesToSkip.add("sync_server_class"); tablesToSkip.add("sync_server_record"); // TODO: figure out which other tables to skip // tablesToSkip.add("obs"); // tablesToSkip.add("concept"); // tablesToSkip.add("patient"); } List<String> tablesToDump = new ArrayList<String>(); Session session = sessionFactory.getCurrentSession(); String schema = (String) session.createSQLQuery("SELECT schema()").uniqueResult(); log.warn("schema: " + schema); // Get all tables that we'll need to dump { Query query = session.createSQLQuery( "SELECT tabs.table_name FROM INFORMATION_SCHEMA.TABLES tabs WHERE tabs.table_schema = '" + schema + "'"); for (Object tn : query.list()) { String tableName = (String) tn; if (!tablesToSkip.contains(tableName.toLowerCase())) tablesToDump.add(tableName); } } log.warn("tables to dump: " + tablesToDump); String thisServerGuid = getGlobalProperty(SyncConstants.PROPERTY_SERVER_UUID); // Write the DDL Header as mysqldump does { out.println("-- ------------------------------------------------------"); out.println("-- Database dump to create an openmrs child server"); out.println("-- Schema: " + schema); out.println("-- Parent GUID: " + thisServerGuid); out.println("-- Parent version: " + OpenmrsConstants.OPENMRS_VERSION); out.println("-- ------------------------------------------------------"); out.println(""); out.println("/*!40101 SET CHARACTER_SET_CLIENT=utf8 */;"); out.println("/*!40101 SET NAMES utf8 */;"); out.println("/*!40103 SET TIME_ZONE='+00:00' */;"); out.println("/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;"); out.println("/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;"); out.println("/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;"); out.println("/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;"); out.println("/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;"); out.println("/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;"); out.println("/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;"); out.println("/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;"); out.println(""); } try { // JDBC way of doing this // Connection conn = // DriverManager.getConnection("jdbc:mysql://localhost/" + schema, // "test", "test"); Connection conn = sessionFactory.getCurrentSession().connection(); try { Statement st = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY); // Get the create database statement ResultSet rs = st.executeQuery("SHOW CREATE DATABASE " + schema); for (String tableName : tablesToDump) { out.println(); out.println("--"); out.println("-- Table structure for table `" + tableName + "`"); out.println("--"); out.println("DROP TABLE IF EXISTS `" + tableName + "`;"); out.println("SET @saved_cs_client = @@character_set_client;"); out.println("SET character_set_client = utf8;"); rs = st.executeQuery("SHOW CREATE TABLE " + tableName); while (rs.next()) { out.println(rs.getString("Create Table") + ";"); } out.println("SET character_set_client = @saved_cs_client;"); out.println(); { out.println("-- Dumping data for table `" + tableName + "`"); out.println("LOCK TABLES `" + tableName + "` WRITE;"); out.println("/*!40000 ALTER TABLE `" + tableName + "` DISABLE KEYS */;"); boolean first = true; rs = st.executeQuery("select * from " + tableName); ResultSetMetaData md = rs.getMetaData(); int numColumns = md.getColumnCount(); int rowNum = 0; boolean insert = false; while (rs.next()) { if (rowNum == 0) { insert = true; out.print("INSERT INTO `" + tableName + "` VALUES "); } ++rowNum; if (first) { first = false; } else { out.print(", "); } if (rowNum % 20 == 0) { out.println(); } out.print("("); for (int i = 1; i <= numColumns; ++i) { if (i != 1) { out.print(","); } if (rs.getObject(i) == null) { out.print("NULL"); } else { switch (md.getColumnType(i)) { case Types.VARCHAR: case Types.CHAR: case Types.LONGVARCHAR: out.print("'"); out.print( rs.getString(i).replaceAll("\n", "\\\\n").replaceAll("'", "\\\\'")); out.print("'"); break; case Types.BIGINT: case Types.DECIMAL: case Types.NUMERIC: out.print(rs.getBigDecimal(i)); break; case Types.BIT: out.print(rs.getBoolean(i)); break; case Types.INTEGER: case Types.SMALLINT: case Types.TINYINT: out.print(rs.getInt(i)); break; case Types.REAL: case Types.FLOAT: case Types.DOUBLE: out.print(rs.getDouble(i)); break; case Types.BLOB: case Types.VARBINARY: case Types.LONGVARBINARY: Blob blob = rs.getBlob(i); out.print("'"); InputStream in = blob.getBinaryStream(); while (true) { int b = in.read(); if (b < 0) { break; } char c = (char) b; if (c == '\'') { out.print("\'"); } else { out.print(c); } } out.print("'"); break; case Types.CLOB: out.print("'"); out.print( rs.getString(i).replaceAll("\n", "\\\\n").replaceAll("'", "\\\\'")); out.print("'"); break; case Types.DATE: out.print("'" + rs.getDate(i) + "'"); break; case Types.TIMESTAMP: out.print("'" + rs.getTimestamp(i) + "'"); break; default: throw new RuntimeException("TODO: handle type code " + md.getColumnType(i) + " (name " + md.getColumnTypeName(i) + ")"); } } } out.print(")"); } if (insert) { out.println(";"); insert = false; } out.println("/*!40000 ALTER TABLE `" + tableName + "` ENABLE KEYS */;"); out.println("UNLOCK TABLES;"); out.println(); } } } finally { conn.close(); } // Now we mark this as a child out.println("-- Now mark this as a child database"); if (uuidForChild == null) uuidForChild = SyncUtil.generateUuid(); out.println("update global_property set property_value = '" + uuidForChild + "' where property = '" + SyncConstants.PROPERTY_SERVER_UUID + "';"); // Write the footer of the DDL script { out.println("/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;"); out.println("/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;"); out.println("/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;"); out.println("/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;"); out.println("/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;"); out.println("/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;"); out.println("/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;"); out.println("/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;"); } out.flush(); out.close(); } catch (IOException ex) { log.error("IOException", ex); } catch (SQLException ex) { log.error("SQLException", ex); } }