Java Statement.addBatch方法代码示例

import java.sql.Statement; //导入方法依赖的package包/类
/**
 * Should measure statement.
 *
 * @throws SQLException
 *             the SQL exception
 */
@Test
public void shouldMeasureStatement() throws SQLException {
	Statement statements[] = { dataSource.getConnection().createStatement(),
			dataSource.getConnection().createStatement(1, 1), dataSource.getConnection().createStatement(1, 1, 1) };
	for (Statement statement : statements) {
		statement.execute("select");
		statement.execute("select", 1);
		statement.execute("select", new int[] {});
		statement.execute("select", new String[] {});
		statement.executeQuery("select");
		statement.executeUpdate("select");
		statement.executeUpdate("select", 1);
		statement.executeUpdate("select", new int[] {});
		statement.executeUpdate("select", new String[] {});
		statement.executeLargeUpdate("select");
		statement.executeLargeUpdate("select", 1);
		statement.executeLargeUpdate("select", new int[] {});
		statement.executeLargeUpdate("select", new String[] {});
		statement.addBatch("select");
		statement.executeBatch();
		statement.executeLargeBatch();
	}
	assertThat(registry.get("jdbc.Statement.Invocations", "select")).isEqualTo(1L * 15 * statements.length);
	assertThat(registry.get("jdbc.Statement.Durations", "select")).isEqualTo(123456789L * 15 * statements.length);
}
 

import java.sql.Statement; //导入方法依赖的package包/类
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));
  }

}
 

import java.sql.Statement; //导入方法依赖的package包/类
/**
 * Updates the status for configuration & test when they're done.
 *
 * @param connection a JDBC connection to the database.
 * @param configName name of the configuration.
 * @param testName name of the test.
 * @param resultTableName name of the appropriate result table.
 * @param timeStamp start time of the configuration.
 * @param endTime end time of the configuration.
 */
private void updateStatus(Connection connection, String configName, String testName,
    String resultTableName, long timeStamp, long endTime) throws SQLException {
  String query1 = "UPDATE TESTS SET STATUS='DONE', END_TIME=" + endTime + " WHERE TEST_NAME='"
      + testName + "' AND RESULT_TABLE='" + resultTableName + "';";

  String query2 = "UPDATE CONFIG_EXECUTION SET STATUS='DONE', END_TIME=" + endTime
      + " WHERE CONFIG_NAME='" + configName + "' AND START_TIME=" + timeStamp + " ;";
  Statement statement = null;
  try {
    statement = connection.createStatement();
    statement.addBatch(query1);
    statement.addBatch(query2);
    if (log.isDebugEnabled())
      log.debug("Executing Status Update: " + query1);
    if (log.isDebugEnabled())
      log.debug("Executing Status Update: " + query2);
    statement.executeBatch();
  } finally {
    Utility.closeDBResources(statement, null);
  }
}
 

import java.sql.Statement; //导入方法依赖的package包/类
/**
 * Updates the status for configuration & test when they're done.
 *
 * @param connection a JDBC connection to the database.
 * @param browserList list of browsers to put in the BROWSERS Table if not already exist
 */
private void putInBrowserTable(Connection connection, List<Browser> browserList)
    throws SQLException {
  List<String> queryList = new ArrayList<>();
  for (Browser browser : browserList) {
    queryList.add(
        "INSERT INTO BROWSERS(NAME, VERSION, PLATFORM) " + "SELECT '" + browser.getName() + "','"
            + browser.getVersion() + "','" + browser.getPlatform() + "' " + "WHERE NOT EXISTS( "
            + "SELECT 1 FROM BROWSERS " + "WHERE NAME='" + browser.getName() + "' AND VERSION='"
            + browser.getVersion() + "' AND PLATFORM='" + browser.getPlatform() + "');");
  }
  Statement statement = null;
  try {
    statement = connection.createStatement();
    for (String query : queryList) {
      statement.addBatch(query);
      if (log.isDebugEnabled())
        log.debug("Executing browser entry Update: " + query);
      System.out.println("Executing browser entry Update: " + query);
    }
    statement.executeBatch();
  } finally {
    Utility.closeDBResources(statement, null);
  }
}
 

import java.sql.Statement; //导入方法依赖的package包/类
@Override public ExecuteBatchResult prepareAndExecuteBatch(StatementHandle h,
    List<String> sqlCommands) throws NoSuchStatementException {
  try {
    // Get the statement
    final StatementInfo info = statementCache.getIfPresent(h.id);
    if (info == null) {
      throw new NoSuchStatementException(h);
    }

    // addBatch() for each sql command
    final Statement stmt = info.statement;
    for (String sqlCommand : sqlCommands) {
      stmt.addBatch(sqlCommand);
    }

    // Execute the batch and return the results
    return new ExecuteBatchResult(AvaticaUtils.executeLargeBatch(stmt));
  } catch (SQLException e) {
    throw propagate(e);
  }
}
 

import java.sql.Statement; //导入方法依赖的package包/类
@Test
public void testQueryBatch() throws SQLException
{
    Statement batch = conn.createStatement();
    for (Data d : data) {
        String q = String.format("update %s set value='%s'", d.tablename, "whatever");
        batch.addBatch(q);
    }
    try {
        int[] resultCodes = batch.executeBatch();
        assertEquals(data.length, resultCodes.length);
        int total_cnt = 0;
        for (int i = 0; i < data.length; ++i) {
            assertEquals(data[i].good.length, resultCodes[i]);
            total_cnt += data[i].good.length;
        }
        //Test update count
        assertEquals(total_cnt, batch.getUpdateCount());
    }
    catch(SQLException e) {
        System.err.printf("ERROR: %s\n", e.getMessage());
        fail();
    }
}
 

import java.sql.Statement; //导入方法依赖的package包/类
private void addBatchItems(Statement statement, PreparedStatement pStmt, String tableName, int i) throws SQLException {
    pStmt.setString(1, "ps_batch_" + i);
    pStmt.setString(2, "ps_batch_" + i);
    pStmt.addBatch();

    statement.addBatch("INSERT INTO " + tableName + " (strdata1, strdata2) VALUES (\"s_batch_" + i + "\",\"s_batch_" + i + "\")");
}
 

import java.sql.Statement; //导入方法依赖的package包/类
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");
}
 

import java.sql.Statement; //导入方法依赖的package包/类
void createDBThenInit() throws Exception {
	Configurator cfg = ConfiguratorFactory.getDefaultInstance();
	String propCreate = cfg.getProperty("create", "false");
	cfg.setProperty("create", "true");
	Connection connection = ConnectionManager.getConnection(cfg);
	//create db success;
	//do DDL create table
	Statement stats = connection.createStatement();
	
	String[] ddls = {
		"CREATE TABLE ITEMS(\r\n" + 
		"ID BIGINT PRIMARY KEY,\r\n" + 
		"NAME VARCHAR(128),\r\n" + 
		"DESCRIPTION VARCHAR(512),\r\n" + 
		"TYPE SMALLINT,\r\n" + 
		"CONTENT CLOB,\r\n" + 
		"READ_COUNT SMALLINT,\r\n" + 
		"CREATE_TIME TIMESTAMP,\r\n" + 
		"UPDATE_TIME TIMESTAMP,\r\n" + 
		"PARENT BIGINT\r\n" + 
		")",
		"CREATE TABLE TAGS(\r\n" + 
		"ID BIGINT PRIMARY KEY,\r\n" + 
		"NAME VARCHAR(128),\r\n" + 
		"CREATE_TIME TIMESTAMP\r\n" + 
		")"
	};
	for(String sql:ddls) {
		stats.addBatch(sql);
	}
	int[] rsts = stats.executeBatch();
	for(int i=0;i<rsts.length;i++) {
		ProcessLogger.debug("Executed {0} DDL result:{1}",i,rsts[i]);
	}
	cfg.setProperty("create", propCreate);
}
 

import java.sql.Statement; //导入方法依赖的package包/类
/**
 * Tests fix for BUG#30550 - executeBatch() on an empty batch when there are
 * no elements in the batch causes a divide-by-zero error when rewriting is
 * enabled.
 * 
 * @throws Exception
 *             if the test fails
 */
public void testBug30550() throws Exception {
    createTable("testBug30550", "(field1 int)");

    Connection rewriteConn = getConnectionWithProps("rewriteBatchedStatements=true");
    PreparedStatement batchPStmt = null;
    Statement batchStmt = null;

    try {
        batchStmt = rewriteConn.createStatement();
        assertEquals(0, batchStmt.executeBatch().length);

        batchStmt.addBatch("INSERT INTO testBug30550 VALUES (1)");
        int[] counts = batchStmt.executeBatch();
        assertEquals(1, counts.length);
        assertEquals(1, counts[0]);
        assertEquals(0, batchStmt.executeBatch().length);

        batchPStmt = rewriteConn.prepareStatement("INSERT INTO testBug30550 VALUES (?)");
        batchPStmt.setInt(1, 1);
        assertEquals(0, batchPStmt.executeBatch().length);
        batchPStmt.addBatch();
        counts = batchPStmt.executeBatch();
        assertEquals(1, counts.length);
        assertEquals(1, counts[0]);
        assertEquals(0, batchPStmt.executeBatch().length);
    } finally {
        if (batchPStmt != null) {
            batchPStmt.close();
        }

        if (batchStmt != null) {
            batchStmt.close();
        }
        if (rewriteConn != null) {
            rewriteConn.close();
        }
    }
}
 

import java.sql.Statement; //导入方法依赖的package包/类
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");
}
 

import java.sql.Statement; //导入方法依赖的package包/类
public static int[] executeUpdate(Connection conn, String[] sqls) throws Exception {
	boolean isShowSql = false;
	List<String> sqlList = new ArrayList<String>();
	for (String sql : sqls) {
		if (StringUtils.hasText(sql)) {
			sqlList.add(sql);
		}
	}
	String[] sqlArray = sqlList.toArray(new String[] {});
	if (isShowSql) {
		for (String s : sqlArray) {
			System.out.println(s);
		}
	}
	Statement st = null;
	int[] rowsAffected = new int[] { sqlArray.length };
	try {
		conn.setAutoCommit(true);
		st = conn.createStatement();
		if (DbJdbcUtils.supportsBatchUpdates(conn)) {
			for (String currentSql : sqlArray) {
				st.addBatch(currentSql);
			}
			rowsAffected = st.executeBatch();
		} else {
			for (int i = 0; i < sqlArray.length; i++) {
				if (!st.execute(sqlArray[i])) {
					rowsAffected[i] = st.getUpdateCount();
				}
			}
		}
	} catch (SQLException e) {
		throw new SQLException(e);
	} finally {
		DbJdbcUtils.closeStatement(st);
	}
	return rowsAffected;
}
 

import java.sql.Statement; //导入方法依赖的package包/类
/**
 * 批量执行
 * 
 * @param sqls
 *            sql集合
 * @param params
 *            参数
 * @return 变更集合
 */
public Object executeBatchUpdate(String sqls, Object... params) {
    Connection con = getCon();
    try {
        if (sqls.indexOf(SEMICOLON) == -1) {
            return executeUpdate(sqls, params);
        }
        String[] sqlss = sqls.replaceAll("\r|\n", EMPTY).split(SEMICOLON);
        int length = sqlss.length;
        Statement st = con.createStatement();
        for (int i = 0; i < length; i++) {
            String sql = sqlss[i];
            if (logger.isDebugEnabled()) {
                logger.debug("addBatch: " + sql);
            }
            st.addBatch(sql);
        }
        if (logger.isDebugEnabled()) {
            logger.debug("executeBatchUpdate size: " + length);
        }
        int[] batch = st.executeBatch();
        st.close();
        return batch;
    } catch (Exception e) {
        throw new JkException(e);
    } finally {
        doReleaseConnection(con);
    }
}
 

import java.sql.Statement; //导入方法依赖的package包/类
/**
 * Tests fix for BUG#77681 - rewrite replace sql like insert when rewriteBatchedStatements=true (contribution)
 * 
 * When using 'rewriteBatchedStatements=true' we rewrite several batched statements into one single query by extending its VALUES clause. Although INSERT
 * REPLACE have the same syntax, this wasn't happening for REPLACE statements.
 * 
 * This tests the number of queries actually sent to server when rewriteBatchedStatements is used and not by using a StatementInterceptor. The test is
 * repeated for server side prepared statements. Without the fix, this test fails while checking the number of expected REPLACE queries.
 */
public void testBug77681() throws Exception {
    createTable("testBug77681", "(id INT, txt VARCHAR(50), PRIMARY KEY (id))");

    Properties props = new Properties();
    props.setProperty("statementInterceptors", TestBug77681StatementInterceptor.class.getName());

    for (int tst = 0; tst < 4; tst++) {
        props.setProperty("useServerPrepStmts", Boolean.toString((tst & 0x1) != 0));
        props.setProperty("rewriteBatchedStatements", Boolean.toString((tst & 0x2) != 0));
        Connection testConn = getConnectionWithProps(props);

        PreparedStatement testPstmt = testConn.prepareStatement("INSERT INTO testBug77681 VALUES (?, ?)");
        testPstmt.setInt(1, 1);
        testPstmt.setString(2, "one");
        testPstmt.addBatch();
        testPstmt.setInt(1, 2);
        testPstmt.setString(2, "two");
        testPstmt.addBatch();
        testPstmt.setInt(1, 3);
        testPstmt.setString(2, "three");
        testPstmt.addBatch();
        testPstmt.setInt(1, 4);
        testPstmt.setString(2, "four");
        testPstmt.addBatch();
        testPstmt.setInt(1, 5);
        testPstmt.setString(2, "five");
        testPstmt.addBatch();
        testPstmt.executeBatch();
        testPstmt.close();

        testPstmt = testConn.prepareStatement("REPLACE INTO testBug77681 VALUES (?, ?)");
        testPstmt.setInt(1, 2);
        testPstmt.setString(2, "TWO");
        testPstmt.addBatch();
        testPstmt.setInt(1, 4);
        testPstmt.setString(2, "FOUR");
        testPstmt.addBatch();
        testPstmt.setInt(1, 6);
        testPstmt.setString(2, "SIX");
        testPstmt.addBatch();
        testPstmt.executeBatch();
        testPstmt.close();

        Statement testStmt = testConn.createStatement();
        testStmt.clearBatch();
        testStmt.addBatch("INSERT INTO testBug77681 VALUES (7, 'seven')");
        testStmt.addBatch("INSERT INTO testBug77681 VALUES (8, 'eight')");
        testStmt.addBatch("INSERT INTO testBug77681 VALUES (9, 'nine')");
        testStmt.addBatch("INSERT INTO testBug77681 VALUES (10, 'ten')");
        testStmt.addBatch("INSERT INTO testBug77681 VALUES (11, 'eleven')");
        testStmt.executeBatch();

        testStmt.clearBatch();
        testStmt.addBatch("REPLACE INTO testBug77681 VALUES (8, 'EIGHT')");
        testStmt.addBatch("REPLACE INTO testBug77681 VALUES (10, 'TEN')");
        testStmt.addBatch("REPLACE INTO testBug77681 VALUES (12, 'TWELVE')");
        testStmt.addBatch("REPLACE INTO testBug77681 VALUES (14, 'FOURTEEN')");
        testStmt.addBatch("REPLACE INTO testBug77681 VALUES (16, 'SIXTEEN')");
        testStmt.executeBatch();

        this.stmt.executeUpdate("DELETE FROM testBug77681");
    }
}
 

import java.sql.Statement; //导入方法依赖的package包/类
@Override
	public Status updatePoints(List<TsPoint> points) {
		//timestamp->device->sensor
		long costTime=0;
//		int count=0;
		Connection connection = null;
		Statement statement = null;
		try {
			connection=getConnection();
			statement=connection.createStatement();
			if(points!=null){
				StringBuilder sc=new StringBuilder();
				for(TsPoint point:points){
					sc.append("update ");
					sc.append(ROOT_SERIES_NAME);
					sc.append(".");
					sc.append(point.getDeviceCode());
					sc.append(".");
					sc.append(point.getSensorCode());
					sc.append(" set value=");
					sc.append(point.getValue());
					sc.append(" where");
					sc.append(" time=");
					sc.append(point.getTimestamp());
					statement.addBatch(sc.toString());
					sc.setLength(0);
				}
			}
			long startTime=System.nanoTime();
			statement.executeBatch();//批量更新
			statement.clearBatch();
			long endTime=System.nanoTime();
			costTime=endTime-startTime;
		}catch(Exception e){
			return Status.FAILED(-1);
		}finally{
			closeStatement(statement);
			closeConnection(connection);
		}
		return Status.OK(costTime);
	}