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:com.oracle.tutorial.jdbc.CoffeesTable.java

public void batchUpdate() throws SQLException {

    Statement stmt = null;
    try {/*w  ww. j av a 2 s.c o  m*/

        this.con.setAutoCommit(false);
        stmt = this.con.createStatement();

        stmt.addBatch("INSERT INTO COFFEES " + "VALUES('Amaretto', 49, 9.99, 0, 0)");
        stmt.addBatch("INSERT INTO COFFEES " + "VALUES('Hazelnut', 49, 9.99, 0, 0)");
        stmt.addBatch("INSERT INTO COFFEES " + "VALUES('Amaretto_decaf', 49, 10.99, 0, 0)");
        stmt.addBatch("INSERT INTO COFFEES " + "VALUES('Hazelnut_decaf', 49, 10.99, 0, 0)");

        int[] updateCounts = stmt.executeBatch();
        this.con.commit();

    } catch (BatchUpdateException b) {
        JDBCTutorialUtilities.printBatchUpdateException(b);
    } catch (SQLException ex) {
        JDBCTutorialUtilities.printSQLException(ex);
    } finally {
        if (stmt != null) {
            stmt.close();
        }
        this.con.setAutoCommit(true);
    }
}

---

From source file:org.fosstrak.epcis.repository.capture.CaptureOperationsBackendSQL.java

/**
 * {@inheritDoc}/*www.  j a  va 2  s  . c  o m*/
 */
public void dbReset(final Connection dbconnection, final String dbResetScript)
        throws SQLException, IOException {
    LOG.info("Running db reset script.");
    Statement stmt = null;
    BufferedReader reader = null;
    try {
        stmt = dbconnection.createStatement();
        if (dbResetScript != null) {
            reader = new BufferedReader(new FileReader(dbResetScript));
            String line;
            while ((line = reader.readLine()) != null) {
                stmt.addBatch(line);
            }
        }
        stmt.executeBatch();
    } finally {
        if (stmt != null) {
            stmt.close();
        }
        if (reader != null) {
            reader.close();
        }
    }
}

---

From source file:com.thinkmore.framework.orm.hibernate.SimpleHibernateDao.java

/**
 * ?//from  w  w w  .  j av a 2s . c  o  m
 * @param list sql?
 */
public void executeBatch(final List<String> list) {
    Connection conn = null;
    Statement st = null;
    try {
        conn = SessionFactoryUtils.getDataSource(getSessionFactory()).getConnection();
        conn.setAutoCommit(false); // ??
        st = conn.createStatement();
        for (int i = 1, j = list.size(); i < j; i++) {
            String sql = list.get(i);
            st.addBatch(sql);
            if (i % 240 == 0) {//?240?sql???
                st.executeBatch();
                conn.commit();
                st.clearBatch();
            } else if (i % j == 0) {//??
                st.executeBatch();
                conn.commit();
                st.clearBatch();
            }
        }
    } catch (Exception e) {
        try {
            conn.rollback();
        } catch (SQLException e1) {
            e1.printStackTrace();
        }
        e.printStackTrace();
    } finally {
        closeAll(st, conn);
    }
}

---

From source file:xbird.util.jdbc.QueryRunner.java

public int[] batch(Connection conn, String... sqls) throws SQLException {
    final boolean autoCommit = conn.getAutoCommit();
    if (autoCommit) {
        conn.setAutoCommit(false);/*from   w w w  .j  a v a  2s .c  om*/
    }
    Statement stmt = null;
    int[] rows = null;
    try {
        stmt = conn.createStatement();
        for (int i = 0; i < sqls.length; i++) {
            verboseQuery(sqls[i], (Object[]) null);
            stmt.addBatch(sqls[i]);
        }
        rows = stmt.executeBatch();
        conn.commit();
    } catch (SQLException e) {
        DbUtils.rollback(conn);
        rethrow(e, sqls);
    } finally {
        close(stmt);
    }
    // change back commit mode.
    if (autoCommit) {
        conn.setAutoCommit(true);
    }
    return rows;
}

---

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

/**
 * Enables/disables the referential integrity checkings in the database.
 *
 * @param isset//from   ww w  . j  av  a 2s.  co m
 * @throws SQLException
 */
protected void setReferentialIntegrityCheckings(boolean isset) throws SQLException {

    String sql = null;

    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();

    log.debug("DBUnitTest.setReferentialIntegrityCheckings(), DBMS Name: '" + dbmsName + "' Catalog: '"
            + dbmsCatalog + "'");

    if (dbmsName.contains("h2"))
        sql = "SET REFERENTIAL_INTEGRITY " + isset;
    else if (dbmsName.contains("mysql"))
        sql = "set FOREIGN_KEY_CHECKS = " + (isset ? "1" : "0");
    else if (dbmsName.contains("oracle")) {
        // Oracle is quite messy...
        String sqlCs = "select css.*, decode(CONSTRAINT_TYPE, 'P', '0', 'C', '1', 'U', 2, 'R', '3', 1000) ctype "
                + "from sys.all_constraints css where owner = '" + dbmsCatalog + "' order by ctype "
                + (isset ? "ASC" : "DESC");

        ResultSet rs = connection.createStatement().executeQuery(sqlCs);
        Statement csDelStmt = connection.createStatement();
        while (rs.next()) {
            String sqlCsCmd = isset ? "enable" : "disable";
            String tbname = rs.getString("TABLE_NAME");
            String csname = rs.getString("CONSTRAINT_NAME");

            String sqlCsDel = "alter table " + dbmsCatalog + "." + tbname + " " + sqlCsCmd + " constraint "
                    + csname;
            log.debug("DBUnitTest, adding sql: " + sqlCsDel);
            csDelStmt.addBatch(sqlCsDel);
        }
        csDelStmt.executeBatch();
        return;
    }

    if (sql == null)
        throw new SQLException(
                "Don't know how to change referential integrity checks for the database: '" + dbmsName + "'");

    connection.createStatement().execute(sql);
}

---

From source file:de.tudarmstadt.ukp.csniper.ml.JdbcCustomReader.java

private void query() {
    try {/*from  w  ww.  j av  a 2 s.com*/
        Statement statement = sqlConnection.createStatement();

        // execute query which sets user variables
        Iterator<String> it = Arrays.asList(StringUtils.split(setterQuery, "\n")).iterator();
        StringBuilder sb = new StringBuilder();
        while (it.hasNext()) {
            String line = it.next();
            if (line.trim().startsWith("#")) {
                continue;
            } else if (line.trim().endsWith(";")) {
                sb.append(line);
                statement.addBatch(sb.toString());
                sb = new StringBuilder();
            } else {
                sb.append(line);
            }
        }
        statement.executeBatch();

        statement.executeQuery(query);
        resultSet = statement.getResultSet();
        resultSet.last();
        resultSetSize = resultSet.getRow();
        resultSet.beforeFirst();
        completed = 0;

        // Store available column names
        columnNames = new HashSet<String>();
        ResultSetMetaData meta = resultSet.getMetaData();
        for (int i = 1; i < meta.getColumnCount() + 1; i++) {
            String columnName = meta.getColumnLabel(i);
            columnNames.add(columnName);
            if (!CAS_COLUMNS.contains(columnName)) {
                getLogger().warn("Unknown column [" + columnName + "].");
            }
        }
    } catch (SQLException e) {
        throw new RuntimeException("There was an unrecoverable error executing the specified SQL statement.",
                e);
    }
}

---

From source file:com.china317.gmmp.gmmp_report_analysis.App.java

private static void DgmFobbidenStoreIntoDB(Map<String, DgmForbidden> map, ApplicationContext context) {
    /**/*w w w .  j  a va  2s .  c o m*/
     * INSERT INTO TAB_GPSEVENT_AREA SELECT
     * CODE,ALARMTYPE,BEGINTIME,ENDTIME,'' FROM ALARMFOBBIDEN_REA
     */
    Connection conn = null;
    String sql = "";
    try {

        SqlMapClient sc = (SqlMapClient) context.getBean("sqlMapClientDgm");
        conn = sc.getDataSource().getConnection();
        conn.setAutoCommit(false);
        Statement st = conn.createStatement();
        Iterator<String> it = map.keySet().iterator();
        while (it.hasNext()) {
            String key = it.next();
            DgmForbidden pos = map.get(key);
            /*
             * String sql = "insert into ALARMFOBBIDEN_REA " +
             * " (CODE,LICENSE,LICENSECOLOR,ALARMTYPE,BEGINTIME,ENDTIME) " +
             * " values (" + "'" + pos.getCode() + "'," + "'" +
             * pos.getLicense() + "'," + "'" + pos.getLicenseColor() + "',"
             * + "'" + pos.getAlarmType() + "'," + "'" + pos.getBeginTime()
             * + "','" + pos.getEndTime() + "')";
             */
            sql = "insert into TAB_GPSEVENT_AREA " + " (VID,TYPE,BEGIN_TIME,END_TIME,DETAIL) " + " values ("
                    + "'" + pos.getCode() + "'," + "'" + pos.getAlarmType() + "'," + "'" + pos.getBeginTime()
                    + "'," + "'" + pos.getEndTime() + "'," + "'" + "')";
            log.info(sql);
            st.addBatch(sql);
        }
        st.executeBatch();
        conn.commit();
        log.info("[insertIntoDB DgmForbidden success!!!]");
    } catch (Exception e) {
        e.printStackTrace();
        log.error(sql);
    } finally {
        DgmAnalysisImp.getInstance().getForbiddeningMap().clear();
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

---

From source file:de.forsthaus.h2.My_H2_SampleDataFiller.java

@Override
public void afterPropertiesSet() throws Exception {
    final Logger logger = Logger.getLogger(getClass());
    final Map<Integer, String> allSql = new HashMap<Integer, String>();
    final Connection conn = this.dataSource.getConnection();
    try {/*from  w  ww  . jav a  2s  .  c  om*/
        // reads the sql-file from the classpath
        final InputStream inputStream = getClass().getResourceAsStream("/createSampleData.sql");
        try {

            final Statement stat = conn.createStatement();

            final BufferedReader in = new BufferedReader(new InputStreamReader(inputStream, "UTF-8"));
            String str;
            StringBuilder sb = new StringBuilder();
            int count = 0;
            while ((str = in.readLine()) != null) {
                sb.append(str);
                // make a linefeed at each readed line
                if (StringUtils.endsWith(str.trim(), ";")) {
                    final String sql = sb.toString();
                    stat.addBatch(sql);
                    sb = new StringBuilder();
                    allSql.put(Integer.valueOf(count++), sql);
                } else {
                    sb.append("\n");
                }
            }

            final int[] ar = stat.executeBatch();
            final int i = ar.length;

            logger.info("Create DemoData");
            logger.info("count batch updates : " + i);

        } finally {
            try {
                inputStream.close();
            } catch (final IOException e) {
                logger.warn("", e);
            }
        }
    } catch (final BatchUpdateException e) {
        final BatchUpdateException be = e;
        final int[] updateCounts = be.getUpdateCounts();
        if (updateCounts != null) {
            for (int i = 0; i < updateCounts.length; i++) {
                final int j = updateCounts[i];
                if (j < 0) {
                    logger.error("SQL errorcode: " + j + " -> in SQL\n" + allSql.get(Integer.valueOf(i)));
                }
            }
        }
        throw e;
    } finally {
        try {
            conn.close();
        } catch (final SQLException e) {
            logger.warn("", e);
        }
    }
}

---

From source file:com.china317.gmmp.gmmp_report_analysis.App.java

private static void DgmIllegalParkingStoreIntoDB(Map<String, DgmIllegalParking> map,
        ApplicationContext context) {/*from w  w w  .j  a  va 2s .co m*/
    /*
     * INSERT INTO TAB_GPSEVENT_ILLEGALPARKING SELECT
     * CODE,'illegalParking',BEGIN_TIME,END_TIME,'',FLAG FROM
     * ALARMILLEGALPARKING_REA
     */
    Connection conn = null;
    String sql = "";
    try {

        SqlMapClient sc = (SqlMapClient) context.getBean("sqlMapClientDgm");
        conn = sc.getDataSource().getConnection();
        conn.setAutoCommit(false);
        Statement st = conn.createStatement();
        Iterator<String> it = map.keySet().iterator();
        while (it.hasNext()) {
            String key = it.next();
            DgmIllegalParking pos = map.get(key);
            /*
             * String sql = "insert into TAB_GPSEVENT_ILLEGALPARKING " +
             * " (CODE,LICENSE,TYPE,BEGINTIME,ENDTIME,FLAG) " + " values ("
             * + "'" + pos.getCode() + "'," + "'" + pos.getLicense() + "',"
             * + "'" + pos.getType() + "'," + "'" + pos.getBeginTime() +
             * "'," + "'" + pos.getEndTime() + "','" + pos.getFlag() + "')";
             */
            sql = "insert into TAB_GPSEVENT_ILLEGALPARKING " + " (VID,TYPE,BEGIN_TIME,END_TIME,DETAIL,IS_END) "
                    + " values (" + "'" + pos.getCode() + "'," + "'" + pos.getType() + "'," + "'"
                    + pos.getBeginTime() + "'," + "'" + pos.getEndTime() + "'," + "'" + "'," + pos.getFlag()
                    + ")";
            log.info(sql);
            st.addBatch(sql);
        }
        st.executeBatch();
        conn.commit();
        log.info("[insertIntoDB DgmIllegalParking success!!!]");
    } catch (Exception e) {
        e.printStackTrace();
        log.error(sql);
    } finally {
        DgmAnalysisImp.getInstance().getIllegalParking().clear();
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

---

From source file:com.china317.gmmp.gmmp_report_analysis.App.java

private static void DgmEntryExitStoreIntoDB(Map<String, DgmEntryExit> map, ApplicationContext context) {
    /**/* w w  w.  j  a  v a  2s .  c  om*/
     * INSERT INTO TAB_GPSEVENT_ILLEGALENTRYEXIT SELECT
     * CODE,'illegalEntryExit',BEGIN_TIME,END_TIME,DETAIL,ROAD FROM
     * ALARMILLEGALEXIT_REA
     */
    String sql = "";
    Connection conn = null;
    try {

        SqlMapClient sc = (SqlMapClient) context.getBean("sqlMapClientDgm");
        conn = sc.getDataSource().getConnection();
        conn.setAutoCommit(false);
        Statement st = conn.createStatement();
        Iterator<String> it = map.keySet().iterator();
        while (it.hasNext()) {
            String key = it.next();
            DgmEntryExit pos = map.get(key);
            /*
             * String sql = "insert into ALARMILLEGALEXIT_REA " +
             * " (CODE,TYPE,BEGIN_TIME,END_TIME,DETAIL,ROAD) " + " values ("
             * + "'" + pos.getCode() + "'," + "'" + pos.getType() + "'," +
             * "'" + pos.getBegin_time() + "'," + "'" + pos.getEnd_time() +
             * "'," + "'" + pos.getDetail() + "','" + pos.getRoad() + "')";
             */
            sql = "insert into TAB_GPSEVENT_ILLEGALENTRYEXIT " + " (VID,TYPE,BEGIN_TIME,END_TIME,DETAIL,ROAD) "
                    + " values (" + "'" + pos.getCode() + "'," + "'illegalEntryExit'," + "'"
                    + pos.getBegin_time() + "'," + "'" + pos.getEnd_time() + "'," + "'" + pos.getDetail()
                    + "','" + pos.getRoad() + "')";
            log.info(sql);
            st.addBatch(sql);
        }
        st.executeBatch();
        conn.commit();
        log.info("[insertIntoDB DgmEntryExit success!!!]");
    } catch (Exception e) {
        e.printStackTrace();
        log.error(sql);
    } finally {
        DgmAnalysisImp.getInstance().getExitMap().clear();
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

---

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