数据库访问JavaBean的设计

//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 }

 

转载于:https://www.cnblogs.com/liao-pxsoftware15/p/7786527.html