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.flexive.ejb.beans.BriefcaseEngineBean.java

/**
 * {@inheritDoc}//w  w w. ja  v a 2  s .c o m
 */
@Override
@TransactionAttribute(TransactionAttributeType.REQUIRED)
public void addItems(long id, Collection<FxPK> contents) throws FxApplicationException {
    Connection con = null;
    PreparedStatement stmt = null;
    final Briefcase br = load(id);
    checkEditBriefcase(br);
    try {
        con = Database.getDbConnection();

        // keep lookup table of existing items to avoid adding an item twice
        final Set<Long> existingItems = new HashSet<Long>();
        final long[] items = getItems(id);
        for (long item : items) {
            existingItems.add(item);
        }

        stmt = con.prepareStatement("SELECT MAX(pos) FROM " + TBL_BRIEFCASE_DATA + " WHERE briefcase_id=?");
        stmt.setLong(1, id);
        final ResultSet rs = stmt.executeQuery();
        int pos = rs.next() ? rs.getInt(1) : 0;
        stmt.close();
        stmt = con.prepareStatement(
                "INSERT INTO " + TBL_BRIEFCASE_DATA + "(briefcase_id, id, pos, amount) VALUES (?, ?, ?, 1)");
        stmt.setLong(1, id);
        for (FxPK pk : contents) {
            if (!existingItems.contains(pk.getId())) {
                stmt.setLong(2, pk.getId());
                stmt.setLong(3, ++pos);
                stmt.addBatch();
                existingItems.add(pk.getId());
            }
        }
        stmt.executeBatch();
    } catch (Exception e) {
        EJBUtils.rollback(ctx);
        throw new FxUpdateException(LOG, e, "ex.briefcase.addItems", br.getName(), e);
    } finally {
        closeObjects(BriefcaseEngineBean.class, con, stmt);
    }
}

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

private PreparedStatement insertPhones(Connection con, EntityId entityId, Map<String, Phone> phones)
        throws SQLException {
    PreparedStatement ps;
    ps = con.prepareStatement(/*from w ww.ja va  2 s.  c o  m*/
            "INSERT INTO Phone (phone_entity_id, phone_label, phone_number) " + "VALUES (?, ?, ?)");
    for (Entry<String, Phone> entry : phones.entrySet()) {
        ps.setInt(1, entityId.getId());
        ps.setString(2, entry.getKey());
        ps.setString(3, entry.getValue().getNumber());
        ps.addBatch();
    }
    ps.executeBatch();
    return ps;
}

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

private PreparedStatement insertEmails(Connection con, EntityId entityId, Map<String, EmailAddress> emails)
        throws SQLException {
    PreparedStatement ps;
    ps = con.prepareStatement(/*  ww  w .  j a va 2 s. c o m*/
            "INSERT INTO Email (email_entity_id, email_label, email_address) " + "VALUES (?, ?, ?)");
    for (Entry<String, EmailAddress> entry : emails.entrySet()) {
        ps.setInt(1, entityId.getId());
        ps.setString(2, entry.getKey());
        ps.setString(3, entry.getValue().get());
        ps.addBatch();
    }
    ps.executeBatch();
    return ps;
}

From source file:com.mmnaseri.dragonfly.data.impl.DefaultDataAccess.java

private PreparedStatement internalExecuteUpdate(Statement statement, Map<String, Object> values) {
    waitForSessionInitialization();//  www . ja  v  a2  s .c  o  m
    if (isInBatchMode()) {
        if (batchOperation.get() == null) {
            batchOperation.set(new Stack<BatchOperationDescriptor>());
        }
        final List<BatchOperationDescriptor> operationDescriptors = batchOperation.get();
        boolean firstStep = operationDescriptors.isEmpty();
        if (!firstStep) {
            String sql = statement.getSql();
            if (statement.isDynamic()) {
                sql = new FreemarkerSecondPassStatementBuilder(statement, session.getDatabaseDialect(), values)
                        .getStatement(statement.getTableMetadata()).getSql();
            }
            firstStep = !sql.equals(operationDescriptors.get(operationDescriptors.size() - 1).getSql());
        }
        final PreparedStatement preparedStatement;
        if (!firstStep) {
            preparedStatement = operationDescriptors.get(operationDescriptors.size() - 1)
                    .getPreparedStatement();
            statementPreparator.prepare(preparedStatement, statement.getTableMetadata(), values,
                    operationDescriptors.get(operationDescriptors.size() - 1).getSql());
        } else {
            final BatchOperationDescriptor operationDescriptor = getPreparedStatement(statement, values);
            operationDescriptors.add(operationDescriptor);
            preparedStatement = operationDescriptor.getPreparedStatement();
        }
        try {
            preparedStatement.addBatch();
        } catch (SQLException e) {
            throw new BatchOperationExecutionError("Failed to add batch operation", e);
        }
        return preparedStatement;
    } else {
        final PreparedStatement preparedStatement = getPreparedStatement(statement, values)
                .getPreparedStatement();
        try {
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            throw new UnsuccessfulOperationError("Failed to execute update", e);
        }
        return preparedStatement;
    }
}

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 {//w  w  w  .  j av  a  2s .  c om
        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);
    }
}

From source file:org.wso2.carbon.cluster.coordinator.rdbms.RDBMSCommunicationBusContextImpl.java

@Override
public void insertRemovedNodeDetails(String removedMember, String groupId, List<String> clusterNodes,
        Map<String, Object> removedPropertiesMap) throws ClusterCoordinationException {
    Connection connection = null;
    PreparedStatement storeRemovedMembersPreparedStatement = null;
    String task = "Storing removed member: " + removedMember + " in group " + groupId;
    try {/*from   w ww . ja v a  2  s . co m*/
        connection = getConnection();
        storeRemovedMembersPreparedStatement = connection
                .prepareStatement(RDBMSConstants.PS_INSERT_REMOVED_MEMBER_DETAILS);
        ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
        ObjectOutputStream objectOutputStream = new ObjectOutputStream(byteArrayOutputStream);
        objectOutputStream.writeObject(removedPropertiesMap);
        objectOutputStream.flush();
        byteArrayOutputStream.flush();
        objectOutputStream.close();
        byteArrayOutputStream.close();
        byte[] propertiesMapAsBytes = byteArrayOutputStream.toByteArray();
        // ByteArrayInputStream byteArrayInputStream = new ByteArrayInputStream(propertiesMapAsBytes);
        for (String clusterNode : clusterNodes) {
            storeRemovedMembersPreparedStatement.setString(1, clusterNode);
            storeRemovedMembersPreparedStatement.setString(2, groupId);
            storeRemovedMembersPreparedStatement.setString(3, removedMember);
            storeRemovedMembersPreparedStatement.setBinaryStream(4,
                    new ByteArrayInputStream(propertiesMapAsBytes));
            storeRemovedMembersPreparedStatement.addBatch();
        }
        storeRemovedMembersPreparedStatement.executeBatch();

        connection.commit();
    } catch (SQLException e) {
        rollback(connection, task);
        throw new ClusterCoordinationException(
                "Error storing removed member: " + removedMember + " in group " + groupId, e);
    } catch (IOException e) {
        throw new ClusterCoordinationException("Error while inserting removed node data", e);
    } finally {
        close(storeRemovedMembersPreparedStatement, task);
        close(connection, task);
    }
}

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

@Override
public Policy addPolicyToDevice(List<Device> devices, Policy policy) throws PolicyManagerDAOException {
    Connection conn;//w ww .  j a v  a  2  s  . co m
    PreparedStatement stmt = null;
    try {
        conn = this.getConnection();
        String query = "INSERT INTO DM_DEVICE_POLICY (DEVICE_ID, POLICY_ID, ENROLMENT_ID, DEVICE) VALUES (?, ?, "
                + "?, ?)";
        stmt = conn.prepareStatement(query);
        for (Device device : devices) {
            stmt.setInt(1, device.getId());
            stmt.setInt(2, policy.getId());
            stmt.setInt(3, device.getEnrolmentInfo().getId());
            stmt.setBytes(4, PolicyManagerUtil.getBytes(device));
            stmt.addBatch();
        }
        stmt.executeBatch();
    } catch (SQLException e) {
        throw new PolicyManagerDAOException(
                "Error occurred while adding the device ids  with policy to " + "database", e);
    } catch (IOException e) {
        throw new PolicyManagerDAOException("Error occurred while getting the byte array from device.", e);
    } finally {
        PolicyManagementDAOUtil.cleanupResources(stmt, null);
    }
    return policy;
}

From source file:lib.JdbcTemplate.java

@Override
public <T> int[][] batchUpdate(String sql, final Collection<T> batchArgs, final int batchSize,
        final ParameterizedPreparedStatementSetter<T> pss) throws DataAccessException {

    if (logger.isDebugEnabled()) {
        logger.debug("Executing SQL batch update [" + sql + "] with a batch size of " + batchSize);
    }/*from w w  w  . j  av a2 s  . c  o m*/
    return execute(sql, new PreparedStatementCallback<int[][]>() {
        @Override
        public int[][] doInPreparedStatement(PreparedStatement ps) throws SQLException {
            List<int[]> rowsAffected = new ArrayList<int[]>();
            try {
                boolean batchSupported = true;
                if (!JdbcUtils.supportsBatchUpdates(ps.getConnection())) {
                    batchSupported = false;
                    logger.warn(
                            "JDBC Driver does not support Batch updates; resorting to single statement execution");
                }
                int n = 0;
                for (T obj : batchArgs) {
                    pss.setValues(ps, obj);
                    n++;
                    if (batchSupported) {
                        ps.addBatch();
                        if (n % batchSize == 0 || n == batchArgs.size()) {
                            if (logger.isDebugEnabled()) {
                                int batchIdx = (n % batchSize == 0) ? n / batchSize : (n / batchSize) + 1;
                                int items = n - ((n % batchSize == 0) ? n / batchSize - 1 : (n / batchSize))
                                        * batchSize;
                                logger.debug(
                                        "Sending SQL batch update #" + batchIdx + " with " + items + " items");
                            }
                            rowsAffected.add(ps.executeBatch());
                        }
                    } else {
                        int i = ps.executeUpdate();
                        rowsAffected.add(new int[] { i });
                    }
                }
                int[][] result = new int[rowsAffected.size()][];
                for (int i = 0; i < result.length; i++) {
                    result[i] = rowsAffected.get(i);
                }
                return result;
            } finally {
                if (pss instanceof ParameterDisposer) {
                    ((ParameterDisposer) pss).cleanupParameters();
                }
            }
        }
    });
}

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 + "]");
    }/*  ww  w . j  av a2  s.c  om*/

    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: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(// www  . j a va2s. c  om
            "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;
}