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:org.wso2.carbon.policy.mgt.core.dao.impl.PolicyDAOImpl.java

@Override
public Policy updateUserOfPolicy(List<String> usersToAdd, Policy policy) throws PolicyManagerDAOException {
    Connection conn;/* ww w. ja v  a2 s  .  c  o  m*/
    PreparedStatement insertStmt = null;
    PreparedStatement deleteStmt = null;
    final List<String> currentUsers = policy.getUsers();

    SetReferenceTransformer<String> transformer = new SetReferenceTransformer<String>();

    transformer.transform(currentUsers, usersToAdd);
    usersToAdd = transformer.getObjectsToAdd();
    List<String> usersToDelete = transformer.getObjectsToRemove();
    try {
        conn = this.getConnection();
        if (usersToAdd.size() > 0) {
            String query = "INSERT INTO DM_USER_POLICY (POLICY_ID, USERNAME) VALUES (?, ?)";
            insertStmt = conn.prepareStatement(query);
            for (String username : usersToAdd) {
                insertStmt.setInt(1, policy.getId());
                insertStmt.setString(2, username);
                insertStmt.addBatch();
            }
            insertStmt.executeBatch();
        }
        if (usersToDelete.size() > 0) {
            String deleteQuery = "DELETE FROM DM_USER_POLICY WHERE USERNAME=? AND POLICY_ID=?";
            deleteStmt = conn.prepareStatement(deleteQuery);
            for (String username : usersToDelete) {
                deleteStmt.setString(1, username);
                deleteStmt.setInt(2, policy.getId());
                deleteStmt.addBatch();
            }
            deleteStmt.executeBatch();
        }

    } catch (SQLException e) {
        throw new PolicyManagerDAOException("Error occurred while adding the user name with policy to database",
                e);
    } finally {
        PolicyManagementDAOUtil.cleanupResources(insertStmt, null);
        PolicyManagementDAOUtil.cleanupResources(deleteStmt, null);
    }
    return policy;
}

From source file:org.apache.ctakes.ytex.uima.mapper.DocumentMapperServiceImpl.java

private BiMap<Annotation, Integer> saveAnnoBase(final JCas jcas, final Set<String> setTypesToIgnore,
        final int docId) {
    final AnnotationIndex<Annotation> annoIdx = jcas.getAnnotationIndex(Annotation.typeIndexID);
    final List<Annotation> listAnno = new ArrayList<Annotation>(annoIdx.size());
    final BiMap<Annotation, Integer> mapAnnoToId = HashBiMap.create();
    final FSIterator<Annotation> annoIterator = annoIdx.iterator();
    this.sessionFactory.getCurrentSession().doWork(new Work() {

        @Override/*  www  .j  av  a  2  s.  co m*/
        public void execute(Connection conn) throws SQLException {
            PreparedStatement ps = null;
            ResultSet rs = null;
            try {
                ps = conn.prepareStatement("insert into " + getTablePrefix()
                        + "anno_base (document_id, span_begin, span_end, uima_type_id) values (?, ?, ?, ?)",
                        Statement.RETURN_GENERATED_KEYS);
                while (annoIterator.hasNext()) {
                    Annotation anno = (Annotation) annoIterator.next();
                    String annoClass = anno.getClass().getName();
                    if (!setTypesToIgnore.contains(annoClass) && uimaTypeMap.containsKey(annoClass)) {
                        // should not ignore, and we know how to map this
                        // annotation
                        listAnno.add(anno);
                        ps.setInt(1, docId);
                        ps.setInt(2, anno.getBegin());
                        ps.setInt(3, anno.getEnd());
                        ps.setInt(4, uimaTypeMap.get(annoClass).getUimaTypeID());
                        ps.addBatch();
                    }
                }
                ps.executeBatch();
                rs = ps.getGeneratedKeys();
                int annoIndex = 0;
                while (rs.next()) {
                    mapAnnoToId.put(listAnno.get(annoIndex), rs.getInt(1));
                    annoIndex++;
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            } finally {
                if (rs != null) {
                    try {
                        rs.close();
                    } catch (SQLException e) {
                    }
                }
                if (ps != null) {
                    try {
                        ps.close();
                    } catch (SQLException e) {
                    }
                }
            }
        }
    });
    return mapAnnoToId;
}

From source file:org.wso2.carbon.policy.mgt.core.dao.impl.PolicyDAOImpl.java

@Override
public Policy updateRolesOfPolicy(List<String> rolesToAdd, Policy previousPolicy)
        throws PolicyManagerDAOException {
    Connection conn;//from  w  w w  .ja va  2 s. c  om
    PreparedStatement insertStmt = null;
    PreparedStatement deleteStmt = null;

    final List<String> currentRoles = previousPolicy.getRoles();

    SetReferenceTransformer<String> transformer = new SetReferenceTransformer<String>();

    transformer.transform(currentRoles, rolesToAdd);
    rolesToAdd = transformer.getObjectsToAdd();
    List<String> rolesToDelete = transformer.getObjectsToRemove();
    try {
        conn = this.getConnection();
        if (rolesToAdd.size() > 0) {
            String query = "INSERT INTO DM_ROLE_POLICY (ROLE_NAME, POLICY_ID) VALUES (?, ?)";
            insertStmt = conn.prepareStatement(query);
            for (String role : rolesToAdd) {
                insertStmt.setString(1, role);
                insertStmt.setInt(2, previousPolicy.getId());
                insertStmt.addBatch();
            }
            insertStmt.executeBatch();
        }
        if (rolesToDelete.size() > 0) {
            String deleteQuery = "DELETE FROM DM_ROLE_POLICY WHERE ROLE_NAME=? AND POLICY_ID=?";
            deleteStmt = conn.prepareStatement(deleteQuery);
            for (String role : rolesToDelete) {
                deleteStmt.setString(1, role);
                deleteStmt.setInt(2, previousPolicy.getId());
                deleteStmt.addBatch();
            }
            deleteStmt.executeBatch();
        }
    } catch (SQLException e) {
        throw new PolicyManagerDAOException("Error occurred while adding the role name with policy to database",
                e);
    } finally {
        PolicyManagementDAOUtil.cleanupResources(insertStmt, null);
        PolicyManagementDAOUtil.cleanupResources(deleteStmt, null);
    }
    return previousPolicy;
}

From source file:com.flexive.ejb.beans.structure.TypeEngineBean.java

private void storeTypeOptions(Connection con, String table, String primaryColumn, long id, // Long assignmentId,
        List<FxStructureOption> options, boolean update) throws SQLException, FxInvalidParameterException {
    PreparedStatement ps = null;
    try {//from w  w  w.  j  a  v a2 s.c  o m
        if (update) {
            ps = con.prepareStatement("DELETE FROM " + table + " WHERE " + primaryColumn + "=?");
            ps.setLong(1, id);
            ps.executeUpdate();
            ps.close();
        }

        if (options == null || options.size() == 0)
            return;
        //                                                        1                 2      3           4        5
        ps = con.prepareStatement("INSERT INTO " + table + " (" + primaryColumn
                + ",OPTKEY,MAYOVERRIDE,ISINHERITED,OPTVALUE)VALUES(?,?,?,?,?)");
        for (FxStructureOption option : options) {
            ps.setLong(1, id);
            if (StringUtils.isEmpty(option.getKey()))
                throw new FxInvalidParameterException("key", "ex.structure.option.key.empty",
                        option.getValue());
            ps.setString(2, option.getKey());
            ps.setBoolean(3, option.isOverridable());
            ps.setBoolean(4, option.getIsInherited());
            ps.setString(5, option.getValue());
            ps.addBatch();
        }
        ps.executeBatch();
    } finally {
        if (ps != null)
            ps.close();
    }
}

From source file:org.openmrs.util.databasechange.ConceptValidatorChangeSet.java

/**
 * Executes all the changes to the concept names as a batch update.
 *
 * @param connection The database connection
 *///from ww w .  j  a  va2s  .c  om
private void runBatchUpdate(JdbcConnection connection) {
    PreparedStatement pStmt = null;

    try {
        connection.setAutoCommit(false);
        pStmt = connection.prepareStatement(
                "UPDATE concept_name SET locale = ?, concept_name_type = ?, locale_preferred = ?, voided = ?, date_voided = ?, void_reason = ?, voided_by = ? WHERE concept_name_id = ?");

        Integer userId = DatabaseUpdater.getAuthenticatedUserId();
        //is we have no authenticated user(for API users), set as Daemon
        if (userId == null || userId < 1) {
            userId = getInt(connection, "SELECT min(user_id) FROM users");
            //leave it as null rather than setting it to 0
            if (userId < 1) {
                userId = null;
            }
        }

        for (ConceptName conceptName : updatedConceptNames) {
            pStmt.setString(1, conceptName.getLocale().toString());
            pStmt.setString(2,
                    (conceptName.getConceptNameType() != null) ? conceptName.getConceptNameType().toString()
                            : null);
            pStmt.setBoolean(3, conceptName.isLocalePreferred());
            pStmt.setBoolean(4, conceptName.isVoided());
            pStmt.setDate(5, conceptName.isVoided() ? new Date(System.currentTimeMillis()) : null);
            pStmt.setString(6, conceptName.getVoidReason());
            // "Not all databases allow for a non-typed Null to be sent to the backend", so we can't use setInt
            pStmt.setObject(7, (conceptName.isVoided() && userId != null) ? userId : null, Types.INTEGER);
            pStmt.setInt(8, conceptName.getConceptNameId());

            pStmt.addBatch();
        }

        try {
            int[] updateCounts = pStmt.executeBatch();
            for (int i = 0; i < updateCounts.length; i++) {
                if (updateCounts[i] > -1) {
                    log.debug("Successfully executed: updateCount=" + updateCounts[i]);
                } else if (updateCounts[i] == Statement.SUCCESS_NO_INFO) {
                    log.debug("Successfully executed; No Success info");
                } else if (updateCounts[i] == Statement.EXECUTE_FAILED) {
                    log.warn("Failed to execute update");
                }
            }

            log.debug("Committing updates...");
            connection.commit();
        } catch (BatchUpdateException be) {
            log.warn("Error generated while processsing batch update", be);
            int[] updateCounts = be.getUpdateCounts();

            for (int i = 0; i < updateCounts.length; i++) {
                if (updateCounts[i] > -1) {
                    log.warn("Executed with exception: updateCount=" + updateCounts[i]);
                } else if (updateCounts[i] == Statement.SUCCESS_NO_INFO) {
                    log.warn("Executed with exception; No Success info");
                } else if (updateCounts[i] == Statement.EXECUTE_FAILED) {
                    log.warn("Failed to execute update with exception");
                }
            }

            try {
                log.warn("Rolling back batch", be);
                connection.rollback();
            } catch (Exception rbe) {
                log.warn("Error generated while rolling back batch update", be);
            }
        }
    } catch (SQLException e) {
        log.warn("Error generated", e);
    } catch (DatabaseException e) {
        log.warn("Error generated", e);
    } finally {
        //reset to auto commit mode
        try {
            connection.setAutoCommit(true);
        } catch (DatabaseException e) {
            log.warn("Failed to reset auto commit back to true", e);
        }

        if (pStmt != null) {
            try {
                pStmt.close();
            } catch (SQLException e) {
                log.warn("Failed to close the prepared statement object");
            }
        }
    }
}

From source file:org.springframework.jdbc.core.JdbcTemplate.java

public int[] batchUpdate(String sql, final BatchPreparedStatementSetter pss) throws DataAccessException {
    if (logger.isDebugEnabled()) {
        logger.debug("Executing SQL batch update [" + sql + "]");
    }/*from  w  ww.  j a v  a  2s .  c  om*/
    return (int[]) execute(sql, new PreparedStatementCallback() {
        public Object doInPreparedStatement(PreparedStatement ps) throws SQLException {
            int batchSize = pss.getBatchSize();
            DatabaseMetaData dbmd = ps.getConnection().getMetaData();
            try {
                boolean supportsBatchUpdates = false;
                try {
                    if (dbmd != null) {
                        if (dbmd.supportsBatchUpdates()) {
                            if (logger.isDebugEnabled()) {
                                logger.debug("Batch Updates supported for [" + dbmd.getDriverName() + " "
                                        + dbmd.getDriverVersion() + "]");
                            }
                            supportsBatchUpdates = true;
                        } else {
                            if (logger.isDebugEnabled()) {
                                logger.debug("Batch Updates are not supported for [" + dbmd.getDriverName()
                                        + " " + dbmd.getDriverVersion() + "]");
                            }
                        }
                    }
                } catch (AbstractMethodError ame) {
                    logger.warn("Driver does not support JDBC 2.0 method supportsBatchUpdatres ["
                            + dbmd.getDriverName() + " " + dbmd.getDriverVersion() + "]");
                }
                if (supportsBatchUpdates) {
                    for (int i = 0; i < batchSize; i++) {
                        pss.setValues(ps, i);
                        ps.addBatch();
                    }
                    return ps.executeBatch();
                } else {
                    int[] rowsAffected = new int[batchSize];
                    for (int i = 0; i < batchSize; i++) {
                        pss.setValues(ps, i);
                        rowsAffected[i] = ps.executeUpdate();
                    }
                    return rowsAffected;
                }
            } finally {
                if (pss instanceof ParameterDisposer) {
                    ((ParameterDisposer) pss).cleanupParameters();
                }
            }
        }
    });
}

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

private PreparedStatement insertAddresses(Connection con, EntityId entityId, Map<String, Address> addresses)
        throws SQLException {
    PreparedStatement ps;
    ps = con.prepareStatement("INSERT INTO Address (address_entity_id, address_label, "
            + "address_street, address_zipcode, address_town,"
            + "address_expresspostal, address_country, address_state) " + "VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
    for (Entry<String, Address> entry : addresses.entrySet()) {
        Address ad = entry.getValue();// w w w.ja v  a2  s.co  m
        ps.setInt(1, entityId.getId());
        ps.setString(2, entry.getKey());
        ps.setString(3, ad.getStreet());
        ps.setString(4, ad.getZipCode());
        ps.setString(5, ad.getTown());
        ps.setString(6, ad.getExpressPostal());
        ps.setString(7, getCountryIso3166(con, ad.getCountry()));
        ps.setString(8, ad.getState());
        ps.addBatch();
    }
    ps.executeBatch();
    return ps;
}

From source file:org.springframework.jdbc.core.JdbcTemplateTests.java

public void testBatchUpdateWithPreparedStatement() throws Exception {
    final String sql = "UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = ?";
    final int[] ids = new int[] { 100, 200 };
    final int[] rowsAffected = new int[] { 1, 2 };

    MockControl ctrlPreparedStatement = MockControl.createControl(PreparedStatement.class);
    PreparedStatement mockPreparedStatement = (PreparedStatement) ctrlPreparedStatement.getMock();
    mockPreparedStatement.getConnection();
    ctrlPreparedStatement.setReturnValue(mockConnection);
    mockPreparedStatement.setInt(1, ids[0]);
    ctrlPreparedStatement.setVoidCallable();
    mockPreparedStatement.addBatch();/*from www .  j a  v a  2 s .c om*/
    ctrlPreparedStatement.setVoidCallable();
    mockPreparedStatement.setInt(1, ids[1]);
    ctrlPreparedStatement.setVoidCallable();
    mockPreparedStatement.addBatch();
    ctrlPreparedStatement.setVoidCallable();
    mockPreparedStatement.executeBatch();
    ctrlPreparedStatement.setReturnValue(rowsAffected);
    if (debugEnabled) {
        mockPreparedStatement.getWarnings();
        ctrlPreparedStatement.setReturnValue(null);
    }
    mockPreparedStatement.close();
    ctrlPreparedStatement.setVoidCallable();

    MockControl ctrlDatabaseMetaData = MockControl.createControl(DatabaseMetaData.class);
    DatabaseMetaData mockDatabaseMetaData = (DatabaseMetaData) ctrlDatabaseMetaData.getMock();
    mockDatabaseMetaData.getDatabaseProductName();
    ctrlDatabaseMetaData.setReturnValue("MySQL");
    mockDatabaseMetaData.supportsBatchUpdates();
    ctrlDatabaseMetaData.setReturnValue(true);

    mockConnection.prepareStatement(sql);
    ctrlConnection.setReturnValue(mockPreparedStatement);
    mockConnection.getMetaData();
    ctrlConnection.setReturnValue(mockDatabaseMetaData, 2);

    ctrlPreparedStatement.replay();
    ctrlDatabaseMetaData.replay();
    replay();

    BatchPreparedStatementSetter setter = new BatchPreparedStatementSetter() {
        public void setValues(PreparedStatement ps, int i) throws SQLException {
            ps.setInt(1, ids[i]);
        }

        public int getBatchSize() {
            return ids.length;
        }
    };

    JdbcTemplate template = new JdbcTemplate(mockDataSource, false);

    int[] actualRowsAffected = template.batchUpdate(sql, setter);
    assertTrue("executed 2 updates", actualRowsAffected.length == 2);
    assertEquals(rowsAffected[0], actualRowsAffected[0]);
    assertEquals(rowsAffected[1], actualRowsAffected[1]);

    ctrlPreparedStatement.verify();
    ctrlDatabaseMetaData.verify();
}

From source file:org.springframework.jdbc.core.JdbcTemplateTests.java

public void testBatchUpdateFails() throws Exception {
    final String sql = "UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = ?";
    final int[] ids = new int[] { 100, 200 };
    SQLException sex = new SQLException();

    MockControl ctrlPreparedStatement = MockControl.createControl(PreparedStatement.class);
    PreparedStatement mockPreparedStatement = (PreparedStatement) ctrlPreparedStatement.getMock();
    mockPreparedStatement.getConnection();
    ctrlPreparedStatement.setReturnValue(mockConnection);
    mockPreparedStatement.setInt(1, ids[0]);
    ctrlPreparedStatement.setVoidCallable();
    mockPreparedStatement.addBatch();/*from  w  w  w .  ja  v a 2s. c  o  m*/
    ctrlPreparedStatement.setVoidCallable();
    mockPreparedStatement.setInt(1, ids[1]);
    ctrlPreparedStatement.setVoidCallable();
    mockPreparedStatement.addBatch();
    ctrlPreparedStatement.setVoidCallable();
    mockPreparedStatement.executeBatch();
    ctrlPreparedStatement.setThrowable(sex);
    mockPreparedStatement.close();
    ctrlPreparedStatement.setVoidCallable();

    MockControl ctrlDatabaseMetaData = MockControl.createControl(DatabaseMetaData.class);
    DatabaseMetaData mockDatabaseMetaData = (DatabaseMetaData) ctrlDatabaseMetaData.getMock();
    mockDatabaseMetaData.getDatabaseProductName();
    ctrlDatabaseMetaData.setReturnValue("MySQL");
    mockDatabaseMetaData.supportsBatchUpdates();
    ctrlDatabaseMetaData.setReturnValue(true);

    ctrlConnection.reset();
    mockConnection.prepareStatement(sql);
    ctrlConnection.setReturnValue(mockPreparedStatement);
    mockConnection.getMetaData();
    ctrlConnection.setReturnValue(mockDatabaseMetaData, 2);
    mockConnection.close();
    ctrlConnection.setVoidCallable(2);

    ctrlPreparedStatement.replay();
    ctrlDatabaseMetaData.replay();
    replay();

    BatchPreparedStatementSetter setter = new BatchPreparedStatementSetter() {
        public void setValues(PreparedStatement ps, int i) throws SQLException {
            ps.setInt(1, ids[i]);
        }

        public int getBatchSize() {
            return ids.length;
        }
    };

    try {
        JdbcTemplate template = new JdbcTemplate(mockDataSource);
        template.batchUpdate(sql, setter);
        fail("Should have failed because of SQLException in bulk update");
    } catch (DataAccessException ex) {
        assertTrue("Root cause is SQLException", ex.getCause() == sex);
    }

    ctrlPreparedStatement.verify();
    ctrlDatabaseMetaData.verify();
}

From source file:org.springframework.jdbc.core.JdbcTemplateTests.java

public void testInterruptibleBatchUpdateWithBaseClass() throws Exception {
    final String sql = "UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = ?";
    final int[] ids = new int[] { 100, 200 };
    final int[] rowsAffected = new int[] { 1, 2 };

    MockControl ctrlPreparedStatement = MockControl.createControl(PreparedStatement.class);
    PreparedStatement mockPreparedStatement = (PreparedStatement) ctrlPreparedStatement.getMock();
    mockPreparedStatement.getConnection();
    ctrlPreparedStatement.setReturnValue(mockConnection);
    mockPreparedStatement.setInt(1, ids[0]);
    ctrlPreparedStatement.setVoidCallable();
    mockPreparedStatement.addBatch();// w ww . j a  v  a2  s.  co m
    ctrlPreparedStatement.setVoidCallable();
    mockPreparedStatement.setInt(1, ids[1]);
    ctrlPreparedStatement.setVoidCallable();
    mockPreparedStatement.addBatch();
    ctrlPreparedStatement.setVoidCallable();
    mockPreparedStatement.executeBatch();
    ctrlPreparedStatement.setReturnValue(rowsAffected);
    if (debugEnabled) {
        mockPreparedStatement.getWarnings();
        ctrlPreparedStatement.setReturnValue(null);
    }
    mockPreparedStatement.close();
    ctrlPreparedStatement.setVoidCallable();

    MockControl ctrlDatabaseMetaData = MockControl.createControl(DatabaseMetaData.class);
    DatabaseMetaData mockDatabaseMetaData = (DatabaseMetaData) ctrlDatabaseMetaData.getMock();
    mockDatabaseMetaData.getDatabaseProductName();
    ctrlDatabaseMetaData.setReturnValue("MySQL");
    mockDatabaseMetaData.supportsBatchUpdates();
    ctrlDatabaseMetaData.setReturnValue(true);

    mockConnection.prepareStatement(sql);
    ctrlConnection.setReturnValue(mockPreparedStatement);
    mockConnection.getMetaData();
    ctrlConnection.setReturnValue(mockDatabaseMetaData, 2);

    ctrlPreparedStatement.replay();
    ctrlDatabaseMetaData.replay();
    replay();

    BatchPreparedStatementSetter setter = new AbstractInterruptibleBatchPreparedStatementSetter() {
        protected boolean setValuesIfAvailable(PreparedStatement ps, int i) throws SQLException {
            if (i < ids.length) {
                ps.setInt(1, ids[i]);
                return true;
            } else {
                return false;
            }
        }
    };

    JdbcTemplate template = new JdbcTemplate(mockDataSource, false);

    int[] actualRowsAffected = template.batchUpdate(sql, setter);
    assertTrue("executed 2 updates", actualRowsAffected.length == 2);
    assertEquals(rowsAffected[0], actualRowsAffected[0]);
    assertEquals(rowsAffected[1], actualRowsAffected[1]);

    ctrlPreparedStatement.verify();
    ctrlDatabaseMetaData.verify();
}