Example usage for java.sql PreparedStatement 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:db.IitbInfo.java

public void populateTable(Connection connection)
        throws SQLException, UnsupportedEncodingException, FileNotFoundException, IOException {
    PreparedStatement preparedStatement = connection.prepareStatement(INSERT_TABLE_SQL);
    BufferedReader br = new BufferedReader(
            new InputStreamReader(new FileInputStream(Config.LDAP_DUMP_FILE), "UTF-8"));
    int batchCounter = 0;
    String line;/*from   ww w . j  av  a2 s  .  com*/
    while ((line = br.readLine()) != null) {
        String[] fields = line.split("\\t");
        Integer id = Integer.parseInt(fields[0]);
        String ldapId = fields[1];
        String rollNo = fields[2];
        String employeeType = fields[3];
        String name = WordUtils.capitalizeFully(fields[4]);
        String department = LdapSearch.getDepartment(fields[5]);

        preparedStatement.setInt(1, id);
        preparedStatement.setString(2, ldapId);
        preparedStatement.setString(3, rollNo);
        preparedStatement.setString(4, employeeType);
        preparedStatement.setString(5, name);
        preparedStatement.setString(6, department);
        preparedStatement.addBatch();
        ++batchCounter;
        if (batchCounter >= BATCH_MAX_SIZE) {
            batchCounter = 0;
            preparedStatement.executeBatch();
        }
    }
    preparedStatement.executeBatch();
    preparedStatement.close();
    br.close();
}

---

From source file:com.ea.core.orm.handle.impl.HibernateSqlORMHandle.java

@Override
protected Object execute(ORMParamsDTO dto) throws Exception {
    // TODO Auto-generated method stub
    Session session = this.getHibernateSessionFactory().getCurrentSession();
    final ORMParamsDTO tmp = dto;
    session.doWork(new Work() {
        @SuppressWarnings("rawtypes")
        public void execute(Connection connection) throws SQLException {
            // connectionJDBC
            // closeconnection
            System.out.println("sql:" + tmp.getSqlid());
            PreparedStatement ps = connection.prepareStatement(tmp.getSqlid());
            if (tmp.getParam() != null) {
                Object data = tmp.getParam();
                if (data instanceof Object[]) {
                    Object[] array = (Object[]) data;
                    int index = 1;
                    for (Object obj : array) {
                        setParam(ps, index++, obj);
                    }//from  w  w w . jav  a 2 s .c  o  m
                    ps.execute();
                } else if (data instanceof Collection) {
                    for (Object array : (Collection) data) {
                        if (array instanceof Object[]) {
                            int index = 1;
                            for (Object obj : (Object[]) array) {
                                setParam(ps, index++, obj);
                            }
                            ps.addBatch();
                        } else {
                            throw new SQLException("SQL?Object[]???!");
                        }

                    }
                    ps.executeBatch();
                } else {
                    throw new SQLException(
                            "SQL????Object[]???????CollectionObject[]!");
                }
            }

        }
    });
    return null;
}

---

From source file:org.apache.eagle.alert.metadata.impl.JdbcMetadataHandler.java

public OpResult addPublishmentsToPolicy(String policyId, List<String> publishmentIds) {
    OpResult result = new OpResult();
    Connection connection = null;
    PreparedStatement statement = null;
    try {/*from w  w  w  .j  av a2 s  .co m*/
        connection = dataSource.getConnection();
        connection.setAutoCommit(false);
        statement = connection.prepareStatement(DELETE_PUBLISHMENT_STATEMENT);
        statement.setString(1, policyId);
        int status = statement.executeUpdate();
        LOG.info("delete {} records from policy_publishment", status);
        closeResource(null, statement, null);

        statement = connection.prepareStatement(INSERT_POLICYPUBLISHMENT_STATEMENT);
        for (String pub : publishmentIds) {
            statement.setString(1, policyId);
            statement.setString(2, pub);
            statement.addBatch();
        }
        int[] num = statement.executeBatch();
        connection.commit();
        connection.setAutoCommit(true);
        int sum = 0;
        for (int i : num) {
            sum += i;
        }
        result.code = OpResult.SUCCESS;
        result.message = String.format("Add %d records into policy_publishment", sum);
    } catch (SQLException ex) {
        LOG.error("Error to add publishments to policy {}", policyId, ex);
        result.code = OpResult.FAILURE;
        result.message = ex.getMessage();
    } finally {
        closeResource(null, statement, connection);
    }
    LOG.info(result.message);
    return result;
}

---

From source file:com.wso2telco.dep.operatorservice.dao.BlackListWhiteListDAO.java

/**
 * blacklist list given msisdns//from   www  . jav a2s. c o m
 *
 * @param msisdns
 * @param apiID
 * @param apiName
 * @param userID
 * @throws Exception
 */
public void blacklist(MSISDNValidationDTO msisdns, final String apiID, final String apiName,
        final String userID) throws Exception {

    log.debug("BlackListWhiteListDAO.blacklist triggerd MSISDN["
            + StringUtils.join(msisdns.getValidProcessed().toArray(), ",") + "] apiID:" + apiID + " apiName:"
            + apiName + " userID:" + userID);

    StringBuilder sql = new StringBuilder();
    sql.append(" INSERT INTO ");
    sql.append(OparatorTable.BLACKLIST_MSISDN.getTObject());
    sql.append("(PREFIX,MSISDN,API_ID,API_NAME,USER_ID,VALIDATION_REGEX)");
    sql.append(" VALUES (?, ?, ?, ?, ?, ?)");

    Connection conn = null;
    PreparedStatement ps = null;

    try {
        conn = DbUtils.getDbConnection(DataSourceNames.WSO2AM_STATS_DB);
        ps = conn.prepareStatement(sql.toString());

        conn.setAutoCommit(false);

        for (MsisdnDTO msisdn : msisdns.getValidProcessed()) {

            ps.setString(1, msisdn.getPrefix());
            ps.setString(2, msisdn.getDigits());
            ps.setString(3, apiID);
            ps.setString(4, apiName);
            ps.setString(5, userID);
            ps.setString(6, msisdns.getValidationRegex());
            ps.addBatch();
        }

        ps.executeBatch();
        conn.commit();

    } catch (Exception e) {
        if (conn != null) {
            conn.rollback();
        }
        throw e;
    } finally {
        DbUtils.closeAllConnections(ps, conn, null);
    }

}

---

From source file:netflow.DatabaseProxy.java

public void saveHosts(Map<String, HostTraffic> cache, java.util.Date date) {
    if (cache.size() == 0) {
        log.debug("Host cache empty");
        return;/*from www.j a va2  s  .  c o m*/
    }
    log.debug("Saving " + cache.size() + " records for " + date);
    String sql = getQuery("neflow.details.insert");
    try {
        PreparedStatement pstmt = con.prepareStatement(sql);
        Timestamp t = new java.sql.Timestamp(date.getTime());
        for (String key : cache.keySet()) {
            HostTraffic traffic = cache.get(key);
            if (!hasRecord(t, traffic.getHostAddress(), traffic.getNetworkId())) {
                pstmt.setTimestamp(1, t);
                pstmt.setString(2, traffic.getHostAddress());
                pstmt.setInt(3, traffic.getNetworkId());
                pstmt.setLong(4, traffic.getInputBytes());
                pstmt.setLong(5, traffic.getOutputBytes());
                pstmt.addBatch();
            }
        }
        int[] results = pstmt.executeBatch();
        log.info("saveHosts(): saved " + results.length + " records");
        pstmt.close();
        pstmt.clearParameters();
    } catch (SQLException e) {
        log.error("Saving hosts error: " + e.getMessage());
        SQLException ex = e.getNextException();
        if (ex != null) {
            log.error(ex.getMessage());
        }
        e.printStackTrace(System.err);
    }
}

---

From source file:consultor.CSVLoader.java

/**
 * Parse CSV file using OpenCSV library and load in 
 * given database table. //from w  w w . j a  v  a2s.  c o  m
 * @param csvFile Input CSV file
 * @param tableName Database table name to import data
 * @param truncateBeforeLoad Truncate the table before inserting 
 *          new records.
 * @throws Exception
 */
public void loadCSV(String csvFile, String tableName, boolean truncateBeforeLoad) throws Exception {

    CSVReader csvReader = null;
    if (null == this.connection) {
        throw new Exception("Not a valid connection.");
    }
    try {

        csvReader = new CSVReader(new FileReader(csvFile), this.seprator);

    } catch (Exception e) {
        e.printStackTrace();
        throw new Exception("Error occured while executing file. " + e.getMessage());
    }

    String[] headerRow = csvReader.readNext();

    if (null == headerRow) {
        throw new FileNotFoundException(
                "No columns defined in given CSV file." + "Please check the CSV file format.");
    }

    String questionmarks = StringUtils.repeat("?,", headerRow.length);
    questionmarks = (String) questionmarks.subSequence(0, questionmarks.length() - 1);

    String query = SQL_INSERT.replaceFirst(TABLE_REGEX, tableName);
    query = query.replaceFirst(KEYS_REGEX, StringUtils.join(headerRow, ","));
    query = query.replaceFirst(VALUES_REGEX, questionmarks);

    System.out.println("Query: " + query);

    String[] nextLine;
    Connection con = null;
    PreparedStatement ps = null;
    try {
        con = this.connection;
        con.setAutoCommit(false);
        ps = con.prepareStatement(query);

        if (truncateBeforeLoad) {
            //delete data from table before loading csv
            con.createStatement().execute("DELETE FROM " + tableName);
        }

        final int batchSize = 1000;
        int count = 0;
        Date date = null;
        while ((nextLine = csvReader.readNext()) != null) {

            if (null != nextLine) {
                int index = 1;
                for (String string : nextLine) {
                    date = DateUtil.convertToDate(string);
                    if (null != date) {
                        ps.setDate(index++, new java.sql.Date(date.getTime()));
                    } else {
                        ps.setString(index++, string);
                    }
                }
                ps.addBatch();
            }
            if (++count % batchSize == 0) {
                ps.executeBatch();
            }
        }
        ps.executeBatch(); // insert remaining records
        con.commit();
    } catch (Exception e) {
        con.rollback();
        e.printStackTrace();
        throw new Exception("Error occured while loading data from file to database." + e.getMessage());
    } finally {
        if (null != ps)
            ps.close();
        if (null != con)
            con.close();

        csvReader.close();
    }
}

---

From source file:org.sonar.server.db.migrations.MassUpdater.java

public <S> void execute(InputLoader<S> inputLoader, InputConverter<S> converter) {
    long count = 0;
    Connection readConnection = null;
    Statement stmt = null;/*from w w w  .j  av a2s. c  o m*/
    ResultSet rs = null;
    Connection writeConnection = null;
    PreparedStatement writeStatement = null;
    try {
        writeConnection = db.getDataSource().getConnection();
        writeConnection.setAutoCommit(false);
        writeStatement = writeConnection.prepareStatement(converter.updateSql());

        readConnection = db.getDataSource().getConnection();
        readConnection.setAutoCommit(false);

        stmt = readConnection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
        stmt.setFetchSize(GROUP_SIZE);
        if (db.getDialect().getId().equals(MySql.ID)) {
            stmt.setFetchSize(Integer.MIN_VALUE);
        } else {
            stmt.setFetchSize(GROUP_SIZE);
        }
        rs = stmt.executeQuery(convertSelectSql(inputLoader.selectSql(), db));

        int cursor = 0;
        while (rs.next()) {
            if (converter.convert(inputLoader.load(rs), writeStatement)) {
                writeStatement.addBatch();
                cursor++;
                count++;
            }

            if (cursor == GROUP_SIZE) {
                writeStatement.executeBatch();
                writeConnection.commit();
                cursor = 0;
            }
        }
        if (cursor > 0) {
            writeStatement.executeBatch();
            writeConnection.commit();
        }

    } catch (SQLException e) {
        SqlUtil.log(LOGGER, e);
        throw processError(e);
    } catch (Exception e) {
        throw processError(e);
    } finally {
        DbUtils.closeQuietly(writeStatement);
        DbUtils.closeQuietly(writeConnection);
        DbUtils.closeQuietly(readConnection, stmt, rs);

        LOGGER.info("{} rows have been updated", count);
    }
}

---

From source file:org.forgerock.openidm.repo.jdbc.impl.GenericTableHandler.java

/**
 * Writes all properties of a given resource to the properties table and links them to the main table record.
 *
 * @param fullId the full URI of the resource the belongs to
 * @param dbId the generated identifier to link the properties table with the main table (foreign key)
 * @param localId the local identifier of the resource these properties belong to
 * @param value the JSON value with the properties to write
 * @param connection the DB connection/* w  ww  .  java2 s  .  c o  m*/
 * @throws SQLException if the insert failed
 */
void writeValueProperties(String fullId, long dbId, String localId, JsonValue value, Connection connection)
        throws SQLException {
    if (cfg.hasPossibleSearchableProperties()) {
        Integer batchingCount = 0;
        PreparedStatement propCreateStatement = getPreparedStatement(connection,
                QueryDefinition.PROPCREATEQUERYSTR);
        try {
            batchingCount = writeValueProperties(fullId, dbId, localId, value, connection, propCreateStatement,
                    batchingCount);
            if (enableBatching && batchingCount > 0) {
                int[] numUpdates = propCreateStatement.executeBatch();
                logger.debug("Batch update of objectproperties updated: {}", numUpdates);
                if (logger.isDebugEnabled()) {
                    logger.debug("Writing batch of objectproperties, updated: {}", Arrays.asList(numUpdates));
                }
                propCreateStatement.clearBatch();
            }
        } finally {
            CleanupHelper.loggedClose(propCreateStatement);
        }
    }
}

---

From source file:com.wso2telco.dep.operatorservice.dao.BlackListWhiteListDAO.java

/**
 * when the subscription id is known/*from w w  w  . ja v  a 2s.  co  m*/
 *
 * @param userMSISDNs
 * @param subscriptionId
 * @param apiID
 * @param applicationID
 * @throws SQLException
 * @throws Exception
 */
public void whitelist(MSISDNValidationDTO msisdns, String subscriptionId, String apiID, String applicationID)
        throws Exception {

    StringBuilder sql = new StringBuilder();
    sql.append("INSERT INTO ");
    sql.append(OparatorTable.SUBSCRIPTION_WHITELIST.getTObject());
    sql.append(" (subscriptionID, prefix, msisdn, api_id, application_id, validation_regex)");
    sql.append(" VALUES (?,?,?,?,?,?);");

    Connection conn = null;
    PreparedStatement ps = null;

    try {
        conn = DbUtils.getDbConnection(DataSourceNames.WSO2AM_STATS_DB);
        ps = conn.prepareStatement(sql.toString());

        conn.setAutoCommit(false);
        for (MsisdnDTO msisdn : msisdns.getValidProcessed()) {

            ps.setString(1, subscriptionId);
            ps.setString(2, msisdn.getPrefix());
            ps.setString(3, msisdn.getDigits());
            ps.setString(4, apiID);
            ps.setString(5, applicationID);
            ps.setString(6, msisdns.getValidationRegex());

            ps.addBatch();
        }
        ps.executeBatch();
        conn.commit();

    } catch (Exception e) {
        if (conn != null) {
            conn.rollback();
        }
        log.error("", e);
        throw e;
    } finally {
        DbUtils.closeAllConnections(ps, conn, null);
    }

}

---

From source file:org.openbel.framework.core.kam.JdbcKAMLoaderImpl.java

/**
 * {@inheritDoc}/*from  w w w .  j a va  2  s  .  com*/
 */
@Override
public Map<String, Integer> loadFunctionTypes() throws SQLException {
    PreparedStatement ps = getPreparedStatement(FUNCTION_TYPE_SQL);

    Map<String, Integer> functionTypeIdMap = new HashMap<String, Integer>();

    int ftid = 0;
    for (FunctionEnum f : FunctionEnum.values()) {
        String functionName = f.getDisplayValue();
        ps.setInt(1, ftid);
        ps.setString(2, functionName);
        ps.addBatch();
        functionTypeIdMap.put(functionName, ftid);
        ftid++;
    }

    ps.executeBatch();
    return functionTypeIdMap;
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44
• 45
• 46
• 47
• 48
• 49
• 50
• »