使用JDBC批量添加数据和删除数据

直接上码：

1、批量添加数据

public class Demo05 {
	public static void main(String[] args) {
		Connection conn = null;
		Statement stmt = null;
		try {
			//加载驱动类
			Class.forName("com.mysql.cj.jdbc.Driver");
			//建立数据库连接
			conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/testjdbc?&useSSL=false&serverTimezone=UTC","root","123456");
			
			conn.setAutoCommit(false);//设为手动提交
			long start = System.currentTimeMillis();
			stmt = conn.createStatement();
			
			//批量添加
			for (int i = 0; i < 20000; i++) {
				stmt.addBatch("insert into t_user (username,pwd,regTime) values ('gao "+ i +"',666666,now())");
			}
			
			stmt.executeBatch();//执行批处理
			conn.commit();//提交事务
			long end = System.currentTimeMillis();
			System.out.println("插入20000行数据，耗时：" + (end - start) + "ms");
							
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {		
			try {
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}

2、批量删除数据

public class Demo06 {
	public static void main(String[] args) {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			//加载驱动类
			Class.forName("com.mysql.cj.jdbc.Driver");
			//建立数据库连接
			conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/testjdbc?&useSSL=false&serverTimezone=UTC","root","123456");
						
			//批量删除
			conn.setAutoCommit(false);//设为手动提交
			//带有占位符的sql
			String sql = "delete from t_user where id=?";//?是占位符
			ps = conn.prepareStatement(sql);
			
			for (int i = 10000; i < 20000; i++) {
				//1是占位符的位置，i是取代占位符
				//这里占位符参数索引是从1开始计算，而不是0
				ps.setInt(1, i);
				//添加批量删除
				ps.addBatch();	
			}
			//执行批处理并返回批量执行的条数
			int[] result = stmt.executeBatch();
			conn.commit();//提交事务
			System.out.println(result.length);			
			
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				if (ps != null) {
					ps.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}