Example usage for java.sql PreparedStatement setBlob

List of usage examples for java.sql PreparedStatement setBlob

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. PreparedStatement
  6. setBlob

Introduction

In this page you can find the example usage for java.sql PreparedStatement setBlob.

Prototype

void setBlob(int parameterIndex, InputStream inputStream) throws SQLException;

Source Link

Document

Sets the designated parameter to a InputStream object.

Usage

From source file:image.JdbcInsertFileOne.java

public void enregistrer(String path, String type, Voiture voiture, String numPac) {

    String url = "jdbc:mysql://localhost:3306/loc_voiture_db";
    String user = "root";
    String password = "";

    String filePath = path;/*from   w w w .j  a va2  s. c o m*/
    List<Parc> parcs = metier.listerParc();

    for (Iterator<Parc> iterator = parcs.iterator(); iterator.hasNext();) {
        Parc next = iterator.next();
        if (next.getNumParc().equals(numPac)) {
            idParc = next.getIdPac();
            break;
        }
    }

    try {
        Connection conn = DriverManager.getConnection(url, user, password);

        String sql = "INSERT INTO `voiture` (`immatriculation_voiture`,"
                + " `marque_voiture`, `modele_voiture`, `categorie_voiture`, `couleur_voiture`, "
                + "`carburant_voiture`, `boite_vitesse_voiture`, `climatisation_voiture`, "
                + "`nbr_portes_voiture`, `nbr_places_voiture`, `type_voiture`, `etat_voiture`, "
                + "`id_pac_voiture`, `annee_mise_service`, `type`,`image`, `prix_par_jour_3jour`,"
                + " `prix_par_jour_1semaine`)" + " values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        PreparedStatement statement = conn.prepareStatement(sql);
        statement.setString(1, voiture.getImmatriculationVoiture());
        statement.setString(2, voiture.getMarqueVoiture());
        statement.setString(3, voiture.getModeleVoiture());
        statement.setString(4, voiture.getCategorieVoiture());
        statement.setString(5, voiture.getCouleurVoiture());
        statement.setString(6, voiture.getCarburantVoiture());
        statement.setString(7, voiture.getBoiteVitesseVoiture());

        statement.setString(8, voiture.getClimatisationVoiture());
        statement.setInt(9, voiture.getNbrPortesVoiture());
        statement.setInt(10, voiture.getNbrPortesVoiture());
        statement.setString(11, voiture.getTypeVoiture());
        statement.setString(12, voiture.getEtatVoiture());
        statement.setInt(13, idParc);
        statement.setString(14, voiture.getAnneeMiseService());
        statement.setString(15, type);

        InputStream inputStream = new FileInputStream(new File(filePath));

        statement.setBlob(16, inputStream);
        statement.setString(17, voiture.getPrixParJour3jour());
        statement.setString(18, voiture.getPrixParJour1semaine());

        int row = statement.executeUpdate();
        if (row > 0) {
            System.out.println("A contact was inserted with photo image.");
        }
        conn.close();
    } catch (SQLException ex) {
        ex.printStackTrace();
    } catch (IOException ex) {
        ex.printStackTrace();
    }
}

From source file:org.quartz.impl.jdbcjobstore.oracle.OracleDelegate.java

public int insertCalendar(Connection conn, String calendarName, Calendar calendar)
        throws IOException, SQLException {
    ByteArrayOutputStream baos = serializeObject(calendar);

    PreparedStatement ps = null;/* ww  w  .j a va2s.  c  o m*/
    PreparedStatement ps2 = null;
    ResultSet rs = null;

    try {
        ps = conn.prepareStatement(rtp(INSERT_ORACLE_CALENDAR));
        ps.setString(1, calendarName);

        ps.executeUpdate();
        ps.close();

        ps = conn.prepareStatement(rtp(SELECT_ORACLE_CALENDAR_BLOB));
        ps.setString(1, calendarName);

        rs = ps.executeQuery();

        if (rs.next()) {
            Blob dbBlob = writeDataToBlob(rs, 1, baos.toByteArray());
            ps2 = conn.prepareStatement(rtp(UPDATE_ORACLE_CALENDAR_BLOB));

            ps2.setBlob(1, dbBlob);
            ps2.setString(2, calendarName);

            return ps2.executeUpdate();
        }

        return 0;

    } finally {
        closeResultSet(rs);
        closeStatement(ps);
        closeStatement(ps2);
    }
}

From source file:org.apache.oozie.tools.OozieDBCLI.java

private void convertClobToBlobInOracle(Connection conn) throws Exception {
    if (conn == null) {
        return;/*from  ww w.  jav a  2 s  .co m*/
    }
    System.out.println("Converting clob columns to blob for all tables");
    Statement statement = conn.createStatement();
    CallableStatement tempBlobCall = conn.prepareCall("{call dbms_lob.CREATETEMPORARY(?, TRUE)}");
    tempBlobCall.registerOutParameter(1, java.sql.Types.BLOB);
    CallableStatement dbmsLobCallStmt = conn
            .prepareCall("{call dbms_lob.CONVERTTOBLOB(?, ?, ?, ?, ?, 0, ?, ?)}");
    dbmsLobCallStmt.registerOutParameter(1, java.sql.Types.BLOB);
    // Lob max size
    dbmsLobCallStmt.setInt(3, Integer.MAX_VALUE);
    dbmsLobCallStmt.registerOutParameter(4, java.sql.Types.INTEGER);
    // dest_offset
    dbmsLobCallStmt.setInt(4, 1);
    // src_offset
    dbmsLobCallStmt.registerOutParameter(5, java.sql.Types.INTEGER);
    dbmsLobCallStmt.setInt(5, 1);
    // blob_csid
    dbmsLobCallStmt.registerOutParameter(6, java.sql.Types.INTEGER);
    // lang_context
    dbmsLobCallStmt.setInt(6, 0);
    // warning
    dbmsLobCallStmt.registerOutParameter(7, java.sql.Types.INTEGER);
    dbmsLobCallStmt.setInt(7, 1);
    for (Map.Entry<String, List<String>> tableClobColumnMap : getTableClobColumnMap().entrySet()) {
        String tableName = tableClobColumnMap.getKey();
        List<String> columnNames = tableClobColumnMap.getValue();
        for (String column : columnNames) {
            statement.executeUpdate(getAddColumnQuery(tableName, TEMP_COLUMN_PREFIX + column, "blob"));
        }
        ResultSet rs = statement.executeQuery(getSelectQuery(tableName, columnNames));
        while (rs.next()) {
            for (int i = 0; i < columnNames.size(); i++) {
                Clob srcClob = rs.getClob(columnNames.get(i));
                if (srcClob == null || srcClob.length() < 1) {
                    continue;
                }
                tempBlobCall.execute();
                Blob destLob = tempBlobCall.getBlob(1);
                dbmsLobCallStmt.setBlob(1, destLob);
                dbmsLobCallStmt.setClob(2, srcClob);
                dbmsLobCallStmt.execute();
                Blob blob = dbmsLobCallStmt.getBlob(1);
                PreparedStatement ps = conn.prepareStatement("update " + tableName + " set "
                        + TEMP_COLUMN_PREFIX + columnNames.get(i) + "=? where id = ?");
                ps.setBlob(1, blob);
                ps.setString(2, rs.getString(1));
                ps.executeUpdate();
                ps.close();
            }
        }
        rs.close();
        for (String column : columnNames) {
            statement.executeUpdate(getDropColumnQuery(tableName, column));
            statement.executeUpdate(getRenameColumnQuery(tableName, TEMP_COLUMN_PREFIX + column, column));
        }
    }
    dbmsLobCallStmt.close();
    tempBlobCall.close();
    System.out.println("Done");
}

From source file:org.wso2.intcloud.core.dao.ApplicationDAO.java

public void updateApplicationIcon(Connection dbConnection, InputStream inputStream, int applicationId)
        throws IntCloudException {

    PreparedStatement preparedStatement = null;

    try {/*from  www.  j a  v a 2 s .  c  om*/

        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;
        log.error(msg, e);
        throw new IntCloudException(msg, e);

    } finally {
        DBUtil.closePreparedStatement(preparedStatement);
    }
}

From source file:org.wso2.carbon.identity.certificateauthority.dao.CsrDAO.java

private String addCsr(PKCS10CertificationRequest request, String userName, int tenantID, String userStoreDomain,
        String transactionId) throws CaException {
    String csrSerialNo = new BigInteger(32, new SecureRandom()).toString();
    Connection connection = null;
    Date requestDate = new Date();
    String sql = null;/*  ww w.  j  av a2s .c  o  m*/
    PreparedStatement prepStmt = null;
    RDN[] orgRdNs = request.getSubject().getRDNs(BCStyle.O);
    String organization = "";
    if (orgRdNs.length > 0) {
        organization = orgRdNs[0].getFirst().getValue().toString();
    }
    RDN[] cnRdNs = request.getSubject().getRDNs(BCStyle.CN);
    String commonName = "";
    if (cnRdNs.length > 0) {
        commonName = cnRdNs[0].getFirst().getValue().toString();
    }
    try {
        log.debug("adding csr file to database");
        connection = JDBCPersistenceManager.getInstance().getDBConnection();
        sql = "INSERT INTO CA_CSR_STORE (CSR_CONTENT, STATUS, USER_NAME, REQUESTED_DATE, SERIAL_NO, TENANT_ID,COMMON_NAME,ORGANIZATION,UM_DOMAIN_NAME,TRANSACTION_ID) VALUES (?,?,?,?,?,?,?,?,?,?) ";
        prepStmt = connection.prepareStatement(sql);
        prepStmt.setBlob(1, new ByteArrayInputStream(request.getEncoded()));
        prepStmt.setString(2, CsrStatus.PENDING.toString());
        prepStmt.setString(3, userName);
        prepStmt.setTimestamp(4, new Timestamp(requestDate.getTime()));
        prepStmt.setString(5, csrSerialNo);
        prepStmt.setInt(6, tenantID);
        prepStmt.setString(7, commonName);
        prepStmt.setString(8, organization);
        prepStmt.setString(9, userStoreDomain);
        prepStmt.setString(10, transactionId);
        prepStmt.execute();
        connection.commit();
    } catch (IdentityException e) {
        String errorMsg = "Error when getting an Identity Persistence Store instance.";
        log.error(errorMsg, e);
        throw new CaException(errorMsg, e);
    } catch (SQLException e) {
        log.error("Error when executing the SQL : " + sql, e);
    } catch (IOException e) {
        log.error(e.getMessage(), e);
    } finally {
        IdentityDatabaseUtil.closeAllConnections(connection, null, prepStmt);
    }
    return csrSerialNo;
}

From source file:org.apache.gora.sql.store.SqlStore.java

/** Serializes the field using Avro to a BLOB field */
protected void setField(PreparedStatement statement, Column column, Schema schema, int index, Object object)
        throws IOException, SQLException {

    OutputStream os = null;/*from  w w w  .  j  a va  2  s. c o  m*/
    Blob blob = null;

    JdbcType type = column.getJdbcType();

    switch (type) {
    case BLOB:
        blob = connection.createBlob();
        os = blob.setBinaryStream(1);
        break;
    case BINARY:
    case VARBINARY:
    case LONGVARBINARY:
        os = new ByteBufferOutputStream();
        break;
    }

    IOUtils.serialize(os, datumWriter, schema, object);
    os.close();

    switch (type) {
    case BLOB:
        statement.setBlob(index, blob);
        break;
    case BINARY:
    case VARBINARY:
        statement.setBytes(index, IOUtils.getAsBytes(((ByteBufferOutputStream) os).getBufferList()));
        break;
    case LONGVARBINARY:
        statement.setBinaryStream(index,
                new ByteBufferInputStream(((ByteBufferOutputStream) os).getBufferList()));
        break;
    }
}

From source file:com.runwaysdk.dataaccess.database.general.Oracle.java

/**
 * Add a blob attribute to a PreparedStatement and the given index.
 *
 * @param prepared The statement to add the blob to
 * @param index The index to add the blob to
 * @param current The current value of the blob
 * @param newBytes The new bytes to write to the blob
 * @return//from  ww  w  .  j ava 2  s.  c  om
 * @throws SQLException
 */
private static int addBlobToStatement(PreparedStatement prepared, int index, Blob current, byte[] newBytes)
        throws SQLException {
    int written = 0;

    if (current == null) {
        prepared.setBytes(index, newBytes);
        written = newBytes.length;
    } else {
        written = current.setBytes(1, newBytes);
        prepared.setBlob(index, current);
    }

    return written;
}

From source file:org.bytesoft.openjtcc.supports.logger.DbTransactionLoggerImpl.java

@Override
public void delistService(TransactionContext transactionContext, CompensableArchive holder) {
    Connection connection = null;
    PreparedStatement stmt = null;
    try {//w w  w.  j  a v  a2 s.  c  o  m
        connection = this.getConnection();

        StringBuilder ber = new StringBuilder();
        ber.append("update tcc_compensable set variable = ?, try_committed = ? ");
        ber.append("where application = ? and endpoint = ? and global_tx_id = ? and branch_qualifier = ?");
        stmt = connection.prepareStatement(ber.toString());

        XidImpl internalXid = holder.branchXid;

        Blob blob = connection.createBlob();
        OutputStream output = blob.setBinaryStream(1);
        Serializable variable = holder.variable;
        if (variable != null) {
            byte[] bytes = this.serializer.serialize(variable);
            output.write(bytes);
        }
        output.close();

        stmt.setBlob(1, blob);
        stmt.setBoolean(2, holder.tryCommitted);

        stmt.setString(3, this.instanceKey.getApplication());
        stmt.setString(4, this.instanceKey.getEndpoint());

        stmt.setString(5, ByteUtils.byteArrayToString(internalXid.getGlobalTransactionId()));
        if (transactionContext.isCoordinator()) {
            stmt.setString(6, TransactionLogger.NULL);
        } else {
            stmt.setString(6, ByteUtils.byteArrayToString(internalXid.getBranchQualifier()));
        }

        stmt.executeUpdate();
    } catch (Exception ex) {
        ex.printStackTrace();
    } finally {
        closeStatement(stmt);
        this.releaseConnection(connection);
    }
}

From source file:org.kawanfw.test.api.client.InsertAndUpdateBlobTestNew.java

/**
 * Insert a blob/* w  ww. j  a  v  a 2s . c o  m*/
 * 
 * @throws Exception
 *             it any Exception occurs
 */
public void insertLoopPrepStatement(Connection connection, int numberToInsert, File blobFile) throws Exception {

    // We can now use our Remote JDBC Connection as a regular Connection!
    connection.setAutoCommit(false);

    // We will do all our remote insert in a SQL Transaction
    try {

        String sql = "insert into orderlog values ( ?, ?, ?, ?, ?, ?, ?, ?, ? )";

        // Create a new Prepared Statement
        PreparedStatement prepStatement = null;

        MessageDisplayer.display("");
        MessageDisplayer.display("Inserting " + numberToInsert + " orderlog...");

        for (int customerId = 1; customerId < numberToInsert + 1; customerId++) {
            int i = 1;
            long theTime = new java.util.Date().getTime();

            // We will insert a Blob (the image of the product).
            // The transfer will be done in streaming both on the client
            // and on the Servlet Server: we can upload/download very big
            // files.

            InputStream in = null;
            OutputStream out = null;

            try {
                in = new FileInputStream(blobFile);
                Blob blob = connection.createBlob();
                out = blob.setBinaryStream(1);
                IOUtils.copy(in, out);

                prepStatement = connection.prepareStatement(sql);

                prepStatement.setInt(i++, customerId);
                prepStatement.setInt(i++, customerId);
                prepStatement.setString(i++, "Item Description No " + customerId);
                prepStatement.setBigDecimal(i++, new BigDecimal(customerId));
                prepStatement.setDate(i++, new java.sql.Date(theTime));
                prepStatement.setTimestamp(i++, new Timestamp(theTime));
                prepStatement.setBlob(i++, blob);

                SqlUtil sqlUtil = new SqlUtil(connection);
                if (sqlUtil.isIngres()) {
                    prepStatement.setInt(i++, 0);
                } else {
                    prepStatement.setBoolean(i++, false);
                }

                prepStatement.setInt(i++, customerId);

                // SystemOutHandle.display("Before executeUpdate...");
                prepStatement.executeUpdate();

                // Close and free are important to delete temp files
                prepStatement.close();
                blob.free();
            } finally {
                IOUtils.closeQuietly(in);
                IOUtils.closeQuietly(out);
            }

        }

        // We do either everything in a single transaction or nothing
        connection.commit(); // Commit is propagated on Server
        MessageDisplayer.display("Remote Commit Done on AceQL Server!");
    } catch (Exception e) {
        connection.rollback();
        throw e;
    } finally {
        connection.setAutoCommit(true);
    }

}

From source file:org.quartz.impl.jdbcjobstore.oracle.OracleDelegate.java

public int updateJobDetail(Connection conn, JobDetail job) throws IOException, SQLException {

    ByteArrayOutputStream baos = serializeJobData(job.getJobDataMap());
    byte[] data = baos.toByteArray();

    PreparedStatement ps = null;//  www  .  j  a v  a  2 s.c  om
    PreparedStatement ps2 = null;
    ResultSet rs = null;

    try {
        ps = conn.prepareStatement(rtp(UPDATE_ORACLE_JOB_DETAIL));
        ps.setString(1, job.getDescription());
        ps.setString(2, job.getJobClass().getName());
        setBoolean(ps, 3, job.isDurable());
        setBoolean(ps, 4, job.isVolatile());
        setBoolean(ps, 5, job.isStateful());
        setBoolean(ps, 6, job.requestsRecovery());
        ps.setString(7, job.getName());
        ps.setString(8, job.getGroup());

        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;

        if (rs.next()) {
            Blob dbBlob = writeDataToBlob(rs, 1, data);
            ps2 = conn.prepareStatement(rtp(UPDATE_ORACLE_JOB_DETAIL_BLOB));

            ps2.setBlob(1, dbBlob);
            ps2.setString(2, job.getName());
            ps2.setString(3, job.getGroup());

            res = ps2.executeUpdate();
        }

        if (res > 0) {
            deleteJobListeners(conn, job.getName(), job.getGroup());
            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);
        closeStatement(ps2);
    }
}