//db.properties
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8
username=root
password=184896
//JdbcUtil.java
1 package dbc;
2
3 import java.sql.*;
4 import java.util.Properties;
5 public final class JdbcUtil {
6
7 private static String driver;
8 private static String user;
9 private static String password;
10 private static String url;
11 private static Properties pr=new Properties();
12 private JdbcUtil(){}
13 //
14 static{
15 try {
16 pr.load(JdbcUtil.class.getClassLoader().getResourceAsStream("db.properties"));
17 driver= pr.getProperty("driver");
18 url = pr.getProperty("url");
19 user = pr.getProperty("username");
20 password = pr.getProperty("password");
21 Class.forName(driver);
22
23 } catch (Exception e) {
24 throw new ExceptionInInitializerError(e);
25 }
26 }
27 //
28 public static Connection getConnection()throws SQLException
29 {
30 return DriverManager.getConnection(url, user, password);
31 }
32
33
34 //
35 public static void free(ResultSet rs,Statement st,Connection conn)
36 {
37 try
38 {
39 if(rs!=null)
40 rs.close();
41 }catch (SQLException e)
42 {
43 e.printStackTrace();
44 }finally{
45 try
46 {
47 if(st!=null)
48 st.close();
49 }catch (SQLException e) { e.printStackTrace();
50 } finally{
51 if(conn!=null)
52 try
53 {
54 conn.close();
55 }
56 catch (SQLException e) {
57 e.printStackTrace();
58 }
59 }
60 }
61 }
62 }
//User.java
1 package vo;
2
3 public class User
4 {
5 private String userid,username,sex;
6 public String getUserid(){return userid;}
7 public void setUerid(String userid){this.userid=userid;}
8 public String getUsername(){return username;}
9 public void setUsername(String username){this.username=username;}
10 public String getsex(){return sex;}
11 public void setSex(String sex){this.sex=sex;}
12 }
//UserDao.java
1 package dao;
2
3 import java.sql.*;
4 import java.util.ArrayList;
5 import java.util.List;
6
7 import vo.User;
8 import dbc.JdbcUtil;
9 public class UserDao {
10 public void add(User user)throws Exception{
11 Connection conn=null;
12 PreparedStatement ps=null;
13 try{
14 conn=JdbcUtil.getConnection();
15 String sql="insert into user values(?,?,?)";
16 ps=conn.prepareStatement(sql);
17 ps.setString(1,user.getUserid());
18 ps.setString(2,user.getUsername());
19 ps.setString(3,user.getsex());
20 ps.executeUpdate();
21 }finally{JdbcUtil.free(null,ps,conn);}
22 }
23 public void update(User user)throws Exception{
24 Connection conn=null;
25 PreparedStatement ps=null;
26 try{
27 conn=JdbcUtil.getConnection();
28 String sql="update user set username=?,sex=? where userid=?";
29 ps=conn.prepareStatement(sql);
30 ps.setString(1,user.getUserid());
31 ps.setString(2,user.getUsername());
32 ps.setString(3,user.getsex());
33 ps.executeUpdate();
34 }finally{JdbcUtil.free(null,ps,conn);}
35 }
36
37 public void delete(String userId)throws Exception{
38 Connection conn=null;
39 PreparedStatement ps=null;
40 try{
41 conn=JdbcUtil.getConnection();
42 String sql="delete from user where userid=?";
43 ps=conn.prepareStatement(sql);
44 ps.setString(1,userId);
45 ps.executeUpdate();
46 }finally{JdbcUtil.free(null,ps,conn);}
47 }
48 public User findUserById(String userId)throws Exception{
49 Connection conn=null;
50 PreparedStatement ps=null;
51 ResultSet rs=null;
52 User user=null;
53 try{
54 conn=JdbcUtil.getConnection();
55 String sql="select* from user where userid=?";
56 ps=conn.prepareStatement(sql);
57 ps.setString(1,userId);
58 rs=ps.executeQuery();
59 if(rs.next()){
60 user=new User();
61 user.setUerid(rs.getString(1));
62 user.setUsername(rs.getString(2));
63 user.setSex(rs.getString(3));
64 }
65 }finally{JdbcUtil.free(null,ps,conn);}
66 return user;
67 }
68 public List<User> QueryAll()throws Exception{
69 Connection conn=null;
70 PreparedStatement ps=null;
71 ResultSet rs=null;
72 List<User>userList=new ArrayList<User>();
73 try{
74 conn=JdbcUtil.getConnection();
75 String sql="select* from user";
76 ps=conn.prepareStatement(sql);
77 rs=ps.executeQuery();
78 while(rs.next()){
79 User user=new User();
80 user.setUerid(rs.getString(1));
81 user.setUsername(rs.getString(2));
82 user.setSex(rs.getString(3));
83 userList.add(user);
84 }
85 }finally{
86 JdbcUtil.free(rs, ps, conn);
87 }
88 return userList;
89
90
91
92 }
93 }