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:mayoapp.migrations.V0075_0003__update_tenant_configurations.java

@Override
public void migrate(Connection connection) throws Exception {
    connection.setAutoCommit(false);//from w w w  .  j a v  a  2 s  . com

    Statement queryIdsStatement = connection.createStatement();
    ResultSet tenants = queryIdsStatement.executeQuery("SELECT entity_id, slug, configuration FROM entity "
            + "INNER JOIN tenant ON entity.id = tenant.entity_id");

    Map<UUID, ConfigurationAndName> tenantsData = Maps.newHashMap();

    while (tenants.next()) {
        String json = tenants.getString("configuration");
        String name = tenants.getString("slug");
        ObjectMapper objectMapper = new ObjectMapper();
        Map<String, Object> configuration = objectMapper.readValue(json,
                new TypeReference<Map<String, Object>>() {
                });
        if (configuration.containsKey("general")) {
            Map<String, Object> generalConfiguration = (Map<String, Object>) configuration.get("general");
            if (generalConfiguration.containsKey("name")) {
                name = (String) generalConfiguration.get("name");
                ((Map<String, Object>) configuration.get("general")).remove("name");
                json = objectMapper.writeValueAsString(configuration);
            }

        }
        ConfigurationAndName configurationAndName = new ConfigurationAndName(json, name);
        tenantsData.put((UUID) tenants.getObject("entity_id"), configurationAndName);
    }

    queryIdsStatement.close();

    PreparedStatement statement = connection
            .prepareStatement("UPDATE tenant SET name=?, configuration=? WHERE entity_id =?");

    for (UUID id : tenantsData.keySet()) {
        statement.setString(1, tenantsData.get(id).getName());
        statement.setString(2, tenantsData.get(id).getConfiguration());
        statement.setObject(3, new PG_UUID(id));
        statement.addBatch();
    }

    try {
        statement.executeBatch();
    } finally {
        statement.close();
    }
}

From source file:org.wso2.carbon.device.mgt.core.archival.dao.impl.DataDeletionDAOImpl.java

@Override
public void deleteEnrolmentMappings() throws ArchivalDAOException {
    PreparedStatement stmt = null;
    try {//from  w w w  . j  a  v  a 2 s .  com
        Connection conn = ArchivalDestinationDAOFactory.getConnection();
        conn.setAutoCommit(false);
        String sql = "DELETE FROM DM_ENROLMENT_OP_MAPPING_ARCH WHERE ARCHIVED_AT < DATE_SUB(NOW(), INTERVAL ? DAY)";
        stmt = conn.prepareStatement(sql);
        stmt.setInt(1, this.retentionPeriod);
        stmt.addBatch();
        stmt.executeBatch();
        conn.commit();
    } catch (SQLException e) {
        throw new ArchivalDAOException("Error occurred while deleting enrolment mappings", e);
    } finally {
        ArchivalDAOUtil.cleanupResources(stmt);
    }
}

From source file:org.schedoscope.metascope.tasks.repository.mysql.impl.TableDependencyEntityMySQLRepository.java

@Override
public void insertOrUpdate(Connection connection, List<TableDependencyEntity> tableDependencies) {
    String insertDependencySql = "insert into table_dependency_entity ("
            + JDBCUtil.getDatabaseColumnsForClass(TableDependencyEntity.class) + ") values ("
            + JDBCUtil.getValuesCountForClass(TableDependencyEntity.class) + ") " + "on duplicate key update "
            + MySQLUtil.getOnDuplicateKeyString(TableDependencyEntity.class);
    PreparedStatement stmt = null;
    try {//from w w  w. ja  va  2 s .c  o  m
        int batch = 0;
        connection.setAutoCommit(false);
        stmt = connection.prepareStatement(insertDependencySql);
        for (TableDependencyEntity dependency : tableDependencies) {
            stmt.setString(1, dependency.getFqdn());
            stmt.setString(2, dependency.getDependencyFqdn());
            stmt.addBatch();
            batch++;
            if (batch % 1024 == 0) {
                stmt.executeBatch();
            }
        }
        stmt.executeBatch();
        connection.commit();
        connection.setAutoCommit(true);
    } catch (SQLException e) {
        LOG.error("Could not save table dependency", e);
    } finally {
        DbUtils.closeQuietly(stmt);
    }
}

From source file:mayoapp.migrations.V0400_2024__move_order_items_to_own_table.java

@Override
public void migrate(Connection connection) throws Exception {
    connection.setAutoCommit(false);/*ww w. j a  v  a2s . c  om*/

    Statement queryStatement = connection.createStatement();
    ResultSet data = queryStatement.executeQuery("SELECT * from purchase_order");

    List<Order> orders = Lists.newArrayList();
    List<OrderItem> orderItems = Lists.newArrayList();

    ObjectMapper mapper = new ObjectMapper();

    while (data.next()) {
        Order order = new Order();
        order.setId((UUID) data.getObject("entity_id"));

        String orderDataString = data.getString("order_data");
        Map<String, Object> orderData = mapper.readValue(orderDataString,
                new TypeReference<Map<String, Object>>() {
                });

        List<Map<String, Object>> items = (List<Map<String, Object>>) orderData.get("items");

        for (Map<String, Object> item : items) {
            OrderItem orderItem = new OrderItem();

            orderItem.setId(UUID.randomUUID());
            orderItem.setOrderId(order.getId());

            if (item.containsKey("id") && String.class.isAssignableFrom(item.get("id").getClass())) {
                orderItem.setPurchasableId(UUID.fromString((String) item.get("id")));
            }
            orderItem.setType((String) item.get("type"));
            orderItem.setTitle((String) item.get("title"));
            orderItem.setQuantity(((Integer) item.get("quantity")).longValue());
            orderItem.setUnitPrice(BigDecimal.valueOf((Double) item.get("unitPrice")));
            orderItem.setItemTotal(BigDecimal.valueOf((Double) item.get("itemTotal")));
            if (item.containsKey("vatRate")) {
                orderItem.setVatRate(BigDecimal.valueOf((Double) item.get("vatRate")));
            }

            if (item.containsKey("addons")) {
                orderItem.addData("addons", convertAddonsToMap((List<Map<String, Object>>) item.get("addons")));
            }

            orderItems.add(orderItem);
        }

        orderData.remove("items");
        order.setOrderData(orderData);
        orders.add(order);
    }

    queryStatement.close();

    // 1. Update orders

    PreparedStatement updateOrders = connection
            .prepareStatement("UPDATE purchase_order SET order_data = CAST (? AS json) WHERE entity_id =?");

    for (Order order : orders) {
        updateOrders.setObject(1, mapper.writeValueAsString(order.getOrderData()));
        updateOrders.setObject(2, order.getId());
        updateOrders.addBatch();
    }

    updateOrders.executeBatch();

    // 2. Insert items

    PreparedStatement insertItems = connection.prepareStatement(
            "INSERT INTO purchase_order_item (id, order_id, purchasable_id, type, title, quantity, unit_price, "
                    + "item_total, vat_rate, data) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, CAST (? as json))");

    for (OrderItem item : orderItems) {
        insertItems.setObject(1, item.getId());
        insertItems.setObject(2, item.getOrderId());
        insertItems.setObject(3, item.getPurchasableId());
        insertItems.setString(4, item.getType());
        insertItems.setString(5, item.getTitle());
        insertItems.setLong(6, item.getQuantity());
        insertItems.setBigDecimal(7, item.getUnitPrice());
        insertItems.setBigDecimal(8, item.getItemTotal());
        insertItems.setBigDecimal(9, item.getVatRate());
        insertItems.setString(10, mapper.writeValueAsString(item.getData()));
        insertItems.addBatch();
    }

    insertItems.executeBatch();
}

From source file:org.wso2.carbon.device.mgt.core.archival.dao.impl.DataDeletionDAOImpl.java

@Override
public void deleteNotifications() throws ArchivalDAOException {
    PreparedStatement stmt = null;
    try {//from   ww  w .jav a 2 s  . com
        Connection conn = ArchivalDestinationDAOFactory.getConnection();
        conn.setAutoCommit(false);
        String sql = "DELETE FROM DM_NOTIFICATION_ARCH"
                + "  WHERE ARCHIVED_AT < DATE_SUB(NOW(), INTERVAL ? DAY)";
        stmt = conn.prepareStatement(sql);
        stmt.setInt(1, this.retentionPeriod);
        stmt.addBatch();
        stmt.executeBatch();
        conn.commit();
    } catch (SQLException e) {
        throw new ArchivalDAOException("Error occurred while deleting notifications", e);
    } finally {
        ArchivalDAOUtil.cleanupResources(stmt);
    }
}

From source file:de.is24.infrastructure.gridfs.http.metadata.generation.PrimaryDbGenerator.java

private void writeRequires(PreparedStatement ps, int pkgKey, List<YumPackageRequirement> dependencies)
        throws SQLException {
    for (YumPackageRequirement dependency : dependencies) {
        int c = fillStatementForYumPackageFormatEntry(ps, dependency, 1);
        ps.setInt(c++, pkgKey);/*  ww w.  j ava 2  s  . c om*/
        ps.setString(c, dependency.isPre().toString().toUpperCase());
        ps.addBatch();
    }
    if (!dependencies.isEmpty()) {
        ps.executeBatch();
    }
}

From source file:org.wso2.carbon.device.mgt.core.archival.dao.impl.DataDeletionDAOImpl.java

@Override
public void deleteCommandOperations() throws ArchivalDAOException {
    PreparedStatement stmt = null;
    try {//  w  w  w . ja va 2s. c o m
        Connection conn = ArchivalDestinationDAOFactory.getConnection();
        conn.setAutoCommit(false);
        String sql = "DELETE FROM DM_COMMAND_OPERATION_ARCH"
                + "  WHERE ARCHIVED_AT < DATE_SUB(NOW(), INTERVAL ? DAY)";
        stmt = conn.prepareStatement(sql);
        stmt.setInt(1, this.retentionPeriod);
        stmt.addBatch();
        stmt.executeBatch();
        conn.commit();
    } catch (SQLException e) {
        throw new ArchivalDAOException("Error occurred while deleting command operations", e);
    } finally {
        ArchivalDAOUtil.cleanupResources(stmt);
    }
}

From source file:org.wso2.carbon.device.mgt.core.archival.dao.impl.DataDeletionDAOImpl.java

@Override
public void deleteProfileOperations() throws ArchivalDAOException {
    PreparedStatement stmt = null;
    try {//w  ww .  ja  va  2  s .  c o  m
        Connection conn = ArchivalDestinationDAOFactory.getConnection();
        conn.setAutoCommit(false);
        String sql = "DELETE FROM DM_PROFILE_OPERATION_ARCH"
                + "  WHERE ARCHIVED_AT < DATE_SUB(NOW(), INTERVAL ? DAY)";
        stmt = conn.prepareStatement(sql);
        stmt.setInt(1, this.retentionPeriod);
        stmt.addBatch();
        stmt.executeBatch();
        conn.commit();
    } catch (SQLException e) {
        throw new ArchivalDAOException("Error occurred while deleting profile operations", e);
    } finally {
        ArchivalDAOUtil.cleanupResources(stmt);
    }
}

From source file:com.pactera.edg.am.metamanager.extractor.dao.helper.CreateMetadataAlterHelper.java

public Object doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
    startTime = AdapterExtractorContext.getInstance().getGlobalTime();
    taskInstanceId = AdapterExtractorContext.getInstance().getTaskInstanceId();

    for (MMMetaModel metaModel : metaModels) {
        batchLoadCreate(ps, metaModel);/* w  w w .j a  v a  2s  .co m*/
    }

    if (super.count % super.batchSize != 0) {
        ps.executeBatch();
        ps.clearBatch();

    }
    return null;
}

From source file:org.springframework.batch.item.database.JdbcBatchItemWriter.java

@SuppressWarnings("unchecked")
@Override/*  w w w.  java2  s . c  o m*/
public void write(final List<? extends T> items) throws Exception {

    if (!items.isEmpty()) {

        if (logger.isDebugEnabled()) {
            logger.debug("Executing batch with " + items.size() + " items.");
        }

        int[] updateCounts;

        if (usingNamedParameters) {
            if (items.get(0) instanceof Map && this.itemSqlParameterSourceProvider == null) {
                updateCounts = namedParameterJdbcTemplate.batchUpdate(sql,
                        items.toArray(new Map[items.size()]));
            } else {
                SqlParameterSource[] batchArgs = new SqlParameterSource[items.size()];
                int i = 0;
                for (T item : items) {
                    batchArgs[i++] = itemSqlParameterSourceProvider.createSqlParameterSource(item);
                }
                updateCounts = namedParameterJdbcTemplate.batchUpdate(sql, batchArgs);
            }
        } else {
            updateCounts = namedParameterJdbcTemplate.getJdbcOperations().execute(sql,
                    new PreparedStatementCallback<int[]>() {
                        @Override
                        public int[] doInPreparedStatement(PreparedStatement ps)
                                throws SQLException, DataAccessException {
                            for (T item : items) {
                                itemPreparedStatementSetter.setValues(item, ps);
                                ps.addBatch();
                            }
                            return ps.executeBatch();
                        }
                    });
        }

        if (assertUpdates) {
            for (int i = 0; i < updateCounts.length; i++) {
                int value = updateCounts[i];
                if (value == 0) {
                    throw new EmptyResultDataAccessException("Item " + i + " of " + updateCounts.length
                            + " did not update any rows: [" + items.get(i) + "]", 1);
                }
            }
        }
    }
}