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:org.latticesoft.util.resource.DatabaseUtil.java

/**
 * Executes the query in batch statement
 * @param c the collection of SQL statements to execute
 * @param conn the connection/*from  www  . ja  va2  s .  c o  m*/
 * @param closeConn true to close the connection after use
 * @return int[] a integer array of the executed result
 */
public int[] executeBatch(Collection c, Connection conn, boolean closeConn) {
    if (c == null || conn == null)
        return null;
    if (c.size() == 0)
        return null;
    Statement stmt = null;
    String sql = null;
    Iterator iter = c.iterator();
    int[] retVal = null;
    try {
        stmt = conn.createStatement();
        while (iter.hasNext()) {
            Object o = iter.next();
            if (o == null)
                continue;
            sql = o.toString();
            stmt.addBatch(sql);
        }
        retVal = stmt.executeBatch();
    } catch (Exception e) {
        if (log.isErrorEnabled()) {
            log.error(e);
        }
    } finally {
        try {
            stmt.close();
        } catch (Exception e) {
        }
        if (closeConn) {
            try {
                conn.close();
            } catch (Exception e) {
            }
        }
        stmt = null;
        conn = null;
    }
    return retVal;
}

From source file:com.hangum.tadpole.importdb.core.dialog.importdb.sql.SQLToDBImportDialog.java

/**
 * select? execute  .// ww  w  . j  ava 2  s.c o  m
 * 
 * @param listQuery
 * @throws Exception
 */
private int runSQLExecuteBatch(List<String> listQuery) throws Exception {
    java.sql.Connection conn = null;
    Statement statement = null;
    int result = 0;

    try {
        SqlMapClient client = TadpoleSQLManager.getInstance(userDB);
        conn = client.getDataSource().getConnection();
        conn.setAutoCommit(false);
        statement = conn.createStatement();
        int count = 0;

        for (String strQuery : listQuery) {
            if ("".equals(StringUtils.trimToEmpty(strQuery))) //$NON-NLS-1$
                continue;

            statement.addBatch(strQuery);
            if (++count % batchSize == 0) {
                try {
                    statement.executeBatch();
                } catch (SQLException e) {
                    logger.error("Execute Batch error", e); //$NON-NLS-1$
                    bufferBatchResult.append(e.getMessage() + "\n"); //$NON-NLS-1$

                    SQLException ne = e.getNextException();
                    while (ne != null) {
                        logger.error("NEXT SQLException is ", ne);//$NON-NLS-1$
                        bufferBatchResult.append(ne.getMessage() + "\n");
                        ne = ne.getNextException();
                    }

                    if (btnIgnore.getSelection()) {
                        conn.commit();
                        continue;
                    } else {
                        conn.rollback();
                        result = -1;
                        break;
                    }
                }
            }
        }

        statement.executeBatch();
        conn.commit();
        conn.setAutoCommit(true);

        if (result < 0 && !"".equals(bufferBatchResult.toString())) { //$NON-NLS-1$
            MessageDialog.openError(null, Messages.CsvToRDBImportDialog_4, bufferBatchResult.toString());
        }
    } catch (SQLException e) {
        logger.error("Execute Batch error", e); //$NON-NLS-1$
        bufferBatchResult.append(e.getMessage() + "\n"); //$NON-NLS-1$
        if (btnIgnore.getSelection()) {
            conn.commit();
        } else {
            conn.rollback();
        }

        SQLException ne = e.getNextException();
        while (ne != null) {
            logger.error("Execute Batch error", e); //$NON-NLS-1$
            bufferBatchResult.append(e.getMessage() + "\n"); //$NON-NLS-1$
            ne = ne.getNextException();
        }
    } catch (Exception e) {
        result = -1;
        logger.error("Execute Batch error", e); //$NON-NLS-1$
        bufferBatchResult.append(e.getMessage() + "\n"); //$NON-NLS-1$
        conn.rollback();
        throw e;
    } finally {
        try {
            if (statement != null)
                statement.close();
        } catch (Exception e) {
        }
        try {
            if (conn != null)
                conn.close();
        } catch (Exception e) {
        }
    }
    return result;
}

From source file:com.hangum.tadpole.importexport.core.dialogs.SQLToDBImportDialog.java

/**
 * select? execute  .//from w  w  w . j  av  a 2 s . com
 * 
 * @param listQuery
 * @throws Exception
 */
private int runSQLExecuteBatch(List<String> listQuery) throws Exception {
    java.sql.Connection conn = null;
    Statement statement = null;
    int result = 0;

    try {
        SqlMapClient client = TadpoleSQLManager.getInstance(userDB);
        conn = client.getDataSource().getConnection();
        conn.setAutoCommit(false);
        statement = conn.createStatement();
        int count = 0;

        for (String strQuery : listQuery) {
            if ("".equals(StringUtils.trimToEmpty(strQuery))) //$NON-NLS-1$
                continue;

            statement.addBatch(strQuery);
            if (++count % batchSize == 0) {
                try {
                    statement.executeBatch();
                } catch (SQLException e) {
                    logger.error("Execute Batch error", e); //$NON-NLS-1$
                    bufferBatchResult.append(e.getMessage() + "\n"); //$NON-NLS-1$

                    SQLException ne = e.getNextException();
                    while (ne != null) {
                        logger.error("NEXT SQLException is ", ne);//$NON-NLS-1$
                        bufferBatchResult.append(ne.getMessage() + "\n"); //$NON-NLS-1$
                        ne = ne.getNextException();
                    }

                    if (btnIgnore.getSelection()) {
                        conn.commit();
                        continue;
                    } else {
                        conn.rollback();
                        result = -1;
                        break;
                    }
                }
            }
        }

        statement.executeBatch();
        conn.commit();
        conn.setAutoCommit(true);

        if (result < 0 && !"".equals(bufferBatchResult.toString())) { //$NON-NLS-1$
            MessageDialog.openWarning(null, Messages.get().Warning, bufferBatchResult.toString());
        }
    } catch (SQLException e) {
        logger.error("Execute Batch error", e); //$NON-NLS-1$
        bufferBatchResult.append(e.getMessage() + "\n"); //$NON-NLS-1$
        if (btnIgnore.getSelection()) {
            conn.commit();
        } else {
            conn.rollback();
        }

        SQLException ne = e.getNextException();
        while (ne != null) {
            logger.error("Execute Batch error", e); //$NON-NLS-1$
            bufferBatchResult.append(e.getMessage() + "\n"); //$NON-NLS-1$
            ne = ne.getNextException();
        }
    } catch (Exception e) {
        result = -1;
        logger.error("Execute Batch error", e); //$NON-NLS-1$
        bufferBatchResult.append(e.getMessage() + "\n"); //$NON-NLS-1$
        conn.rollback();
        throw e;
    } finally {
        try {
            if (statement != null)
                statement.close();
        } catch (Exception e) {
        }
        try {
            if (conn != null)
                conn.close();
        } catch (Exception e) {
        }
    }
    return result;
}

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

/**
 * Does a bulk delete of a Collection of objects that match the type for this persister.
 *
 *//* www .jav a2 s .co m*/
public void delete(List objects) throws RegistryException {
    //Return immediatley if no objects to insert
    if (objects.size() == 0) {
        return;
    }

    log.trace(ServerResourceBundle.getInstance().getString("message.DeletingRowsInTable",
            new Object[] { new Integer(objects.size()), getTableName() }));
    action = DAO_ACTION_DELETE;

    Statement stmt = null;

    try {
        stmt = context.getConnection().createStatement();
        Iterator iter = objects.iterator();
        while (iter.hasNext()) {
            Object obj = iter.next();

            prepareToDelete(obj);

            String str = getSQLStatementFragment(obj);
            log.trace("SQL = " + str);
            stmt.addBatch(str);

            // HIEOS/BHT (Added block to get rid of MySQL performance bug with DB views).
            String mirrorImageStr = this.getSQLStatementFragmentForMirrorImage(obj);
            if (mirrorImageStr != null) {
                log.trace("SQL = " + mirrorImageStr); // HIEOS/BHT (DEBUG)
                stmt.addBatch(mirrorImageStr); // Now, DELETE the mirror.
            }
        }

        int[] updateCounts = stmt.executeBatch();

        iter = objects.iterator();
        while (iter.hasNext()) {
            Object obj = iter.next();
            onDelete(obj);
        }

    } 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.AbstractDAO.java

/**
 * @see org.freebxml.omar.server.persistence.rdb.OMARDAO#update(org.oasis.ebxml.registry.bindings.rim.UserType, java.sql.Connection, java.util.List, java.util.HashMap)
 *///from  ww w. java 2s . c  o  m
public void update(List objects) throws RegistryException {

    //Return immediatley if no objects to insert
    if (objects.size() == 0) {
        return;
    }

    log.trace(ServerResourceBundle.getInstance().getString("message.UpdatingRowsInTable",
            new Object[] { new Integer(objects.size()), getTableName() }));
    action = DAO_ACTION_UPDATE;

    Statement stmt = null;

    try {
        stmt = context.getConnection().createStatement();
        Iterator iter = objects.iterator();
        while (iter.hasNext()) {
            Object obj = iter.next();

            prepareToUpdate(obj);

            String str = getSQLStatementFragment(obj);
            log.trace("SQL = " + str);
            stmt.addBatch(str);

            // HIEOS/BHT (Added block to get rid of MySQL performance bug with DB views).
            String mirrorImageStr = this.getSQLStatementFragmentForMirrorImage(obj);
            if (mirrorImageStr != null) {
                log.trace("SQL = " + mirrorImageStr); // HIEOS/BHT (DEBUG)
                stmt.addBatch(mirrorImageStr); // Now, update the mirror.
            }
        }

        int[] updateCounts = stmt.executeBatch();

        iter = objects.iterator();
        while (iter.hasNext()) {
            Object obj = iter.next();

            onUpdate(obj);
        }

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

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

/**
 * Does a bulk insert of a Collection of objects that match the type for this persister.
 *
 *//*from w ww.  j  av  a 2  s.com*/
public void insert(String parentId, List<?> emailAddresss) throws RegistryException {
    log.debug(ServerResourceBundle.getInstance().getString("message.InsertingEmailAddresss",
            new Object[] { new Integer(emailAddresss.size()) }));

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

    Statement stmt = null;

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

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

        while (iter.hasNext()) {
            EmailAddressType emailAddress = (EmailAddressType) iter.next();

            //Log.print(Log.TRACE, 8, "\tDATABASE EVENT: storing EmailAddress " );
            String address = emailAddress.getAddress();

            String type = emailAddress.getType();

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

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

        if (emailAddresss.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.AbstractDAO.java

/**
 * @see it.cnr.icar.eric.server.persistence.rdb.OMARDAO#insert(org.oasis.ebxml.registry.bindings.rim.UserType, java.sql.Connection, java.util.List, java.util.HashMap)
 *///from   w  ww.ja va  2 s  . c o  m
@SuppressWarnings("unchecked")
public void insert(@SuppressWarnings("rawtypes") List objects) throws RegistryException {

    //Return immediatley if no objects to insert
    if (objects.size() == 0) {
        return;
    }

    //First process any objects that may already exists in persistence layer
    objects = processExistingObjects(objects);

    //Return immediately if no objects to insert
    if (objects.size() == 0) {
        return;
    }

    log.trace(ServerResourceBundle.getInstance().getString("message.InsertingRowsInTable",
            new Object[] { new Integer(objects.size()), getTableName() }));
    action = DAO_ACTION_INSERT;

    Statement stmt = null;

    try {
        stmt = context.getConnection().createStatement();
        Iterator<ExtrinsicObjectType> iter = objects.iterator();
        while (iter.hasNext()) {
            Object obj = iter.next();

            String str = getSQLStatementFragment(obj);
            log.trace("stmt = " + str);
            stmt.addBatch(str);

            prepareToInsert(obj);
        }

        long startTime = System.currentTimeMillis();
        log.trace("AbstractDAO.insert: doing executeBatch");
        @SuppressWarnings("unused")
        int[] updateCounts = stmt.executeBatch();
        long endTime = System.currentTimeMillis();
        log.trace("AbstractDAO.insert: done executeBatch elapedTimeMillis=" + (endTime - startTime));

        iter = objects.iterator();
        while (iter.hasNext()) {
            Object obj = iter.next();

            onInsert(obj);
        }

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

}

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

/**
 * @see it.cnr.icar.eric.server.persistence.rdb.OMARDAO#update(org.oasis.ebxml.registry.bindings.rim.UserType, java.sql.Connection, java.util.List, java.util.HashMap)
 *///from   w  w  w.ja v  a  2  s  . c o  m
public void update(@SuppressWarnings("rawtypes") List objects) throws RegistryException {

    //Return immediatley if no objects to insert
    if (objects.size() == 0) {
        return;
    }

    log.trace(ServerResourceBundle.getInstance().getString("message.UpdatingRowsInTable",
            new Object[] { new Integer(objects.size()), getTableName() }));
    action = DAO_ACTION_UPDATE;

    Statement stmt = null;

    try {
        stmt = context.getConnection().createStatement();
        Iterator<?> iter = objects.iterator();
        while (iter.hasNext()) {
            Object obj = iter.next();

            prepareToUpdate(obj);

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

        @SuppressWarnings("unused")
        int[] updateCounts = stmt.executeBatch();

        iter = objects.iterator();
        while (iter.hasNext()) {
            Object obj = iter.next();

            onUpdate(obj);
        }

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

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

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

    Statement stmt = null;

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

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

        while (iter.hasNext()) {
            EmailAddressType ebEmailAddressType = (EmailAddressType) iter.next();

            String address = ebEmailAddressType.getAddress();

            String type = ebEmailAddressType.getType();

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

            String str = "UPDATE EmailAddress SET " +
            //"accesControlPolicy = null, " +
                    "SET address = '" + address + "', " + "SET type = " + type + " 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:org.apache.tajo.catalog.store.DBStore.java

private void upgrade(int from, int to) throws SQLException {
    String sql;/*from ww w . j  a v  a2s .  c o  m*/
    Statement stmt;
    if (from == 0) {
        if (to == 1) {
            sql = "DROP INDEX idx_options_key";
            LOG.info(sql);

            stmt = conn.createStatement();
            stmt.addBatch(sql);

            sql = "CREATE INDEX idx_options_key on " + TB_OPTIONS + " (" + C_TABLE_ID + ")";
            stmt.addBatch(sql);
            LOG.info(sql);
            stmt.executeBatch();
            stmt.close();

            LOG.info("DB Upgraded from " + from + " to " + to);
        } else {
            LOG.info("DB Upgraded from " + from + " to " + to);
        }
    }
}