JDBC——数据层DAO

DAO：Data Access Object
　　DAO　　　　数据层
　　Service　　逻辑业务层
　　View　　　视图层
　　entity　　实体层
实现增、删、改、查的数据层
 1 public class EmpDAO {
 2     
 3     public Employee findEmpById(int id) {
 4         
 5         Connection conn = null;
 6         PreparedStatement ps = null;
 7         ResultSet rs = null;
 8         Employee emp = null;
 9         
10         conn = DBTool.getInstance().getConnection();
11         String sql = "SELECT * FROM Employee WHERE empId = ?";
12         try {
13             ps = conn.prepareStatement(sql);
14             ps.setInt(1, id);
15             rs = ps.executeQuery();
16             while(rs.next()){
17                 emp = new Employee();
18                 emp.setEmpNo(rs.getInt("empId"));
19                 emp.seteName(rs.getString("empName"));
20                 emp.setSalary(rs.getDouble("salary"));
21             }
22         } catch (SQLException e) {
23             // TODO Auto-generated catch block
24             e.printStackTrace();
25         }
26         finally{
27             DBTool.closeAll(conn, ps, rs);
28         }
29         return emp;
30     }
31     public int addEmp(Employee emp){
32         int row = 0;
33         Connection conn = null;
34         PreparedStatement ps = null;
35         conn = DBTool.getInstance().getConnection();
36         String sql = "INSERT INTO Employee (empName,salary) values (?,?)";
37         try {
38             ps = conn.prepareStatement(sql);
39             ps.setString(1, emp.geteName());
40             ps.setDouble(2, emp.getSalary());
41             row = ps.executeUpdate();
42         } catch (SQLException e) {
43             // TODO Auto-generated catch block
44             e.printStackTrace();
45         }
46         finally{
47             DBTool.closeAll(conn, ps, null);
48         }
49         return row;
50     }
51     public int updateEmp(Employee emp){
52         int row = 0;
53         Connection conn = null;
54         PreparedStatement ps = null;
55         conn = DBTool.getInstance().getConnection();
56         String sql = "UPDATE Employee SET empName = ?,Salary = ? WHERE empId = ?"; 
57         try {
58             ps = conn.prepareStatement(sql);
59             ps.setString(1, emp.geteName());
60             ps.setDouble(2, emp.getSalary());
61             ps.setInt(3, emp.getEmpNo());
62             row = ps.executeUpdate();
63         } catch (SQLException e) {
64             // TODO Auto-generated catch block
65             e.printStackTrace();
66         }
67         finally{
68             DBTool.closeAll(conn, ps, null);
69         }
70         return row;
71     }
72     public int deleteEmp(int id){
73         int row = 0;
74         Connection conn = null;
75         PreparedStatement ps = null;
76         conn = DBTool.getInstance().getConnection();
77         String sql = "delete from employee where empId = ?"; 
78         try {
79             ps = conn.prepareStatement(sql);
80             ps.setInt(1, id);
81             row = ps.executeUpdate();
82         } catch (SQLException e) {
83             // TODO Auto-generated catch block
84             e.printStackTrace();
85         }
86         finally{
87             DBTool.closeAll(conn, ps, null);
88         }
89         return row;
90     }
91 }