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:it.cnr.icar.eric.server.persistence.rdb.InternationalStringDAO.java

public void insert(String parentId, InternationalStringType is) throws RegistryException {
    PreparedStatement pstmt = null;

    try {//w  w  w .  j a  v a 2s  . c o m
        String str = "INSERT INTO " + getTableName() + " VALUES(?, " + // charsetName
                "?," + // lang
                "?, " + // value
                "?)"; // parentId
        pstmt = context.getConnection().prepareStatement(str);

        if (is != null) {
            Iterator<LocalizedStringType> lsItems = is.getLocalizedString().iterator();

            while (lsItems.hasNext()) {
                LocalizedStringType ebLocalizedStringType = lsItems.next();
                @SuppressWarnings("unused")
                String charset = ebLocalizedStringType.getCharset();
                String lang = ebLocalizedStringType.getLang();
                String value = ebLocalizedStringType.getValue();
                String charsetName = ebLocalizedStringType.getCharset();

                if (value != null && value.length() > 0) {
                    pstmt.setString(1, charsetName);
                    pstmt.setString(2, lang);
                    pstmt.setString(3, value);
                    pstmt.setString(4, parentId);

                    log.trace("stmt = " + pstmt.toString());
                    pstmt.addBatch();
                }
            }
        }

        if (is != null) {
            @SuppressWarnings("unused")
            int[] updateCounts = pstmt.executeBatch();
        }
    } catch (SQLException e) {
        log.error(ServerResourceBundle.getInstance().getString("message.CaughtException1"), e);
        throw new RegistryException(e);
    } finally {
        closeStatement(pstmt);
    }
}

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

@Override
public void updateAttempts(List<Integer> deviceIds, boolean reset) throws MonitoringDAOException {
    Connection conn;//w  w w.  java2s.  com
    PreparedStatement stmt = null;
    Timestamp currentTimestamp = new Timestamp(Calendar.getInstance().getTime().getTime());
    int tenantId = PrivilegedCarbonContext.getThreadLocalCarbonContext().getTenantId();
    try {
        conn = this.getConnection();
        String query = "";
        if (reset) {
            query = "UPDATE DM_POLICY_COMPLIANCE_STATUS SET ATTEMPTS = 0, LAST_REQUESTED_TIME = ? "
                    + "WHERE DEVICE_ID = ? AND TENANT_ID = ?";
        } else {
            query = "UPDATE DM_POLICY_COMPLIANCE_STATUS SET ATTEMPTS = ATTEMPTS + 1, LAST_REQUESTED_TIME = ? "
                    + "WHERE DEVICE_ID = ? AND TENANT_ID = ?";
        }
        stmt = conn.prepareStatement(query);
        for (int deviceId : deviceIds) {
            stmt.setTimestamp(1, currentTimestamp);
            stmt.setInt(2, deviceId);
            stmt.setInt(3, tenantId);
            stmt.addBatch();
        }
        stmt.executeBatch();
    } catch (SQLException e) {
        throw new MonitoringDAOException("Unable to update the attempts  data in database.", e);
    } finally {
        PolicyManagementDAOUtil.cleanupResources(stmt, null);
    }
}

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

protected void doInPreparedStatement(PreparedStatement ps, String metaModelCode, boolean hasChildMetaModel,
        List<AbstractMetadata> metadatas) throws SQLException {
    try {//from w  w w.  j a v a2 s  . co  m
        for (AbstractMetadata metadata : metadatas) {
            // ?ID
            String sequenceId = sequenceDao.getUuid();
            ps.setString(1, sequenceId);

            // ID
            ps.setString(3, taskInstanceId);
            // ?ID
            ps.setString(4, metadata.getId());
            // 
            ps.setString(5, metaModelCode);
            // ID
            ps.setString(7, userId);

            // : ALTERATION_TIME
            ps.setLong(9, startTime);

            // ? ? 2010-05-18 fbchen
            //ps.setString(3, genAttrs(metadata));

            setPs(ps, metadata, metaModelCode, hasChildMetaModel);

            String parentId = metadata.getParentMetadata().getId();
            if (parentId == null || parentId.equals("")) {
                parentId = "0";
            }
            ps.setString(11, parentId);
            ps.addBatch();
            ps.clearParameters();

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

        }
    } catch (SQLException e) {
        // ??,????,,??
        log.warn("??!", e);
    }

}

From source file:org.jasig.ssp.util.importer.job.staging.PostgresStagingTableWriter.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;/*from  www . j a  va  2  s  .com*/
        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 POSTGRES******");
}

From source file:org.sonar.core.persistence.DbTemplate.java

public DbTemplate copyTable(DataSource source, DataSource dest, String table, String... whereClauses) {
    LOG.debug("Copy table {}", table);

    String selectQuery = selectQuery(table, whereClauses);
    truncate(dest, table);/*from  w ww  . j ava2  s.  co  m*/

    Connection sourceConnection = null;
    Statement sourceStatement = null;
    ResultSet sourceResultSet = null;
    Connection destConnection = null;
    ResultSet destResultSet = null;
    PreparedStatement destStatement = null;
    try {
        sourceConnection = source.getConnection();
        sourceStatement = sourceConnection.createStatement();
        sourceResultSet = sourceStatement.executeQuery(selectQuery);

        if (sourceResultSet.next()) {
            List<String> columnNames = columnNames(sourceResultSet);
            int colCount = columnNames.size();

            destConnection = dest.getConnection();
            destConnection.setAutoCommit(false);

            String insertSql = new StringBuilder().append("INSERT INTO ").append(table).append("(")
                    .append(Joiner.on(",").join(columnNames)).append(") VALUES(")
                    .append(StringUtils.repeat("?", ",", colCount)).append(")").toString();
            destStatement = destConnection.prepareStatement(insertSql);
            int count = 0;
            do {
                for (int col = 1; col <= colCount; col++) {
                    Object value = sourceResultSet.getObject(columnNames.get(col - 1));
                    destStatement.setObject(col, value);
                }
                count++;
                destStatement.addBatch();
                if (count % BatchSession.MAX_BATCH_SIZE == 0) {
                    destStatement.executeBatch();
                    destConnection.commit();

                }
            } while (sourceResultSet.next());

            destStatement.executeBatch();
            destConnection.commit();
        }
    } catch (SQLException e) {
        LOG.error("Fail to copy table " + table, e);
        throw new IllegalStateException("Fail to copy table " + table, e);
    } finally {
        DatabaseUtils.closeQuietly(destStatement);
        DatabaseUtils.closeQuietly(destResultSet);
        DatabaseUtils.closeQuietly(destConnection);
        DatabaseUtils.closeQuietly(sourceResultSet);
        DatabaseUtils.closeQuietly(sourceStatement);
        DatabaseUtils.closeQuietly(sourceConnection);
    }

    return this;
}

From source file:org.wso2.carbon.apimgt.migration.client.MigrateFrom19to110.java

private void updateAuthzUserName() throws SQLException {
    log.info("Updating Authz UserName for API Manager started");
    Connection connection = null;
    PreparedStatement selectStatement = null;
    ResultSet resultSet = null;//from  ww  w  . j ava  2  s.  c o m

    ArrayList<AccessTokenInfo> updateValues = new ArrayList<>();
    try {
        String selectQuery = "SELECT DISTINCT AUTHZ_USER FROM IDN_OAUTH2_ACCESS_TOKEN WHERE AUTHZ_USER LIKE '%@%'";

        connection = APIMgtDBUtil.getConnection();
        selectStatement = connection.prepareStatement(selectQuery);
        resultSet = selectStatement.executeQuery();

        while (resultSet.next()) {
            String authzUser = resultSet.getString("AUTHZ_USER");
            String usernameWithoutDomain = MultitenantUtils.getTenantAwareUsername(authzUser);

            AccessTokenInfo accessTokenInfo = new AccessTokenInfo(usernameWithoutDomain, authzUser);
            updateValues.add(accessTokenInfo);
        }
    } finally {
        APIMgtDBUtil.closeAllConnections(selectStatement, connection, resultSet);
    }

    if (!updateValues.isEmpty()) { // If user names that need to be updated exist
        PreparedStatement updateStatement = null;

        try {
            connection = APIMgtDBUtil.getConnection();
            connection.setAutoCommit(false);

            updateStatement = connection.prepareStatement(
                    "UPDATE IDN_OAUTH2_ACCESS_TOKEN SET AUTHZ_USER = ?" + " WHERE AUTHZ_USER = ?");

            for (AccessTokenInfo accessTokenInfo : updateValues) {
                updateStatement.setString(1, accessTokenInfo.usernameWithoutDomain);
                updateStatement.setString(2, accessTokenInfo.authzUser);
                updateStatement.addBatch();
            }
            updateStatement.executeBatch();

            connection.commit();
        } finally {
            APIMgtDBUtil.closeAllConnections(updateStatement, connection, null);
        }
    }
    log.info("Updating Authz UserName for API Manager completed");
}

From source file:com.nabla.dc.server.handler.fixed_asset.Asset.java

static public void dispose(final Connection conn, final Integer assetId, final IDisposal disposal)
        throws SQLException, DispatchException {
    final PreparedStatement redo = conn
            .prepareStatement("INSERT INTO fa_transaction_redo (fa_asset_id, command) VALUES(?,?);");
    try {//from  w  ww.  ja v  a2 s  .  c  o m
        redo.setInt(1, assetId);
        // backup transaction after disposal if any
        if (log.isDebugEnabled())
            log.debug("backing up transactions after disposal date");
        // charge monthly depreciation in disposal month if disposal is after 15
        final Calendar dt = Util.dateToCalendar(disposal.getDate());
        if (dt.get(GregorianCalendar.DAY_OF_MONTH) >= dt.getActualMaximum(GregorianCalendar.DAY_OF_MONTH) / 2)
            dt.add(GregorianCalendar.MONTH, 1);
        dt.set(GregorianCalendar.DAY_OF_MONTH, 1);
        final Date from = Util.calendarToSqlDate(dt);
        // get list of transactions to backup before we delete them
        final IntegerSet transIds = new IntegerSet();
        final PreparedStatement stmt = StatementFormat.prepare(conn,
                "SELECT t.*" + " FROM fa_transaction AS t INNER JOIN period_end AS p ON t.period_end_id=p.id"
                        + " WHERE t.fa_asset_id=? AND p.end_date>?;",
                assetId, from);
        try {
            final ResultSet rs = stmt.executeQuery();
            try {
                while (rs.next()) {
                    transIds.add(rs.getInt("id"));
                    final String command = MessageFormat.format("INSERT INTO fa_transaction"
                            + " (id,fa_asset_id,period_end_id,amount,class,type,depreciation_period)"
                            + " VALUES({0,number,0},{1,number,0},{2,number,0},{3,number,0},''{4}'',''{5}'',{6,number,0});",
                            rs.getInt("id"), rs.getInt("fa_asset_id"), rs.getInt("period_end_id"),
                            rs.getInt("amount"), rs.getString("class"), rs.getString("type"),
                            Database.getInteger(rs, "depreciation_period"));
                    if (log.isTraceEnabled())
                        log.trace("redo = " + command);
                    redo.setString(2, command);
                    redo.addBatch();
                }
            } finally {
                rs.close();
            }
        } finally {
            stmt.close();
        }
        // remove any transaction after disposal date
        if (log.isDebugEnabled())
            log.debug("removing transactions after disposal date");
        Database.executeUpdate(conn, "DELETE FROM fa_transaction WHERE id IN (?);", transIds);
        // add disposal transactions
        if (log.isDebugEnabled())
            log.debug("adding transactions for disposal");
        final TransactionList transactions = new TransactionList(assetId);
        // closing cost
        transactions.add(new Transaction(TransactionClasses.COST, TransactionTypes.CLOSING, disposal.getDate(),
                -1 * getAssetCostBeforeDisposal(conn, assetId)));
        // closing accumulated depreciation
        transactions.add(new Transaction(TransactionClasses.DEP, TransactionTypes.CLOSING, disposal.getDate(),
                -1 * getAssetDepreciationBeforeDisposal(conn, assetId)));
        for (Integer newTransId : transactions.save(conn, true)) {
            redo.setString(2,
                    MessageFormat.format("DELETE FROM fa_transaction WHERE id={0,number,0};", newTransId));
            redo.addBatch();
        }
        if (!Database.isBatchCompleted(redo.executeBatch()))
            throw new InternalErrorException("failed to save disposal transactions");
    } finally {
        redo.close();
    }
}

From source file:uta.ak.CollectTweets.java

public void collectTweetsByKeyWords(String keyWords, String sinceDate, String untilDate, String tag) {
    try {/*from   w w  w . ja  v a  2 s. c o m*/

        ConfigurationBuilder cb = new ConfigurationBuilder();
        cb.setDebugEnabled(true).setOAuthConsumerKey("LuhVZOucqdHX6x0lcVgJO6QK3")
                .setOAuthConsumerSecret("6S7zbGLvHMXDMgRXq7jRIA6QmMpdI8i5IJNpnjlB55vpHpFMpj")
                .setOAuthAccessToken("861637891-kLunD37VRY8ipAK3TVOA0YKOKxeidliTqMtNb7wf")
                .setOAuthAccessTokenSecret("vcKDxs6qHnEE8fhIJr5ktDcTbPGql5o3cNtZuztZwPYl4");
        TwitterFactory tf = new TwitterFactory(cb.build());
        Twitter twitter = tf.getInstance();

        Connection con = null; //MYSQL
        Class.forName("com.mysql.jdbc.Driver").newInstance(); //MYSQL
        con = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/USTTMP", "root", "root.123"); //MYSQL
        System.out.println("connection yes");

        String insertSQL = "INSERT INTO c_rawtext(mme_lastupdate, mme_updater, title, text, tag, text_createdate) VALUES (NOW(), \"AK\", ?, ?, ?, ?)";
        PreparedStatement insertPS = con.prepareStatement(insertSQL);

        Calendar cal = Calendar.getInstance();
        cal.add(Calendar.DATE, 1);
        SimpleDateFormat format1 = new SimpleDateFormat("yyyy-MM-dd");

        Query query = new Query(keyWords);
        query.setSince(sinceDate);
        query.setUntil(untilDate);
        query.setCount(100);
        query.setLang("en");

        QueryResult result = twitter.search(query);
        for (Status status : result.getTweets()) {
            //                    System.out.println("@" + status.getUser().getScreenName() +
            //                                       " | " + status.getCreatedAt().toString() +
            //                                       ":" + status.getText());
            //                    System.out.println("Inserting the record into the table...");

            String formattedDate = format1.format(status.getCreatedAt());

            insertPS.setString(1, status.getUser().getScreenName());
            insertPS.setString(2, status.getText());
            insertPS.setString(3, tag);
            insertPS.setString(4, formattedDate);
            insertPS.addBatch();
        }

        System.out.println("Start to insert records...");
        insertPS.clearParameters();
        int[] results = insertPS.executeBatch();

    } catch (Exception te) {
        te.printStackTrace();
        System.out.println("Failed: " + te.getMessage());
        System.exit(-1);
    }
}

From source file:org.wso2.carbon.identity.claim.metadata.mgt.dao.LocalClaimDAO.java

private void addClaimAttributeMappings(Connection connection, int localClaimId,
        List<AttributeMapping> attributeMappings, int tenantId) throws ClaimMetadataException {

    PreparedStatement prepStmt = null;
    if (localClaimId > 0 && attributeMappings != null) {
        try {//from w  w  w . j  a  v  a 2s. co m
            String query = SQLConstants.ADD_CLAIM_MAPPED_ATTRIBUTE;
            prepStmt = connection.prepareStatement(query);
            for (AttributeMapping attributeMapping : attributeMappings) {
                if (StringUtils.isBlank(attributeMapping.getUserStoreDomain())) {
                    throw new ClaimMetadataException(
                            "User store domain of mapped Attribute cannot be empty for "
                                    + "the local claim id : " + localClaimId);
                } else if (StringUtils.isBlank(attributeMapping.getAttributeName())) {
                    throw new ClaimMetadataException(
                            "Mapped attribute of the local claim id : " + localClaimId + " cannot be empty");
                }
                prepStmt.setInt(1, localClaimId);
                prepStmt.setString(2, attributeMapping.getUserStoreDomain());
                prepStmt.setString(3, attributeMapping.getAttributeName());
                prepStmt.setInt(4, tenantId);
                prepStmt.addBatch();
            }

            prepStmt.executeBatch();
        } catch (SQLException e) {
            throw new ClaimMetadataException("Error while adding attribute mappings", e);
        } finally {
            IdentityDatabaseUtil.closeStatement(prepStmt);
        }
    }
}

From source file:com.spvp.dal.MySqlDatabase.java

@Override
public Boolean ucitajPrognozeUBazu(ArrayList<Prognoza> prognoze) throws SQLException {

    Connection conn = null;//from  ww  w. ja va2s.  c  om
    Boolean status = false;

    try {
        conn = getConnection();
        conn.setAutoCommit(false);

        Statement s = conn.createStatement();
        ResultSet rs = s.executeQuery("SELECT AUTO_INCREMENT " + "FROM  INFORMATION_SCHEMA.TABLES "
                + "WHERE TABLE_SCHEMA = 'weather_forecasting' " + "AND   TABLE_NAME   = 'historija_prognoze';");

        int zadnjiId = -1;

        rs.next();
        zadnjiId = rs.getInt("AUTO_INCREMENT");

        int idGrada = -1;

        PreparedStatement pstmt = conn.prepareStatement(
                "INSERT INTO historija_prognoze (id, vrijeme, temp, pritisak, brzina_vjetra, vlaznost_zraka, datum) "
                        + "VALUES(?, ?,?,?,?,?,?)");

        PreparedStatement pstmt3 = conn
                .prepareStatement("INSERT INTO gradovi_prognoze (prognoza_id, grad_id) " + "VALUES(?,?)");

        for (Prognoza x : prognoze) {

            pstmt.clearParameters();
            pstmt.setInt(1, zadnjiId);
            pstmt.setString(2, x.getVrijeme());
            pstmt.setString(3, x.getTemperatura());
            pstmt.setString(4, x.getPritisakZraka());
            pstmt.setString(5, x.getBrzinaVjetra());
            pstmt.setString(6, x.getVlaznostZraka());
            pstmt.setDate(7, new java.sql.Date(x.getDatum().getTime()));
            pstmt.addBatch();

            idGrada = dajIdGradaPoImenu(x.getZaGrad().getImeGrada());

            pstmt3.clearParameters();
            pstmt3.setInt(1, zadnjiId);
            pstmt3.setInt(2, idGrada);

            pstmt3.addBatch();

            zadnjiId++;
        }

        pstmt.executeBatch();
        pstmt3.executeBatch();

        conn.commit();
        status = true;

    } catch (SQLException ex) {
        Logger.getLogger(MySqlDatabase.class.getName()).log(Level.SEVERE, null, ex);
        if (conn != null)
            conn.rollback();

    } finally {

        if (conn != null)
            conn.close();
    }

    return status;
}