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="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" 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 请输入用户名 : 31 <input type="text" name="content" size="40" value="<%=content%>"/> 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="width:100px;" /></td><br> 15 16 </tr> 17 <tr> 18 <td align="right" colspan="1">密 码<input type="password" name="pass" style="width:100px;"/></td> 19 </tr> 20 <tr> 21 <td align="right" colspan="1">确认密码<input type="password" name="pass1" style="width:100px;"/></td> 22 </tr> 23 <tr> 24 <td align="right" colspan="1">手机号<input type="text" name="phone" style="width:100px;"/></td> 25 </tr> 26 <tr> 27 28 29 <td align="right"><input type="submit" value="注册"style="width: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="width:100px;" value="<%=user.getId()%>"/> 22 <td align="right" colspan="2">用户名<input type="text" name="username" style="width:100px;" value="<%=user.getName()%>"/></td><br> 23 24 </tr> 25 <tr> 26 <td align="right" colspan="2">密 码<input type="password" name="pass" style="width:100px;"/></td> 27 </tr> 28 <tr> 29 30 31 <td align="right"><input type="submit" value="更新"style="width: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>