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

@Override
public void migrate(Connection connection) throws Exception {
    connection.setAutoCommit(false);//  ww  w .  j  a  va  2  s. c  o  m

    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.springframework.batch.item.database.JdbcBatchItemWriter.java

@SuppressWarnings("unchecked")
@Override//  www .  ja  v  a 2s  . 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);
                }
            }
        }
    }
}

From source file:org.springframework.jdbc.object.BatchSqlUpdateTests.java

private void doTestBatchUpdate(boolean flushThroughBatchSize) 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.setObject(1, new Integer(ids[0]), Types.INTEGER);
    ctrlPreparedStatement.setVoidCallable();
    mockPreparedStatement.addBatch();
    ctrlPreparedStatement.setVoidCallable();
    mockPreparedStatement.setObject(1, new Integer(ids[1]), Types.INTEGER);
    ctrlPreparedStatement.setVoidCallable();
    mockPreparedStatement.addBatch();/*from  w  w  w .j av  a  2s.com*/
    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.supportsBatchUpdates();
    ctrlDatabaseMetaData.setReturnValue(true);

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

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

    BatchSqlUpdate update = new BatchSqlUpdate(mockDataSource, sql);
    update.declareParameter(new SqlParameter(Types.INTEGER));
    if (flushThroughBatchSize) {
        update.setBatchSize(2);
    }

    update.update(ids[0]);
    update.update(ids[1]);

    if (flushThroughBatchSize) {
        assertEquals(0, update.getQueueCount());
        assertEquals(2, update.getRowsAffected().length);
    } else {
        assertEquals(2, update.getQueueCount());
        assertEquals(0, update.getRowsAffected().length);
    }

    int[] actualRowsAffected = update.flush();
    assertEquals(0, update.getQueueCount());

    if (flushThroughBatchSize) {
        assertTrue("flush did not execute updates", actualRowsAffected.length == 0);
    } else {
        assertTrue("executed 2 updates", actualRowsAffected.length == 2);
        assertEquals(rowsAffected[0], actualRowsAffected[0]);
        assertEquals(rowsAffected[1], actualRowsAffected[1]);
    }

    actualRowsAffected = update.getRowsAffected();
    assertTrue("executed 2 updates", actualRowsAffected.length == 2);
    assertEquals(rowsAffected[0], actualRowsAffected[0]);
    assertEquals(rowsAffected[1], actualRowsAffected[1]);

    update.reset();
    assertEquals(0, update.getRowsAffected().length);

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

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);//from  w  w w  .j  a va 2 s .c o  m
        ps.setString(c, dependency.isPre().toString().toUpperCase());
        ps.addBatch();
    }
    if (!dependencies.isEmpty()) {
        ps.executeBatch();
    }
}

From source file:com.dsf.dbxtract.cdc.AppJournalDeleteTest.java

/**
 * Rigourous Test :-)//www.j ava  2  s.  c  o m
 * 
 * @throws Exception
 *             in case of any error
 */
@Test(timeOut = 120000)
public void testAppWithJournalDelete() throws Exception {

    final Config config = new Config(configFile);

    BasicDataSource ds = new BasicDataSource();
    Source source = config.getDataSources().getSources().get(0);
    ds.setDriverClassName(source.getDriver());
    ds.setUsername(source.getUser());
    ds.setPassword(source.getPassword());
    ds.setUrl(source.getConnection());

    // prepara os dados
    Connection conn = ds.getConnection();

    conn.createStatement().execute("truncate table test");
    conn.createStatement().execute("truncate table j$test");

    // Carrega os dados de origem
    PreparedStatement ps = conn.prepareStatement("insert into test (key1,key2,data) values (?,?,?)");
    for (int i = 0; i < TEST_SIZE; i++) {
        if ((i % 100) == 0) {
            ps.executeBatch();
        }
        ps.setInt(1, i);
        ps.setInt(2, i);
        ps.setInt(3, (int) Math.random() * 500);
        ps.addBatch();
    }
    ps.executeBatch();
    ps.close();

    app = new App(config);
    app.start();

    Assert.assertEquals(config.getHandlers().iterator().next().getStrategy(), JournalStrategy.DELETE);

    // Popula as tabelas de journal
    ps = conn.prepareStatement("insert into j$test (key1,key2) values (?,?)");
    for (int i = 0; i < TEST_SIZE; i++) {
        if ((i % 500) == 0) {
            ps.executeBatch();
        }
        ps.setInt(1, i);
        ps.setInt(2, i);
        ps.addBatch();
    }
    ps.executeBatch();
    ps.close();

    while (true) {
        TimeUnit.MILLISECONDS.sleep(500);

        ResultSet rs = conn.createStatement().executeQuery("select count(*) from j$test");
        if (rs.next()) {
            long count = rs.getLong(1);
            System.out.println("remaining journal rows: " + count);
            rs.close();
            if (count == 0L)
                break;
        }
    }
    conn.close();
    ds.close();
}

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

@Override
public void deleteOperations() throws ArchivalDAOException {
    PreparedStatement stmt = null;
    try {/*  www .ja v  a2  s.  c o m*/
        Connection conn = ArchivalDestinationDAOFactory.getConnection();
        conn.setAutoCommit(false);
        String sql = "DELETE FROM DM_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 operations", e);
    } finally {
        ArchivalDAOUtil.cleanupResources(stmt);
    }
}

From source file:org.apache.sqoop.mapreduce.SQLServerExportDBExecThread.java

/**
 * Generate the PreparedStatement object that will be used to insert records
 * to the database. All parameterized fields of the PreparedStatement must
 * be set in this method as well; this is usually based on the records
 * collected from the user in the records list
 *
 * This method must be overridden by sub-classes to define the database
 * operation to be executed for user records
 *//* ww  w  .  java 2  s.com*/
@Override
protected PreparedStatement getPreparedStatement(List<SqoopRecord> records) throws SQLException {

    PreparedStatement stmt = null;
    Connection conn = getConnection();

    // Create a PreparedStatement object to insert all records
    stmt = conn.prepareStatement(getInsertStatement(records.size()));

    // Inject the record parameters into the VALUES clauses.
    for (SqoopRecord record : records) {
        record.write(stmt, 0);
        stmt.addBatch();
    }

    return stmt;
}

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 www . j a v a  2s  .  c o  m*/
        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:mayoapp.migrations.V0400_2024__move_order_items_to_own_table.java

@Override
public void migrate(Connection connection) throws Exception {
    connection.setAutoCommit(false);//from   w  w w  . j  a  va 2 s . c  o m

    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.hlc.quickdb.executor.statement.PreparedStatementHandler.java

public void batchParameterize(Statement statement) throws SQLException {
    PreparedStatement preparedStatement = ((PreparedStatement) statement);
    if (logger.isDebugEnabled()) {
        logger.debug(sql);/*  w w w . j  a  va  2  s  .  co  m*/
    }
    Iterator<StatementParameter[]> iterator = sql.iterator();
    StatementParameter[] temps = null;
    while (iterator.hasNext()) {
        temps = iterator.next();
        for (StatementParameter item : temps) {
            item.parameterize(statement);
        }
        preparedStatement.addBatch();
    }
}