Example usage for java.sql PreparedStatement executeBatch

List of usage examples for java.sql PreparedStatement executeBatch

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

Introduction

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

Prototype

int[] executeBatch() throws SQLException;

Source Link

Document

Submits a batch of commands to the database for execution and if all commands execute successfully, returns an array of update counts.

Usage

From source file:fr.aliacom.obm.common.contact.ContactDaoJdbcImpl.java

private PreparedStatement insertImIdenfifiers(Connection con, EntityId entityId,
        Map<String, InstantMessagingId> imIdentifiers) throws SQLException {
    PreparedStatement ps;
    ps = con.prepareStatement(/*  w  ww . j  a v a 2s.c  o m*/
            "INSERT INTO IM (im_entity_id, im_label, im_protocol, im_address) " + "VALUES (?, ?, ?, ?)");
    for (Entry<String, InstantMessagingId> entry : imIdentifiers.entrySet()) {
        ps.setInt(1, entityId.getId());
        ps.setString(2, entry.getKey());
        ps.setString(3, entry.getValue().getProtocol());
        ps.setString(4, entry.getValue().getId());
        ps.addBatch();
    }
    ps.executeBatch();
    return ps;
}

From source file:org.bml.util.errorconsumer.ParseErrorWorkerThread.java

/**
 * TOOD: Add a temp ordered list to store ParseError objects as they are
 * taken from the queue and log if any rows are rejected by the DB server
 * TODO: abstract out the handleDBEntry base logic and use <T> for entry and
 * a static method for marshaling into a Prepared Statement (Consider adding
 * the marshal method to a TABLE definition object).
 *///from w  w  w.  ja  v a  2s . c om
public void handleDBEntry() {

    Connection myConnection = null;
    PreparedStatement myPreparedStatement = null;

    Connection myPageViewConnection = null;

    int batchExecutionResults[] = null;

    List<ParseError> theBatchTrackingList = new LinkedList<ParseError>();

    //DeviceType aDeviceType = null;
    //DeviceClass aDeviceClass = null;

    //Change to reusable map
    Map<String, String> tmpMap = null;

    //Change to StringBuilder 
    //String tmpString = null; 

    //theBatchTrackingList = new ArrayList<PageViewData>(dataQueue.size());
    boolean dbErrror = false;

    try {
        ParseError aParseError = null;
        try {
            aParseError = errorQueue.remove();
            theBatchTrackingList.add(aParseError);
        } catch (NoSuchElementException e) {
            LOG.info("There are no ParseError Objects to push into the DB");
            return;
        }
        StopWatch connectionAge = new StopWatch();
        connectionAge.start();
        setWorkerState(WORKER_STATE.ACQUIRING_CONNECTION);
        myConnection = DBUtil.getDefaultDataSource().getConnection();
        setWorkerState(WORKER_STATE.CONFIGURING_CONNECTION);
        myConnection.clearWarnings();
        myConnection.setAutoCommit(false);
        setWorkerState(WORKER_STATE.PREPARING_SQL);
        myPreparedStatement = myConnection.prepareStatement(ParseErrorTable.PREPARED_INSERT_SQL);
        setWorkerState(WORKER_STATE.BATCHING);

        while ((connectionAge.getTime() / 1000) <= 20) {
            ParseErrorTable.populatePreparedStatement(myPreparedStatement, aParseError.toParamMap(),
                    Boolean.FALSE);
            myPreparedStatement.addBatch();
            try {
                aParseError = errorQueue.remove();
                theBatchTrackingList.add(aParseError);
            } catch (NoSuchElementException e) {
                break;
            }
        }

        this.setWorkerState(WORKER_STATE.EXECUTING_BATCH);
        batchExecutionResults = myPreparedStatement.executeBatch();

        myConnection.commit();

        this.setWorkerState(WORKER_STATE.VERIFYING_BATCH);
        if (batchExecutionResults.length != theBatchTrackingList.size()) {

        }

    } catch (SQLException sqle) {
        if (LOG.isFatalEnabled()) {
            LOG.fatal(
                    "SQLException caught. The ErrorConsumer is unable to push data to a database. ParseErrors will be dumped to /tmp/error_consumer/",
                    sqle);
        }
    } catch (Exception e) {
        if (LOG.isFatalEnabled()) {
            LOG.fatal(
                    "Exception caught. The ErrorConsumer is unable to push data to a database. Errors will be dumped to /tmp/error_consumer/",
                    e);
        }

    } finally {
        DbUtils.closeQuietly(myPreparedStatement);
        DbUtils.closeQuietly(myConnection);
    }
}

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
 *//*  ww w  .  j  av 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:lib.JdbcTemplate.java

@Override
public int[] batchUpdate(String sql, final BatchPreparedStatementSetter pss) throws DataAccessException {
    if (logger.isDebugEnabled()) {
        logger.debug("Executing SQL batch update [" + sql + "]");
    }//  w w w  .  jav  a  2s. co  m

    return execute(sql, new PreparedStatementCallback<int[]>() {
        @Override
        public int[] 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<Integer> rowsAffected = new ArrayList<Integer>();
                    for (int i = 0; i < batchSize; i++) {
                        pss.setValues(ps, i);
                        if (ipss != null && ipss.isBatchExhausted(i)) {
                            break;
                        }
                        rowsAffected.add(ps.executeUpdate());
                    }
                    int[] rowsAffectedArray = new int[rowsAffected.size()];
                    for (int i = 0; i < rowsAffectedArray.length; i++) {
                        rowsAffectedArray[i] = rowsAffected.get(i);
                    }
                    return rowsAffectedArray;
                }
            } finally {
                if (pss instanceof ParameterDisposer) {
                    ((ParameterDisposer) pss).cleanupParameters();
                }
            }
        }
    });
}

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
 *//*from w w  w.j ava 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.policy.mgt.core.dao.impl.PolicyDAOImpl.java

@Override
public void addDeviceGroupsToPolicy(Policy policy) throws PolicyManagerDAOException {

    Connection conn;//w w  w .jav a  2s  .com
    PreparedStatement stmt = null;
    List<DeviceGroupWrapper> deviceGroupWrappers = policy.getDeviceGroups();
    int tenantId = PrivilegedCarbonContext.getThreadLocalCarbonContext().getTenantId();

    try {
        conn = this.getConnection();
        String query = "INSERT INTO DM_DEVICE_GROUP_POLICY (DEVICE_GROUP_ID, POLICY_ID, TENANT_ID) VALUES (?, ?, ?)";
        stmt = conn.prepareStatement(query);
        for (DeviceGroupWrapper wrapper : deviceGroupWrappers) {
            stmt.setInt(1, wrapper.getId());
            stmt.setInt(2, policy.getId());
            stmt.setInt(3, tenantId);
            stmt.addBatch();
        }
        stmt.executeBatch();

    } catch (SQLException e) {
        throw new PolicyManagerDAOException(
                "Error occurred while adding the device group details to the policy.", e);
    } finally {
        PolicyManagementDAOUtil.cleanupResources(stmt, null);
    }
}

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 w  w  .  j  av a  2 s  .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: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
 */// w  w  w .  ja  v  a  2 s  . c o 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:com.fortmoon.utils.CSVDBLoader.java

public void executeBatch() {
    PreparedStatement stmt = null;
    //Statement stmt = null;

    boolean first = true;
    StringBuffer insert = new StringBuffer("insert into " + this.tableName + " (");
    for (String col : this.columnNames) {
        if (first)
            insert.append(col);/*w  ww.  j  a v  a2  s .  c  om*/
        else
            insert.append(", " + col);
        first = false;
    }
    insert.append(") values(");
    first = true;
    for (String col : this.columnNames) {
        if (first)
            insert.append("?");
        else
            insert.append(", ?");
        first = false;
    }
    insert.append(")");
    log.info("Insert statement: " + insert);

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

        //stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
        stmt = con.prepareStatement(insert.toString());

        con.setAutoCommit(false);
        TimeUnit SECONDS = TimeUnit.SECONDS;
        ArrayList<String> statements = null;
        boolean complete = false;
        while (!complete) {
            try {
                statements = (ArrayList<String>) queue.poll(10, SECONDS);
                if (statements == null) {
                    return;
                }
            } catch (InterruptedException e) {
                log.error("Poll timed out");
                e.printStackTrace();
                complete = true;
                return;
            }

            for (String statement : statements) {
                stmt.addBatch(statement);
            }
            //log.info("Starting execute.");
            int[] updateCounts = stmt.executeBatch();
            //log.info("Starting commit.");
            con.commit();
            stmt.clearBatch();
            log.info("Committed number of rows: " + updateCounts.length);
        }
    } catch (SQLException ex) {
        log.error(ex.getMessage(), ex);
    } finally {
        try {
            if (stmt != null)
                stmt.close();
            //if (pst != null)
            //   pst.close();
            if (con != null)
                con.close();
        } catch (SQLException ex) {
            log.error(ex.getMessage(), ex);
        }
        // statements.clear();
    }
    log.info("\nLoad complete. Goodbye.\n");
}

From source file:hoot.services.db.DbUtils.java

public static void batchRecordsDirectNodes(final long mapId, final List<?> records,
        final RecordBatchType recordBatchType, Connection conn, int maxRecordBatchSize) throws Exception {
    PreparedStatement ps = null;
    try {/*from  w  w  w.ja va2 s . co m*/
        String sql = null;
        long execResult = -1;
        //conn.setAutoCommit(false);
        int count = 0;

        switch (recordBatchType) {
        case INSERT:

            sql = "insert into current_nodes_" + mapId + " (id, latitude, "
                    + "longitude, changeset_id, visible, \"timestamp\", tile, version, tags) "
                    + "values (?, ?, ?, ?, ?, ?, ?, ?, ?)";

            ps = conn.prepareStatement(sql);
            for (Object o : records) {
                CurrentNodes node = (CurrentNodes) o;

                ps.setLong(1, node.getId());
                ps.setInt(2, node.getLatitude());
                ps.setInt(3, node.getLongitude());
                ps.setLong(4, node.getChangesetId());
                ps.setBoolean(5, node.getVisible());
                ps.setTimestamp(6, node.getTimestamp());
                ps.setLong(7, node.getTile());
                ps.setLong(8, node.getVersion());

                Map<String, String> tags = (Map<String, String>) node.getTags();

                String hstoreStr = "";
                Iterator it = tags.entrySet().iterator();
                while (it.hasNext()) {
                    Map.Entry pairs = (Map.Entry) it.next();
                    if (hstoreStr.length() > 0) {
                        hstoreStr += ",";
                    }
                    hstoreStr += "\"" + pairs.getKey() + "\"=>\"" + pairs.getValue() + "\"";
                }
                ps.setObject(9, hstoreStr, Types.OTHER);
                ps.addBatch();

                if (maxRecordBatchSize > -1) {
                    if (++count % maxRecordBatchSize == 0) {
                        ps.executeBatch();
                        //conn.commit();

                    }
                }

            }

            break;

        case UPDATE:

            sql = "update current_nodes_" + mapId + " set  latitude=?, "
                    + "longitude=?, changeset_id=?, visible=?, \"timestamp\"=?, tile=?, version=?, tags=? "
                    + "where id=?";
            ps = conn.prepareStatement(sql);
            for (Object o : records) {
                CurrentNodes node = (CurrentNodes) o;

                ps.setInt(1, node.getLatitude());
                ps.setInt(2, node.getLongitude());
                ps.setLong(3, node.getChangesetId());
                ps.setBoolean(4, node.getVisible());
                ps.setTimestamp(5, node.getTimestamp());
                ps.setLong(6, node.getTile());
                ps.setLong(7, node.getVersion());

                Map<String, String> tags = (Map<String, String>) node.getTags();

                String hstoreStr = "";
                Iterator it = tags.entrySet().iterator();
                while (it.hasNext()) {
                    Map.Entry pairs = (Map.Entry) it.next();
                    if (hstoreStr.length() > 0) {
                        hstoreStr += ",";
                    }
                    hstoreStr += "\"" + pairs.getKey() + "\"=>\"" + pairs.getValue() + "\"";
                }
                ps.setObject(8, hstoreStr, Types.OTHER);

                ps.setLong(9, node.getId());

                ps.addBatch();

                if (maxRecordBatchSize > -1) {
                    if (++count % maxRecordBatchSize == 0) {
                        ps.executeBatch();
                        //conn.commit();
                        ps.clearBatch();
                    }
                }
            }

            break;

        case DELETE:

            sql = "delete from current_nodes_" + mapId + " where id=?";
            ps = conn.prepareStatement(sql);
            for (Object o : records) {
                CurrentNodes node = (CurrentNodes) o;

                ps.setLong(1, node.getId());

                ps.addBatch();
                if (maxRecordBatchSize > -1) {
                    if (++count % maxRecordBatchSize == 0) {
                        ps.executeBatch();
                        //conn.commit();
                        ps.clearBatch();
                    }
                }
            }

            break;

        default:
            throw new Exception("");
        }

        ps.executeBatch();
        //conn.commit();
    } catch (Exception e) {
        //conn.rollback();
        String msg = "Error executing batch query.";
        msg += "  " + e.getMessage();
        msg += " Cause:" + e.getCause().toString();
        throw new Exception(msg);
    } finally {
        if (ps != null) {
            ps.close();
        }
        //conn.setAutoCommit(true);
    }
}