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

 1 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="100px;" /></td><br>
16             
17             </tr>
18             <tr>
19             <td align="right" colspan="2">密    码<input type="password"  name="pass" style="100px;"/></td>
20             </tr>
21                 <tr>
22                     <td align="left" style="60px;height:10px;font-size:10px;">没有用户名?<a href="register.jsp">[注册]</a>一个吧</td>
23                     
24                     <td align="right"><input type="submit" value="添加"style="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"
 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 <head>
 9 <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
10 <title>Insert title here</title>
11 <%
12     UserImp userDao = new UserImp();
13     String content=request.getParameter("content");
14     if(content==null||"".equals(content)){
15         content="";}
16     
17         List<User> users = userDao.load(content);
18     
19 %>
20 </head>
21 <a href="addInput.jsp">继续添加</a>
22 <body>
23     <table align="center" border="1" width="500">
24     <tr align="center">
25         <td colspan="5" >增删改查</td>
26         </tr>
27     <tr>
28             <form action="list.jsp" method="post">
29                 <td colspan="4">
30                     请输入用户名 : &nbsp;
31                     <input type="text" name="content" size="40" value="<%=content%>"/> &nbsp; 
32                     <input type="submit" value="搜索" />
33                 </td>
34             </form>
35         </tr>
36         
37         <tr>
38             <td>用户编号</td>
39             <td>用户名</td>
40             <td>用户密码</td>
41             <td colspan="2"> 用户操作</td>
42         </tr>
43         <%
44             for( User user : users ){
45         %>
46         <tr>
47             <td> <%=user.getId() %></td>
48             <td> <%=user.getName()%></td>
49             <td> <%=user.getPassword() %></td>
50             
51             <td> <a href="delete.jsp?id=<%=user.getId() %>" >删除</a></td>
52             <td> <a href="update.jsp?id=<%=user.getId() %>" >更新</a></td>
53             
54         </tr>
55         <%
56             }
57         %>
58     </table>
59     
60 </body>
61 
62 </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     </head>
 9 <body>
10     <form action="addInput.jsp" method="post">
11             <table  border="1" bordercolor="#FFFF33" align="center" >
12             <tr>
13             
14             <td align="right" colspan="1">用户名<input type="text"  name="username" style="100px;" /></td><br>
15             
16             </tr>
17             <tr>
18             <td align="right" colspan="1">密    码<input type="password"  name="pass" style="100px;"/></td>
19             </tr>
20             <tr>
21             <td align="right" colspan="1">确认密码<input type="password"  name="pass1" style="100px;"/></td>
22             </tr>
23             <tr>
24             <td align="right" colspan="1">手机号<input type="text"  name="phone" style="100px;"/></td>
25             </tr>
26                 <tr>
27                     
28                     
29                     <td align="right"><input type="submit" value="注册"style="100px;height:30px;font-size:16px;"/></td>
30                     
31                 </tr>
32                 
33             </table>
34             
35         </form>
36         
37 </body>
38 
39 </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 <%
11 int id = Integer.parseInt(request.getParameter("id"));
12 UserImp userImp=new UserImp();
13 User user=new User();
14 user=userImp.load(id);
15 %>
16 </head>
17     <body>
18         <form action="update1.jsp" method="post">
19             <table  border="1" bordercolor="#FFFF33" align="center" >
20             <tr>
21             <input type="hidden"  name="id" style="100px;" value="<%=user.getId()%>"/>
22             <td align="right" colspan="2">用户名<input type="text"  name="username" style="100px;" value="<%=user.getName()%>"/></td><br>
23             
24             </tr>
25             <tr>
26             <td align="right" colspan="2">密    码<input type="password"  name="pass" style="100px;"/></td>
27             </tr>
28                 <tr>
29                     
30                     
31                     <td align="right"><input type="submit" value="更新"style="100px;height:30px;font-size:16px;"/></td>
32                     
33                 </tr>
34                 
35             </table>
36             
37         </form>
38     </body>
39     
40 </html>

<%@page import="dao.UserImp"%>
<%@page import="model.User"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
<%
int id = Integer.parseInt(request.getParameter("id"));
String name=request.getParameter("name");
String password=request.getParameter("pass");
UserImp userImp=new UserImp();
User user=new User();
user = userImp.load(id);
//user.setName(name);
user.setPassword(password);


userImp.update(user);
response.sendRedirect("list.jsp");
%>
</head>
    <body>
    
    </body>
</html>