预编译处理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();
}// 回滚操作
}
}