PreparedStatement对象的使用:
下面结合Junit展示PreparedStatement的基本使用:
/**
* 使用Junit4方式测试DBUtil数据库连接工具类
* Junit4测试类:
* 可以让满足以下条件的任意方法单独直接执行。
* 条件:
* 1.public
* 2.void
* 3.无参数
* 4.方法前必须添加@Test
* @author Cher_du
*
*/
public class TestDay02 {
/**
* PreparedStatement的修改
*/
@Test
public void test4(){
//假设用户传入了如下要修改的数据
int empno =80;
String ename = "悟空";
String job = "保镖";
int mgr = 1111;
Date date = new Date(System.currentTimeMillis());
double sal =5000.0;
double comm = 0;
int deptno = 2;
Connection conn =null;
try {
conn = DBUtil.getConnection();
String sql = "update emp set "
+"ename=?, "
+"job=?, "
+"mgr=?, "
+"hiredate=?, "
+"sal=?, "
+"comm=?, "
+"deptno=? "
+"where empno=? ";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, ename);
ps.setString(2, job);
ps.setInt(3, mgr);
ps.setDate(4, date);
ps.setDouble(5, sal);
ps.setDouble(6, comm);
ps.setInt(7, deptno);
ps.setInt(8, empno);
ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException("修改员工失败!",e);
}finally{
DBUtil.close(conn);
}
}
/**
* PreparedStatement的增加
*/
@Test
public void test3(){
//假设用户传入了如下要添加的数据
int empno = 1111;
String ename = "唐僧";
String job = "领导";
int mgr = 0;
//java.sql.Date
Date date = new Date(System.currentTimeMillis());
double sal = 9000.0;
double comm = 3000.0;
int deptno = 2;
Connection conn = null;
try {
conn = DBUtil.getConnection();
String sql = "insert into emp values("
+"?,?,?,?,?,?,?,?)";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setInt(1, empno);
ps.setString(2, ename);
ps.setString(3, job);
ps.setInt(4, mgr);
ps.setDate(5, date);
ps.setDouble(6, sal);
ps.setDouble(7, comm);
ps.setInt(8, deptno);
ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException("增加员工失败!",e);
}finally{
DBUtil.close(conn);
}
}
/**
* PreparedStatement之查询
* 查询工资高于某值的所有员工
*/
@Test
public void test2() {
// 假设传入的工资
double salary = 4500.0;
Connection conn = null;
try {
conn = DBUtil.getConnection();
String sql = "select * from emp "
+ "where sal>=?";// 预处理设置占位符
// 创建PreparedStatement对象
// 发送SQL并建立执行计划
PreparedStatement ps = conn.prepareStatement(sql);
// 设置参数
// ps.set类型(?的索引,?的值)
ps.setDouble(1, salary);
// 执行SQL
ResultSet rs = ps.executeQuery();
while (rs.next()) {
//System.out.println(rs.getInt(1));
System.out.println(rs.getInt("empno"));
System.out.println(rs.getString("ename"));
}
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException("查询员工失败!",e);
} finally {
DBUtil.close(conn);
}
}
/**
* 根据ID查询员工
*/
@Test
public void test1(){
//假设传入的员工ID是
int id =7499;
//创建连接
Connection conn = null;
try {
conn = DBUtil.getConnection();
String sql ="select * from emp "
+"where empno="+id;
Statement smt = conn.createStatement();
//执行查询
ResultSet rs = smt.executeQuery(sql);
//结果集中封装了多行数据,需要遍历
while(rs.next()){
//rs.get类型(字段名)
//rs.get类型(字段索引)
System.out.println(rs.getInt(1));
System.out.println(rs.getInt("empno"));
System.out.println(rs.getString("ename"));
}
} catch (SQLException e) {
//1.记录日志
e.printStackTrace();
//2.能处理则自己处理(如返回默认值)
//3.处理不了则上报(向上抛出)
throw new RuntimeException("查询员工失败!",e);
}finally{
//归还连接
DBUtil.close(conn);
}
}
}
执行程序后数据库表中数据变化:
大致的处理逻辑参考下面的图示: