JDBC 中的Batch

      在jdbc2.0里增加了批量处理的功能（batch），其允许将多个sql语句作为一个单元送至数据库去执行，这样做可以提高操作效率。

      在操作大量的数据时, 先Prepare一个INSERT语句再多次的执行, 会导致很多次的网络连接. 要减少JDBC的调用次数改善性能, 可以使用PreparedStatement或是Statement 的AddBatch()方法一次性发送多个给数据库（PreparedStatement和Statement 的区别就不说了）

例如：

使用普通的for循环时，效率要低的多

public class testplain {
	public static void main(String[] args) throws Exception {
		Connection conn = getOracleConnection();
		PreparedStatement ps = null;
		try {
			ps = conn
					.prepareStatement("INSERT INTO batchtab employees values (?, ?)");

			conn.setAutoCommit(false);
			for (int n = 1; n < 3; n++) {
				Integer i = new Integer(n);
				ps.setString(1, i.toString());
				ps.setString(2, "value" + i.toString());
				ps.executeUpdate();
			}
			conn.commit();
		}catch (SQLException ex) {
			System.out.println("SQLException: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("Message: " + ex.getMessage());
			System.out.println("Vendor error code: " + ex.getErrorCode());
		} catch (Exception e) {
			e.printStackTrace();
			System.err.println("Exception: " + e.getMessage());
		} finally {
			if (conn != null)
				conn.close();
			if (ps != null)
				ps.close();

		}
	}

	public static Connection getOracleConnection() throws Exception {
		String driver = "oracle.jdbc.driver.OracleDriver";
		String url = "jdbc:oracle:thin:@localhost:1521:test";
		String username = "test";
		String password = "test";

		Class.forName(driver); // load Oracle driver
		Connection conn = DriverManager.getConnection(url, username, password);
		return conn;
	}
}

使用batch，将多个sql操作作为一个单元传输给数据库：

public class testbatch {
	public static void main(String[] args) throws Exception {
		Connection conn = getOracleConnection();
		ResultSet rs = null;
//		Statement stmt = null;
		PreparedStatement stmt=null;
		try {
//			 Create a prepared statement
			String sql = "INSERT INTO batchtab employees values (?, ?)";
			stmt = conn.prepareStatement(sql);
			conn.setAutoCommit(false);
			stmt.clearBatch();
//			 Insert 3 rows of data
	        for (int i=0; i<3; i++) {
	            stmt.setString(1, ""+i);
	            stmt.setString(2, "batch_value"+i);
	            stmt.addBatch();
	        }
			int[] updateCounts = stmt.executeBatch();
			System.out.println(updateCounts);
			conn.commit();
			sql="SELECT * FROM batchtab";
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			while (rs.next()) {
				String id = rs.getString("batch_id");
				String name = rs.getString("batch_value");
				System.out.println("id=" + id + "  name=" + name);
			}
		} catch (BatchUpdateException b) {
			System.out.println("SQLException: " + b.getMessage());
			System.out.println("SQLState: " + b.getSQLState());
			System.out.println("Message: " + b.getMessage());
			System.out.println("Vendor error code: " + b.getErrorCode());
			System.out.print("Update counts: ");
			int[] updateCounts = b.getUpdateCounts();
			for (int i = 0; i < updateCounts.length; i++) {
				System.out.print(updateCounts[i] + " ");
			}
		} catch (SQLException ex) {
			System.out.println("SQLException: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("Message: " + ex.getMessage());
			System.out.println("Vendor error code: " + ex.getErrorCode());
		} catch (Exception e) {
			e.printStackTrace();
			System.err.println("Exception: " + e.getMessage());
		} finally {
			if( conn != null )
			conn.close();
			if(stmt !=null)
			stmt.close();
			if(rs !=null)
			rs.close();	
		}
	}

	public static Connection getOracleConnection() throws Exception {
		String driver = "oracle.jdbc.driver.OracleDriver";
		String url = "jdbc:oracle:thin:@localhost:1521:test";
		String username = "test";
		String password = "test";

		Class.forName(driver); // load Oracle driver
		Connection conn = DriverManager.getConnection(url, username, password);
		return conn;
	}
}

  

下面举个例子（使用statement的例子）：

public class testbatch {
	public static void main(String[] args) throws Exception {
		Connection conn = getOracleConnection();
		ResultSet rs = null;
		Statement stmt = null;
		try {

			stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
					ResultSet.CONCUR_UPDATABLE);
			conn.setAutoCommit(false);
			stmt.clearBatch();
			stmt.addBatch("INSERT INTO batchtab(batch_id, batch_value) VALUES('1', 'batch_value1')");
			stmt.addBatch("INSERT INTO batchtab(batch_id, batch_value) VALUES('2', 'batch_value2')");
			stmt.addBatch("INSERT INTO batchtab(batch_id, batch_value) VALUES('3', 'batch_value3')");
			int[] updateCounts = stmt.executeBatch();
			System.out.println(updateCounts);
			conn.commit();
			rs = stmt.executeQuery("SELECT * FROM batchtab");
			while (rs.next()) {
				String id = rs.getString("batch_id");
				String name = rs.getString("batch_value");
				System.out.println("id=" + id + "  name=" + name);
			}
		} catch (BatchUpdateException b) {
			System.out.println("SQLException: " + b.getMessage());
			System.out.println("SQLState: " + b.getSQLState());
			System.out.println("Message: " + b.getMessage());
			System.out.println("Vendor error code: " + b.getErrorCode());
			System.out.print("Update counts: ");
			int[] updateCounts = b.getUpdateCounts();
			for (int i = 0; i < updateCounts.length; i++) {
				System.out.print(updateCounts[i] + " ");
			}
		} catch (SQLException ex) {
			System.out.println("SQLException: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("Message: " + ex.getMessage());
			System.out.println("Vendor error code: " + ex.getErrorCode());
		} catch (Exception e) {
			e.printStackTrace();
			System.err.println("Exception: " + e.getMessage());
		} finally {
			if( conn != null )
			conn.close();
			if(stmt !=null)
			stmt.close();
			if(rs !=null)
			rs.close();	
		}
	}

	public static Connection getOracleConnection() throws Exception {
		String driver = "oracle.jdbc.driver.OracleDriver";
		String url = "jdbc:oracle:thin:@localhost:1521:testbatch";
		String username = "test";
		String password = "test";

		Class.forName(driver); // load Oracle driver
		Connection conn = DriverManager.getConnection(url, username, password);
		return conn;
	}
}