1 import java.sql.Connection;
2 import java.sql.DriverManager;
3 import java.sql.PreparedStatement;
4 import java.sql.ResultSet;
5 import java.sql.SQLException;
6 import java.sql.Statement;
7 import java.sql.Timestamp;
8
9 public class BaseDao {
10
11 public static final String DRIVER = "com.mysql.jdbc.Driver";
12 public static final String URL = "jdbc:mysql://localhost:3306/chaoshiguanli";
13 public static final String USERNAME = "root";
14 public static final String PASSWORD = "123456";
15 //统一的加载驱动获得连接方法
16 public Connection getConnection(){
17 Connection conn = null;
18 try {
19 Class.forName(DRIVER);
20 conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
21 } catch (ClassNotFoundException e) {
22 e.printStackTrace();
23 } catch (SQLException e) {
24 e.printStackTrace();
25 }
26 return conn;
27 }
28
29 //统一的关闭方法
30 public void closeResource(Connection conn,Statement stmt,ResultSet rs){
31 try {
32 if(rs!=null)
33 rs.close();
34 if(stmt!=null)
35 stmt.close();
36 if(conn!=null)
37 conn.close();
38 } catch (SQLException e) {
39 e.printStackTrace();
40 }
41 }
42 //统一的增删改方法
43 public int executeUpdate(String sql,Object[] objs){
44 Connection conn = null;
45 PreparedStatement ps = null;
46 try{
47 conn = getConnection();
48 ps = conn.prepareStatement(sql);
49 if(objs!=null){
50 for(int i=0;i<objs.length;i++){
51 if(objs[i] instanceof java.util.Date){
52 objs[i] = new Timestamp(((java.util.Date)objs[i]).getTime());
53 }
54 ps.setObject(i+1, objs[i]);
55 }
56 }
57 return ps.executeUpdate();
58 } catch (SQLException e) {
59 e.printStackTrace();
60 return -1;
61 }finally{
62 closeResource(conn, ps, null);
63 }
64 }
65
66 }
查询
1 import java.sql.Connection;
2 import java.sql.PreparedStatement;
3 import java.sql.ResultSet;
4 import java.sql.SQLException;
5 import java.sql.Statement;
6 import java.util.ArrayList;
7 import java.util.List;
8
9 import com.pb.entity.Users;
10
11 public class UsersDaoImpl extends BaseDao implements UsersDao {
12
13 @Override
14 public List<Users> findAll() {
15 List<Users> list = new ArrayList<Users>();
16 Connection conn = null;
17 Statement stmt = null;
18 ResultSet rs = null;
19 String sql = "select * from users";
20 try{
21 conn = super.getConnection();
22 stmt = conn.createStatement();
23 rs = stmt.executeQuery(sql);
24 while(rs.next()){
25 Users u = new Users();
26 u.setUserId(rs.getInt("userid"));
27 u.setUserName(rs.getString("username"));
28 u.setPassword(rs.getString("password"));
29 u.setSex(rs.getInt("sex"));
30 u.setAge(rs.getInt("age"));
31 u.setPhoneNumber(rs.getNString("phoneNumber"));
32 u.setAddress(rs.getNString("address"));
33 u.setRole(rs.getInt("role"));
34 list.add(u);
35 }
36 } catch (SQLException e) {
37 e.printStackTrace();
38 }finally{
39 super.closeResource(conn, stmt, rs);
40 }
41 return list;
42 }
43
44
45 @Override
46 public Users findById(int id) {
47 Users u = null;
48 Connection conn = null;
49 PreparedStatement ps = null;
50 ResultSet rs = null;
51 String sql = "select * from users where userid=?";
52 try{
53 conn = super.getConnection();
54 ps = conn.prepareStatement(sql);
55 ps.setInt(1, id);
56 rs = ps.executeQuery();
57 if(rs.next()){
58 u = new Users();
59 u.setUserId(rs.getInt("userid"));
60 u.setUserName(rs.getString("username"));
61 u.setPassword(rs.getString("password"));
62 u.setSex(rs.getInt("sex"));
63 u.setAge(rs.getInt("age"));
64 u.setPhoneNumber(rs.getNString("phoneNumber"));
65 u.setAddress(rs.getNString("address"));
66 u.setRole(rs.getInt("role"));
67
68 }
69 } catch (SQLException e) {
70 e.printStackTrace();
71 }finally{
72 super.closeResource(conn, ps, rs);
73 }
74 return u;
75 }
