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:com.hangum.tadpole.rdb.core.editors.main.execute.sub.ExecuteBatchSQL.java

/**
 * select? execute  ./*from  www  . j  a va 2s .co  m*/
 * @param errMsg 
 * 
 * @param listQuery
 * @param reqQuery
 * @param userDB
 * @param userType
 * @param intCommitCount
 * @param userEmail
 * @throws Exception
 */
public static void runSQLExecuteBatch(String errMsg, List<String> listQuery, final RequestQuery reqQuery,
        final UserDBDAO userDB, final String userType, final int intCommitCount, final String userEmail)
        throws Exception {
    if (!PermissionChecker.isExecute(userType, userDB, listQuery)) {
        throw new Exception(errMsg);
    }
    // Check the db access control 
    for (String strQuery : listQuery) {
        if (PublicTadpoleDefine.YES_NO.YES.name().equals(userDB.getDbAccessCtl().getDdl_lock())) {
            throw new Exception(errMsg);
        }

        PublicTadpoleDefine.QUERY_DML_TYPE queryType = SQLUtil.sqlQueryType(strQuery);
        if (reqQuery.getSqlType() == SQL_TYPE.DDL) {
            if (PublicTadpoleDefine.YES_NO.YES.name().equals(userDB.getDbAccessCtl().getDdl_lock())) {
                throw new Exception(errMsg);
            }
        }
        if (queryType == QUERY_DML_TYPE.INSERT) {
            if (PublicTadpoleDefine.YES_NO.YES.name().equals(userDB.getDbAccessCtl().getInsert_lock())) {
                throw new Exception(errMsg);
            }
        }
        if (queryType == QUERY_DML_TYPE.UPDATE) {
            if (PublicTadpoleDefine.YES_NO.YES.name().equals(userDB.getDbAccessCtl().getUpdate_lock())) {
                throw new Exception(errMsg);
            }
        }
        if (queryType == QUERY_DML_TYPE.DELETE) {
            if (PublicTadpoleDefine.YES_NO.YES.name().equals(userDB.getDbAccessCtl().getDelete_locl())) {
                throw new Exception(errMsg);
            }
        }
    }

    java.sql.Connection javaConn = null;
    Statement statement = null;

    try {
        if (reqQuery.isAutoCommit()) {
            SqlMapClient client = TadpoleSQLManager.getInstance(userDB);
            javaConn = client.getDataSource().getConnection();
        } else {
            javaConn = TadpoleSQLTransactionManager.getInstance(userEmail, userDB);
        }
        statement = javaConn.createStatement();

        int count = 0;
        for (String strQuery : listQuery) {
            //  ? commit? rollback?    ???
            if (!TransactionManger.calledCommitOrRollback(reqQuery.getSql(), userEmail, userDB)) {

                if (StringUtils.startsWithIgnoreCase(strQuery.trim(), "CREATE TABLE")) { //$NON-NLS-1$
                    strQuery = StringUtils.replaceOnce(strQuery, "(", " ("); //$NON-NLS-1$ //$NON-NLS-2$
                }

            }
            statement.addBatch(strQuery);

            if (++count % intCommitCount == 0) {
                statement.executeBatch();
                count = 0;
            }
        }
        statement.executeBatch();
    } catch (Exception e) {
        logger.error("Execute batch update", e); //$NON-NLS-1$
        throw e;
    } finally {
        try {
            statement.close();
        } catch (Exception e) {
        }

        if (reqQuery.isAutoCommit()) {
            try {
                javaConn.close();
            } catch (Exception e) {
            }
        }
    }
}

From source file:org.sipfoundry.sipxconfig.admin.dialplan.AttendantMigrationContextImpl.java

private void cleanSchema() {
    try {/*from ww  w.  ja  v a  2  s  .  c  o m*/
        Session currentSession = getHibernateTemplate().getSessionFactory().getCurrentSession();
        Connection connection = currentSession.connection();
        Statement statement = connection.createStatement();
        for (int i = 0; i < SQL.length; i++) {
            statement.addBatch(SQL[i]);
        }
        statement.executeBatch();
        statement.close();
    } catch (SQLException e) {
        LOG.warn("cleaning schema", e);
    }
}

From source file:dao.RuleDao.java

/**
 * Create a new rule based on incoming NewRule class
 * //from   w  ww .j a  va2 s. com
 * @param rule
 * @return
 * @throws SQLException
 */
public boolean createRule(NewRule rule) throws SQLException {
    Logger log = new Logger();
    log.out(Level.INFORMATIVE, "createRule", "Start making rules");
    Statement statement = this.getApexConnection().createStatement();

    statement.addBatch("DELETE RULE_COLUMN WHERE BUSINESSRULEID=" + rule.getBusinessrule());
    statement.addBatch("DELETE RULE_VALUE WHERE BUSINESSRULEID=" + rule.getBusinessrule());
    statement.addBatch("DELETE RULE_TABLE WHERE BUSINESSRULEID=" + rule.getBusinessrule());

    if (rule.getColumns() != null) {
        for (String column : rule.getColumns()) {
            statement.addBatch("INSERT INTO RULE_COLUMN (NAME, BUSINESSRULEID) VALUES ('" + column + "', '"
                    + rule.getBusinessrule() + "')");
        }
    } else {
        log.out(Level.ERROR, "createRule", "No columns found");
    }
    if (rule.getValues() != null) {
        for (String value : rule.getValues()) {
            statement.addBatch("INSERT INTO RULE_VALUE (NAME, BUSINESSRULEID) VALUES ('" + value + "', '"
                    + rule.getBusinessrule() + "')");
        }
    } else {
        log.out(Level.ERROR, "createRule", "No values found");
    }
    if (rule.getTables() != null) {
        for (String table : rule.getTables()) {
            statement.addBatch("INSERT INTO RULE_TABLE (NAME, BUSINESSRULEID) VALUES ('" + table + "', '"
                    + rule.getBusinessrule() + "')");
        }
    } else {
        log.out(Level.ERROR, "createRule", "No tables found");
    }
    statement.executeBatch();
    statement.close();
    return true;
}

From source file:com.cyclopsgroup.tornado.hibernate.taglib.ExecuteSqlsTagBase.java

/**
 * Override method processTag in class CreateTablesTag
 *
 * @see com.cyclopsgroup.waterview.utils.TagSupportBase#processTag(org.apache.commons.jelly.XMLOutput)
 */// w  w w .  ja  v a2 s.  c o  m
protected void processTag(XMLOutput output) throws Exception {
    HibernateTag hibernate = (HibernateTag) requireInside(HibernateTag.class);
    String[] sqls = getSqls(hibernate);
    Connection dbcon = hibernate.getConnection();
    Statement s = dbcon.createStatement();
    if (isBatched()) {
        for (int i = 0; i < sqls.length; i++) {
            s.addBatch(sqls[i]);
        }
        try {
            s.executeBatch();
        } catch (Exception e) {
            logger.debug("Dropping table error", e);
        }
    } else {
        for (int i = 0; i < sqls.length; i++) {
            try {
                s.execute(sqls[i]);
            } catch (Exception e) {
                logger.debug("Dropping table error", e);
            }
        }
    }
    s.close();
}

From source file:dao.RuleDao.java

/**
 * Deletes a rule from a target DB and from the local APEX db
 * //from w  w  w  .j  a v a  2s . c  om
 * @param id
 * @return
 * @throws Exception
 */
public boolean deleteRule(int id) throws Exception {
    int brgId = 0;
    String name = "";
    PreparedStatement statement = this.getApexConnection()
            .prepareStatement("SELECT BRG_PROJECTID, NAME FROM BUSINESSRULE WHERE ID=?");
    statement.setInt(1, id);
    ResultSet result = statement.executeQuery();
    while (result.next()) {
        brgId = result.getInt("BRG_PROJECTID");
        name = result.getString("NAME");
    }
    statement.close();
    Statement statementTarget = null;

    try {
        Project project = this.getProject(brgId);
        statementTarget = this.getTargetConnection(project.getTargetConfig()).createStatement();
        statementTarget.executeUpdate("DROP TRIGGER " + name.toUpperCase());
        statementTarget.close();
    } catch (Exception e) {
        Logger log = new Logger();
        log.out(Level.ERROR, "deleteRule", "Rule or project doesn't excist");
    }

    Statement statementRemoveApex = this.getApexConnection().createStatement();
    statementRemoveApex.addBatch("DELETE FROM RULE_COLUMN WHERE BUSINESSRULEID=" + id);
    statementRemoveApex.addBatch("DELETE FROM RULE_TABLE WHERE BUSINESSRULEID=" + id);
    statementRemoveApex.addBatch("DELETE FROM RULE_VALUE WHERE BUSINESSRULEID=" + id);
    statementRemoveApex.addBatch("DELETE FROM BUSINESSRULE WHERE ID=" + id);
    statementRemoveApex.executeBatch();
    statementRemoveApex.close();

    return true;
}

From source file:org.xmetdb.rest.protocol.db.user.test.User_crud_test.java

License:asdf

@Override
protected IQueryUpdate<T, DBUser> createQuery() throws Exception {
    IDatabaseConnection c = getConnection();
    Statement st = c.getConnection().createStatement();
    st.addBatch("USE aalocal_test;");
    st.addBatch("DELETE FROM aalocal_test.users;");
    st.addBatch("DELETE FROM aalocal_test.user_registration;");
    st.addBatch("DELETE FROM aalocal_test.user_roles;");
    st.executeBatch();/*  www.  j  a va2  s. com*/
    ITable table = c.createQueryTable("EXPECTED", String.format("SELECT user_name from aalocal_test.users"));
    Assert.assertEquals(0, table.getRowCount());
    table = c.createQueryTable("EXPECTED",
            String.format("SELECT user_name from aalocal_test.user_registration"));
    Assert.assertEquals(0, table.getRowCount());
    table = c.createQueryTable("EXPECTED", String.format("SELECT role_name from aalocal_test.user_roles"));
    Assert.assertEquals(0, table.getRowCount());
    st.close();
    c.close();

    DBUser user = new DBUser();
    user.setFirstname("QWERTY");
    user.setLastname("ASDFG");
    user.setUserName("testuser");
    user.setCredentials(new UserCredentials(null, "test"));
    return (IQueryUpdate<T, DBUser>) new CreateUser(user, new UserRegistration(code), "aalocal_test");
}

From source file:de.unidue.inf.is.ezdl.dlbackend.AbstractBackendTestBase.java

/**
 * Creates tables in the test database./*from   w ww  .j ava  2 s  .  c  o m*/
 * 
 * @param createTableStatementsResourceName
 */
protected final void createTables(String createTableStatementsResourceName) {
    Connection connection = null;
    try {
        InputStream is = getClass().getResourceAsStream(createTableStatementsResourceName);
        String createTableStatements = IOUtils.toString(is, "UTF-8");
        String[] createTableStatementsArray = createTableStatements.split("\\n\\s*\\n");
        connection = provider.connection();
        Statement st = connection.createStatement();
        for (String createTableStatement : createTableStatementsArray) {
            st.addBatch(createTableStatement);
        }
        st.executeBatch();
    } catch (SQLException e) {
        throw new RuntimeException(e);
    } catch (IOException e) {
        throw new RuntimeException(e);
    } finally {
        ClosingUtils.close(connection);
    }
}

From source file:org.apache.sqoop.manager.sqlserver.MSSQLTestUtils.java

public void populateLineItem() {
    String sql = "insert into tpch1m_lineitem values (1,2,3,4,5,6,7,8,'AB',"
            + "'CD','abcd','efgh','hijk','dothis','likethis','nocomments')";
    String sql2 = "insert into tpch1m_lineitem values (2,3,4,5,6,7,8,9,'AB'"
            + ",'CD','abcd','efgh','hijk','dothis','likethis','nocomments')";
    String sql3 = "insert into tpch1m_lineitem values (3,4,5,6,7,8,9,10,'AB',"
            + "'CD','abcd','efgh','hijk','dothis','likethis','nocomments')";
    String sql4 = "insert into tpch1m_lineitem values (4,5,6,7,8,9,10,11,'AB'"
            + ",'CD','abcd','efgh','hijk','dothis','likethis','nocomments')";
    Connection dbcon = this.getConnection();
    Statement st;
    try {/*from   www  . j  ava  2  s .c  om*/
        st = dbcon.createStatement();
        st.addBatch(sql);
        st.addBatch(sql2);
        st.addBatch(sql3);
        st.addBatch(sql4);
        int[] res = st.executeBatch();

        System.out.println(res);
    } catch (SQLException e) {
        LOG.error(StringUtils.stringifyException(e));
    }

}

From source file:de.tudarmstadt.ukp.dkpro.core.io.jdbc.JdbcReaderExample.java

@Test
public void hsqldbExampleTest() throws SQLException, UIMAException, IOException {
    // Setup in-memory database.
    Connection conn = null;// ww w.j a va2 s  .  c om
    Statement stmnt = null;
    try {
        conn = DriverManager.getConnection("jdbc:hsqldb:mem:/" + DB_NAME, DB_USER, DB_PASS);
        stmnt = conn.createStatement();
        stmnt.addBatch("CREATE TABLE " + TBL_NAME + " (title varchar(50), text varchar(100));");
        stmnt.addBatch("INSERT INTO " + TBL_NAME + " (title, text) VALUES ('title1', 'text...1');");
        stmnt.addBatch("INSERT INTO " + TBL_NAME + " (title, text) VALUES ('title2', 'text...2');");
        stmnt.addBatch("INSERT INTO " + TBL_NAME + " (title, text) VALUES ('title3', 'text...3');");
        stmnt.addBatch("INSERT INTO " + TBL_NAME + " (title, text) VALUES ('title4', 'text...4');");
        stmnt.executeBatch();
    } finally {
        DbUtils.closeQuietly(stmnt);
        DbUtils.closeQuietly(conn);
    }
    // Read out with JdbcReader.
    CollectionReader jdbcReader = CollectionReaderFactory.createReader(JdbcReader.class,
            JdbcReader.PARAM_DATABASE, "test_db", JdbcReader.PARAM_USER, "root", JdbcReader.PARAM_PASSWORD, "",
            JdbcReader.PARAM_QUERY, query, JdbcReader.PARAM_DRIVER, "org.hsqldb.jdbc.JDBCDriver",
            JdbcReader.PARAM_CONNECTION, "jdbc:hsqldb:mem:");

    int i = 1;
    while (jdbcReader.hasNext()) {
        // Does it still have a next row?
        jdbcReader.hasNext();
        // Really?
        jdbcReader.hasNext();

        CAS cas = JCasFactory.createJCas().getCas();
        jdbcReader.getNext(cas);
        Assert.assertEquals("title" + i, DocumentMetaData.get(cas).getDocumentTitle());
        Assert.assertEquals("text..." + i, cas.getDocumentText());
        i++;
    }
}

From source file:org.rti.zcore.dar.utils.DatabaseUtils.java

/**
 * Execute sql batch file with line-break-separated statements
 * @param conn/*  w  w  w  .ja  v a  2 s  .  co  m*/
 * @param statements
 * @return
 * @throws Exception
 */
public static int[] executeQueryBatch(Connection conn, String statements) throws Exception {

    int[] updateCounts;
    try {
        Statement s = conn.createStatement();
        String[] sqlArray = statements.split("\n");
        for (int i = 0; i < sqlArray.length; i++) {
            String sql = sqlArray[i];
            s.addBatch(sql);
        }
        updateCounts = s.executeBatch();
        s.clearBatch();
        s.close();
    } catch (Exception ex) {
        log.info("Sql: " + statements);
        log.error(ex);
        throw new ServletException("Cannot retrieve results:", ex);
    }
    return updateCounts;
}