JDBC批量插入优化

批量插入大量数据

/**
 * 批量插入如何提高性能
 */
public class InsertManyRecord {

	/**
	 * 普通方法
	 */
	@Test
	public void insertMany() throws Exception{
		Class.forName("com.mysql.cj.jdbc.Driver");
		Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test", "root", "123456");
		String sql = "insert into test.t_user (account, password,nickname) values (?, ?, ?)";
		PreparedStatement statement = connection.prepareStatement(sql);
		long start = System.currentTimeMillis();
		for (int i = 0; i < 10000; i++) {
			statement.setObject(1, "dd" + i);
			statement.setObject(2, "pwd" + i);
			statement.setObject(3, "name" + i);
			statement.executeUpdate();
		}

		long end = System.currentTimeMillis();
		System.out.println((end - start) / 1000);

		statement.close();
		connection.close();

	}

	/**
	 * 优化
	 */
	@Test
	public void insertMany2() throws Exception{
		Class.forName("com.mysql.cj.jdbc.Driver");
		// url中添加rewriteBatchedStatements=true
		Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test?rewriteBatchedStatements=true", "root", "123456");
		// sql语句末尾不能有';'
		String sql = "insert into test.t_user (account, password,nickname) values (?, ?, ?)";
		PreparedStatement statement = connection.prepareStatement(sql);
		long start = System.currentTimeMillis();
		for (int i = 0; i < 10000; i++) {
			statement.setObject(1, "dd" + i);
			statement.setObject(2, "pwd" + i);
			statement.setObject(3, "name" + i);
			// 不执行 先把内容添加到values后面
			statement.addBatch();
		}
		// 执行批量插入
		statement.executeBatch();
		long end = System.currentTimeMillis();
		System.out.println((end - start) / 1000);

		statement.close();
		connection.close();

	}
}