JDBC 批处理

package cn.itcast.batch;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Statement;

import org.junit.Test;

import cn.itcast.jdbc.JDBCUtils;

public class BatchTest {
	@Test
	public void demo2() {
		long start = System.currentTimeMillis();
		// 向mysql 通过PreparedStatement 插入50000条数据
		Connection conn = null;
		PreparedStatement stmt = null;

		String sql = "insert into person values(?,?,?)";
		try {
			conn = JDBCUtils.getConnection();
			stmt = conn.prepareStatement(sql);// 预编译
			for (int i = 1; i <= 100000; i++) {
				stmt.setInt(1, i);
				stmt.setString(2, "name" + i);
				stmt.setString(3, "email" + i);
				// 将刚刚设置参数 加入批处理
				stmt.addBatch();

				if (i % 1000 == 0) { // 每1000条向数据库传递一次
					stmt.executeBatch();
					stmt.clearBatch();
				}
			}

			// 为了确保 缓存没有sql
			stmt.executeBatch();

			long end = System.currentTimeMillis();
			System.out.println("执行时间：" + (end - start));
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.release(stmt, conn);
		}
	}

	@Test
	public void demo1() {
		// 使用Statement接口 批处理操作
		// 批处理操作一般没有查询语句， insert update delete其它sql
		Connection conn = null;
		Statement stmt = null;

		try {
			conn = JDBCUtils.getConnection();
			stmt = conn.createStatement();

			// 连续执行多条sql
			stmt.addBatch("create database mytest");
			stmt.addBatch("use mytest");
			stmt.addBatch("create table mytable(id int , name varchar(20))");
			stmt.addBatch("insert into mytable values(1,'aaa')");
			stmt.addBatch("insert into mytable values(2,'bbb')");
			stmt.addBatch("insert into mytable values(3,'ccc')");
			stmt.addBatch("insert into mytable values(4,'ddd')");

			stmt.addBatch("update mytable set name ='abcd' where id = 2");
			stmt.addBatch("delete from mytable where id =3 ");

			stmt.executeBatch();

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.release(stmt, conn);
		}

	}
}