eclipse连接mysql数据库实现怎删改查操作实例（附带源码）

package model; 2 3 public class User { 4 5 private int id; 6 private String name; 7 private String password; 8 public int getId() { 9 return id; 10 } 11 public void setId(int id) { 12 this.id = id; 13 } 14 public String getName() { 15 return name; 16 } 17 public void setName(String name) { 18 this.name = name; 19 } 20 public String getPassword() { 21 return password; 22 } 23 public void setPassword(String password) { 24 this.password = password; 25 } 26 27 28 29 }

 1 package dao;
 2 
 3 
 4 
 5 import java.util.List;
 6 
 7 import model.User;
 8 
 9 public interface IUser {
10     public void add(User user);
11     public void delete(int id);
12     public void update(User user);
13     public User load(int id);
14     public List<User> load();
15     public List<User> load(String content);
16 }

  1 package dao;
  2 
  3 import java.sql.Connection;
  4 import java.sql.PreparedStatement;
  5 import java.sql.ResultSet;
  6 import java.sql.SQLException;
  7 import java.util.ArrayList;
  8 import java.util.List;
  9 
 10 import com.sun.xml.internal.bind.v2.runtime.Name;
 11 
 12 import model.User;
 13 import util.DBUtil;
 14 import util.UserException;
 15 
 16 
 17 public class UserImp implements IUser{
 18 
 19     
 20     public void add(User user) {
 21         
 22         Connection connection=DBUtil.getConnection();
 23         
 24         String sql = "select count(*) from t_user where name = ?";//准备sql语句
 25         
 26         PreparedStatement preparedStatement = null;
 27         ResultSet resultSet = null;
 28         
 29         try {
 30             preparedStatement =  connection.prepareStatement(sql);
 31             preparedStatement.setString(1, user.getName());
 32             
 33             resultSet = preparedStatement.executeQuery();
 34             
 35             while(resultSet.next()) {
 36                 if (resultSet.getInt(1) > 0) {
 37                     throw new UserException("用户已存在") ;
 38                 }
 39             }
 40             
 41             String sql1 = "insert into t_user(name,password) value (?,?)";
 42             preparedStatement = connection.prepareStatement(sql1);
 43             preparedStatement.setString(1, user.getName());
 44             preparedStatement.setString(2, user.getPassword());
 45             preparedStatement.executeUpdate();
 46         } catch (SQLException e) {
 47             
 48             e.printStackTrace();
 49         }finally {
 50             
 51             DBUtil.close(resultSet);
 52             DBUtil.close(preparedStatement);
 53             DBUtil.close(connection);
 54         }
 55         
 56     }
 57 
 58     
 59     public void delete(int id) {
 60         Connection connection = DBUtil.getConnection();
 61         String sql = "delete from t_user where id = ?";
 62         PreparedStatement preparedStatement = null;
 63         
 64         try {
 65             preparedStatement = connection.prepareStatement(sql);
 66             preparedStatement.setInt(1, id);
 67             preparedStatement.executeUpdate();
 68         } catch (SQLException e) {
 69         
 70             e.printStackTrace();
 71         }finally {
 72             DBUtil.close(preparedStatement);
 73             DBUtil.close(connection);
 74         }
 75         
 76         
 77     }
 78 
 79     
 80     public void update(User user) {
 81         
 82         Connection connection = DBUtil.getConnection();
 83         //准备sql语句
 84         String sql = "update t_user set name = ? , password=? where id = ?";
 85         //创建语句传输对象
 86         PreparedStatement preparedStatement = null;
 87         try {
 88             preparedStatement = connection.prepareStatement(sql);
 89             preparedStatement.setString(1, user.getName());
 90             preparedStatement.setString(2, user.getPassword());
 91             preparedStatement.setInt(3, user.getId());
 92             preparedStatement.executeUpdate();
 93         } catch (SQLException e) {
 94             
 95             e.printStackTrace();
 96         }finally {
 97             DBUtil.close(preparedStatement);
 98             DBUtil.close(connection);
 99         }
100         
101     }
102 
103     public User load(int id) {
104         Connection connection = DBUtil.getConnection();
105         //准备sql语句
106         String sql = "select * from t_user  where id = ?";
107         //创建语句传输对象
108         PreparedStatement preparedStatement = null;
109         ResultSet resultSet = null;
110         User user = null;
111         try {
112             preparedStatement = connection.prepareStatement(sql);
113             
114             preparedStatement.setInt(1, id);
115             resultSet = preparedStatement.executeQuery();
116             while(resultSet.next()) {
117                 user = new User();
118                 user.setId(id);
119                 user.setName(resultSet.getString("name"));;
120                 user.setPassword(resultSet.getString("password"));
121                 
122             }
123         } catch (SQLException e) {
124             
125             e.printStackTrace();
126         }finally {
127             DBUtil.close(resultSet);
128             DBUtil.close(preparedStatement);
129             DBUtil.close(connection);
130         }
131         return  user;
132     }
133 
134 
135     @Override
136     public List<User> load() {
137         Connection connection = DBUtil.getConnection();
138         //准备sql语句
139         String sql = "select * from t_user ";
140         //创建语句传输对象
141         PreparedStatement preparedStatement = null;
142         ResultSet resultSet = null;
143         //集合中只能放入user对象
144         List<User> users = new ArrayList<User>();
145         User user = null;
146         try {
147         
148                 preparedStatement = connection.prepareStatement(sql);
149             
150                 
151                 
152             resultSet = preparedStatement.executeQuery();
153             while(resultSet.next()) {
154                 user = new User();
155                 user.setId(resultSet.getInt("id"));
156                 user.setName(resultSet.getString("name"));
157                 user.setPassword(resultSet.getString("password"));
158                 
159                 users.add(user);
160             }    
161         } catch (SQLException e) {
162         
163             e.printStackTrace();
164         }finally {
165             DBUtil.close(resultSet);
166             DBUtil.close(preparedStatement);
167             DBUtil.close(connection);
168         }
169         return  users;
170     }
171 
172 
173     @Override
174     public List <User> load(String content) {
175         Connection connection = DBUtil.getConnection();
176         //准备sql语句
177         String sql = "select * from t_user ";
178         //创建语句传输对象
179         PreparedStatement preparedStatement = null;
180         ResultSet resultSet = null;
181         //集合中只能放入user对象
182         List<User> users = new ArrayList<User>();
183         User user = null;
184         try {
185             if (content == null || "".equals(content)) {
186                 preparedStatement = connection.prepareStatement(sql);
187             }else {
188                 sql += "where name like ? ";
189                 preparedStatement = connection.prepareStatement(sql);
190                 preparedStatement.setString(1, "%"+ content +"%");
191                 
192             }
193             resultSet = preparedStatement.executeQuery();
194             while(resultSet.next()) {
195                 user = new User();
196                 user.setId(resultSet.getInt("id"));
197                 user.setName(resultSet.getString("name"));
198                 user.setPassword(resultSet.getString("password"));
199                 
200                 users.add(user);
201             }    
202         } catch (SQLException e) {
203         
204             e.printStackTrace();
205         }finally {
206             DBUtil.close(resultSet);
207             DBUtil.close(preparedStatement);
208             DBUtil.close(connection);
209         }
210         return  users;
211     }    
212 }

 1 package filter;
 2 
 3 import java.io.IOException;
 4 
 5 import javax.servlet.Filter;
 6 import javax.servlet.FilterChain;
 7 import javax.servlet.FilterConfig;
 8 import javax.servlet.ServletException;
 9 import javax.servlet.ServletRequest;
10 import javax.servlet.ServletResponse;
11 
12 public class CharFilter implements Filter{
13     String encoding = null;
14     public void init(FilterConfig filterConfig) throws ServletException {
15         encoding = filterConfig.getInitParameter("encoding");
16     }
17 
18     
19 
20     public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain)
21             throws IOException, ServletException {
22 
23             request.setCharacterEncoding(encoding);
24             chain.doFilter(request, response);
25     }
26 
27     public void destroy() {
28         
29     }
30 }

 1 package util;
 2 
 3 import java.sql.Connection;
 4 import java.sql.DriverManager;
 5 import java.sql.PreparedStatement;
 6 import java.sql.ResultSet;
 7 import java.sql.SQLException;
 8 
 9 public class DBUtil {
10 
11 
12 
13         public  static  Connection getConnection() {
14             /*
15              * 加载驱动
16              */
17             try {
18                 
19                 Class.forName("com.mysql.jdbc.Driver").newInstance();
20             } catch (InstantiationException | IllegalAccessException | ClassNotFoundException e) {
21                 
22                 e.printStackTrace();
23             }
24             String user = "root";
25             String password = "root";
26             String url = "jdbc:mysql://localhost:3306/mysql";
27             /*
28              * 创建连接对象
29              */
30             Connection connection = null;
31             try {
32                 
33                  connection = DriverManager.getConnection(url,user,password);
34             } catch (SQLException e) {
35                 
36                 e.printStackTrace();
37             }
38             return connection;
39         }
40         /*
41          * 关闭资源的方法
42          */
43         
44         public static void close(Connection connection) {//关闭连接对象的方法
45             try {
46                 if (connection != null) {
47                     connection.close();
48                 }
49                 
50             } catch (SQLException e) {
51                 
52                 e.printStackTrace();
53             }
54         }
55         public static void close(PreparedStatement preparedStatement ) {//关闭语句传输对象的方法
56             try {
57                 if (preparedStatement != null) {
58                     preparedStatement.close();
59                 }
60                 
61             } catch (SQLException e) {
62                 
63                 e.printStackTrace();
64             }
65         }
66         public static void close(ResultSet resultSet ) {//关闭结果集的方法
67             try {
68                 if (resultSet != null) {
69                     resultSet.close();
70                 }
71                 
72             } catch (SQLException e) {
73                 
74                 e.printStackTrace();
75             }
76         }
77         
78 
79 
80 
81     }

 1 package util;
 2 
 3 
 4 
 5 public class UserException extends RuntimeException  
 6 {
 7 
 8     public UserException() {
 9         super();
10         // TODO Auto-generated constructor stub
11     }
12 
13     public UserException(String message, Throwable cause, boolean enableSuppression, boolean writableStackTrace) {
14         super(message, cause, enableSuppression, writableStackTrace);
15         // TODO Auto-generated constructor stub
16     }
17 
18     public UserException(String message, Throwable cause) {
19         super(message, cause);
20         // TODO Auto-generated constructor stub
21     }
22 
23     public UserException(String message) {
24         super(message);
25         // TODO Auto-generated constructor stub
26     }
27 
28     public UserException(Throwable cause) {
29         super(cause);
30         // TODO Auto-generated constructor stub
31     }
32 
33 }

 1 <%@page import="com.sun.org.apache.xalan.internal.xsltc.compiler.sym"%>
 2 <%@page import="dao.UserImp"%>
 3 <%@page import="model.User"%>
 4 <%@ page language="java" contentType="text/html; charset=UTF-8"
 5     pageEncoding="UTF-8"%>
 6 <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
 7 <html>
 8 <%
 9 String name=(String)request.getParameter("username");
10 String password=(String)request.getParameter("pass");
11 User user=new User();
12 user.setName(name);
13 user.setPassword(password);
14 
15 System.out.print(user.getName());
16 System.out.print(user.getPassword());
17 
18 UserImp userImp=new UserImp();
19 try{
20 userImp.add(user);}catch(Exception e){}
21 
22 
23 response.sendRedirect("list.jsp");
24 %>
25 
26 <h2>添加成功！</h2>
27 
28 
29 </html>

 1 <%@ page language="java" contentType="text/html; charset=UTF-8"
 2     pageEncoding="UTF-8"%>
 3 <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
 4 <html>
 5         <head>
 6         <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
 7         <title>Insert title here</title>
 8         
 9         </head>
10     <body>
11         <form action="add.jsp" method="post">
12             <table  border="1" bordercolor="#FFFF33" align="center" >
13             <tr>
14             
15             <td align="right" colspan="2">用户名<input type="text"  name="username" style="width:100px;" /></td><br>
16             
17             </tr>
18             <tr>
19             <td align="right" colspan="2">密    码<input type="password"  name="pass" style="width:100px;"/></td>
20             </tr>
21                 <tr>
22                     <td align="left" style="width:60px;height:10px;font-size:10px;">没有用户名?<a href="register.jsp">[注册]</a>一个吧</td>
23                     
24                     <td align="right"><input type="submit" value="添加"style="width:100px;height:30px;font-size:16px;"/></td>
25                     
26                 </tr>
27                 
28             </table>
29             
30         </form>
31         
32     </body>
33 </html>

 1 <%@page import="model.User"%>
 2 <%@page import="dao.UserImp"%>
 3 <%@ page language="java" contentType="text/html; charset=UTF-8"
 4     pageEncoding="UTF-8"%>
 5 <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
 6 <html>
 7 <head>
 8 <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
 9 <title>Insert title here</title>
10 </head>
11 <%
12 int id = Integer.parseInt(request.getParameter("id"));
13 UserImp userImp=new UserImp();
14 User user=new User();
15 userImp.delete(id);
16 response.sendRedirect("list.jsp");
17 %>
18 
19 
20 </html>

 1 <%@page import="model.User"%>
 2 <%@page import="java.util.List"%>
 3 <%@page import="dao.UserImp"%>
 4 <%@ page language="java" contentType="text/html; charset=UTF-8"<