Example usage for java.sql PreparedStatement addBatch

List of usage examples for java.sql PreparedStatement addBatch

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

Introduction

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

Prototype

void addBatch() throws SQLException;

Source Link

Document

Adds a set of parameters to this PreparedStatement object's batch of commands.

Usage

From source file:com.archivas.clienttools.arcutils.utils.database.ManagedJobSchema.java

/**
 * @param conn//from ww  w . jav  a 2 s  .c o m
 *            - the db connection to use
 * @param filesToDiscover
 *            - list of files to insert
 * @param isInitialList
 *            - true if the list is generated by what the user selected/specified in the job
 *            definition
 * @return - the max RECORD_ID inserted
 * @throws DatabaseException
 *             -
 * @throws SQLException
 *             - if a db error occurred
 */
private long insertFilesToDiscover(PooledDbConnection conn, Collection<ArcProcessFile> filesToDiscover,
        boolean isInitialList) throws DatabaseException, SQLException {

    PreparedStatement preparedStatement = conn.prepareStatement(INSERT_FILES_TO_DISCOVER_STMT_NAME,
            insertFilesToDiscoverySql);
    long recordId = getNextBlockOfDbRecordIds(filesToDiscover.size()); // the last record id
                                                                       // inserted; we're going
                                                                       // to increment back up
                                                                       // to this
    int maxPathDepth = 0;
    for (ArcProcessFile file : filesToDiscover) {
        int pathDepth = file.getPathDepth();
        maxPathDepth = Math.max(maxPathDepth, pathDepth);
        recordId++;
        setInsertFilesToDiscoverySqlParams(file, preparedStatement, isInitialList, recordId, pathDepth);
        preparedStatement.addBatch();
    }

    // execute the batch statement we created in the for loop
    preparedStatement.executeBatch();

    if (!isInitialList) {
        // now update overall job stats to reflect these changes. A "select count(*)" from a
        // large table is a table scan! so we keep track of the count ourselves
        ManagedJobsSchema.getInstance().updateDiscoveredObjCnt(conn, jobId, filesToDiscover.size(), recordId,
                maxPathDepth);
    }

    return recordId;
}

From source file:cc.tooyoung.common.db.JdbcTemplate.java

@SuppressWarnings("unchecked")
public int[] batchUpdate(String sql, final BatchPreparedStatementSetter pss) throws DataAccessException {
    if (ApiLogger.isTraceEnabled()) {
        ApiLogger.trace("Executing SQL batch update [" + sql + "]");
    }//from  w  w  w  .  j  a v a2s  .c o m

    return (int[]) execute(sql, new PreparedStatementCallback() {
        public Object doInPreparedStatement(PreparedStatement ps) throws SQLException {
            try {
                int batchSize = pss.getBatchSize();
                InterruptibleBatchPreparedStatementSetter ipss = (pss instanceof InterruptibleBatchPreparedStatementSetter
                        ? (InterruptibleBatchPreparedStatementSetter) pss
                        : null);
                if (JdbcUtils.supportsBatchUpdates(ps.getConnection())) {
                    for (int i = 0; i < batchSize; i++) {
                        pss.setValues(ps, i);
                        if (ipss != null && ipss.isBatchExhausted(i)) {
                            break;
                        }
                        ps.addBatch();
                    }
                    return ps.executeBatch();
                } else {
                    List rowsAffected = new ArrayList();
                    for (int i = 0; i < batchSize; i++) {
                        pss.setValues(ps, i);
                        if (ipss != null && ipss.isBatchExhausted(i)) {
                            break;
                        }
                        rowsAffected.add(new Integer(ps.executeUpdate()));
                    }
                    int[] rowsAffectedArray = new int[rowsAffected.size()];
                    for (int i = 0; i < rowsAffectedArray.length; i++) {
                        rowsAffectedArray[i] = ((Integer) rowsAffected.get(i)).intValue();
                    }
                    return rowsAffectedArray;
                }
            } finally {
                if (pss instanceof ParameterDisposer) {
                    ((ParameterDisposer) pss).cleanupParameters();
                }
            }
        }
    }, true);
}

From source file:org.wso2.carbon.appfactory.core.dao.JDBCApplicationDAO.java

/**
 * This method is used to delete the version information of the given application
 *
 * @param databaseConnection The current database connection.
 * @param applicationKey     The application key of the deleted application.
 * @param versionIDs         List of version IDs of the application.
 * @throws org.wso2.carbon.appfactory.common.AppFactoryException If any SQLException occurs
 *///from  w  ww . j a  va  2s .  co m
private void deleteFromApplicationVersion(Connection databaseConnection, String applicationKey,
        List<Integer> versionIDs) throws AppFactoryException {

    // The versionIDs can be empty when there are issues in app creation.
    // Hence if the list of version id are empty, we simply return a empty list.
    if (versionIDs.isEmpty()) {
        handleDebugLog("The list of version IDs are empty for application key : " + applicationKey);
        return;
    }
    PreparedStatement preparedStatement = null;
    try {
        preparedStatement = databaseConnection.prepareStatement(SQLConstants.DELETE_APPLICATION_VERSION);
        for (Integer versionID : versionIDs) {
            preparedStatement.setInt(1, versionID);
            preparedStatement.addBatch();
            handleDebugLog("Adding repository id : " + versionID + " of application key : " + applicationKey
                    + " for " + "deletion of application version information");
        }
        preparedStatement.executeBatch();
        JDBCApplicationCacheManager.getAppVersionNameListCache()
                .remove(JDBCApplicationCacheManager.constructAppVersionNameListCacheKey(applicationKey));
        JDBCApplicationCacheManager.getAppVersionListCache().remove(applicationKey);
        handleDebugLog(
                "Successfully deleted all application version information of application : " + applicationKey);
    } catch (SQLException e) {

        // We do not rollback at this level since that is done from the calling method
        // We log here so that we can get a more specific message on where the failure happen.
        handleException("Error while deleting version information of application key : " + applicationKey, e);
    } finally {

        // We close only the preparedStatement since the database connection is passed from the calling method.
        // Database connection will be closed in that method.
        AppFactoryDBUtil.closePreparedStatement(preparedStatement);
    }
}

From source file:AIR.Common.DB.AbstractDLL.java

protected void executePreparedStatementBatch(SQLConnection connection, String query,
        List<Map<Integer, Object>> paramsList) throws ReturnStatusException {
    PreparedStatement prepStmt = null;
    try {/*from   w w w  .  j a v  a  2 s . c  om*/
        boolean preexistingAutoCommitMode = connection.getAutoCommit();
        connection.setAutoCommit(false);
        prepStmt = connection.prepareStatement(query);

        if (paramsList != null) {
            for (Map<Integer, Object> params : paramsList) {
                Iterator<Entry<Integer, Object>> param = params.entrySet().iterator();
                while (param.hasNext()) {
                    Entry<Integer, Object> entry = param.next();
                    if (entry.getValue() instanceof String) {
                        prepStmt.setString(entry.getKey(), entry.getValue().toString());
                    } else if (entry.getValue() instanceof Integer) {
                        prepStmt.setInt(entry.getKey(), (Integer) entry.getValue());
                    } else if (entry.getValue() instanceof Date) {
                        prepStmt.setString(entry.getKey(), String.format("%s", AbstractDateUtilDll
                                .getDateAsFormattedMillisecondsString((Date) entry.getValue())));
                    } else if (entry.getValue() instanceof UUID) {
                        String newStr = entry.getValue().toString().replaceAll("-", "");
                        prepStmt.setBytes(entry.getKey(), DatatypeConverter.parseHexBinary(newStr));
                    } else if (entry.getValue() instanceof Boolean) {
                        prepStmt.setBoolean(entry.getKey(), (Boolean) entry.getValue());
                    }

                }
                prepStmt.addBatch();
            }
        }
        prepStmt.executeBatch();
        prepStmt.close();
        connection.commit();
        // reset autocommit.
        connection.setAutoCommit(preexistingAutoCommitMode);
    } catch (SQLException exp) {
        throw new ReturnStatusException(exp);
    } finally {
        if (prepStmt != null)
            try {
                prepStmt.close();
            } catch (SQLException e) {

            }
    }
}

From source file:org.wso2.carbon.appfactory.core.dao.JDBCApplicationDAO.java

/**
 * This method is used to delete the build information of the given application
 *
 * @param databaseConnection The current database connection.
 * @param applicationKey     The application key of the deleted application.
 * @param repositoryIDs      List of version repository IDs of the application.
 * @throws org.wso2.carbon.appfactory.common.AppFactoryException If any SQLException occurs
 *//*from  w w  w  . jav a  2 s  . c o m*/
private void deleteFromBuildStatus(Connection databaseConnection, String applicationKey,
        List<Integer> repositoryIDs) throws AppFactoryException {

    // If the list of repository IDs are empty, then there is no need to execute the following
    if (repositoryIDs.isEmpty()) {
        handleDebugLog("The list of repository IDs are empty for application : " + applicationKey);
        return;
    }
    PreparedStatement preparedStatement = null;
    try {
        preparedStatement = databaseConnection.prepareStatement(SQLConstants.DELETE_BUILD_STATUS_SQL);
        for (Integer repositoryID : repositoryIDs) {
            preparedStatement.setInt(1, repositoryID);
            preparedStatement.addBatch();

        }
        handleDebugLog("Adding repository id " + repositoryIDs + " of application" + applicationKey + " for "
                + "deletion of build status information");
        preparedStatement.executeBatch();
        handleDebugLog("Successfully deleted all application build status information of application : "
                + applicationKey);
    } catch (SQLException e) {

        // We do not rollback at this level since that is done from the calling method
        // We log here so that we can get a more specific message on where the failure happen.
        handleException("Error while deleting build status information of application : " + applicationKey, e);
    } finally {

        // We close only the preparedStatement since the database connection is passed from the calling method.
        // Database connection will be closed in that method.
        AppFactoryDBUtil.closePreparedStatement(preparedStatement);
    }
}

From source file:org.wso2.carbon.appfactory.core.dao.JDBCApplicationDAO.java

/**
 * This method is used to delete the deploy information of the given application
 *
 * @param databaseConnection The current database connection.
 * @param applicationKey     The application key of the deleted application.
 * @param repositoryIDs      List of version repository IDs of the application.
 * @throws org.wso2.carbon.appfactory.common.AppFactoryException If any SQLException occurs
 *///ww  w .  j av  a 2 s .  c  o  m
private void deleteFromDeployStatus(Connection databaseConnection, String applicationKey,
        List<Integer> repositoryIDs) throws AppFactoryException {

    // If the list of repository IDs are empty, then there is no need to execute the following
    if (repositoryIDs.isEmpty()) {
        handleDebugLog("The list of repository IDs are empty for application : " + applicationKey);
        return;
    }
    PreparedStatement preparedStatement = null;
    try {
        preparedStatement = databaseConnection.prepareStatement(SQLConstants.DELETE_DEPLOY_STATUS_SQL);
        for (Integer repositoryID : repositoryIDs) {
            preparedStatement.setInt(1, repositoryID);
            preparedStatement.addBatch();
            handleDebugLog("Adding repository id " + repositoryID + " of application" + applicationKey + " for "
                    + "deletion of deploy status information");
        }
        preparedStatement.executeBatch();
        handleDebugLog("Successfully deleted all application deploy status information of application : "
                + applicationKey);
    } catch (SQLException e) {

        // We do not rollback at this level since that is done from the calling method
        // We log here so that we can get a more specific message on where the failure happen.
        handleException("Error while deleting deploy status information of application : " + applicationKey, e);
    } finally {

        // We close only the preparedStatement since the database connection is passed from the calling method.
        // Database connection will be closed in that method.
        AppFactoryDBUtil.closePreparedStatement(preparedStatement);
    }

}

From source file:org.wso2.carbon.appfactory.core.dao.JDBCApplicationDAO.java

/**
 * This method is used to delete the repository information of the given application
 *
 * @param databaseConnection The current database connection.
 * @param applicationKey     The application key of the deleted application.
 * @param repositoryIDs      List of version repository IDs of the application.
 * @throws org.wso2.carbon.appfactory.common.AppFactoryException If any SQLException occurs
 *//*from   w  ww.  j a  va  2s. c o  m*/
private void deleteFromRepository(Connection databaseConnection, String applicationKey,
        List<Integer> repositoryIDs) throws AppFactoryException {
    // If the list of repository IDs are empty, then there is no need to execute the following
    if (repositoryIDs.isEmpty()) {
        // debug log
        handleDebugLog("The list of repository IDs are empty for application key : " + applicationKey);
        return;
    }
    PreparedStatement preparedStatement = null;
    try {
        preparedStatement = databaseConnection.prepareStatement(SQLConstants.DELETE_APPLICATION_REPOSITORY_SQL);
        for (Integer repositoryID : repositoryIDs) {
            preparedStatement.setInt(1, repositoryID);
            preparedStatement.addBatch();
            handleDebugLog("Adding repository id " + repositoryID + " of application" + applicationKey + " for "
                    + "deletion of repository information");
        }
        preparedStatement.executeBatch();
        handleDebugLog("Successfully deleted all application repository information of application key : "
                + applicationKey);
    } catch (SQLException e) {

        // We do not rollback at this level since that is done from the calling method
        // We log here so that we can get a more specific message on where the failure happen.
        handleException("Error while deleting repository information of application key : " + applicationKey,
                e);
    } finally {

        // We close only the preparedStatement since the database connection is passed from the calling method.
        // Database connection will be closed in that method.
        AppFactoryDBUtil.closePreparedStatement(preparedStatement);
    }
}

From source file:com.mtgi.analytics.JdbcBehaviorEventPersisterImpl.java

public void persist(final Queue<BehaviorEvent> events) {

    getJdbcTemplate().execute(new ConnectionCallback() {

        public Object doInConnection(Connection con) throws SQLException, DataAccessException {

            //if this connection is behavior tracking, suspend tracking.
            //we don't generate more events while persisting.
            BehaviorTrackingConnectionProxy bt = null;
            for (Connection c = con; bt == null
                    && c instanceof ConnectionProxy; c = ((ConnectionProxy) c).getTargetConnection()) {
                if (c instanceof BehaviorTrackingConnectionProxy) {
                    bt = (BehaviorTrackingConnectionProxy) c;
                    bt.suspendTracking();
                }/*from w ww  .j a va 2  s .  c o m*/
            }

            try {
                boolean doBatch = supportsBatchUpdates(con);
                EventDataElementSerializer dataSerializer = new EventDataElementSerializer(xmlFactory);

                PreparedStatement[] idStmt = { null };
                PreparedStatement insert = con.prepareStatement(insertSql);
                try {

                    //keep track of statements added to the batch so that we can time our
                    //flushes.
                    int batchCount = 0;

                    for (BehaviorEvent next : events) {

                        //event may already have an ID assigned if any
                        //of its child events has been persisted.
                        assignIds(next, con, idStmt);

                        //populate identifying information for the event into the insert statement.
                        insert.setLong(1, (Long) next.getId());

                        BehaviorEvent parent = next.getParent();
                        nullSafeSet(insert, 2, parent == null ? null : parent.getId(), Types.BIGINT);

                        insert.setString(3, next.getApplication());
                        insert.setString(4, next.getType());
                        insert.setString(5, next.getName());
                        insert.setTimestamp(6, new java.sql.Timestamp(next.getStart().getTime()));
                        insert.setLong(7, next.getDurationNs());

                        //set optional context information on the event.
                        nullSafeSet(insert, 8, next.getUserId(), Types.VARCHAR);
                        nullSafeSet(insert, 9, next.getSessionId(), Types.VARCHAR);
                        nullSafeSet(insert, 10, next.getError(), Types.VARCHAR);

                        //convert event data to XML
                        String data = dataSerializer.serialize(next.getData(), true);
                        nullSafeSet(insert, 11, data, Types.VARCHAR);

                        if (doBatch) {
                            insert.addBatch();
                            if (++batchCount >= batchSize) {
                                insert.executeBatch();
                                batchCount = 0;
                            }
                        } else {
                            insert.executeUpdate();
                        }
                    }

                    //flush any lingering batch inserts through to the server.
                    if (batchCount > 0)
                        insert.executeBatch();

                } finally {
                    closeStatement(insert);
                    closeStatement(idStmt[0]);
                }

                return null;

            } finally {
                if (bt != null)
                    bt.resumeTracking();
            }
        }

    });
}

From source file:com.octo.captcha.engine.bufferedengine.buffer.DatabaseCaptchaBuffer.java

/**
 * Remove a precise number of captcha with a locale
 *
 * @param number The number of captchas to remove
 * @param locale The locale of the removed captchas
 *
 * @return a collection of captchas/*from w w w.  ja  v a  2s .c o m*/
 */
public Collection removeCaptcha(int number, Locale locale) {
    Connection con = null;
    PreparedStatement ps = null;
    PreparedStatement psdel = null;
    ResultSet rs = null;
    Collection collection = new UnboundedFifoBuffer();
    Collection temp = new UnboundedFifoBuffer();
    if (number < 1) {
        return collection;
    }
    try {
        if (log.isDebugEnabled()) {
            log.debug("try to remove " + number + " captchas");
        }
        ;
        con = datasource.getConnection();

        ps = con.prepareStatement(
                "select *  from " + table + " where " + localeColumn + " = ? order by " + timeMillisColumn);

        psdel = con.prepareStatement(
                "delete from " + table + " where " + timeMillisColumn + "= ? and " + hashCodeColumn + "= ? ");//and " + localeColumn
        //+ "= ?");
        ps.setString(1, locale.toString());
        ps.setMaxRows(number);
        //read
        rs = ps.executeQuery();
        int i = 0;
        while (rs.next() && i < number) {
            try {
                i++;
                InputStream in = rs.getBinaryStream(captchaColumn);
                ObjectInputStream objstr = new ObjectInputStream(in);
                Object captcha = objstr.readObject();
                temp.add(captcha);
                //and delete
                long time = rs.getLong(timeMillisColumn);
                long hash = rs.getLong(hashCodeColumn);
                psdel.setLong(1, time);
                psdel.setLong(2, hash);
                //psdel.setString(3, rs.getString(localeColumn));
                psdel.addBatch();

                if (log.isDebugEnabled()) {
                    log.debug("remove captcha added to batch : " + time + ";" + hash);
                }

            } catch (IOException e) {
                log.error("error during captcha deserialization, "
                        + "check your class versions. removing row from database", e);
                psdel.execute();
            } catch (ClassNotFoundException e) {
                log.error("Serialized captcha class in database is not in your classpath!", e);
            }

        }
        //execute batch delete
        psdel.executeBatch();
        log.debug("batch executed");
        rs.close();
        //commit the whole stuff
        con.commit();
        log.debug("batch commited");
        //only add after commit
        collection.addAll(temp);
    } catch (SQLException e) {
        log.error(DB_ERROR, e);
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException ex) {
            }
        }

    } finally {

        if (ps != null) {
            try {
                ps.close();
            } // rollback on error
            catch (SQLException e) {
            }
        }
        if (con != null) {
            try {
                con.close();
            } // rollback on error
            catch (SQLException e) {
            }
        }
    }
    return collection;
}

From source file:org.rhq.enterprise.server.measurement.CallTimeDataManagerBean.java

@TransactionAttribute(TransactionAttributeType.REQUIRES_NEW)
public void insertCallTimeDataKeys(Set<CallTimeData> callTimeDataSet) {

    int[] results;
    String insertKeySql;/*w w w. j  a va  2s  .c om*/
    PreparedStatement ps = null;
    Connection conn = null;

    try {
        conn = rhqDs.getConnection();
        DatabaseType dbType = DatabaseTypeFactory.getDatabaseType(conn);

        if (dbType instanceof Postgresql83DatabaseType) {
            Statement st = null;
            try {
                // Take advantage of async commit here
                st = conn.createStatement();
                st.execute("SET synchronous_commit = off");
            } finally {
                JDBCUtil.safeClose(st);
            }
        }

        if (dbType instanceof PostgresqlDatabaseType || dbType instanceof OracleDatabaseType
                || dbType instanceof H2DatabaseType) {
            String keyNextvalSql = JDBCUtil.getNextValSql(conn, "RHQ_calltime_data_key");
            insertKeySql = String.format(CALLTIME_KEY_INSERT_STATEMENT, keyNextvalSql);
        } else if (dbType instanceof SQLServerDatabaseType) {
            insertKeySql = CALLTIME_KEY_INSERT_STATEMENT_AUTOINC;
        } else {
            throw new IllegalArgumentException("Unknown database type, can't continue: " + dbType);
        }

        ps = conn.prepareStatement(insertKeySql);
        for (CallTimeData callTimeData : callTimeDataSet) {
            ps.setInt(1, callTimeData.getScheduleId());
            ps.setInt(3, callTimeData.getScheduleId());
            Set<String> callDestinations = callTimeData.getValues().keySet();
            for (String callDestination : callDestinations) {
                ps.setString(2, callDestination);
                ps.setString(4, callDestination);
                ps.addBatch();
            }
        }

        results = ps.executeBatch();

        int insertedRowCount = 0;
        for (int i = 0; i < results.length; i++) {
            if (((results[i] < 0) || (results[i] > 1)) && (results[i] != -2)) // oracle returns -2 because it can't count updated rows
            {
                throw new MeasurementStorageException("Failed to insert call-time data key rows - result ["
                        + results[i] + "] for batch command [" + i + "] is less than 0 or greater than 1.");
            }

            insertedRowCount += results[i] == -2 ? 1 : results[i]; // If Oracle returns -2, just count 1 row
        }

        log.debug(
                "Inserted new call-time data key rows for " + ((insertedRowCount >= 0) ? insertedRowCount : "?")
                        + " out of " + results.length + " reported key-value pairs.");
    } catch (SQLException e) {
        logSQLException("Failed to persist call-time data keys", e);
    } catch (Throwable t) {
        log.error("Failed to persist call-time data keys", t);
    } finally {
        JDBCUtil.safeClose(conn, ps, null);
    }
}