批量执行SQL语句

批量执行SQL语句

首选创建名为test的表

//sql
create table test(id int,name,varchar(30));

1、使用createStatement()方法

//java
public static void main(String[] args) {
		try {
			//从连接池获得连接
			Connection conn = DBUtil.getConnection();
			//获得执行SQL语句的对象
			Statement st = conn.createStatement();
			//添加SQL语句
			st.addBatch("insert into test values(1,'test1')");
			st.addBatch("insert into test values(2,'test2')");
			st.addBatch("insert into test values(3,'test3')");
			st.addBatch("insert into test values(4,'test4')");
			//执行SQL语句
			/**
			 * 返回值是int类型的数组，所以执行的SQL语句不能是select语句
			 */
			int[] batch = st.executeBatch();
			for (int i : batch) {
				System.out.println(i);
			}
			DBUtil.closeConnection(conn);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

2、使用prepareStatement(sql)方法

//java
public static void main(String[] args) {
		try {
			//从连接池获得连接
			Connection conn = DBUtil.getConnection();
			//设置SQL语句
			String sql = "insert into test values(?,?)";
			//创建prepareStatement对象
			PreparedStatement ps = conn.prepareStatement(sql);
			//添加SQL语句
			for(int i=4;i>=1;i--) {
				ps.setInt(1, i);
				ps.setString(2, "test"+i);
				ps.addBatch();
			}
			//执行SQL语句
			int[] batch = ps.executeBatch();
			for (int i : batch) {
				System.out.println(i);
			}
			//关闭连接
			DBUtil.closeConnection(conn);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}