List of usage examples for java.sql Types BINARY
int BINARY
To view the source code for java.sql Types BINARY.
Click Source Link
The constant in the Java programming language, sometimes referred to as a type code, that identifies the generic SQL type BINARY.
From source file:gsn.storage.StorageManager.java
public void executeInsert(CharSequence tableName, DataField[] fields, StreamElement streamElement, Connection connection) throws SQLException { PreparedStatement ps = null;// w w w . j av a2s. co m String query = getStatementInsert(tableName, fields).toString(); try { ps = connection.prepareStatement(query); int counter = 1; for (DataField dataField : fields) { if (dataField.getName().equalsIgnoreCase("timed")) continue; Serializable value = streamElement.getData(dataField.getName()); switch (dataField.getDataTypeID()) { case DataTypes.VARCHAR: if (value == null) ps.setNull(counter, Types.VARCHAR); else ps.setString(counter, value.toString()); break; case DataTypes.CHAR: if (value == null) ps.setNull(counter, Types.CHAR); else ps.setString(counter, value.toString()); break; case DataTypes.INTEGER: if (value == null) ps.setNull(counter, Types.INTEGER); else ps.setInt(counter, ((Number) value).intValue()); break; case DataTypes.SMALLINT: if (value == null) ps.setNull(counter, Types.SMALLINT); else ps.setShort(counter, ((Number) value).shortValue()); break; case DataTypes.TINYINT: if (value == null) ps.setNull(counter, Types.TINYINT); else ps.setByte(counter, ((Number) value).byteValue()); break; case DataTypes.DOUBLE: if (value == null) ps.setNull(counter, Types.DOUBLE); else ps.setDouble(counter, ((Number) value).doubleValue()); break; case DataTypes.FLOAT: if (value == null) ps.setNull(counter, Types.FLOAT); else ps.setFloat(counter, ((Number) value).floatValue()); break; case DataTypes.BIGINT: if (value == null) ps.setNull(counter, Types.BIGINT); else ps.setLong(counter, ((Number) value).longValue()); break; case DataTypes.BINARY: if (value == null) ps.setNull(counter, Types.BINARY); else ps.setBytes(counter, (byte[]) value); break; default: logger.error("The type conversion is not supported for : " + dataField.getName() + "(" + dataField.getDataTypeID() + ") : "); } counter++; } ps.setLong(counter, streamElement.getTimeStamp()); ps.execute(); } catch (GSNRuntimeException e) { //if (e.getType() == GSNRuntimeException.UNEXPECTED_VIRTUAL_SENSOR_REMOVAL) { // if (logger.isDebugEnabled()) // logger.debug("An stream element dropped due to unexpected virtual sensor removal. (Stream element: " + streamElement.toString() + ")+ Query: " + query, e); //} else logger.warn("Inserting a stream element failed : " + streamElement.toString(), e); } catch (SQLException e) { if (e.getMessage().toLowerCase().contains("duplicate entry")) logger.info("Error occurred on inserting data to the database, an stream element dropped due to: " + e.getMessage() + ". (Stream element: " + streamElement.toString() + ")+ Query: " + query); else logger.warn("Error occurred on inserting data to the database, an stream element dropped due to: " + e.getMessage() + ". (Stream element: " + streamElement.toString() + ")+ Query: " + query); throw e; } finally { close(ps); } }
From source file:com.flexive.core.storage.genericSQL.GenericBinarySQLStorage.java
/** * Transfer a binary from the transit to the 'real' binary table * * @param _con open and valid connection * @param binary the binary descriptor//from w w w.j a v a2s .c om * @param id desired id * @param version desired version * @param quality desired quality * @return descriptor of final binary * @throws FxDbException on errors looking up the sequencer */ private BinaryDescriptor binaryTransit(Connection _con, BinaryDescriptor binary, long id, int version, int quality) throws FxDbException { PreparedStatement ps = null; BinaryDescriptor created; FileInputStream fis = null; boolean dbTransit; boolean dbStorage; final long dbThreshold; final long dbPreviewThreshold; final int divisionId = FxContext.get().getDivisionId(); try { final DivisionConfigurationEngine divisionConfig = EJBLookup.getDivisionConfigurationEngine(); dbTransit = divisionConfig.get(SystemParameters.BINARY_TRANSIT_DB); if (id >= 0) { dbThreshold = divisionConfig.get(SystemParameters.BINARY_DB_THRESHOLD); dbPreviewThreshold = divisionConfig.get(SystemParameters.BINARY_DB_PREVIEW_THRESHOLD); } else { //force storage of system binaries in the database dbThreshold = -1; dbPreviewThreshold = -1; } dbStorage = dbThreshold < 0 || binary.getSize() < dbThreshold; } catch (FxApplicationException e) { throw e.asRuntimeException(); } Connection con = null; try { con = Database.getNonTXDataSource(divisionId).getConnection(); con.setAutoCommit(false); double resolution = 0.0; int width = 0; int height = 0; boolean isImage = binary.getMimeType().startsWith("image/"); if (isImage) { try { width = Integer .parseInt(defaultString(FxXMLUtils.getElementData(binary.getMetadata(), "width"), "0")); height = Integer.parseInt( defaultString(FxXMLUtils.getElementData(binary.getMetadata(), "height"), "0")); resolution = Double.parseDouble( defaultString(FxXMLUtils.getElementData(binary.getMetadata(), "xResolution"), "0")); } catch (NumberFormatException e) { //ignore LOG.warn(e, e); } } created = new BinaryDescriptor(CacheAdmin.getStreamServers(), id, version, quality, System.currentTimeMillis(), binary.getName(), binary.getSize(), binary.getMetadata(), binary.getMimeType(), isImage, resolution, width, height, binary.getMd5sum()); //we can copy the blob directly into the binary table if the database is used for transit and the final binary is //stored in the filesystem final boolean copyBlob = dbTransit && dbStorage; boolean storePrev1FS = false, storePrev2FS = false, storePrev3FS = false, storePrev4FS = false; long prev1Length = -1, prev2Length = -1, prev3Length = -1, prev4Length = -1; if (dbPreviewThreshold >= 0) { //we have to check if preview should be stored on the filesystem ps = con.prepareStatement(BINARY_TRANSIT_PREVIEW_SIZES); ps.setString(1, binary.getHandle()); ResultSet rs = ps.executeQuery(); if (!rs.next()) throw new FxDbException("ex.content.binary.transitNotFound", binary.getHandle()); rs.getLong(1); //check if previewref is null if (rs.wasNull()) { //if previews are not referenced, check thresholds storePrev1FS = (prev1Length = rs.getLong(2)) >= dbPreviewThreshold && !rs.wasNull(); storePrev2FS = (prev2Length = rs.getLong(3)) >= dbPreviewThreshold && !rs.wasNull(); storePrev3FS = (prev3Length = rs.getLong(4)) >= dbPreviewThreshold && !rs.wasNull(); storePrev4FS = (prev4Length = rs.getLong(5)) >= dbPreviewThreshold && !rs.wasNull(); } } if (ps != null) ps.close(); String previewSelect = (storePrev1FS ? ",NULL" : ",PREV1") + (storePrev2FS ? ",NULL" : ",PREV2") + (storePrev3FS ? ",NULL" : ",PREV3") + (storePrev4FS ? ",NULL" : ",PREV4"); //check if the binary is to be replaced ps = con.prepareStatement( "SELECT COUNT(*) FROM " + TBL_CONTENT_BINARY + " WHERE ID=? AND VER=? AND QUALITY=?"); ps.setLong(1, created.getId()); ps.setInt(2, created.getVersion()); //version ps.setInt(3, created.getQuality()); //quality ResultSet rsExist = ps.executeQuery(); final boolean replaceBinary = rsExist != null && rsExist.next() && rsExist.getLong(1) > 0; ps.close(); int paramIndex = 1; if (replaceBinary) { ps = con.prepareStatement(BINARY_TRANSIT_REPLACE + (copyBlob ? BINARY_TRANSIT_REPLACE_FBLOB_COPY : BINARY_TRANSIT_REPLACE_FBLOB_PARAM) + BINARY_TRANSIT_REPLACE_PARAMS); FxBinaryUtils.removeBinary(divisionId, created.getId()); } else { ps = con.prepareStatement((copyBlob ? BINARY_TRANSIT : BINARY_TRANSIT_FILESYSTEM) + previewSelect + BINARY_TRANSIT_PREVIEW_WHERE); ps.setLong(paramIndex++, created.getId()); ps.setInt(paramIndex++, created.getVersion()); //version ps.setInt(paramIndex++, created.getQuality()); //quality } File binaryTransit = null; boolean removeTransitFile = false; if (dbTransit) { //transit is handled in the database try { if (!dbStorage) { //binaries are stored on the filesystem binaryTransit = getBinaryTransitFileInfo(binary).getBinaryTransitFile(); removeTransitFile = true; //have to clean up afterwards since its a temporary file we get } } catch (FxApplicationException e) { if (e instanceof FxDbException) throw (FxDbException) e; throw new FxDbException(e); } } else { //transit file resides on the local file system binaryTransit = FxBinaryUtils.getTransitFile(divisionId, binary.getHandle()); removeTransitFile = true; // temporary transit file can be removed as well if (binaryTransit == null) throw new FxDbException("ex.content.binary.transitNotFound", binary.getHandle()); } boolean needExplicitBlobInsert = false; if (copyBlob && replaceBinary) ps.setString(paramIndex++, binary.getHandle()); if (!copyBlob) { //we do not perform a simple blob copy operation in the database if (dbStorage) { //binary is stored in the database -> copy it from the transit file (might be a temp. file) if (blobInsertSelectAllowed()) { fis = new FileInputStream(binaryTransit); ps.setBinaryStream(paramIndex++, fis, (int) binaryTransit.length()); } else { ps.setNull(paramIndex++, Types.BINARY); needExplicitBlobInsert = true; } } else { //binary is stored on the filesystem -> move transit file to binary storage file try { if (!FxFileUtils.moveFile(binaryTransit, FxBinaryUtils.createBinaryFile(divisionId, created.getId(), created.getVersion(), created.getQuality(), PreviewSizes.ORIGINAL.getBlobIndex()))) throw new FxDbException(LOG, "ex.content.binary.fsCopyFailed", created.getId()); } catch (IOException e) { throw new FxDbException(LOG, "ex.content.binary.fsCopyFailedError", created.getId(), e.getMessage()); } ps.setNull(paramIndex++, Types.BINARY); } } // int cnt = paramIndex; //copyBlob ? 4 : 5; ps.setString(paramIndex++, created.getName()); ps.setLong(paramIndex++, created.getSize()); setBigString(ps, paramIndex++, created.getMetadata()); ps.setString(paramIndex++, created.getMimeType()); if (replaceBinary) ps.setNull(paramIndex++, java.sql.Types.NUMERIC); //set preview ref to null ps.setBoolean(paramIndex++, created.isImage()); ps.setDouble(paramIndex++, created.getResolution()); ps.setInt(paramIndex++, created.getWidth()); ps.setInt(paramIndex++, created.getHeight()); ps.setString(paramIndex++, created.getMd5sum()); if (replaceBinary) { ps.setLong(paramIndex++, created.getId()); ps.setInt(paramIndex++, created.getVersion()); //version ps.setInt(paramIndex, created.getQuality()); //quality } else ps.setString(paramIndex, binary.getHandle()); ps.executeUpdate(); if (needExplicitBlobInsert) { ps.close(); ps = con.prepareStatement( "UPDATE " + TBL_CONTENT_BINARY + " SET FBLOB=? WHERE ID=? AND VER=? AND QUALITY=?"); fis = new FileInputStream(binaryTransit); ps.setBinaryStream(1, fis, (int) binaryTransit.length()); ps.setLong(2, created.getId()); ps.setInt(3, created.getVersion()); //version ps.setInt(4, created.getQuality()); //quality ps.executeUpdate(); } if (removeTransitFile && binaryTransit != null) { //transit file was a temp. file -> got to clean up FxFileUtils.removeFile(binaryTransit); } if (replaceBinary) { ps.close(); //set all preview entries to the values provided by the transit table ps = con.prepareStatement("UPDATE " + TBL_CONTENT_BINARY + " SET PREV1=NULL,PREV2=NULL,PREV3=NULL,PREV4=NULL WHERE ID=? AND VER=? AND QUALITY=?"); ps.setLong(1, created.getId()); ps.setInt(2, created.getVersion()); //version ps.setInt(3, created.getQuality()); //quality ps.executeUpdate(); ps.close(); ps = con.prepareStatement( "SELECT PREV1_WIDTH,PREV1_HEIGHT,PREV1SIZE,PREV2_WIDTH,PREV2_HEIGHT,PREV2SIZE,PREV3_WIDTH,PREV3_HEIGHT,PREV3SIZE,PREV4_WIDTH,PREV4_HEIGHT,PREV4SIZE FROM " + TBL_BINARY_TRANSIT + " WHERE BKEY=?"); ps.setString(1, binary.getHandle()); ResultSet rsPrev = ps.executeQuery(); if (rsPrev != null && rsPrev.next()) { long[] data = new long[12]; for (int d = 0; d < 12; d++) data[d] = rsPrev.getLong(d + 1); ps.close(); ps = con.prepareStatement("UPDATE " + TBL_CONTENT_BINARY + " SET PREV1_WIDTH=?,PREV1_HEIGHT=?,PREV1SIZE=?,PREV2_WIDTH=?,PREV2_HEIGHT=?,PREV2SIZE=?,PREV3_WIDTH=?,PREV3_HEIGHT=?,PREV3SIZE=?,PREV4_WIDTH=?,PREV4_HEIGHT=?,PREV4SIZE=? WHERE ID=? AND VER=? AND QUALITY=?"); for (int d = 0; d < 12; d++) ps.setLong(d + 1, data[d]); ps.setLong(13, created.getId()); ps.setInt(14, created.getVersion()); //version ps.setInt(15, created.getQuality()); //quality ps.executeUpdate(); } } //finally fetch the preview blobs from transit and store them on the filesystem if required if (storePrev1FS || storePrev2FS || storePrev3FS || storePrev4FS) { ps.close(); previewSelect = (!storePrev1FS ? ",NULL" : ",PREV1") + (!storePrev2FS ? ",NULL" : ",PREV2") + (!storePrev3FS ? ",NULL" : ",PREV3") + (!storePrev4FS ? ",NULL" : ",PREV4"); ps = con.prepareStatement("SELECT " + previewSelect.substring(1) + BINARY_TRANSIT_PREVIEW_WHERE); ps.setString(1, binary.getHandle()); ResultSet rs = ps.executeQuery(); if (!rs.next()) throw new FxDbException("ex.content.binary.transitNotFound", binary.getHandle()); if (storePrev1FS) try { if (!FxFileUtils.copyStream2File(prev1Length, rs.getBinaryStream(1), FxBinaryUtils.createBinaryFile(divisionId, created.getId(), created.getVersion(), created.getQuality(), PreviewSizes.PREVIEW1.getBlobIndex()))) throw new FxDbException(LOG, "ex.content.binary.fsCopyFailed", created.getId() + "[" + PreviewSizes.PREVIEW1.getBlobIndex() + "]"); } catch (IOException e) { throw new FxDbException(LOG, "ex.content.binary.fsCopyFailedError", created.getId() + "[" + PreviewSizes.PREVIEW1.getBlobIndex() + "]", e.getMessage()); } if (storePrev2FS) try { if (!FxFileUtils.copyStream2File(prev2Length, rs.getBinaryStream(2), FxBinaryUtils.createBinaryFile(divisionId, created.getId(), created.getVersion(), created.getQuality(), PreviewSizes.PREVIEW2.getBlobIndex()))) throw new FxDbException(LOG, "ex.content.binary.fsCopyFailed", created.getId() + "[" + PreviewSizes.PREVIEW2.getBlobIndex() + "]"); } catch (IOException e) { throw new FxDbException(LOG, "ex.content.binary.fsCopyFailedError", created.getId() + "[" + PreviewSizes.PREVIEW2.getBlobIndex() + "]", e.getMessage()); } if (storePrev3FS) try { if (!FxFileUtils.copyStream2File(prev3Length, rs.getBinaryStream(3), FxBinaryUtils.createBinaryFile(divisionId, created.getId(), created.getVersion(), created.getQuality(), PreviewSizes.PREVIEW3.getBlobIndex()))) throw new FxDbException(LOG, "ex.content.binary.fsCopyFailed", created.getId() + "[" + PreviewSizes.PREVIEW3.getBlobIndex() + "]"); } catch (IOException e) { throw new FxDbException(LOG, "ex.content.binary.fsCopyFailedError", created.getId() + "[" + PreviewSizes.PREVIEW3.getBlobIndex() + "]", e.getMessage()); } if (storePrev4FS) try { if (!FxFileUtils.copyStream2File(prev4Length, rs.getBinaryStream(4), FxBinaryUtils.createBinaryFile(divisionId, created.getId(), created.getVersion(), created.getQuality(), PreviewSizes.SCREENVIEW.getBlobIndex()))) throw new FxDbException(LOG, "ex.content.binary.fsCopyFailed", created.getId() + "[" + PreviewSizes.SCREENVIEW.getBlobIndex() + "]"); } catch (IOException e) { throw new FxDbException(LOG, "ex.content.binary.fsCopyFailedError", created.getId() + "[" + PreviewSizes.SCREENVIEW.getBlobIndex() + "]", e.getMessage()); } } con.commit(); } catch (SQLException e) { throw new FxDbException(e, "ex.db.sqlError", e.getMessage()); } catch (FileNotFoundException e) { throw new FxDbException(e, "ex.content.binary.IOError", binary.getHandle()); } finally { Database.closeObjects(GenericBinarySQLStorage.class, con, ps); FxSharedUtils.close(fis); } return created; }
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;//ww w . ja v a2s . 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:org.wso2.carbon.dataservices.core.odata.RDBMSDataHandler.java
private String getValueFromResultSet(int columnType, String column, ResultSet resultSet) throws SQLException { String paramValue;/*from w w w.j a v a 2s . c om*/ 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.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 www.j a v a 2 s .com*/ */ 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:gobblin.source.extractor.extract.jdbc.JdbcExtractor.java
private static boolean isBlob(int columnType) { return columnType == Types.LONGVARBINARY || columnType == Types.BINARY; }
From source file:org.seasar.dbflute.logic.replaceschema.loaddata.impl.DfAbsractDataWriter.java
/** * Get the bind type to find a value type. * @param tableName The name of table corresponding to column. (NotNull) * @param columnMeta The meta info of column. (NotNull) * @return The type of column. (NullAllowed: However Basically NotNull) */// w ww . j a va2 s. c o m protected Class<?> getBindType(String tableName, DfColumnMeta columnMeta) { Map<String, Class<?>> cacheMap = _bindTypeCacheMap.get(tableName); if (cacheMap == null) { cacheMap = StringKeyMap.createAsFlexibleOrdered(); _bindTypeCacheMap.put(tableName, cacheMap); } final String columnName = columnMeta.getColumnName(); Class<?> bindType = cacheMap.get(columnName); if (bindType != null) { // cache hit return bindType; } // use mapped JDBC defined value if found (basically found) // because it has already been resolved about JDBC specification per DBMS final String jdbcType = _columnHandler.getColumnJdbcType(columnMeta); Integer jdbcDefValue = TypeMap.getJdbcDefValueByJdbcType(jdbcType); if (jdbcDefValue == null) { // basically no way jdbcDefValue = columnMeta.getJdbcDefValue(); // as plain } // ReplaceSchema uses an own original mapping way // (not uses Generate mapping) // it's simple mapping (for string processor) if (jdbcDefValue == Types.CHAR || jdbcDefValue == Types.VARCHAR || jdbcDefValue == Types.LONGVARCHAR || jdbcDefValue == Types.CLOB) { bindType = String.class; } else if (jdbcDefValue == Types.TINYINT || jdbcDefValue == Types.SMALLINT || jdbcDefValue == Types.INTEGER) { bindType = Integer.class; } else if (jdbcDefValue == Types.BIGINT) { bindType = Long.class; } else if (jdbcDefValue == Types.DECIMAL || jdbcDefValue == Types.NUMERIC) { bindType = BigDecimal.class; } else if (jdbcDefValue == Types.TIMESTAMP) { bindType = Timestamp.class; } else if (jdbcDefValue == Types.TIME) { bindType = Time.class; } else if (jdbcDefValue == Types.DATE) { // it depends on value type settings // that which is bound java.sql.Date or java.sql.Timestamp bindType = java.util.Date.class; } else if (jdbcDefValue == Types.BIT || jdbcDefValue == Types.BOOLEAN) { bindType = Boolean.class; } else if (jdbcDefValue == Types.BINARY || jdbcDefValue == Types.VARBINARY || jdbcDefValue == Types.LONGVARBINARY || jdbcDefValue == Types.BLOB) { bindType = byte[].class; } else if (jdbcDefValue == Types.OTHER && TypeMap.UUID.equalsIgnoreCase(jdbcType)) { // [UUID Headache]: The reason why UUID type has not been supported yet on JDBC. bindType = UUID.class; } else { bindType = Object.class; } cacheMap.put(columnName, bindType); return bindType; }
From source file:org.apache.sqoop.mapreduce.hcat.SqoopHCatUtilities.java
/** * Resolve a database-specific type to HCat data type. Largely follows Sqoop's * hive translation./*from w ww. j a v a 2 s. com*/ * @param sqlType * sql type * @return hcat type */ public static String toHCatType(int sqlType) { switch (sqlType) { // Ideally TINYINT and SMALLINT should be mapped to their // HCat equivalents tinyint and smallint respectively // But the Sqoop Java type conversion has them mapped to Integer // Even though the referenced Java doc clearly recommends otherwise. // Changing this now can cause many of the sequence file usages to // break as value class implementations will change. So, we // just use the same behavior here. case Types.SMALLINT: case Types.TINYINT: case Types.INTEGER: return "int"; case Types.VARCHAR: return "varchar"; case Types.CHAR: return "char"; case Types.LONGVARCHAR: case Types.NVARCHAR: case Types.NCHAR: case Types.LONGNVARCHAR: case Types.DATE: case Types.TIME: case Types.TIMESTAMP: case Types.CLOB: return "string"; case Types.FLOAT: case Types.REAL: return "float"; case Types.NUMERIC: case Types.DECIMAL: return "decimal"; case Types.DOUBLE: return "double"; case Types.BIT: case Types.BOOLEAN: return "boolean"; case Types.BIGINT: return "bigint"; case Types.BINARY: case Types.VARBINARY: case Types.BLOB: case Types.LONGVARBINARY: return "binary"; default: throw new IllegalArgumentException("Cannot convert SQL type to HCatalog type " + sqlType); } }
From source file:org.jumpmind.symmetric.db.AbstractTriggerTemplate.java
protected String buildKeyVariablesDeclare(Column[] columns, String prefix) { String text = ""; for (int i = 0; i < columns.length; i++) { text += "declare @" + prefix + "pk" + i + " "; switch (columns[i].getMappedTypeCode()) { case Types.TINYINT: case Types.SMALLINT: case Types.INTEGER: case Types.BIGINT: text += "bigint\n"; break; case Types.NUMERIC: case Types.DECIMAL: text += "decimal\n"; break; case Types.FLOAT: case Types.REAL: case Types.DOUBLE: text += "float\n"; break; case Types.CHAR: case Types.VARCHAR: case ColumnTypes.NVARCHAR: case ColumnTypes.LONGNVARCHAR: case Types.LONGVARCHAR: text += "varchar(1000)\n"; break; case Types.DATE: text += "date\n"; break; case Types.TIME: text += "time\n"; break; case Types.TIMESTAMP: text += "datetime\n"; break; case Types.BOOLEAN: case Types.BIT: text += "bit\n"; break; case Types.CLOB: text += "varchar(max)\n"; break; case Types.BLOB: case Types.BINARY: case Types.VARBINARY: case Types.LONGVARBINARY: case -10: // SQL-Server ntext binary type text += "varbinary(max)\n"; break; case Types.OTHER: text += "varbinary(max)\n"; break; default:/*from w w w. j a v a2 s. c o m*/ if (columns[i].getJdbcTypeName() != null && columns[i].getJdbcTypeName().equalsIgnoreCase("interval")) { text += "interval"; break; } throw new NotImplementedException(columns[i] + " is of type " + columns[i].getMappedType()); } } return text; }
From source file:com.mirth.connect.donkey.test.util.TestUtils.java
private static String typeToString(int sqlType) { switch (sqlType) { case Types.ARRAY: return "ARRAY"; case Types.BIGINT: return "BIGINT"; case Types.BINARY: return "BINARY"; case Types.BIT: return "BIT"; case Types.BLOB: return "BLOB"; case Types.BOOLEAN: return "BOOLEAN"; case Types.CHAR: return "CHAR"; case Types.CLOB: return "CLOB"; case Types.DATALINK: return "DATALINK"; case Types.DATE: return "DATE"; case Types.DECIMAL: return "DECIMAL"; case Types.DISTINCT: return "DISTINCT"; case Types.DOUBLE: return "DOUBLE"; case Types.FLOAT: return "FLOAT"; case Types.INTEGER: return "INTEGER"; case Types.JAVA_OBJECT: return "JAVA_OBJECT"; case Types.LONGNVARCHAR: return "LONGNVARCHAR"; case Types.LONGVARBINARY: return "LONGVARBINARY"; case Types.LONGVARCHAR: return "LONGVARCHAR"; case Types.NCHAR: return "NCHAR"; case Types.NCLOB: return "NCLOB"; case Types.NULL: return "NULL"; case Types.NUMERIC: return "NUMERIC"; case Types.NVARCHAR: return "NVARCHAR"; case Types.OTHER: return "OTHER"; case Types.REAL: return "REAL"; case Types.REF: return "REF"; case Types.ROWID: return "ROWID"; case Types.SMALLINT: return "SMALLINT"; case Types.SQLXML: return "SQLXML"; case Types.STRUCT: return "STRUCT"; case Types.TIME: return "TIME"; case Types.TIMESTAMP: return "TIMESTAMP"; case Types.TINYINT: return "TINYINT"; case Types.VARBINARY: return "VARBINARY"; case Types.VARCHAR: return "VARCHAR"; default:// w ww. java 2s .c om return "UNKNOWN"; } }