Java Code Examples for java.sql.Statement#addBatch()

The following examples show how to use java.sql.Statement#addBatch() . You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example 1
Source File: TestXMLCatalogSchemaManager.java    From tajo with Apache License 2.0 6 votes vote down vote up
@Test
public void testCheckExistance() throws Exception {
  XMLCatalogSchemaManager manager;
  Statement stmt;
  
  manager = new XMLCatalogSchemaManager("schemas/derbytest/querytest");
  assertThat(manager.isLoaded(), is(true));
  
  stmt = conn.createStatement();
  stmt.addBatch("create schema " + manager.getCatalogStore().getSchema().getSchemaName());
  stmt.addBatch("set current schema " + manager.getCatalogStore().getSchema().getSchemaName());
  stmt.executeBatch();
  manager.createBaseSchema(conn);
  
  assertThat(manager.checkExistence(conn, DatabaseObjectType.TABLE, "TESTTABLE1"), is(true));
  assertThat(manager.checkExistence(conn, DatabaseObjectType.TABLE, "testtable2"), is(true));
  assertThat(manager.checkExistence(conn, DatabaseObjectType.TABLE, "TESTTABLE3"), is(false));
  assertThat(manager.checkExistence(conn, DatabaseObjectType.INDEX, "testtable1", "TESTINDEX1"), is(true));
  assertThat(manager.checkExistence(conn, DatabaseObjectType.TRIGGER, "TESTTRIGGER1"), is(true));
  assertThat(manager.checkExistence(conn, DatabaseObjectType.SEQUENCE, "TESTSEQ"), is(true));
  assertThat(manager.checkExistence(conn, DatabaseObjectType.VIEW, "TESTVIEW"), is(true));
}
 
Example 2
Source File: GfxdSerialWanDUnit.java    From gemfirexd-oss with Apache License 2.0 6 votes vote down vote up
public static Runnable batchInsert() {
  CacheSerializableRunnable senderConf = new CacheSerializableRunnable(
      "Sender Configurator") {
    @Override
    public void run2() throws CacheException {
      try {
        Connection conn = TestUtil.jdbcConn;
        Statement st = conn.createStatement();
        st.addBatch("insert into EMP.TESTTABLE values (1, 'First', 'J 604')");
        st.executeBatch();
      }
      catch (SQLException sqle) {
        throw GemFireXDRuntimeException.newRuntimeException(null, sqle);
      }
    }

  };
  return senderConf;
}
 
Example 3
Source File: CacheSessionDataTest.java    From gemfirexd-oss with Apache License 2.0 6 votes vote down vote up
public void testChangeIsoLevelSQLInBatch() throws SQLException {
    Statement s = createStatement();
    for (int i = 0; i < isoLevels.length; ++i) {
        s.addBatch("SET ISOLATION " + isoLevels[i].getSqlName());
    }

    try {
        s.executeBatch();
    } catch (SQLException e) {
        SQLException prev = e;
        while (e != null) {
            prev = e;
            e = e.getNextException();
        }
        throw prev;
    }
    verifyCachedIsolation(s.getConnection());
    s.close();
}
 
Example 4
Source File: MSSQLTestUtils.java    From aliyun-maxcompute-data-collectors with Apache License 2.0 6 votes vote down vote up
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 {
    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));
  }

}
 
Example 5
Source File: CacheSessionDataTest.java    From gemfirexd-oss with Apache License 2.0 6 votes vote down vote up
public void testChangeIsoLevelProcedureSqlBatch() throws SQLException {
    Statement s = createStatement();
    for (int i = 0; i < isoLevels.length; ++i) {
        s.addBatch("CALL SET_ISOLATION_SQL('" + isoLevels[i].getSqlName() + "')");
    }
    
    try {
        s.executeBatch();
    } catch (SQLException e) {
        SQLException prev = e;
        while (e != null) {
            prev = e;
            e = e.getNextException();
        }
        throw prev;
    }
    verifyCachedIsolation(s.getConnection());
    s.close();
}
 
Example 6
Source File: ScriptRunner.java    From lams with GNU General Public License v2.0 6 votes vote down vote up
/**
    * Executes an array of statements againt a connection.
    * Note that it will *WILL NOT* close the connection, commit or roll back.
    */
   protected void executeStatements(String[] statements, Connection newConn) throws DeployException {
Statement stmt = null;
try {
    stmt = newConn.createStatement();
    for (int i = 0, length = statements.length; i < length; i++) {
	stmt.addBatch(statements[i]);
    }

    stmt.executeBatch();

} catch (SQLException sqlex) {
    throw new DeployException("Could not execute statements", sqlex);
} finally {
    DbUtils.closeQuietly(stmt);
}
   }
 
Example 7
Source File: DataSourceTest.java    From gemfirexd-oss with Apache License 2.0 5 votes vote down vote up
private void assertStatementOK(String dsName, Connection conn, Statement s)
throws SQLException {

    // checks currently only implemented for embedded
    if (usingEmbedded())
    {
        SecurityCheck.assertSourceSecurity(s, "java.sql.Statement");
    }

    Connection c1 = s.getConnection();
    if (c1 != conn)
    {
        // with DerbyNetClient and any kind of DataSource, this goes wrong
        if (!usingDerbyNetClient() && (dsName.indexOf("DataSource") >= 0))
            fail ("incorrect connection object returned for Statement.getConnection()");
    }

    s.addBatch("insert into intTable values 1");
    s.addBatch("insert into intTable values 2,3");
    int[] states = s.executeBatch();
    if (states[0] != 1)
        fail ("invalid update count for first batch statement");
    if (states[1] != 2)
        fail ("invalid update count for second batch statement");

    ResultSet rs = s.executeQuery("VALUES 1");
    if (rs.getStatement() != s)
        fail ("incorrect Statement object returned for ResultSet.getStatement for " + dsName);
    rs.close();
    s.close();
}
 
Example 8
Source File: BatchUpdateTest.java    From gemfirexd-oss with Apache License 2.0 5 votes vote down vote up
public void testCombinationsOfClearBatch() throws SQLException {

        Statement stmt = createStatement();

        println("Positive Statement: add 3 statements, clear and execute batch");
        stmt.addBatch("insert into t1 values(2)");
        stmt.addBatch("insert into t1 values(2)");
        stmt.addBatch("insert into t1 values(2)");
        stmt.clearBatch();

        // Batch should be cleared, there should be no update count
        assertBatchUpdateCounts(new int[0], stmt.executeBatch());
        assertTableRowCount("T1", 0);

        println("Positive Statement: add 3 statements, clear batch, " +
            "add 3 more statements and execute batch");
        stmt.addBatch("insert into t1 values(2)");
        stmt.addBatch("insert into t1 values(2)");
        stmt.addBatch("insert into t1 values(2)");
        stmt.clearBatch();
        stmt.addBatch("insert into t1 values(2)");
        stmt.addBatch("insert into t1 values(2)");
        stmt.addBatch("insert into t1 values(2)");

        assertBatchUpdateCounts(new int[] {1,1,1}, stmt.executeBatch());
        assertTableRowCount("T1", 3);

        stmt.close();
        commit();
    }
 
Example 9
Source File: BatchUpdateTest.java    From gemfirexd-oss with Apache License 2.0 5 votes vote down vote up
public void testMultipleStatementsBatch() throws SQLException {

        Statement stmt = createStatement();
        ResultSet rs;

        println("Positive Statement: testing 2 inserts and 1 update batch");
        stmt.addBatch("insert into t1 values(2)");
        stmt.addBatch("update t1 set c1=4");
        stmt.addBatch("insert into t1 values(3)");

        assertBatchUpdateCounts(new int[] {1,1,1}, stmt.executeBatch());

        rs = stmt.executeQuery("select count(*) from t1 where c1=2");
        rs.next();
        assertEquals("expect 0 rows with c1 = 2", 0, rs.getInt(1));
        rs.close();

        rs = stmt.executeQuery("select count(*) from t1 where c1=4");
        rs.next();
        assertEquals("expect 1 row with c1 = 4", 1, rs.getInt(1));
        rs.close();

        rs = stmt.executeQuery("select count(*) from t1 where c1=3");
        rs.next();
        assertEquals("expect 1 row with c1 = 3", 1, rs.getInt(1));
        rs.close();

        assertTableRowCount("T1", 2);

        stmt.close();

        commit();
    }
 
Example 10
Source File: SqlExceptionTest.java    From gemfirexd-oss with Apache License 2.0 5 votes vote down vote up
/**
 * Verify that a SQLException generated by the derby network client
 * driver can be serialized (DERBY-790).
 */
public void testSerializedException() throws Exception {

    try {
        Connection conn = getConnection();
        Statement stmt = conn.createStatement();
        // generate some exception by inserting some duplicate
        // primary keys in the same batch
        // This will generate some chained / nested transactions
        // as well
        String insertData = "INSERT INTO tableWithPK values " +
            "(1, 1), (2, 2), (3, 3), (4, 4), (5, 5)";
        stmt.addBatch(insertData);
        stmt.addBatch(insertData);
        stmt.addBatch(insertData);
        stmt.executeBatch();

        // In case the statement completes successfully which is not
        // expected
        fail("Unexpected: SQL statement should have failed");
    } catch (SQLException se) {
        // Verify the SQLException can be serialized (DERBY-790)
        SQLException se_ser = recreateSQLException(se);
        // and that the original and serialized exceptions are equals
        assertSQLState("Unexpected SQL State", se.getSQLState(), se_ser);
        assertSQLExceptionEquals(se, se_ser);
    }
}
 
Example 11
Source File: PostgresITest.java    From crate with Apache License 2.0 5 votes vote down vote up
@Test
public void testExecuteBatchWithDifferentStatements() throws Exception {
    try (Connection conn = DriverManager.getConnection(url(RW), properties)) {
        conn.setAutoCommit(true);
        Statement stmt = conn.createStatement();
        stmt.executeUpdate("create table t (x int) with (number_of_replicas = 0)");
        ensureYellow();

        Statement statement = conn.createStatement();
        statement.addBatch("insert into t (x) values (1)");
        statement.addBatch("insert into t (x) values (2)");

        int[] results = statement.executeBatch();
        assertThat(results, is(new int[]{1, 1}));

        statement.executeUpdate("refresh table t");
        ResultSet resultSet = statement.executeQuery("select * from t order by x");
        assertThat(resultSet.next(), is(true));
        assertThat(resultSet.getInt(1), is(1));

        // add another batch
        statement.addBatch("insert into t (x) values (3)");

        // only the batches after last execution will be executed
        results = statement.executeBatch();
        assertThat(results, is(new int[]{1}));

        statement.executeUpdate("refresh table t");
        resultSet = statement.executeQuery("select * from t order by x desc");
        assertThat(resultSet.next(), is(true));
        assertThat(resultSet.getInt(1), is(3));
    }
}
 
Example 12
Source File: MySqlBatchStatementTest.java    From high-performance-java-persistence with Apache License 2.0 5 votes vote down vote up
private void executeStatement(Statement statement, String dml, AtomicInteger statementCount) throws SQLException {
    statement.addBatch(dml);
    int count = statementCount.incrementAndGet();
    if(count % getBatchSize() == 0) {
        statement.executeBatch();
    }
}
 
Example 13
Source File: VSysDbmsTableDisService.java    From danyuan-application with Apache License 2.0 5 votes vote down vote up
/**
 * @throws SQLException
 * @方法名 runsql
 * @功能 TODO(这里用一句话描述这个方法的作用)
 * @参数 @param sVSysDbmsTableDis
 * @参数 @return
 * @返回 String
 * @author Administrator
 * @throws
 */
public String runsql(VSysDbmsTableDis sVSysDbmsTableDis) throws SQLException {
	Connection connection = multiDatasourceConfig.getConnection(sVSysDbmsTableDis.getJdbcUuid());
	Statement statement = connection.createStatement();
	statement.execute(sVSysDbmsTableDis.getDisSql());
	statement.close();
	statement = connection.createStatement();
	statement.addBatch(sVSysDbmsTableDis.getDropSql());
	statement.addBatch(sVSysDbmsTableDis.getRenameSql());
	statement.addBatch(sVSysDbmsTableDis.getResetSql());
	statement.executeBatch();
	return "1";
}
 
Example 14
Source File: StandBatchInsertTest.java    From dble with GNU General Public License v2.0 5 votes vote down vote up
public static void testJDBCBatchInsert(Connection theCon)
        throws SQLException {
    theCon.setAutoCommit(false);
    Statement stmt = theCon.createStatement();
    int batchSize = 10;
    for (int i = 0; i < batchSize; i++) {
        String sql = "insert into travelrecord (id,user_id,traveldate,fee,days) values("
                + i + ",'wang','2014-01-05',510.5,3)";
        stmt.addBatch(sql);
    }
    stmt.executeBatch();
    theCon.commit();
    System.out.println("succees");
}
 
Example 15
Source File: DataSourceTest.java    From gemfirexd-oss with Apache License 2.0 5 votes vote down vote up
private void assertStatementOK(String dsName, Connection conn, Statement s)
throws SQLException {

    // checks currently only implemented for embedded
    if (usingEmbedded())
    {
        SecurityCheck.assertSourceSecurity(s, "java.sql.Statement");
    }

    Connection c1 = s.getConnection();
    if (c1 != conn)
    {
        // with DerbyNetClient and any kind of DataSource, this goes wrong
        if (!usingDerbyNetClient() && (dsName.indexOf("DataSource") >= 0))
            fail ("incorrect connection object returned for Statement.getConnection()");
    }

    s.addBatch("insert into intTable values 1");
    s.addBatch("insert into intTable values 2,3");
    int[] states = s.executeBatch();
    if (states[0] != 1)
        fail ("invalid update count for first batch statement");
    if (states[1] != 2)
        fail ("invalid update count for second batch statement");

    ResultSet rs = s.executeQuery("VALUES 1");
    if (rs.getStatement() != s)
        fail ("incorrect Statement object returned for ResultSet.getStatement for " + dsName);
    rs.close();
    s.close();
}
 
Example 16
Source File: J2EEDataSourceTest.java    From gemfirexd-oss with Apache License 2.0 5 votes vote down vote up
private void assertStatementOK(String dsName, Connection conn, Statement s)
throws SQLException {

    // checks currently only implemented for embedded 
    if (usingEmbedded())
    {
        SecurityCheck.assertSourceSecurity(s, "java.sql.Statement");
    }

    Connection c1 = s.getConnection();
    if (c1 != conn)
    {
        // with DerbyNetClient and any kind of DataSource, this goes wrong
        if (!usingDerbyNetClient() && (dsName.indexOf("DataSource") >= 0))
            fail ("incorrect connection object returned for Statement.getConnection()");
    }

    s.addBatch("insert into intTable values 1");
    s.addBatch("insert into intTable values 2,3");
    int[] states = s.executeBatch();
    if (states[0] != 1)
        fail ("invalid update count for first batch statement");
    if (states[1] != 2)
        fail ("invalid update count for second batch statement");

    ResultSet rs = s.executeQuery("VALUES 1");
    if (rs.getStatement() != s)
        fail ("incorrect Statement object returned for ResultSet.getStatement for " + dsName);
    rs.close();
    s.close();
}
 
Example 17
Source File: SqlUtil.java    From sagacity-sqltoy with Apache License 2.0 4 votes vote down vote up
/**
 * @todo <b>sql文件自动创建到数据库</b>
 * @param conn
 * @param sqlContent
 * @param batchSize
 * @param autoCommit
 * @throws Exception
 */
public static void executeBatchSql(Connection conn, String sqlContent, Integer batchSize, Boolean autoCommit)
		throws Exception {
	String splitSign = DataSourceUtils.getDatabaseSqlSplitSign(conn);
	// 剔除sql中的注释
	sqlContent = SqlUtil.clearMark(sqlContent);
	if (splitSign.indexOf("go") != -1) {
		sqlContent = StringUtil.clearMistyChars(sqlContent, " ");
	}
	// sqlserver sybase 数据库以go 分割,则整个sql文件作为一个语句执行
	String[] statments = StringUtil.splitExcludeSymMark(sqlContent, splitSign, sqlCommentfilters);
	boolean hasSetAutoCommit = false;
	// 是否自动提交
	if (autoCommit != null && autoCommit.booleanValue() != conn.getAutoCommit()) {
		conn.setAutoCommit(autoCommit.booleanValue());
		hasSetAutoCommit = true;
	}
	Statement stat = null;
	try {
		stat = conn.createStatement();
		int meter = 0;
		int realBatch = (batchSize == null || batchSize.intValue() > 1) ? batchSize.intValue() : 100;
		int totalRows = statments.length;
		int i = 0;
		for (String sql : statments) {
			if (StringUtil.isNotBlank(sql)) {
				meter++;
				logger.debug("正在批量执行的sql:{}", sql);
				stat.addBatch(sql);
			}
			if ((meter % realBatch) == 0 || i + 1 == totalRows) {
				stat.executeBatch();
				stat.clearBatch();
			}
			i++;
		}
	} catch (SQLException e) {
		e.printStackTrace();
		throw e;
	} finally {
		if (stat != null) {
			stat.close();
			stat = null;
		}
	}
	// 恢复conn原始autoCommit默认值
	if (hasSetAutoCommit) {
		conn.setAutoCommit(!autoCommit);
	}
}
 
Example 18
Source File: SimpleStatementHandler.java    From mybatis with Apache License 2.0 4 votes vote down vote up
@Override
public void batch(Statement statement) throws SQLException {
  String sql = boundSql.getSql();
  //调用Statement.addBatch
  statement.addBatch(sql);
}
 
Example 19
Source File: BatchTest.java    From incubator-iotdb with Apache License 2.0 4 votes vote down vote up
@SuppressWarnings("serial")
@Test
public void testExecuteBatchSQL1() throws SQLException, TException {
  Statement statement = connection.createStatement();
  statement.addBatch("sql1");
  resp = new TSStatus();
  resp =
      RpcUtils.getStatus(Collections.singletonList(RpcUtils.getStatus(TSStatusCode.SUCCESS_STATUS)));
  when(client.executeBatchStatement(any(TSExecuteBatchStatementReq.class))).thenReturn(resp);
  int[] result = statement.executeBatch();
  assertEquals(1, result.length);

  List<TSStatus> resExpected = new ArrayList<TSStatus>() {
    {
      add(RpcUtils.getStatus(TSStatusCode.SUCCESS_STATUS));
      add(RpcUtils.getStatus(TSStatusCode.SUCCESS_STATUS));
      add(RpcUtils.getStatus(TSStatusCode.SUCCESS_STATUS));
      add(RpcUtils.getStatus(TSStatusCode.SUCCESS_STATUS));
      add(RpcUtils.getStatus(TSStatusCode.SUCCESS_STATUS));
      add(RpcUtils.getStatus(TSStatusCode.SUCCESS_STATUS));
      add(RpcUtils.getStatus(TSStatusCode.SUCCESS_STATUS));
      add(RpcUtils.getStatus(TSStatusCode.SUCCESS_STATUS));
      add(RpcUtils.getStatus(TSStatusCode.SUCCESS_STATUS));
    }
  };
  resp.setSubStatus(resExpected);

  statement.clearBatch();
  statement.addBatch("SET STORAGE GROUP TO root.ln.wf01.wt01");
  statement.addBatch(
      "CREATE TIMESERIES root.ln.wf01.wt01.status WITH DATATYPE=BOOLEAN, ENCODING=PLAIN");
  statement.addBatch(
      "CREATE TIMESERIES root.ln.wf01.wt01.temperature WITH DATATYPE=FLOAT, ENCODING=RLE");
  statement
      .addBatch("insert into root.ln.wf01.wt01(timestamp,status) values(1509465600000,true)");
  statement
      .addBatch("insert into root.ln.wf01.wt01(timestamp,status) values(1509465660000,true)");
  statement
      .addBatch("insert into root.ln.wf01.wt01(timestamp,status) vvvvvv(1509465720000,false)");
  statement.addBatch(
      "insert into root.ln.wf01.wt01(timestamp,temperature) values(1509465600000,25.957603)");
  statement.addBatch(
      "insert into root.ln.wf01.wt01(timestamp,temperature) values(1509465660000,24.359503)");
  statement.addBatch(
      "insert into root.ln.wf01.wt01(timestamp,temperature) vvvvvv(1509465720000,20.092794)");
  result = statement.executeBatch();
  assertEquals(resp.getSubStatus().size(), result.length);
  for (int i = 0; i < resp.getSubStatus().size(); i++) {
    assertEquals(resExpected.get(i).code, result[i]);
  }
  statement.clearBatch();
}
 
Example 20
Source File: OLATUpgrade_5_1_0.java    From olat with Apache License 2.0 4 votes vote down vote up
/**
 * Deletes all publishers and subscribers of the publishertype "ReturnBoxController". The asynchronous notification of users when something changes in their
 * returnboxes is removed, since it is already done synchronously
 */
private void cleanupPublishersAndSubscribersOfReturnBoxes(final UpgradeManager upgradeManager, final UpgradeHistoryData uhd) {

    if (!uhd.getBooleanDataValue(TASK_CLEAN_UP_OF_PUB_AND_SUB_OF_RETURNBOXES_DONE)) {
        final String query_sub = "delete from o_noti_sub where fk_publisher in (select publisher_id from  o_noti_pub where publishertype='ReturnboxController');";
        final String query_pub = "delete from  o_noti_pub where publishertype='ReturnboxController';";

        Connection con = null;
        Statement deleteStmt = null;
        boolean cleaned = false;

        log.info("Audit:+--------------------------------------------------------------+");
        log.info("Audit:+... Deleting all publishers and subscribers of returnboxes ...+");
        log.info("Audit:+--------------------------------------------------------------+");

        try {
            con = upgradeManager.getDataSource().getConnection();
            deleteStmt = con.createStatement();
            deleteStmt.addBatch(query_sub);
            deleteStmt.addBatch(query_pub);
            deleteStmt.executeBatch();
        } catch (final SQLException e) {
            log.warn("%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%");
            log.warn("%%%          Please upgrade your database!          %%%");
            log.warn("%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%");
            log.warn("Could not execute system upgrade! Your database does not support the following syntax: 'WHERE experession IN (subquery)'." + "First query: "
                    + query_sub + " Second query: " + query_pub, e);
        } finally {
            try {
                deleteStmt.close();
            } catch (final SQLException e2) {
                log.warn("Could not close sql delete statement of system upgrade 5.1.0", e2);
                throw new StartupException("Could not close sql delete statement of system upgrade 5.1.0", e2);
            } finally {
                try {
                    con.close();
                } catch (final SQLException e3) {
                    log.warn("Could not close db connection.", e3);
                    throw new StartupException("Could not close db connection.", e3);
                }
            }
        }
        cleaned = true;
        uhd.setBooleanDataValue(TASK_CLEAN_UP_OF_PUB_AND_SUB_OF_RETURNBOXES_DONE, cleaned);

        upgradeManager.setUpgradesHistory(uhd, VERSION);
    }
}