jdbc预编译和批处理sql

预编译处理sql

原因

1.      避免了频繁sql拼接 (可以使用占位符)

2.      可以防止sql注入

代码修改

Dept实体类：

//javabean类|实体类
public class Dept {
	private int deptno;
	private String dname;
	private String loc;

	public Dept() {

	}

	public Dept(int deptno, String dname, String loc) {
		this.deptno = deptno;
		this.dname = dname;
		this.loc = loc;
	}

	public int getDeptno() {
		return deptno;
	}

	public void setDeptno(int deptno) {
		this.deptno = deptno;
	}

	public String getDname() {
		return dname;
	}

	public void setDname(String dname) {
		this.dname = dname;
	}

	public String getLoc() {
		return loc;
	}

	public void setLoc(String loc) {
		this.loc = loc;
	}

	@Override
	public String toString() {
		return "Dept [deptno=" + deptno + ", dname=" + dname + ", loc=" + loc
				+ "]";
	}

}

预编译处理增删改查

public class Program02 {

	@Test
	public void testSelect() {
		Dept dept = new Dept();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "select * from dept where deptno = ?  and loc = ?";
		try {
			conn = DBUtils.getConnection();// 和数据库建立连接
			ps = conn.prepareStatement(sql);// 预编译处理sql语句
			ps.setInt(1, 70);// 设置占位符信息//整型
			ps.setString(2, "江苏连云港");// 占位符信息为字符串类型
			rs = ps.executeQuery();// 执行查询语句，返回结果集
			while (rs.next()) {
				dept.setDeptno(rs.getInt("deptno"));
				dept.setDname(rs.getString(2));
				dept.setLoc(rs.getString("loc"));
			}
			System.out.println(dept);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtils.Close(conn, ps, rs);
		}
	}

	@Test
	public void testAdd() {
		Connection conn = DBUtils.getConnection();
		PreparedStatement ps = null;
		String sql = "insert into dept(deptno,dname,loc)values (?,?,?)";
		int count = -1;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, 16);
			ps.setString(2, "软件服务部");
			ps.setString(3, "江苏");
			count = ps.executeUpdate();
			System.out.println(count);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtils.Close(conn, ps, null);
		}
	}

	@Test
	public void testDelete() {
		Connection conn = DBUtils.getConnection();
		String sql = "delete from dept where deptno = ?";
		PreparedStatement ps = null;
		int count = -1;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, 16);
			count = ps.executeUpdate();// 返回影响的行数
			System.out.println(count);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtils.Close(conn, ps, null);
		}
	}

	@Test
	public void testUpdate() {
		Connection conn = DBUtils.getConnection();
		PreparedStatement ps = null;
		String sql = "update dept set dname = ? where deptno = ?";
		int count = -1;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, "软件维护");
			ps.setInt(2, 16);
			count = ps.executeUpdate();
			System.out.println(count);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtils.Close(conn, ps, null);
		}

	}
}

批处理

 

批处理的相关api

         voidaddBatch(String sql)     添加批处理

         voidclearBatch()            清空批处理

int[] executeBatch()         执行批处理

 

批处理代码

@Test
	public void testAddBatch() {
		List<Dept> list = new ArrayList<Dept>();
		list.add(new Dept(1, "软件维护", "江苏"));
		list.add(new Dept(2, "软件维护", "江苏"));
		list.add(new Dept(3, "软件维护", "江苏"));
		list.add(new Dept(4, "软件维护", "江苏"));
		list.add(new Dept(5, "软件维护", "江苏"));
		list.add(new Dept(6, "软件维护", "江苏"));
		list.add(new Dept(7, "软件维护", "江苏"));
		list.add(new Dept(8, "软件维护", "江苏"));
		// 需要批量加入这些数据到数据库中去
		Connection conn = DBUtils.getConnection();
		PreparedStatement ps = null;
		String sql = "insert into dept(deptno,dname,loc)values (?,?,?)";

		try {
			ps = conn.prepareStatement(sql);
			for (int i = 0; i < list.size(); i++) {
				Dept dept = list.get(i);
				ps.setInt(1, dept.getDeptno());
				ps.setString(2, dept.getDname());
				ps.setString(3, dept.getLoc());
				// 添加批处理
				ps.addBatch();
				// 每4条执行操作
				if ((i + 1) % 4 == 0) {
					ps.executeBatch();// 批量执行
					ps.clearBatch();// 清空批处理
				}

			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtils.Close(conn, ps, null);
		}
	}

	@Test
	public void t4() {
		Connection conn = null;
		Statement st = null;
		try {
			conn = DBUtils.getConnection();
			conn.setAutoCommit(false);
			st = conn.createStatement();
			st.addBatch("insert into dept values('16','16','17')");
			st.addBatch("insert into dept values('17','16','17')");
			st.addBatch("update dept set dname = '开发部' where deptno = '11' ");
			st.executeBatch();
			conn.commit();
			conn.setAutoCommit(true);
		} catch (Exception e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}// 回滚操作
		}
	}