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;}@Overridepublic String toString() {return "Dept [deptno=" + deptno + ", dname=" + dname + ", loc=" + loc+ "]";}}

预编译处理增删改查

public class Program02 {@Testpublic 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 blocke.printStackTrace();} finally {DBUtils.Close(conn, ps, rs);}}@Testpublic 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 blocke.printStackTrace();} finally {DBUtils.Close(conn, ps, null);}}@Testpublic 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 blocke.printStackTrace();} finally {DBUtils.Close(conn, ps, null);}}@Testpublic 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 blocke.printStackTrace();} finally {DBUtils.Close(conn, ps, null);}}}

批处理

 

批处理的相关api

         voidaddBatch(String sql)     添加批处理

         voidclearBatch()            清空批处理

int[] executeBatch()         执行批处理

 

批处理代码

@Testpublic 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 blocke.printStackTrace();} finally {DBUtils.Close(conn, ps, null);}}@Testpublic 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 blocke1.printStackTrace();}// 回滚操作}}