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:org.openvpms.tools.data.migration.DetailsMigrator.java

private void export(Connection connection, String from, String to, String key) throws SQLException {
    System.out.println("Migrating details from " + from + " to " + to);
    Date start = new Date();
    PreparedStatement select = connection
            .prepareStatement("select " + key + ", details from " + from + " where details is not null");
    PreparedStatement insert = connection.prepareStatement(
            "insert into " + to + " (" + key + ", type, name, value) " + "values (?, ?, ?, ?)");
    ResultSet set = select.executeQuery();
    int input = 0;
    int output = 0;
    int batch = 0;
    while (set.next()) {
        ++input;//from w  w  w.  j  av  a 2  s  . c  o  m
        long id = set.getLong(1);
        String details = set.getString(2);
        if (!StringUtils.isEmpty(details)) {
            DynamicAttributeMap map = (DynamicAttributeMap) stream.fromXML(details);
            Map<String, Serializable> attributes = map.getAttributes();
            for (Map.Entry<String, Serializable> entry : attributes.entrySet()) {
                if (entry.getValue() != null) {
                    // don't insert nulls. See OBF-161
                    String name = entry.getKey();
                    TypedValue value = new TypedValue(entry.getValue());
                    insert.setLong(1, id);
                    insert.setString(2, value.getType());
                    insert.setString(3, name);
                    insert.setString(4, value.getValue());
                    insert.addBatch();
                    ++output;
                }
            }
        }
        ++batch;
        if (batch >= 1000) {
            // commit every 1000 input rows
            insert.executeBatch();
            connection.commit();
            batch = 0;
        }
    }
    if (batch != 0) {
        insert.executeBatch();
        connection.commit();
    }
    set.close();
    insert.close();
    select.close();
    Date end = new Date();
    double elapsed = (end.getTime() - start.getTime()) / 1000;
    System.out.printf("Processed %d rows, generating %d rows in %.2fs\n", input, output, elapsed);
}

From source file:com.edgenius.wiki.installation.UpgradeServiceImpl.java

@SuppressWarnings("unused")
private void up1991To1992() throws Exception {
    log.info("Version 1.991 to 1.992 is upgarding");
    String root = DataRoot.getDataRoot();
    if (FileUtil.exist(root + Server.FILE)) {
        Server server = new Server();
        Properties prop = FileUtil.loadProperties(root + Server.FILE);
        server.syncFrom(prop);//w  ww.j  av a 2  s  .c  o  m
        String type = server.getDbType();

        DBLoader loader = new DBLoader();
        ConnectionProxy con = loader.getConnection(type, server.getDbUrl(), server.getDbSchema(),
                server.getDbUsername(), server.getDbPassword());

        Statement stat = con.createStatement();
        ResultSet rs0 = stat.executeQuery("select PUID from EDG_ROLES where NAME='ROLE_SYS_USERS'");
        if (rs0.next()) {
            int roleID = rs0.getInt(1);
            rs0.close();

            PreparedStatement pre0 = con.prepareStatement(
                    "select distinct USER_PUID from EDG_USER_ROLE where USER_PUID in (select PUID from EDG_USERS where PUID not in "
                            + " (select u.PUID from EDG_USERS as u, EDG_USER_ROLE as r where u.PUID=r.USER_PUID and r.ROLE_PUID=?))");
            pre0.setInt(1, roleID);
            ResultSet rs1 = pre0.executeQuery();

            PreparedStatement pre = con
                    .prepareStatement("insert into EDG_USER_ROLE(USER_PUID,ROLE_PUID) values(?,?)");
            while (rs1.next()) {
                pre.setInt(1, rs1.getInt(1));
                pre.setInt(2, roleID);
                pre.addBatch();
            }
            pre.executeBatch();

            rs1.close();
            pre0.close();
            pre.close();
        }
        stat.close();
        con.close();
    }
}

From source file:org.apache.oozie.util.db.SqlStatement.java

/**
 * Preparing Multiple statements for batch execution.
 *
 * @param conn Connection/* w ww .  ja  va2  s  . co  m*/
 * @param values A list of maps that contains the actual values
 * @return Prepared Statement
 * @throws SQLException
 */
public PreparedStatement prepareForBatch(Connection conn, List<? extends Map<Object, Object>> values,
        PreparedStatement pStmt) throws SQLException {
    String stmt = toString();
    if (forUpdate && !Schema.isHsqlConnection(conn)) {
        stmt += " FOR UPDATE";
    }
    // PreparedStatement pStmt = conn.prepareStatement(stmt);
    for (Map<Object, Object> map : values) {
        getNewStatementWithValues(map).prepare(pStmt);
        pStmt.addBatch();
    }
    return pStmt;
}

From source file:org.qi4j.entitystore.sql.SQLEntityStoreMixin.java

public StateCommitter applyChanges(final EntityStoreUnitOfWork unitofwork, final Iterable<EntityState> states) {
    return new StateCommitter() {

        public void commit() {
            Connection connection = null;
            PreparedStatement insertPS = null;
            PreparedStatement updatePS = null;
            PreparedStatement removePS = null;
            try {
                connection = database.getConnection();
                insertPS = database.prepareInsertEntityStatement(connection);
                updatePS = database.prepareUpdateEntityStatement(connection);
                removePS = database.prepareRemoveEntityStatement(connection);
                for (EntityState state : states) {
                    EntityStatus status = state.status();
                    DefaultEntityState defState = ((SQLEntityState) state).getDefaultEntityState();
                    Long entityPK = ((SQLEntityState) state).getEntityPK();
                    if (EntityStatus.REMOVED.equals(status)) {
                        database.populateRemoveEntityStatement(removePS, entityPK, state.identity());
                        removePS.addBatch();
                    } else {
                        StringWriter writer = new StringWriter();
                        writeEntityState(defState, writer, unitofwork.identity());
                        writer.flush();//from w ww.  j a  v  a 2 s .co m
                        if (EntityStatus.UPDATED.equals(status)) {
                            Long entityOptimisticLock = ((SQLEntityState) state).getEntityOptimisticLock();
                            database.populateUpdateEntityStatement(updatePS, entityPK, entityOptimisticLock,
                                    defState.identity(), writer.toString(), unitofwork.currentTime());
                            updatePS.addBatch();
                        } else if (EntityStatus.NEW.equals(status)) {
                            database.populateInsertEntityStatement(insertPS, entityPK, defState.identity(),
                                    writer.toString(), unitofwork.currentTime());
                            insertPS.addBatch();
                        }
                    }
                }

                removePS.executeBatch();
                insertPS.executeBatch();
                updatePS.executeBatch();

                connection.commit();

            } catch (SQLException sqle) {
                SQLUtil.rollbackQuietly(connection);
                if (LOGGER.isDebugEnabled()) {
                    StringWriter sb = new StringWriter();
                    sb.append(
                            "SQLException during commit, logging nested exceptions before throwing EntityStoreException:\n");
                    SQLException e = sqle;
                    while (e != null) {
                        e.printStackTrace(new PrintWriter(sb, true));
                        e = e.getNextException();
                    }
                    LOGGER.debug(sb.toString());
                }
                throw new EntityStoreException(sqle);
            } catch (RuntimeException re) {
                SQLUtil.rollbackQuietly(connection);
                throw new EntityStoreException(re);
            } finally {
                SQLUtil.closeQuietly(insertPS);
                SQLUtil.closeQuietly(updatePS);
                SQLUtil.closeQuietly(removePS);
                SQLUtil.closeQuietly(connection);
            }
        }

        public void cancel() {
        }

    };
}

From source file:egovframework.rte.bat.core.item.database.EgovJdbcBatchItemWriter.java

/**
 * DB Write   ? setValues /*from w  ww  .jav a2  s  .com*/
 * setValues(item, ps, params, sqlTypes, methodMap) : 
 * setValues(item, ps) :  VO
 */
@SuppressWarnings("unchecked")
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 = null;

        updateCounts = (int[]) simpleJdbcTemplate.getJdbcOperations().execute(sql,
                new PreparedStatementCallback() {
                    public Object doInPreparedStatement(PreparedStatement ps)
                            throws SQLException, DataAccessException {
                        // Parameters   item, ps, params, sqlTypes,methodMap  ?  setValues call
                        //  item, ps  ?  setValues call
                        if (usingParameters) {

                            String[] sqlTypes = reflector.getSqlTypeArray(params, items.get(0));
                            try {
                                reflector.generateGetterMethodMap(params, items.get(0));
                            } catch (Exception e) {
                                // generateGetterMethodMap ?  ? 
                                logger.error(e);
                            }
                            Map<String, Method> methodMap = reflector.getMethodMap();

                            for (T item : items) {

                                itemPreparedStatementSetter.setValues(item, ps, params, sqlTypes, methodMap);
                                ps.addBatch();
                            }
                        } else {
                            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.wso2.carbon.certificate.mgt.core.dao.impl.AbstractCertificateDAOImpl.java

@Override
public void addCertificate(List<Certificate> certificates) throws CertificateManagementDAOException {
    Connection conn;//from   w w w. j  a v a2s .c  o m
    PreparedStatement stmt = null;
    try {
        conn = this.getConnection();
        stmt = conn.prepareStatement(
                "INSERT INTO DM_DEVICE_CERTIFICATE (SERIAL_NUMBER, CERTIFICATE, TENANT_ID, USERNAME)"
                        + " VALUES (?,?,?,?)");
        PrivilegedCarbonContext threadLocalCarbonContext = PrivilegedCarbonContext
                .getThreadLocalCarbonContext();
        String username = threadLocalCarbonContext.getUsername();
        for (Certificate certificate : certificates) {
            // the serial number of the certificate used for its creation is set as its alias.
            String serialNumber = certificate.getSerial();
            if (serialNumber == null || serialNumber.isEmpty()) {
                serialNumber = String.valueOf(certificate.getCertificate().getSerialNumber());
            }
            byte[] bytes = Serializer.serialize(certificate.getCertificate());

            stmt.setString(1, serialNumber);
            stmt.setBytes(2, bytes);
            stmt.setInt(3, certificate.getTenantId());
            stmt.setString(4, username);
            stmt.addBatch();
        }
        stmt.executeBatch();
    } catch (SQLException | IOException e) {
        throw new CertificateManagementDAOException("Error occurred while saving certificates. ", e);
    } finally {
        CertificateManagementDAOUtil.cleanupResources(stmt, null);
    }
}

From source file:com.wso2telco.gsma.authenticators.dao.impl.AttributeConfigDaoImpl.java

@Override
public void saveUserConsentedAttributes(List<UserConsentHistory> userConsentHistory)
        throws NamingException, DBUtilException {
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;//from  w  ww  . java  2  s .c o m
    String query = "INSERT INTO " + TableName.USER_CONSENT + "(consent_id,msisdn,expire_time,consent_status,"
            + "client_id,operator) " + "VALUES (?,?,?,?,?,?);";

    try {
        connection = getConnectDBConnection();
        preparedStatement = connection.prepareStatement(query);
        for (UserConsentHistory userConsentHistory1 : userConsentHistory) {

            preparedStatement.setInt(1, userConsentHistory1.getConsentId());
            preparedStatement.setString(2, userConsentHistory1.getMsisdn());
            preparedStatement.setString(3, userConsentHistory1.getConsentExpireTime());
            preparedStatement.setBoolean(4, Boolean.parseBoolean(userConsentHistory1.getConsentStatus()));
            preparedStatement.setString(5, userConsentHistory1.getClientId());
            preparedStatement.setString(6, userConsentHistory1.getOperatorName());
            preparedStatement.addBatch();
        }

        if (log.isDebugEnabled()) {
            log.debug("Query in method saveUserConsentedAttributes:" + preparedStatement);
        }

        preparedStatement.executeBatch();

    } catch (SQLException e) {
        log.error("Exception occurred while inserting data to the database for history : "
                + userConsentHistory.toString() + " :" + e.getMessage());
        throw new DBUtilException(e.getMessage(), e);
    } finally {
        IdentityDatabaseUtil.closeAllConnections(connection, resultSet, preparedStatement);
    }
}

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

@Override
public void moveCommandOperations() throws ArchivalDAOException {
    Statement stmt = null;/*  ww  w  .j  a v a 2  s.co  m*/
    PreparedStatement stmt2 = null;
    Statement stmt3 = null;
    ResultSet rs = null;
    try {
        Connection conn = ArchivalSourceDAOFactory.getConnection();
        String sql = "SELECT * FROM DM_COMMAND_OPERATION WHERE OPERATION_ID IN "
                + "(SELECT ID FROM DM_ARCHIVED_OPERATIONS)";
        stmt = this.createMemoryEfficientStatement(conn);
        rs = stmt.executeQuery(sql);

        Connection conn2 = ArchivalDestinationDAOFactory.getConnection();

        sql = "INSERT INTO DM_COMMAND_OPERATION_ARCH VALUES(?,?,?)";
        stmt2 = conn2.prepareStatement(sql);

        int count = 0;
        while (rs.next()) {
            stmt2.setInt(1, rs.getInt("OPERATION_ID"));
            stmt2.setInt(2, rs.getInt("ENABLED"));
            stmt2.setTimestamp(3, this.currentTimestamp);
            stmt2.addBatch();

            if (++count % batchSize == 0) {
                stmt2.executeBatch();
            }
        }
        stmt2.executeBatch();
        if (log.isDebugEnabled()) {
            log.debug(count + " [COMMAND_OPERATION] Records copied to the archival table. Starting deletion");
        }
        sql = "DELETE FROM DM_COMMAND_OPERATION"
                + "  WHERE OPERATION_ID IN (SELECT ID FROM DM_ARCHIVED_OPERATIONS)";
        stmt3 = conn.createStatement();
        int affected = stmt3.executeUpdate(sql);
        if (log.isDebugEnabled()) {
            log.debug(affected + " Rows deleted");
        }
    } catch (SQLException e) {
        throw new ArchivalDAOException("Error occurred while moving command operations", e);
    } finally {
        ArchivalDAOUtil.cleanupResources(stmt, rs);
        ArchivalDAOUtil.cleanupResources(stmt2);
        ArchivalDAOUtil.cleanupResources(stmt3);
    }
}

From source file:org.jasig.ssp.util.importer.job.staging.SqlServerStagingTableWriter.java

@Override
public void write(final List<? extends RawItem> items) {

    NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
    String fileName = items.get(0).getResource().getFilename();
    final String[] tableName = fileName.split("\\.");

    Integer batchStart = (Integer) (stepExecution.getExecutionContext().get("batchStart") == null ? null
            : stepExecution.getExecutionContext().get("batchStart"));
    Integer batchStop = (Integer) (stepExecution.getExecutionContext().get("batchStop") == null ? null
            : stepExecution.getExecutionContext().get("batchStop"));
    Object currentEntity = stepExecution.getExecutionContext().get("currentEntity");

    if (currentEntity == null || !currentEntity.equals(tableName[0])) {
        batchStart = 0;//w  ww  .j  a v  a 2s  .  c  o  m
        batchStop = items.size() - 1;
        currentEntity = tableName[0];
        stepExecution.getExecutionContext().put("currentEntity", currentEntity);
        stepExecution.getExecutionContext().put("batchStart", batchStart);
        stepExecution.getExecutionContext().put("batchStop", batchStop);
    } else {
        batchStart = batchStop + 1;
        batchStop = (Integer) batchStart + items.size() - 1;
        stepExecution.getExecutionContext().put("batchStart", batchStart);
        stepExecution.getExecutionContext().put("batchStop", batchStop);
    }

    RawItem firstItem = items.get(0);
    Resource firstItemResource = firstItem.getResource();

    if (currentResource == null || !(this.currentResource.equals(firstItemResource))) {
        this.orderedHeaders = writeHeader(firstItem);
        this.currentResource = firstItemResource;
    }

    StringBuilder insertSql = new StringBuilder();
    insertSql.append("INSERT INTO stg_" + tableName[0] + " (batch_id,");
    StringBuilder valuesSqlBuilder = new StringBuilder();
    valuesSqlBuilder.append(" VALUES (?,");
    for (String header : this.orderedHeaders) {
        insertSql.append(header).append(",");
        valuesSqlBuilder.append("?").append(",");
    }
    insertSql.setLength(insertSql.length() - 1); // trim comma
    valuesSqlBuilder.setLength(valuesSqlBuilder.length() - 1); // trim comma
    insertSql.append(")");
    valuesSqlBuilder.append(");");
    insertSql.append(valuesSqlBuilder);

    final AtomicInteger batchStartRef = new AtomicInteger(batchStart);
    final String sql = insertSql.toString();
    jdbcTemplate.getJdbcOperations().execute(sql, new PreparedStatementCallback() {
        @Override
        public Object doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
            for (RawItem item : items) {
                final List<Object> paramsForLog = new ArrayList(orderedHeaders.length);
                int counter = 1;
                paramsForLog.add(batchStartRef.get());
                StatementCreatorUtils.setParameterValue(ps, counter, SqlTypeValue.TYPE_UNKNOWN,
                        batchStartRef.getAndIncrement());
                counter++;
                for (String header : orderedHeaders) {
                    final Map<String, String> record = item.getRecord();
                    String value = record.get(header);
                    final Integer sqlType = metadataRepository.getRepository().getColumnMetadataRepository()
                            .getColumnMetadata(new ColumnReference(tableName[0], header)).getJavaSqlType();
                    paramsForLog.add(value);
                    StatementCreatorUtils.setParameterValue(ps, counter, sqlType, value);
                    counter++;
                }
                sayQuery(sql, paramsForLog);
                ps.addBatch();
            }
            return ps.executeBatch();
        }
    });
    batchStart = batchStartRef.get();
    say("******CHUNK SQLSERVER******");
}

From source file:gobblin.metastore.database.DatabaseJobHistoryStoreV101.java

private void addPropertyToBatch(PreparedStatement upsertStatement, String key, String value, String id)
        throws SQLException {
    Preconditions.checkArgument(!Strings.isNullOrEmpty(id));
    Preconditions.checkArgument(!Strings.isNullOrEmpty(key));
    Preconditions.checkArgument(!Strings.isNullOrEmpty(value));

    int index = 0;
    upsertStatement.setString(++index, id);
    upsertStatement.setString(++index, key);
    upsertStatement.setString(++index, value);
    upsertStatement.addBatch();
}