数据库批量插入

1:使用addBatch()加入一条要执行的sql命令以及executeBatch()执行全部命令两个方法完成

        /**
	 * 插入1万条约耗时30s(不使用事务)
	 * 1W条数据700ms
	 * 10W条数据5S
	 * @param pstm
	 * @throws SQLException
	 */
	public static void insert1(Connection conn) throws SQLException {
		String sql="INSERT INTO t_user1 (id,NAME,sex) VALUES (?,?,?)";
		PreparedStatement pstm=conn.prepareStatement(sql);
		conn.setAutoCommit(false);
		for (int i=1; i < 1000001; i++) {
			pstm.setInt(1, i);
			pstm.setString(2, "小明");
			pstm.setString(3, "男");
			pstm.addBatch();
		}
		pstm.executeBatch();
		conn.commit();
	}

经过测试:不使用事务的速度是非常慢的

2:合并sql

        /**
	 * 合并sql,1万条耗时160ms左右
	 * 10W耗时约700ms
	 * 100W耗时约10S
	 * @param conn
	 * @throws SQLException
	 */
	public static void insert2(Connection conn) throws SQLException {
		StringBuilder sb=new StringBuilder();
		for (int i=1; i < 1000001; i++) {
			sb.append("("+i+","+"\"小明\","+"\"男\"),");
		}
		String substring = sb.substring(0,sb.length()-1);
		String sql="INSERT INTO t_user1 (id,NAME,sex) VALUES "+substring;
		conn.setAutoCommit(false);
		PreparedStatement pstm=conn.prepareStatement(sql);
		pstm.executeUpdate();
		conn.commit();
	}

以插入10W条数据为例,耗时约700ms,100W耗时约10S(如果sql太长,可以执行    SET GLOBAL max_allowed_packet = 2*1024*1024*10   后面的长度自己定)

3:分批插入

        /**
	 * 开启事务
	 * 1W条数据约700ms
	 * 10W条约4S
	 * 100W条约50S(10*10W==100*1W)
	 * @param conn
	 * @throws SQLException
	 */
	public static void insert3(Connection conn) throws SQLException {
		conn.setAutoCommit(false);
		String sql="INSERT INTO t_user1 (NAME,sex) VALUES (?,?)";
		PreparedStatement pstm=conn.prepareStatement(sql);
		for (int j = 1; j < 101; j++) {
			for (int i=1; i < 10001; i++) {
				pstm.setString(1, "小明");
				pstm.setString(2, "男");
				pstm.addBatch();
			}
			pstm.executeBatch();
			conn.commit();
		}
	}

在开启事务的情况下,分批插入和第一个案例速度差距很小.但此处有一个现象是,过多的分批效率会降低

4:逐条sql

        /**
	 * 1W条数据约30s(不开启事务)
	 * 1W条数据约500ms
	 * 10W条数据约4S
	 * @param conn
	 * @throws SQLException
	 */
	public static void insert4(Connection conn) throws SQLException {
		conn.setAutoCommit(false);
		String sql="INSERT INTO t_user1 (NAME,sex) VALUES ("+"\"小明\","+"\"男\")";
		PreparedStatement pstm=conn.prepareStatement(sql);
		for (int j = 1; j < 10001; j++) {
			pstm.executeUpdate();
		}
		conn.commit();
	}

测试发现,在开启事务的情况下,其效率和addBatch几乎是一样的.

5:下面这个和批量处理无关,是一个面试题

public static void insert5(Connection conn) throws SQLException {
		PreparedStatement pstm=null;
		List<String> list=new ArrayList<String>();
		for (int j = 0; j < 20; j++) {
			if(j==10) {
				list.add("INSERT INTO t_user1 (id,NAME,sex) VALUES ("+"\"小明\","+"\"男\")");
			}else {
				list.add("INSERT INTO t_user1 (NAME,sex) VALUES ("+"\"小明\","+"\"男\")");
			}
		}
		conn.setAutoCommit(false);
		for (int i = 0; i < 100; i++) {
			pstm=conn.prepareStatement(list.get(i));
			pstm.executeUpdate();
			System.out.println(i);
		}
		conn.commit();
	}

总结:将所有sql合并为一条sql的效率最高,如果待插入数据太大,可以分批合并并采用自动提交事务的方式,这样在提交事务之前,所有的数据都保存在当前的session中.