Example usage for java.sql Statement addBatch

List of usage examples for java.sql Statement addBatch

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. Statement
  6. addBatch

Introduction

In this page you can find the example usage for java.sql Statement addBatch.

Prototype

void addBatch(String sql) throws SQLException;

Source Link

Document

Adds the given SQL command to the current list of commands for this Statement object.

Usage

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  w  ww.  ja  va 2  s . c o m
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: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//from   w ww  .  j av a  2  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.springframework.jdbc.core.JdbcTemplateTests.java

public void testBatchUpdate() throws Exception {
    final String[] sql = { "UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = 1",
            "UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = 2" };

    MockControl ctrlStatement = MockControl.createControl(Statement.class);
    Statement mockStatement = (Statement) ctrlStatement.getMock();
    mockStatement.getConnection();//w w w  .  j a v  a 2  s  . c  om
    ctrlStatement.setReturnValue(mockConnection);
    mockStatement.addBatch(sql[0]);
    ctrlStatement.setVoidCallable();
    mockStatement.addBatch(sql[1]);
    ctrlStatement.setVoidCallable();
    mockStatement.executeBatch();
    ctrlStatement.setReturnValue(new int[] { 1, 1 });
    if (debugEnabled) {
        mockStatement.getWarnings();
        ctrlStatement.setReturnValue(null);
    }
    mockStatement.close();
    ctrlStatement.setVoidCallable();

    MockControl ctrlDatabaseMetaData = MockControl.createControl(DatabaseMetaData.class);
    DatabaseMetaData mockDatabaseMetaData = (DatabaseMetaData) ctrlDatabaseMetaData.getMock();
    mockDatabaseMetaData.getDatabaseProductName();
    ctrlDatabaseMetaData.setReturnValue("MySQL");
    mockDatabaseMetaData.supportsBatchUpdates();
    ctrlDatabaseMetaData.setReturnValue(true);

    mockConnection.getMetaData();
    ctrlConnection.setReturnValue(mockDatabaseMetaData, 2);
    mockConnection.createStatement();
    ctrlConnection.setReturnValue(mockStatement);

    ctrlStatement.replay();
    ctrlDatabaseMetaData.replay();
    replay();

    JdbcTemplate template = new JdbcTemplate(mockDataSource, false);

    int[] actualRowsAffected = template.batchUpdate(sql);
    assertTrue("executed 2 updates", actualRowsAffected.length == 2);

    ctrlStatement.verify();
    ctrlDatabaseMetaData.verify();
}

From source file:com.flexive.core.storage.genericSQL.GenericTreeStorage.java

/**
 * {@inheritDoc}//  w  w  w  .java 2s  . c  o m
 */
@Override
public void activateAll(Connection con, FxTreeMode mode) throws FxTreeException {
    Statement stmt = null;
    PreparedStatement ps = null;
    try {
        final String FALSE = StorageManager.getBooleanFalseExpression();
        acquireLocksForUpdate(con, FxTreeMode.Live);
        stmt = con.createStatement();
        stmt.addBatch(StorageManager.getReferentialIntegrityChecksStatement(false));
        stmt.addBatch("DELETE FROM " + getTable(FxTreeMode.Live));
        stmt.addBatch("INSERT INTO " + getTable(FxTreeMode.Live) + " SELECT * FROM " + getTable(mode));
        stmt.addBatch("UPDATE " + getTable(mode) + " SET DIRTY=" + FALSE);
        stmt.addBatch("UPDATE " + getTable(FxTreeMode.Live) + " SET DIRTY=" + FALSE);
        stmt.addBatch(StorageManager.getReferentialIntegrityChecksStatement(true));
        stmt.executeBatch();
        //FX-793: activate nodes that are not in the live version
        ps = con.prepareStatement("SELECT DISTINCT c.ID, c.MAX_VER FROM " + getTable(FxTreeMode.Live) + " l, "
                + DatabaseConst.TBL_CONTENT + " c WHERE l.REF IS NOT NULL AND c.ID=l.REF AND c.LIVE_VER=0");
        ResultSet rs = ps.executeQuery();
        final ContentEngine ce = EJBLookup.getContentEngine();
        while (rs != null && rs.next()) {
            FxPK pk = new FxPK(rs.getLong(1), rs.getInt(2));
            FxContent co = ce.load(pk);
            //create a Live version
            pk = createContentLiveVersion(ce, co);
            LOG.info("Created new live version " + pk + " during tree activation");
        }
        if (rs != null)
            rs.close();
    } catch (Throwable t) {
        throw new FxTreeException(LOG, t, "ex.tree.activate.all.failed", mode.name(), t.getMessage());
    } finally {
        Database.closeObjects(GenericTreeStorage.class, stmt, ps);
    }
}

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.
*
*//*from w  w  w . ja va2  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("SQL = " + str);
            stmt.addBatch(str);
        }

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

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

/**
 * Does a bulk insert of a Collection of objects that match the type for this persister.
 *
 *//*w  ww.j  a va  2 s.com*/
public void insert(String parentId, List postalAddresss) throws RegistryException {
    Statement stmt = null;
    log.debug(ServerResourceBundle.getInstance().getString("message.InsertingPostalAddresss",
            new Object[] { new Integer(postalAddresss.size()) }));

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

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

        Iterator iter = postalAddresss.iterator();

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

            //Log.print(Log.TRACE, 8, "\tDATABASE EVENT: storing PostalAddress " );
            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 = "INSERT INTO PostalAddress " + "VALUES( " + city + ", " + country + ", " + postalCode
                    + ", " + state + ", " + street + ", " + streetNum + ", " + "'" + parentId + "' )";
            log.trace("SQL = " + str);
            stmt.addBatch(str);
        }

        if (postalAddresss.size() > 0) {
            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.
*
*//*from   w  ww  .  j a v a 2 s .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("stmt = " + 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 insert of a Collection of objects that match the type for this persister.
 *
 *///from  w  w w .j  av  a2 s  .  c om
public void insert(String parentId, List<?> postalAddresss) throws RegistryException {
    Statement stmt = null;
    log.debug(ServerResourceBundle.getInstance().getString("message.InsertingPostalAddresss",
            new Object[] { new Integer(postalAddresss.size()) }));

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

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

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

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

            //Log.print(Log.TRACE, 8, "\tDATABASE EVENT: storing PostalAddress " );
            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 = "INSERT INTO PostalAddress " + "VALUES( " + city + ", " + country + ", " + postalCode
                    + ", " + state + ", " + street + ", " + streetNum + ", " + "'" + parentId + "' )";
            log.trace("stmt = " + str);
            stmt.addBatch(str);
        }

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

From source file:org.apache.hive.beeline.BeeLine.java

void runBatch(List<String> statements) {
    try {/*from  w w w  . j  a v a2 s  . co  m*/
        Statement stmnt = createStatement();
        try {
            for (Iterator<String> i = statements.iterator(); i.hasNext();) {
                stmnt.addBatch(i.next().toString());
            }
            int[] counts = stmnt.executeBatch();

            output(getColorBuffer().pad(getColorBuffer().bold("COUNT"), 8)
                    .append(getColorBuffer().bold("STATEMENT")));

            for (int i = 0; counts != null && i < counts.length; i++) {
                output(getColorBuffer().pad(counts[i] + "", 8).append(statements.get(i).toString()));
            }
        } finally {
            try {
                stmnt.close();
            } catch (Exception e) {
            }
        }
    } catch (Exception e) {
        handleException(e);
    }
}

From source file:com.flexive.core.storage.genericSQL.GenericTreeStorageSpreaded.java

/**
 * {@inheritDoc}//from  w  ww . j a va 2 s  .  co  m
 */
@Override
public void activateNode(Connection con, SequencerEngine seq, ContentEngine ce, FxTreeMode mode,
        final long nodeId, boolean activateContents) throws FxApplicationException {
    if (mode == FxTreeMode.Live) //Live tree can not be activated!
        return;
    long ids[] = getIdChain(con, mode, nodeId); //all id's up to the root node
    acquireLocksForUpdate(con, mode, Arrays.asList(ArrayUtils.toObject(ids)));
    try {
        // lock node in live tree including all children (which *can* be removed if they were removed in the edit tree)
        acquireLocksForUpdate(con, FxTreeMode.Live,
                selectDirectChildNodeIds(con, FxTreeMode.Live, nodeId, true));
    } catch (SQLException e) {
        throw new FxDbException(e);
    }

    for (long id : ids) {
        if (id == ROOT_NODE)
            continue;
        FxTreeNode srcNode = getNode(con, mode, id);
        //check if the node already exists in the live tree
        if (exists(con, FxTreeMode.Live, id)) {
            //Move and setData will not do anything if the node is already in its correct place and
            move(con, seq, FxTreeMode.Live, id, srcNode.getParentNodeId(), srcNode.getPosition());
            setData(con, FxTreeMode.Live, id, srcNode.getData());
        } else {
            createNode(con, seq, ce, FxTreeMode.Live, srcNode.getId(), srcNode.getParentNodeId(),
                    srcNode.getName(), srcNode.getLabel(), srcNode.getPosition(), srcNode.getReference(),
                    srcNode.getData(), activateContents);
        }

        // Remove all deleted direct child nodes
        Statement stmt = null;
        Statement stmt2 = null;
        try {
            stmt = con.createStatement();
            stmt2 = con.createStatement();
            if (StorageManager.isDisableIntegrityTransactional()) {
                stmt2.execute(StorageManager.getReferentialIntegrityChecksStatement(false));
            }
            try {
                ResultSet rs = stmt.executeQuery(
                        "SELECT DISTINCT tl.ID FROM " + getTable(FxTreeMode.Live) + " tl " + "LEFT JOIN "
                                + getTable(FxTreeMode.Edit) + " te ON tl.ID=te.ID WHERE te.ID=null AND "
                                + "te.PARENT=" + nodeId + " AND tl.PARENT=" + nodeId);
                while (rs != null && rs.next()) {
                    long deleteId = rs.getLong(1);
                    //                        System.out.println("==> deleted:"+deleteId);
                    acquireLocksForUpdate(con, FxTreeMode.Live, Arrays.asList(deleteId));
                    stmt2.addBatch("DELETE FROM " + getTable(FxTreeMode.Live) + " WHERE ID=" + deleteId);

                }
                stmt2.addBatch("UPDATE " + getTable(FxTreeMode.Live) + " SET MODIFIED_AT="
                        + System.currentTimeMillis());
                stmt2.executeBatch();
            } finally {
                if (StorageManager.isDisableIntegrityTransactional()) {
                    stmt2.execute(StorageManager.getReferentialIntegrityChecksStatement(true));
                }
            }
        } catch (SQLException e) {
            throw new FxTreeException("ex.tree.activate.failed", nodeId, false, e.getMessage());
        } finally {
            try {
                if (stmt != null)
                    stmt.close();
            } catch (Exception exc) {
                //ignore
            }
            try {
                if (stmt2 != null)
                    stmt2.close();
            } catch (Exception exc) {
                //ignore
            }
        }
        clearDirtyFlag(con, mode, nodeId);
    }
}