package test.main.DB;

import java.sql.*;

public class TestBatch{

	/**
	 * JDBC批量Insert优化(上)
	 * 
	 * @author leizhimin 2009-7-29 10:03:10
	 */
	public static TestBatchDbConnection	myBroker	= null;
	static {
		myBroker = new TestBatchDbConnection();
	}

	/**
	 * 初始化测试环境
	 * 
	 * @throws SQLException
	 *             异常时抛出
	 * @throws ClassNotFoundException
	 */
	public static void init() throws SQLException, ClassNotFoundException {
		Connection conn = myBroker.getConnection();
		Statement stmt = conn.createStatement();
		stmt.addBatch("IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tuser]') AND type in (N'U'))DROP TABLE [dbo].[tuser]");
		stmt.addBatch("CREATE TABLE tuser (\n" + " id varchar(20) DEFAULT NULL,\n"
				+ " name varchar(12) DEFAULT NULL,\n" + " remark varchar(24) DEFAULT NULL,\n"
				+ " createtime datetime DEFAULT NULL,\n" + " updatetime datetime DEFAULT NULL"
				+ ")");
		stmt.executeBatch();
		myBroker.freeConnection(conn);
	}

	/**
	 * 1000条静态SQL插入
	 * 
	 * @throws Exception
	 *             异常时抛出
	 */
	public static void testInsert() throws Exception {
		init(); // 初始化环境
		Long start = System.currentTimeMillis();
		for (int i = 0; i < 1000; i++) {
			String sql = "\n" + "insert into tuser \n" + "\t(name, \n" + "\tremark, \n"
					+ "\tcreatetime, \n" + "\tupdatetime\n" + "\t)\n" + "\tvalues\n" + "\t('"
					+ (i + 1) + "', \n" + "\t'" + (i + 2) + "',getDate(), \n" + "getDate()\n" + ")";

			// System.out.println(sql);
			Connection conn = myBroker.getConnection();
			Statement stmt = conn.createStatement();
			stmt.execute(sql);
			myBroker.freeConnection(conn);
		}

		Long end = System.currentTimeMillis();
		System.out.println("单条执行1000条Insert操作，共耗时：" + (end - start) / 1000f + "秒!");
	}

	/**
	 * 批处理执行静态SQL测试
	 * 
	 * @param m
	 *            批次
	 * @param n
	 *            每批数量
	 * @throws Exception
	 *             异常时抛出
	 */
	public static void testInsertBatch(int m, int n) throws Exception {
		init(); // 初始化环境
		Long start = System.currentTimeMillis();
		for (int i = 0; i < m; i++) {
			// 从池中获取连接
			Connection conn = myBroker.getConnection();
			Statement stmt = conn.createStatement();
			for (int k = 0; k < n; k++) {
				String sql = "\n" + "insert into tuser \n" + "\t(name, \n" + "\tremark, \n"
						+ "\tcreatetime, \n" + "\tupdatetime\n" + "\t)\n" + "\tvalues\n" + "\t('"
						+ (k + 1) + "', \n" + "\t'" + (k + 2) + "', \n" + "getDate(), \n"
						+ "getDate()\n" + ")";
				// 加入批处理
				stmt.addBatch(sql);
			}
			stmt.executeBatch(); // 执行批处理
			// stmt.clearBatch(); //清理批处理
			stmt.close();
			myBroker.freeConnection(conn); // 连接归池
		}
		Long end = System.currentTimeMillis();
		System.out.println("批量执行" + m + "*" + n + "=" + m * n + "条Insert操作，共耗时：" + (end - start)
				/ 1000f + "秒!");
	}

	/**
	 * 1000条预定义SQL插入
	 * 
	 * @throws Exception
	 *             异常时抛出
	 */
	public static void testInsert2() throws Exception { // 单条执行1000条Insert操作，共耗时：40.422秒!
		init(); // 初始化环境
		Long start = System.currentTimeMillis();
		String sql = "" + "insert into tuser\n" + " (name, remark, createtime, updatetime)\n"
				+ "values\n" + " (?, ?, ?, ?)";
		for (int i = 0; i < 1000; i++) {
			Connection conn = myBroker.getConnection();
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, (i + 1) + "");
			pstmt.setString(2, (i + 2) + "");
			pstmt.setDate(3, new Date(System.currentTimeMillis()));
			pstmt.setDate(4, new Date(System.currentTimeMillis()));
			pstmt.executeUpdate();
			pstmt.close();
			myBroker.freeConnection(conn);
		}
		Long end = System.currentTimeMillis();
		System.out.println("单条执行1000条Insert操作，共耗时：" + (end - start) / 1000f + "秒!");
	}

	/**
	 * 批处理执行预处理SQL测试
	 * 
	 * @param m
	 *            批次
	 * @param n
	 *            每批数量
	 * @throws Exception
	 *             异常时抛出
	 */
	public static void testInsertBatch2(int m, int n) throws Exception {
		init(); // 初始化环境
		Long start = System.currentTimeMillis();
		String sql = "" + "insert into tuser\n" + " (name, remark, createtime, updatetime) values "
				+ " (?, ?, ?, ?)";
		for (int i = 0; i < m; i++) {
			// 从池中获取连接
			Connection conn = myBroker.getConnection();
			PreparedStatement pstmt = conn.prepareStatement(sql);
			for (int k = 0; k < n; k++) {
				pstmt.setString(1, (k + 1) + "");
				pstmt.setString(2, (k + 2) + "");
				pstmt.setDate(3, new Date(System.currentTimeMillis()));
				pstmt.setDate(4, new Date(System.currentTimeMillis()));
				// 加入批处理
				pstmt.addBatch();
			}
			pstmt.executeBatch(); // 执行批处理
			// pstmt.clearBatch(); //清理批处理
			pstmt.close();
			myBroker.freeConnection(conn); // 连接归池
		}
		Long end = System.currentTimeMillis();
		System.out.println("批量执行" + m + "*" + n + "=" + m * n + "条Insert操作，共耗时：" + (end - start)
				/ 1000f + "秒!");
	}

	public static void main(String[] args) throws Exception {
		init();
		Long start = System.currentTimeMillis();
		System.out.println("--------A组测试----------");
		testInsert();
		testInsertBatch(100, 1000);
		testInsertBatch(250, 400);
		testInsertBatch(400, 250);
		testInsertBatch(500, 200);
		testInsertBatch(1000, 100);
		testInsertBatch(2000, 50);
		testInsertBatch(2500, 40);
		testInsertBatch(5000, 20);
		Long end1 = System.currentTimeMillis();
		System.out.println("B组测试过程结束，全部测试耗时：" + (end1 - start) / 1000f + "秒!");

		System.out.println("--------B组测试----------");
		testInsert2();
		testInsertBatch2(100, 1000);
		testInsertBatch2(250, 400);
		testInsertBatch2(400, 250);
		testInsertBatch2(500, 200);
		testInsertBatch2(1000, 100);
		testInsertBatch2(2000, 50);
		testInsertBatch2(2500, 40);
		testInsertBatch2(5000, 20);
		Long end2 = System.currentTimeMillis();
		System.out.println("B组测试过程结束，全部测试耗时：" + (end2 - end1) / 1000f + "秒!");
	}
}