Example usage for java.sql Statement executeBatch

Introduction

In this page you can find the example usage for java.sql Statement 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:org.freebxml.omar.server.persistence.rdb.EmailAddressDAO.java

public void insert(List users) throws RegistryException {
    // log.info(ServerResourceBundle.getInstance().getString("message.InsertingEmailAddresss", new Object[]{new Integer(emailAddresss.size())}));
    if (users.size() == 0) {
        return;// w ww  .jav a2s  . c  o m
    }

    Statement stmt = null;

    try {
        Iterator usersIter = users.iterator();
        stmt = context.getConnection().createStatement();

        while (usersIter.hasNext()) {
            UserType user = (UserType) usersIter.next();

            if (log.isDebugEnabled()) {
                try {
                    StringWriter writer = new StringWriter();
                    bu.rimFac.createMarshaller().marshal(user, writer);
                    log.debug("Inserting user: " + writer.getBuffer().toString());
                } catch (Exception e) {
                    log.debug("Failed to marshal user: ", e);
                }
            }

            String parentId = user.getId();

            List emails = user.getEmailAddress();
            Iterator emailsIter = emails.iterator();

            while (emailsIter.hasNext()) {
                //Log.print(Log.TRACE, 8, "\tDATABASE EVENT: storing EmailAddress " );
                Object obj = emailsIter.next();

                EmailAddressType emailAddress = (EmailAddressType) obj;

                String address = emailAddress.getAddress();

                String type = emailAddress.getType();

                if (type != null) {
                    type = "'" + type + "'";
                }

                String str = "INSERT INTO " + getTableName() + " VALUES( " + "'" + address + "', " + type + ", "
                        + "'" + parentId + "' )";
                log.trace("SQL = " + str);
                stmt.addBatch(str);
            }
        }

        if (users.size() > 0) {
            stmt.executeBatch();
        }
    } catch (SQLException e) {
        RegistryException exception = new RegistryException(e);
        throw exception;
    } finally {
        closeStatement(stmt);
    }
}

---

From source file:lib.JdbcTemplate.java

@Override
public int[] batchUpdate(final String... sql) throws DataAccessException {
    Assert.notEmpty(sql, "SQL array must not be empty");
    if (logger.isDebugEnabled()) {
        logger.debug("Executing SQL batch update of " + sql.length + " statements");
    }/*from  w  ww.  j  a v a2 s.c  o  m*/

    class BatchUpdateStatementCallback implements StatementCallback<int[]>, SqlProvider {

        private String currSql;

        @Override
        public int[] doInStatement(Statement stmt) throws SQLException, DataAccessException {
            int[] rowsAffected = new int[sql.length];
            if (JdbcUtils.supportsBatchUpdates(stmt.getConnection())) {
                for (String sqlStmt : sql) {
                    this.currSql = appendSql(this.currSql, sqlStmt);
                    stmt.addBatch(sqlStmt);
                }
                try {
                    rowsAffected = stmt.executeBatch();
                } catch (BatchUpdateException ex) {
                    String batchExceptionSql = null;
                    for (int i = 0; i < ex.getUpdateCounts().length; i++) {
                        if (ex.getUpdateCounts()[i] == Statement.EXECUTE_FAILED) {
                            batchExceptionSql = appendSql(batchExceptionSql, sql[i]);
                        }
                    }
                    if (StringUtils.hasLength(batchExceptionSql)) {
                        this.currSql = batchExceptionSql;
                    }
                    throw ex;
                }
            } else {
                for (int i = 0; i < sql.length; i++) {
                    this.currSql = sql[i];
                    if (!stmt.execute(sql[i])) {
                        rowsAffected[i] = stmt.getUpdateCount();
                    } else {
                        throw new InvalidDataAccessApiUsageException("Invalid batch SQL statement: " + sql[i]);
                    }
                }
            }
            return rowsAffected;
        }

        private String appendSql(String sql, String statement) {
            return (StringUtils.isEmpty(sql) ? statement : sql + "; " + statement);
        }

        @Override
        public String getSql() {
            return this.currSql;
        }
    }

    return execute(new BatchUpdateStatementCallback());
}

---

From source file:it.cnr.icar.eric.server.persistence.rdb.EmailAddressDAO.java

public void insert(@SuppressWarnings("rawtypes") List users) throws RegistryException {
    // log.info(ServerResourceBundle.getInstance().getString("message.InsertingEmailAddresss", new Object[]{new Integer(emailAddresss.size())}));
    if (users.size() == 0) {
        return;//from  w  ww  . j a  va  2s  .  com
    }

    Statement stmt = null;

    try {
        Iterator<?> usersIter = users.iterator();
        stmt = context.getConnection().createStatement();

        while (usersIter.hasNext()) {
            UserType user = (UserType) usersIter.next();

            if (log.isDebugEnabled()) {
                try {
                    StringWriter writer = new StringWriter();
                    //                        bu.rimFac.createMarshaller()
                    bu.getJAXBContext().createMarshaller().marshal(user, writer);
                    log.debug("Inserting user: " + writer.getBuffer().toString());
                } catch (Exception e) {
                    log.debug("Failed to marshal user: ", e);
                }
            }

            String parentId = user.getId();

            List<EmailAddressType> emails = user.getEmailAddress();
            Iterator<EmailAddressType> emailsIter = emails.iterator();

            while (emailsIter.hasNext()) {
                //Log.print(Log.TRACE, 8, "\tDATABASE EVENT: storing EmailAddress " );
                Object obj = emailsIter.next();

                EmailAddressType emailAddress = (EmailAddressType) obj;

                String address = emailAddress.getAddress();

                String type = emailAddress.getType();

                if (type != null) {
                    type = "'" + type + "'";
                }

                String str = "INSERT INTO " + getTableName() + " VALUES( " + "'" + address + "', " + type + ", "
                        + "'" + parentId + "' )";
                log.trace("stmt = " + str);
                stmt.addBatch(str);
            }
        }

        if (users.size() > 0) {
            stmt.executeBatch();
        }
    } catch (SQLException e) {
        RegistryException exception = new RegistryException(e);
        throw exception;
    } finally {
        closeStatement(stmt);
    }
}

---

From source file:org.freebxml.omar.server.persistence.rdb.RegistryObjectDAO.java

/**
 * Update the status of specified objects (homogenous collection) to the specified status.
 * @param statusUnchanged if an id in registryObjectIds is in this ArrayList, no AuditableEvent
 * generated for that RegistryObject//from   www  .  j  av  a2  s . c o  m
 */
public void updateStatus(RegistryObjectType ro, String status) throws RegistryException {
    Statement stmt = null;

    // HIEOS/BHT/AMS: Changed to also update status in the RegistryObject table.
    try {
        stmt = context.getConnection().createStatement();
        String registryObjectTableName = getTableName();
        // First update the concrete table (e.g. ExtrinsicObject, RegistryPackage).
        String sql = this.getSQLStatementFragmentForStatusUpdate(registryObjectTableName, status, ro.getId());
        log.trace("SQL = " + sql);
        stmt.addBatch(sql);

        // Now, update the RegistryObject table (if not already updated above).
        if (!registryObjectTableName.equals(RegistryObjectDAO.getTableNameStatic())) {
            sql = this.getSQLStatementFragmentForStatusUpdate(RegistryObjectDAO.getTableNameStatic(), status,
                    ro.getId());
            log.trace("SQL = " + sql);
            stmt.addBatch(sql);
        }
        stmt.executeBatch();
    } catch (SQLException e) {
        log.error(ServerResourceBundle.getInstance().getString("message.CaughtException"), e);
        throw new RegistryException(e);
    } finally {
        closeStatement(stmt);
    }
}

---

From source file:uk.ac.ebi.bioinvindex.utils.test.DBUnitTest.java

/**
 * A facility to clean all the tables in the database, independently on the fact they appear or not in the dataset.
 *
 * TODO: Not completely sure it works with Oracle... :-\
 *
 * @throws SQLException//  ww  w.  j  a  v  a2  s .c  o  m
 */
protected void cleanAll() throws SQLException {
    setReferentialIntegrityCheckings(false);

    try {
        Statement delstmt = connection.createStatement();
        DatabaseMetaData dbmsMeta = connection.getMetaData();
        String dbmsName = dbmsMeta.getDatabaseProductName().toLowerCase();
        String dbmsCatalog = connection.getCatalog();
        if (dbmsCatalog == null)
            // Let's try with the user name
            dbmsCatalog = dbmsMeta.getUserName().toUpperCase();

        String dbmsSchema = null;
        if (dbmsName.contains("oracle")) {
            // The damn Oracle needs the schema in getTables(), otherwise it returns undeletable
            // system tables too.
            //
            Statement stmt = connection.createStatement();
            ResultSet rs = stmt
                    .executeQuery("select sys_context('USERENV', 'CURRENT_SCHEMA') CURRENT_SCHEMA from dual");
            if (rs.next())
                dbmsSchema = rs.getString(1);
            stmt.close();
        }

        log.debug("DBMSUnitTest.cleanAll(), DBMS Name: '" + dbmsName + "' Catalog: '" + dbmsCatalog
                + "' Schema: '" + dbmsSchema + "'");

        ResultSet tbrs = dbmsMeta.getTables(dbmsCatalog, dbmsSchema, null, new String[] { "TABLE" });

        while (tbrs.next()) {
            String tbname = StringUtils.trimToNull(tbrs.getString("TABLE_NAME"));
            if (tbname == null)
                continue;
            // Oracle system tables
            String sql = "DROP TABLE " + tbname;
            if (!dbmsName.contains("mysql"))
                sql += " CASCADE CONSTRAINTS";
            log.debug("DBUnitTest, adding sql: " + sql);
            delstmt.addBatch(sql);
        }
        delstmt.executeBatch();
    } finally {
        setReferentialIntegrityCheckings(true);
        connection.close();
        // All tables were deleted, we need this to force schema recreation.
        initEntityManager(true);
    }
}

---

From source file:org.freebxml.omar.server.persistence.rdb.TelephoneNumberDAO.java

/**
 * Does a bulk insert of a Collection of objects that match the type for this persister.
 *
 *//*w  w w  .j  a  v a2s.c om*/
public void insert(String parentId, List telephoneNumbers) throws RegistryException {
    Statement stmt = null;

    if (telephoneNumbers.size() == 0) {
        return;
    }

    log.debug(ServerResourceBundle.getInstance().getString("message.InsertingTelephoneNumbersSize",
            new Object[] { new Integer(telephoneNumbers.size()) }));

    try {
        stmt = context.getConnection().createStatement();

        Iterator iter = telephoneNumbers.iterator();

        while (iter.hasNext()) {
            TelephoneNumberType telephoneNumber = (TelephoneNumberType) iter.next();

            //Log.print(Log.TRACE, 8, "\tDATABASE EVENT: storing TelephoneNumber " );
            String areaCode = telephoneNumber.getAreaCode();

            if (areaCode != null) {
                areaCode = "'" + areaCode + "'";
            }

            String countryCode = telephoneNumber.getCountryCode();

            if (countryCode != null) {
                countryCode = "'" + countryCode + "'";
            }

            String extension = telephoneNumber.getExtension();

            if (extension != null) {
                extension = "'" + extension + "'";
            }

            String number = telephoneNumber.getNumber();

            if (number != null) {
                number = "'" + number + "'";
            }

            String phoneType = telephoneNumber.getPhoneType();

            if (phoneType != null) {
                phoneType = "'" + phoneType + "'";
            }

            String str = "INSERT INTO TelephoneNumber " + "VALUES( " + areaCode + ", " + countryCode + ", "
                    + extension + ", " + number + ", " + phoneType + ", " + "'" + parentId + "' )";

            log.trace("SQL = " + str);
            stmt.addBatch(str);
        }

        if (telephoneNumbers.size() > 0) {
            stmt.executeBatch();
        }
    } catch (SQLException e) {
        log.error(ServerResourceBundle.getInstance().getString("message.CaughtException1"), e);
        throw new RegistryException(e);
    } finally {
        closeStatement(stmt);
    }
}

---

From source file:org.kuali.kpme.core.util.ClearDatabaseLifecycle.java

protected void clearTables(final PlatformTransactionManager transactionManager, final DataSource dataSource,
        final String schemaName) {
    LOG.info("Clearing tables for schema " + schemaName);
    Assert.assertNotNull("DataSource could not be located.", dataSource);

    if (schemaName == null || schemaName.equals("")) {
        Assert.fail("Empty schema name given");
    }//w w w  .jav  a2 s  .co m
    new TransactionTemplate(transactionManager).execute(new TransactionCallback<Object>() {
        public Object doInTransaction(final TransactionStatus status) {
            verifyTestEnvironment(dataSource);
            return new JdbcTemplate(dataSource).execute(new StatementCallback<Object>() {
                public Object doInStatement(Statement statement) throws SQLException {
                    final List<String> reEnableConstraints = new ArrayList<String>();
                    List<List<String>> tableLists = new ArrayList<List<String>>(2);
                    tableLists.add(TABLES_TO_CLEAR);
                    tableLists.add(alternativeTablesToClear);
                    for (List<String> list : tableLists) {
                        for (String tableName : list) {
                            //if there is an id name that doesnt follow convention check and limit accordingly
                            String idName = TABLE_TO_ID_MAP.get(tableName);
                            String deleteStatement = null;
                            Integer clearId = TABLE_START_CLEAR_ID.get(tableName) != null
                                    ? TABLE_START_CLEAR_ID.get(tableName)
                                    : START_CLEAR_ID;
                            if (idName == null) {
                                deleteStatement = "DELETE FROM " + tableName + " WHERE "
                                        + StringUtils.removeEnd(tableName, "_T") + "_ID" + " >= " + clearId;
                            } else {
                                deleteStatement = "DELETE FROM " + tableName + " WHERE " + idName + " >= "
                                        + clearId;
                            }

                            LOG.debug("Clearing contents using statement ->" + deleteStatement + "<-");
                            statement.addBatch(deleteStatement);
                        }
                    }

                    for (final String constraint : reEnableConstraints) {
                        LOG.debug("Enabling constraints using statement ->" + constraint + "<-");
                        statement.addBatch(constraint);
                    }
                    statement.executeBatch();
                    return null;
                }
            });
        }
    });
    LOG.info("Tables successfully cleared for schema " + schemaName);
}

---

From source file:it.cnr.icar.eric.server.persistence.rdb.TelephoneNumberDAO.java

/**
 * Does a bulk insert of a Collection of objects that match the type for this persister.
 *
 *///from ww w  . ja  v a  2  s .c om
public void insert(String parentId, List<?> telephoneNumbers) throws RegistryException {
    Statement stmt = null;

    if (telephoneNumbers.size() == 0) {
        return;
    }

    log.debug(ServerResourceBundle.getInstance().getString("message.InsertingTelephoneNumbersSize",
            new Object[] { new Integer(telephoneNumbers.size()) }));

    try {
        stmt = context.getConnection().createStatement();

        Iterator<?> iter = telephoneNumbers.iterator();

        while (iter.hasNext()) {
            TelephoneNumberType telephoneNumber = (TelephoneNumberType) iter.next();

            //Log.print(Log.TRACE, 8, "\tDATABASE EVENT: storing TelephoneNumber " );
            String areaCode = telephoneNumber.getAreaCode();

            if (areaCode != null) {
                areaCode = "'" + areaCode + "'";
            }

            String countryCode = telephoneNumber.getCountryCode();

            if (countryCode != null) {
                countryCode = "'" + countryCode + "'";
            }

            String extension = telephoneNumber.getExtension();

            if (extension != null) {
                extension = "'" + extension + "'";
            }

            String number = telephoneNumber.getNumber();

            if (number != null) {
                number = "'" + number + "'";
            }

            String phoneType = telephoneNumber.getPhoneType();

            if (phoneType != null) {
                phoneType = "'" + phoneType + "'";
            }

            String str = "INSERT INTO TelephoneNumber " + "VALUES( " + areaCode + ", " + countryCode + ", "
                    + extension + ", " + number + ", " + phoneType + ", " + "'" + parentId + "' )";

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

        if (telephoneNumbers.size() > 0) {
            stmt.executeBatch();
        }
    } catch (SQLException e) {
        log.error(ServerResourceBundle.getInstance().getString("message.CaughtException1"), e);
        throw new RegistryException(e);
    } finally {
        closeStatement(stmt);
    }
}

---

From source file:org.freebxml.omar.server.persistence.rdb.PostalAddressDAO.java

/**
* Does a bulk update of a Collection of objects that match the type for this persister.
*
*//*  ww  w .j av a2s. c o  m*/
public void update(String parentId, List postalAddresss) throws RegistryException {
    Statement stmt = null;
    log.debug(ServerResourceBundle.getInstance().getString("message.UpdatingPostalAddresss",
            new Object[] { new Integer(postalAddresss.size()) }));

    try {
        stmt = context.getConnection().createStatement();

        Iterator iter = postalAddresss.iterator();

        while (iter.hasNext()) {
            PostalAddressType postalAddress = (PostalAddressType) iter.next();

            String city = postalAddress.getCity();

            if (city != null) {
                city = "'" + city + "'";
            }

            String country = postalAddress.getCountry();

            if (country != null) {
                country = "'" + country + "'";
            }

            String postalCode = postalAddress.getPostalCode();

            if (postalCode != null) {
                postalCode = "'" + postalCode + "'";
            }

            String state = postalAddress.getStateOrProvince();

            if (state != null) {
                state = "'" + state + "'";
            }

            String street = postalAddress.getStreet();

            if (street != null) {
                street = "'" + street + "'";
            }

            String streetNum = postalAddress.getStreetNumber();

            if (streetNum != null) {
                streetNum = "'" + streetNum + "'";
            }

            String str = "UPDATE PostalAddress " + "SET city = " + city + ", " + "SET country = " + country
                    + ", " + "SET postalCode = " + postalCode + ", " + "SET state = " + state + ", "
                    + "SET street = " + street + ", " + "SET streetNum = " + streetNum + " "
                    + " WHERE parent = '" + parentId + "' ";
            log.trace("SQL = " + str);
            stmt.addBatch(str);
        }

        stmt.executeBatch();
    } catch (SQLException e) {
        RegistryException exception = new RegistryException(e);
        throw exception;
    } finally {
        closeStatement(stmt);
    }
}

---

From source file:it.cnr.icar.eric.server.persistence.rdb.PostalAddressDAO.java

/**
* Does a bulk update of a Collection of objects that match the type for this persister.
*
*/// w w  w  . j  a v  a 2 s. co m
public void update(String parentId, List<?> postalAddresss) throws RegistryException {
    Statement stmt = null;
    log.debug(ServerResourceBundle.getInstance().getString("message.UpdatingPostalAddresss",
            new Object[] { new Integer(postalAddresss.size()) }));

    try {
        stmt = context.getConnection().createStatement();

        Iterator<?> iter = postalAddresss.iterator();

        while (iter.hasNext()) {
            PostalAddressType postalAddress = (PostalAddressType) iter.next();

            String city = postalAddress.getCity();

            if (city != null) {
                city = "'" + city + "'";
            }

            String country = postalAddress.getCountry();

            if (country != null) {
                country = "'" + country + "'";
            }

            String postalCode = postalAddress.getPostalCode();

            if (postalCode != null) {
                postalCode = "'" + postalCode + "'";
            }

            String state = postalAddress.getStateOrProvince();

            if (state != null) {
                state = "'" + state + "'";
            }

            String street = postalAddress.getStreet();

            if (street != null) {
                street = "'" + street + "'";
            }

            String streetNum = postalAddress.getStreetNumber();

            if (streetNum != null) {
                streetNum = "'" + streetNum + "'";
            }

            String str = "UPDATE PostalAddress " + "SET city = " + city + ", " + "SET country = " + country
                    + ", " + "SET postalCode = " + postalCode + ", " + "SET state = " + state + ", "
                    + "SET street = " + street + ", " + "SET streetNum = " + streetNum + " "
                    + " WHERE parent = '" + parentId + "' ";
            log.trace("stmt = " + str);
            stmt.addBatch(str);
        }

        stmt.executeBatch();
    } catch (SQLException e) {
        RegistryException exception = new RegistryException(e);
        throw exception;
    } finally {
        closeStatement(stmt);
    }
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• »