List of usage examples for java.sql PreparedStatement setBlob
void setBlob(int parameterIndex, InputStream inputStream) throws SQLException;
InputStream object. From source file:fr.gael.dhus.database.liquibase.CopyProductImages.java
@Override public void execute(Database database) throws CustomChangeException { PreparedStatement products = null; ResultSet products_res = null; JdbcConnection db_connection = (JdbcConnection) database.getConnection(); try {// ww w . j a va2 s . c om products = db_connection.prepareStatement("SELECT ID,QUICKLOOK,THUMBNAIL FROM PRODUCTS"); products_res = products.executeQuery(); while (products_res.next()) { PreparedStatement copy_blob_stmt = null; ResultSet generated_key_res = null; try { Blob ql = (Blob) products_res.getObject("QUICKLOOK"); Blob th = (Blob) products_res.getObject("THUMBNAIL"); Long pid = products_res.getLong("ID"); // No images: add false flags if ((ql == null) && (th == null)) { PreparedStatement product_flags_stmt = null; // Add related flags try { product_flags_stmt = db_connection.prepareStatement( "UPDATE PRODUCTS SET THUMBNAIL_FLAG=?,QUICKLOOK_FLAG=? " + "WHERE ID=?"); product_flags_stmt.setBoolean(1, false); product_flags_stmt.setBoolean(2, false); product_flags_stmt.setLong(3, pid); product_flags_stmt.execute(); } finally { if (product_flags_stmt != null) try { product_flags_stmt.close(); } catch (Exception e) { logger.warn("Cannot close Statement !"); } } continue; } copy_blob_stmt = db_connection.prepareStatement( "INSERT INTO PRODUCT_IMAGES (QUICKLOOK,THUMBNAIL) " + "VALUES (?,?)", Statement.RETURN_GENERATED_KEYS); copy_blob_stmt.setBlob(1, ql); copy_blob_stmt.setBlob(2, th); copy_blob_stmt.execute(); generated_key_res = copy_blob_stmt.getGeneratedKeys(); if (generated_key_res.next()) { PreparedStatement set_product_image_id_stmt = null; Long iid = generated_key_res.getLong(1); // Add ProductImages "IMAGES" entry in product try { set_product_image_id_stmt = db_connection .prepareStatement("UPDATE PRODUCTS SET IMAGES_ID=?, THUMBNAIL_FLAG=?, " + "QUICKLOOK_FLAG=? WHERE ID=?"); set_product_image_id_stmt.setLong(1, iid); set_product_image_id_stmt.setBoolean(2, th != null); set_product_image_id_stmt.setBoolean(3, ql != null); set_product_image_id_stmt.setLong(4, pid); set_product_image_id_stmt.execute(); } finally { if (set_product_image_id_stmt != null) try { set_product_image_id_stmt.close(); } catch (Exception e) { logger.warn("Cannot close Statement !"); } } } else { logger.error("Cannot retrieve Image primary key for " + "product ID #" + products_res.getLong("ID")); } } finally { if (generated_key_res != null) try { generated_key_res.close(); } catch (Exception e) { logger.warn("Cannot close ResultSet !"); } if (copy_blob_stmt != null) try { copy_blob_stmt.close(); } catch (Exception e) { logger.warn("Cannot close Statement !"); } } } } catch (Exception e) { throw new CustomChangeException("Cannot move Blobs from product", e); } finally { if (products_res != null) { try { products_res.close(); } catch (Exception e) { logger.warn("Cannot close ResultSet !"); } } if (products != null) { try { products.close(); } catch (Exception e) { logger.warn("Cannot close Statement !"); } } //if (db_connection!=null) try { db_connection.close (); } // catch (Exception e) {} } }
From source file:org.quartz.impl.jdbcjobstore.oracle.OracleDelegate.java
public int insertJobDetail(Connection conn, JobDetail job) throws IOException, SQLException { ByteArrayOutputStream baos = serializeJobData(job.getJobDataMap()); byte[] data = baos.toByteArray(); PreparedStatement ps = null; ResultSet rs = null;// w w w. j a v a 2s . c om try { ps = conn.prepareStatement(rtp(INSERT_JOB_DETAIL)); ps.setString(1, job.getName()); ps.setString(2, job.getGroup()); ps.setString(3, job.getDescription()); ps.setString(4, job.getJobClass().getName()); setBoolean(ps, 5, job.isDurable()); setBoolean(ps, 6, job.isVolatile()); setBoolean(ps, 7, job.isStateful()); setBoolean(ps, 8, job.requestsRecovery()); ps.setBinaryStream(9, null, 0); ps.executeUpdate(); ps.close(); ps = conn.prepareStatement(rtp(UPDATE_ORACLE_JOB_DETAIL_EMPTY_BLOB)); ps.setString(1, job.getName()); ps.setString(2, job.getGroup()); ps.executeUpdate(); ps.close(); ps = conn.prepareStatement(rtp(SELECT_ORACLE_JOB_DETAIL_BLOB)); ps.setString(1, job.getName()); ps.setString(2, job.getGroup()); rs = ps.executeQuery(); int res = 0; Blob dbBlob = null; if (rs.next()) { dbBlob = writeDataToBlob(rs, 1, data); } else { return res; } rs.close(); ps.close(); ps = conn.prepareStatement(rtp(UPDATE_ORACLE_JOB_DETAIL_BLOB)); ps.setBlob(1, dbBlob); ps.setString(2, job.getName()); ps.setString(3, job.getGroup()); res = ps.executeUpdate(); if (res > 0) { String[] jobListeners = job.getJobListenerNames(); for (int i = 0; jobListeners != null && i < jobListeners.length; i++) { insertJobListener(conn, job, jobListeners[i]); } } return res; } finally { closeResultSet(rs); closeStatement(ps); } }
From source file:org.batoo.jpa.jdbc.dbutils.QueryRunner.java
/** * Fill the <code>PreparedStatement</code> replacement parameters with the given objects. * /*from www. j a va 2 s . c o m*/ * @param statement * PreparedStatement to fill * @param params * Query replacement parameters; <code>null</code> is a valid value to pass in. * @throws SQLException * if a database access error occurs */ private void fillStatement(PreparedStatement statement, Object... params) throws SQLException { // use local variable for performance boolean pmdKnownBroken = this.pmdKnownBroken; ParameterMetaData pmd = this.pmd; final boolean hasLob = this.hasLob; if (pmdKnownBroken) { ((PreparedStatementProxy) statement).setParamCount(params.length); } else { ((PreparedStatementProxy) statement).setParamCount(-1); } // if the jdbc adaptor wants to modify the parameters we let it do it its own way final JdbcAdaptor jdbcAdaptor = this.jdbcAdaptor; if ((jdbcAdaptor != null) && jdbcAdaptor.modifiesParameters()) { pmd = this.pmd = statement.getParameterMetaData(); jdbcAdaptor.modifyParameters(pmd, params); } for (int i = 0; i < params.length; i++) { final Object param = params[i]; if ((param != null) && (param != Void.TYPE)) { if (hasLob && (param instanceof Clob)) { if (this.jdbcAdaptor instanceof OracleAdaptor) { statement.setCharacterStream(i + 1, ((Clob) param).getCharacterStream()); } else { statement.setClob(i + 1, (Clob) param); } } else if (hasLob && (param instanceof Blob)) { if (this.jdbcAdaptor instanceof OracleAdaptor) { statement.setBinaryStream(i + 1, ((Blob) param).getBinaryStream()); } else { statement.setBlob(i + 1, (Blob) param); } } else { statement.setObject(i + 1, param); } } else { if (!pmdKnownBroken && (pmd == null)) { pmd = this.pmd = statement.getParameterMetaData(); } // VARCHAR works with many drivers regardless of the actual column type. // Oddly, NULL and OTHER don't work with Oracle's drivers. int sqlType = Types.VARCHAR; if (!pmdKnownBroken) { try { sqlType = pmd.getParameterType(i + 1); } catch (final SQLException e) { pmdKnownBroken = this.pmdKnownBroken = true; } } if (param != Void.TYPE) { statement.setNull(i + 1, sqlType); } } } }
From source file:org.mitre.demo.client.MitreConnectDao.java
/** * This method will create a new record at CLIENT_INFO. * * @param clientInformation- jsonParsed response from OIDC * @throws APIManagementException/*from w ww. ja v a 2s . c om*/ */ public void createClientInformation(OAuthApplicationInfo clientInformation) throws APIManagementException { Connection connection = null; PreparedStatement ps = null; try { connection = getExternalKeyManagerConnection(); connection.setAutoCommit(false); String sqlQuery = "INSERT INTO CLIENT_INFO(" + "CLIENT_ID," + "CONSUMER_KEY," + "CONSUMER_SECRET," + "CONFIGURATION_URL," + "CONFIG_ACCESS_TOKEN," + "CLIENT_NAME," + "REDIRECT_URI," + "CLIENT_TYPE," + "PAYLOAD) " + " VALUES(?,?,?,?,?,?,?,?,?) "; ps = connection.prepareStatement(sqlQuery); String guid = clientInformation.getClientId(); if (guid == null) { guid = UUIDGenerator.generateUUID(); } ps.setString(1, guid); ps.setString(2, clientInformation.getClientId()); ps.setString(3, (String) clientInformation.getParameter(ApplicationConstants.OAUTH_CLIENT_SECRET)); ps.setString(4, (String) clientInformation .getParameter(ApplicationConstants.OAUTH_CLIENT_REGISTRATION_CLIENT_URI)); ps.setString(5, (String) clientInformation .getParameter(ApplicationConstants.OAUTH_CLIENT_REGISTRATION_ACCESSTOKEN)); ps.setString(6, (String) clientInformation.getParameter(ApplicationConstants.OAUTH_CLIENT_NAME)); String redirectUri = null; List<String> redirect = null; if (clientInformation.getParameter(ApplicationConstants.OAUTH_REDIRECT_URIS) instanceof List) { redirect = (List<String>) clientInformation.getParameter(ApplicationConstants.OAUTH_REDIRECT_URIS); } if (redirect != null) { StringBuilder builder = new StringBuilder(); for (String uri : redirect) { builder.append(uri).append(","); } builder.deleteCharAt(builder.length() - 1); redirectUri = builder.toString(); } ps.setString(7, redirectUri); ps.setString(8, "web"); ps.setBlob(9, new ByteArrayInputStream("".getBytes("UTF-8"))); ps.execute(); connection.commit(); } catch (SQLException e) { handleException("SQL error on createMappedClient method", e); } catch (UnsupportedEncodingException e) { handleException("Un-Supported Encoding type given to set payLoad..", e); } finally { APIMgtDBUtil.closeAllConnections(ps, connection, null); } }
From source file:org.wso2.appcloud.core.dao.ApplicationDAO.java
/** * Method for updating application icon. * * @param dbConnection database connection * @param inputStream input stream object * @param applicationId id of application object * @throws AppCloudException//w ww . j a v a2 s .c om */ public void updateApplicationIcon(Connection dbConnection, InputStream inputStream, int applicationId, int tenantId) throws AppCloudException { PreparedStatement preparedStatement = null; try { preparedStatement = dbConnection.prepareStatement(SQLQueryConstants.UPDATE_APPLICATION_ICON); preparedStatement.setBlob(1, inputStream); preparedStatement.setInt(2, applicationId); preparedStatement.execute(); } catch (SQLException e) { String msg = "Error occurred while updating application icon for application with id : " + applicationId + " in tenant " + tenantId; throw new AppCloudException(msg, e); } finally { DBUtil.closePreparedStatement(preparedStatement); } }
From source file:org.athenasource.framework.unidbi.Datatypes.java
/** * Setting the parameter for the given prepared statement. This method will * cast the value to the object expected type (execept BOOLEAN) as * {@linkplain #getClass(int)}./*from w ww . ja v a 2s . c o m*/ * <p>INSERT, UPDATE should always call this method to set parameters before 'WHERE' keyword.</p> * <p><b>Warining</b>: For parameters after 'WHERE' keyword, always remember 'IS NULL' is not equal to '= NULL'.</p> * * @param index * the parameter index * @param value * the value for the parameter, can be <code>null</code>. * @param unidbType * the datatype of the parameter * @throws SQLException * in case of SQL problems or type conversion fails. */ public static void setParameter(PreparedStatement stmt, int index, Object value, int unidbType) throws SQLException { // Derby needs special handling. boolean isDerby = (stmt instanceof DelegatingPreparedStatement) ? ((DelegatingPreparedStatement) stmt).getDelegate().getClass().getName().contains("derby") : stmt.getClass().getName().contains("derby"); if (value == null) { if (isDerby) { if (unidbType == NCHAR) { stmt.setNull(index, Datatypes.getSQLType(CHAR)); } else if (unidbType == NVARCHAR) { stmt.setNull(index, Datatypes.getSQLType(VARCHAR)); } else { stmt.setNull(index, Datatypes.getSQLType(unidbType)); } } else { stmt.setNull(index, Datatypes.getSQLType(unidbType)); } } else { try { switch (unidbType) { case BOOLEAN: stmt.setByte(index, (byte) (((Number) value).intValue() == 1 ? 1 : 0)); break; case TINYINT: stmt.setByte(index, ((Number) value).byteValue()); break; case SMALLINT: stmt.setShort(index, ((Number) value).shortValue()); break; case INTEGER: stmt.setInt(index, ((Number) value).intValue()); break; case BIGINT: stmt.setLong(index, ((Number) value).longValue()); break; case DECIMAL: stmt.setBigDecimal(index, ((BigDecimal) value)); break; case REAL: stmt.setFloat(index, ((Float) value).floatValue()); break; case DOUBLE: stmt.setDouble(index, ((Double) value).doubleValue()); break; case CHAR: stmt.setString(index, (String) value); break; case NCHAR: if (isDerby) { stmt.setString(index, (String) value); } else { stmt.setNString(index, (String) value); } break; case VARCHAR: stmt.setString(index, (String) value); break; case NVARCHAR: if (isDerby) { stmt.setString(index, (String) value); } else { stmt.setNString(index, (String) value); } break; case CLOB: // Clob/NClob can be represented as String without any problem. - Oct 16, 2008. stmt.setString(index, (String) value); // stmt.setClob(index, ((Clob) value)); break; case NCLOB: if (isDerby) { stmt.setString(index, (String) value); } else { stmt.setNString(index, (String) value); // stmt.setNClob(index, ((NClob) value)); } break; case BLOB: stmt.setBlob(index, ((Blob) value)); break; case TIMESTAMP: stmt.setTimestamp(index, ((Timestamp) value)); break; default: throw new IllegalArgumentException("[!NO SUCH UNIDB DATA TYPE: " + unidbType + "]"); } } catch (ClassCastException cce) { throw new SQLException( "Failed to convert " + value + " (" + value.getClass() + ") to " + getName(unidbType)); } } }
From source file:com.nextep.datadesigner.vcs.services.VCSFiles.java
/** * Write the specified file to the given repository file with Oracle-specific Blob support. * //from w w w .j a v a2 s . co m * @param conn Oracle connection * @param file repository file which must have been created * @param localFile local file to dump into the repository file * @throws SQLException when any database connection problems occurs */ private void writeOracleBlob(Connection conn, IRepositoryFile file, File localFile) throws SQLException { PreparedStatement stmt = null; long size = 0; try { /* * Columns names in the SET clause cannot be qualified with an alias name because it * would fail in Postgres. */ stmt = conn.prepareStatement("UPDATE rep_files rf " //$NON-NLS-1$ + " SET file_content = ? " //$NON-NLS-1$ + " , filesize = ? " //$NON-NLS-1$ + "WHERE rf.file_id = ? "); //$NON-NLS-1$ OutputStream os = null; FileInputStream is = null; BLOB tempBlob = null; try { // Get the oracle connection class for checking Class<?> oracleConnectionClass = Class.forName("oracle.jdbc.OracleConnection"); //$NON-NLS-1$ // Make sure connection object is right type if (!oracleConnectionClass.isAssignableFrom(conn.getClass())) { throw new HibernateException(VCSMessages.getString("files.invalidOracleConnection") //$NON-NLS-1$ + VCSMessages.getString("files.invalidOracleConnection.2") //$NON-NLS-1$ + conn.getClass().getName()); } // Create our temp BLOB tempBlob = BLOB.createTemporary(conn, true, BLOB.DURATION_SESSION); tempBlob.open(BLOB.MODE_READWRITE); os = tempBlob.getBinaryOutputStream(); is = new FileInputStream(localFile); // Large 10K buffer for efficient read byte[] buffer = new byte[10240]; int bytesRead = 0; while ((bytesRead = is.read(buffer)) >= 0) { os.write(buffer, 0, bytesRead); size += bytesRead; } } catch (ClassNotFoundException cnfe) { // could not find the class with reflection throw new ErrorException(VCSMessages.getString("files.classUnresolved") //$NON-NLS-1$ + cnfe.getMessage()); } catch (FileNotFoundException fnfe) { throw new ErrorException(VCSMessages.getString("files.fileUnresolved")); //$NON-NLS-1$ } catch (IOException ioe) { throw new ErrorException(VCSMessages.getString("files.readProblem"), ioe); //$NON-NLS-1$ } finally { safeClose(os); safeClose(is); if (tempBlob != null) { tempBlob.close(); } } stmt.setBlob(1, tempBlob); stmt.setLong(2, size); stmt.setLong(3, file.getUID().rawId()); stmt.execute(); } finally { if (stmt != null) { stmt.close(); } file.setFileSizeKB(size / 1024); } }
From source file:cz.lbenda.dataman.db.RowDesc.java
@SuppressWarnings("ConstantConditions") private <T> void putToPS(ColumnDesc columnDesc, T value, PreparedStatement ps, int position) throws SQLException { if (value == null) { ps.setObject(position, null);//www.j a v a 2s . c o m return; } BinaryData bd = value instanceof BinaryData ? (BinaryData) value : null; switch (columnDesc.getDataType()) { case STRING: ps.setString(position, (String) value); break; case BOOLEAN: ps.setBoolean(position, (Boolean) value); break; case TIMESTAMP: ps.setTimestamp(position, (Timestamp) value); break; case DATE: ps.setDate(position, (Date) value); break; case TIME: ps.setTime(position, (Time) value); break; case BYTE: ps.setByte(position, (Byte) value); break; case SHORT: ps.setShort(position, (Short) value); break; case INTEGER: ps.setInt(position, (Integer) value); break; case LONG: ps.setLong(position, (Long) value); break; case FLOAT: ps.setFloat(position, (Float) value); break; case DOUBLE: ps.setDouble(position, (Double) value); break; case DECIMAL: ps.setBigDecimal(position, (BigDecimal) value); break; case UUID: ps.setBytes(position, AbstractHelper.uuidToByteArray((UUID) value)); break; case ARRAY: throw new UnsupportedOperationException("The saving changes in ARRAY isn't supported."); // ps.setArray(position, (Array) value); break; // FIXME the value isn't in type java.sql.Array case BYTE_ARRAY: if (bd == null || bd.isNull()) { ps.setBytes(position, null); } else { try { ps.setBytes(position, IOUtils.toByteArray(bd.getInputStream())); } catch (IOException e) { throw new SQLException(e); } } break; case CLOB: if (bd == null || bd.isNull()) { ps.setNull(position, Types.CLOB); } else { ps.setClob(position, bd.getReader()); } break; case BLOB: if (bd == null || bd.isNull()) { ps.setNull(position, Types.BLOB); } else { ps.setBlob(position, bd.getInputStream()); } break; case OBJECT: ps.setObject(position, value); } }
From source file:org.waarp.common.database.data.AbstractDbData.java
/** * Set Value into PreparedStatement/*from ww w .ja v a2 s . co m*/ * * @param ps * @param value * @param rank * >= 1 * @throws WaarpDatabaseSqlException */ static public void setTrueValue(PreparedStatement ps, DbValue value, int rank) throws WaarpDatabaseSqlException { try { switch (value.type) { case Types.VARCHAR: if (value.value == null) { ps.setNull(rank, Types.VARCHAR); break; } ps.setString(rank, (String) value.value); break; case Types.LONGVARCHAR: if (value.value == null) { ps.setNull(rank, Types.LONGVARCHAR); break; } ps.setString(rank, (String) value.value); break; case Types.BIT: if (value.value == null) { ps.setNull(rank, Types.BIT); break; } ps.setBoolean(rank, (Boolean) value.value); break; case Types.TINYINT: if (value.value == null) { ps.setNull(rank, Types.TINYINT); break; } ps.setByte(rank, (Byte) value.value); break; case Types.SMALLINT: if (value.value == null) { ps.setNull(rank, Types.SMALLINT); break; } ps.setShort(rank, (Short) value.value); break; case Types.INTEGER: if (value.value == null) { ps.setNull(rank, Types.INTEGER); break; } ps.setInt(rank, (Integer) value.value); break; case Types.BIGINT: if (value.value == null) { ps.setNull(rank, Types.BIGINT); break; } ps.setLong(rank, (Long) value.value); break; case Types.REAL: if (value.value == null) { ps.setNull(rank, Types.REAL); break; } ps.setFloat(rank, (Float) value.value); break; case Types.DOUBLE: if (value.value == null) { ps.setNull(rank, Types.DOUBLE); break; } ps.setDouble(rank, (Double) value.value); break; case Types.VARBINARY: if (value.value == null) { ps.setNull(rank, Types.VARBINARY); break; } ps.setBytes(rank, (byte[]) value.value); break; case Types.DATE: if (value.value == null) { ps.setNull(rank, Types.DATE); break; } ps.setDate(rank, (Date) value.value); break; case Types.TIMESTAMP: if (value.value == null) { ps.setNull(rank, Types.TIMESTAMP); break; } ps.setTimestamp(rank, (Timestamp) value.value); break; case Types.CLOB: if (value.value == null) { ps.setNull(rank, Types.CLOB); break; } ps.setClob(rank, (Reader) value.value); break; case Types.BLOB: if (value.value == null) { ps.setNull(rank, Types.BLOB); break; } ps.setBlob(rank, (InputStream) value.value); break; default: throw new WaarpDatabaseSqlException("Type not supported: " + value.type + " at " + rank); } } catch (ClassCastException e) { throw new WaarpDatabaseSqlException("Setting values casting error: " + value.type + " at " + rank, e); } catch (SQLException e) { DbSession.error(e); throw new WaarpDatabaseSqlException("Setting values in error: " + value.type + " at " + rank, e); } }
From source file:org.LexGrid.util.sql.lgTables.SQLTableUtilities.java
/** * Runs SQL Statement "INSERT" on the given table and and table prefix for * the supplied attributeValues/*from www. j a v a2s .c om*/ * * @param table * @param attributeValues * @return * @throws SQLException */ public boolean insertRow(String table, Map attributeValues) throws SQLException { PreparedStatement prepStmt = null; Object attribute = null; boolean success = false; try { prepStmt = sqlConnection_.prepareStatement(getSQLTableConstants().getInsertStatementSQL(table)); for (int i = 0; i < attributeValues.size(); i++) { attribute = attributeValues.get("" + (i + 1)); // If null, we are unable to determine the SQL param type, // so String is assumed by default. if (attribute == null) { prepStmt.setString(i + 1, null); } else if (attribute instanceof String) { prepStmt.setString(i + 1, (String) attribute); } else if (attribute instanceof Blob) { prepStmt.setBlob(i + 1, (Blob) attribute); } else if (attribute instanceof Boolean) { prepStmt.setBoolean(i + 1, ((Boolean) attribute).booleanValue()); } else if (attribute instanceof Byte) { prepStmt.setByte(i + 1, ((Byte) attribute).byteValue()); } else if (attribute instanceof byte[]) { prepStmt.setBytes(i + 1, (byte[]) attribute); } else if (attribute instanceof Date) { prepStmt.setDate(i + 1, (Date) attribute); } else if (attribute instanceof Double) { prepStmt.setDouble(i + 1, ((Double) attribute).doubleValue()); } else if (attribute instanceof Float) { prepStmt.setFloat(i + 1, ((Float) attribute).floatValue()); } else if (attribute instanceof Integer) { prepStmt.setInt(i + 1, ((Integer) attribute).intValue()); } else if (attribute instanceof Long) { prepStmt.setLong(i + 1, ((Long) attribute).longValue()); } else if (attribute instanceof Short) { prepStmt.setShort(i + 1, ((Short) attribute).shortValue()); } else if (attribute instanceof Timestamp) { prepStmt.setTimestamp(i + 1, (Timestamp) attribute); } } success = prepStmt.execute(); } finally { prepStmt.close(); } return success; }