jsp实现mysql删除与修改_jsp 实现修改和删除功能-CSDN博客

本文链接：https://blog.csdn.net/weixin_31286719/article/details/113205488

本文档介绍了如何使用JSP和MySQL数据库实现用户信息的查询、修改和删除操作。首先展示了数据库表`userinfo`的创建和记录插入，接着详细解释了`DbConn.java`用于连接数据库的代码，以及`User.java`和`UserDAO.java`中关于用户数据操作的方法。`GetUserServlet.java`和`LoginServlet.java`分别用于获取用户信息和登录验证，而`GetUsersServlet.java`则负责获取所有用户数据。最后，`DelServlet.java`演示了删除用户数据的步骤，整个流程覆盖了前后端交互和数据库操作的关键部分。

摘要由CSDN通过智能技术生成

1. main.jsp 实现查询在此界面创建超链接到修改界面

点击修改会把数据传递到exit.jsp

修改 edit.jsp

前面数据：

数据库：

1 /*

2 Navicat Premium Data Transfer3

4 Source Server : c10.87.12.2515 Source Server Type : SQL Server6 Source Server Version : 110021007 Source Host : 10.87.12.251:14338 Source Catalog : userdb9 Source Schema : dbo10

11 Target Server Type : SQL Server12 Target Server Version : 1100210013 File Encoding : 6500114

15 Date: 19/06/2019 22:19:4016 */

19 ------------------------------

20 --Table structure for userinfo

21 ------------------------------

22 IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[userinfo]') AND type IN ('U'))23 DROP TABLE [dbo].[userinfo]

24 GO

26 CREATE TABLE [dbo].[userinfo](27 [id] int IDENTITY(1,1) NOT NULL,28 [username] varchar(50) COLLATE Chinese_PRC_CI_AS NULL,29 [userpwd] varchar(50) COLLATE Chinese_PRC_CI_AS NULL,30 [sex] varchar(50) COLLATE Chinese_PRC_CI_AS NULL,31 [age] int NULL,32 [address] varchar(50) COLLATE Chinese_PRC_CI_AS NULL

33 )34 GO

36 ALTER TABLE [dbo].[userinfo] SET (LOCK_ESCALATION = TABLE)37 GO

40 ------------------------------

41 --Records of [userinfo]

42 ------------------------------

43 SET IDENTITY_INSERT [dbo].[userinfo] ON

44 GO

46 INSERT INTO [dbo].[userinfo] ([id], [username], [userpwd], [sex], [age], [address]) VALUES (N'1', N'root', N'123', N'1', N'1', N'1')47 GO

49 SET IDENTITY_INSERT [dbo].[userinfo] OFF

50 GO

用户信息数据库代码

(1)db/DbConn.java

1 packagedb;2

3 importjava.sql.Connection;4 importjava.sql.DriverManager;5 importjava.sql.ResultSet;6 importjava.sql.SQLException;7 importjava.sql.Statement;8

9 public classDbConn {10

11 public staticConnection getConn()12 {13 Connection con =null;14 try{15 //Class.forName("com.mysql.jdbc.Driver");//加载驱动程序

16 Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");17

18 } catch(ClassNotFoundException e) {19 System.out.println("加载驱动程序错误" +e.getMessage());20 }21

22 try{23 //创建连接 testdb是数据库名称

24 con = DriverManager.getConnection("jdbc:sqlserver://localhost:1433;DatabaseName=userdb", "sa", "123456");25

26 } catch(SQLException e) {27

28 System.out.println("数据库连接操作出错" +e.getMessage());29 }30 returncon;31 }32 }

l连接数据库代码

(2) po/User.java

1 packagepo;2

3 public classUser {4 intid;5 String username;6 String password;7 intage;8 String sex;9 String address;10

11 public intgetId() {12 returnid;13 }14 public void setId(intid) {15 this.id =id;16 }17 publicString getUsername() {18 returnusername;19 }20 public voidsetUsername(String username) {21 this.username =username;22 }23 publicString getPassword() {24 returnpassword;25 }26 public voidsetPassword(String password) {27 this.password =password;28 }29 public intgetAge() {30 returnage;31 }32 public void setAge(intage) {33 this.age =age;34 }35 publicString getSex() {36 returnsex;37 }38 public voidsetSex(String sex) {39 this.sex =sex;40 }41 publicString getAddress() {42 returnaddress;43 }44 public voidsetAddress(String address) {45 this.address =address;46 }47

48 }

交互相关字段

(3) dao/UserDAO.java

1 //根据id 查找字段返回给edit表

2 public User getUser(intid)3 {4 User u=newUser();5 try{6 //创建连接 testdb是数据库名称

7 Connection con =DbConn.getConn();8 //创建声明SQL对象

9 Statement stm =con.createStatement();10 //执行SQL语句，得到结果集，结果集放到ResultSet对象中

11 ResultSet rs = stm.executeQuery("select * from userinfo where id="+id);12 //通过循环，从结果集对象中取得数据

13 while(rs.next()) {14

15 String username = rs.getString("username"); //取得字符类型的字段username的值，

16 String userpwd = rs.getString("userpwd");17 String sex=rs.getString("sex");18 int age=rs.getInt("age");19 String address=rs.getString("address");20

21 u.setId(id);22 u.setUsername(username);23 u.setPassword(userpwd);24 u.setSex(sex);25 u.setAge(age);26 u.setAddress(address);27

28 }29 } catch(SQLException e) {30

31 System.out.println("数据库操作出错" +e.getMessage());32 }33 returnu;34 }35 //插入新数据根据id

36 public intedit(User u)37 {38 int n=0;39 try{40 //创建连接 testdb是数据库名称

41 Connection con =DbConn.getConn();42

43 //创建声明SQL对象

44 Statement stm =con.createStatement();45 //执行SQL语句，得到结果集，结果集放到ResultSet对象中

46 String sql="update userinfo set username='"+u.getUsername()+"',userpwd='"+u.getPassword()+"',sex='"+u.getSex()+"',age="+u.getAge()+",address='"+u.getAddress()+"' " +

47 "where id="+u.getId()+"";48 n=stm.executeUpdate(sql);49 } catch(SQLException e) {50

51 System.out.println("数据库操作出错" +e.getMessage());52 }53 returnn;54 }

根据id 查找字段返回给edit表和插入数据

1 public ListgetUserList()2 {3 List ls=new ArrayList();4 try{5 //创建连接 testdb是数据库名称

6 Connection con =DbConn.getConn();7 //创建声明SQL对象

8 Statement stm =con.createStatement();9 //执行SQL语句，得到结果集，结果集放到ResultSet对象中

10 ResultSet rs = stm.executeQuery("select * from userinfo");11 //通过循环，从结果集对象中取得数据

12 while(rs.next()) {13 int id = rs.getInt("id"); //取得int类型的字段id的值，

14 String username = rs.getString("username"); //取得字符类型的字段username的值，

15 String userpwd = rs.getString("userpwd");16 String sex=rs.getString("sex");17 int age=rs.getInt("age");18 String address=rs.getString("address");19 User u=newUser();20 u.setId(id);21 u.setUsername(username);22 u.setPassword(userpwd);23 u.setSex(sex);24 u.setAge(age);25 u.setAddress(address);26 ls.add(u);27 }28 } catch(SQLException e) {29

30 System.out.println("数据库操作出错" +e.getMessage());31 }32 returnls;33 }

查询所有信息

1 public booleanlogin(String username,String userpwd)2 {3 boolean flag=false;4 try{5 //创建连接 testdb是数据库名称

6 Connection con =DbConn.getConn();7 //创建声明SQL对象

8 Statement stm =con.createStatement();9 //执行SQL语句，得到结果集，结果集放到ResultSet对象中

10 ResultSet rs = stm.executeQuery("select * from userinfo where username='"+username+"' and userpwd='"+userpwd+"' ");11 //通过循环，从结果集对象中取得数据

12 if(rs.next()) {13 flag=true;14 }15 else

16 {17 flag=false;18 }19

20 } catch(SQLException e) {21

22 System.out.println("数据库操作出错" +e.getMessage());23 }24 returnflag;25 }

(4)servlet

main里面的

修改调用 GetUserServlet 调用UserDAO. getUser(int id) 方法

GetUserServlet.java

1 packageservlet;2

3 importjava.io.IOException;4 importjava.io.PrintWriter;5 importjava.util.List;6

7 importjavax.servlet.ServletException;8 importjavax.servlet.http.HttpServlet;9 importjavax.servlet.http.HttpServletRequest;10 importjavax.servlet.http.HttpServletResponse;11 importjavax.servlet.http.HttpSession;12

13 importpo.User;14 importdao.UserDAO;15

16 public class GetUserServlet extendsHttpServlet {17

18 /**

19 * Constructor of the object.20 */

21 publicGetUserServlet() {22 super();23 }24

25 /**

26 * Destruction of the servlet.
27 */

28 public voiddestroy() {29 super.destroy(); //Just puts "destroy" string in log30 //Put your code here

31 }32

33 /**

34 * The doGet method of the servlet.
35 *36 * This method is called when a form has its tag value method equals to get.37 *38 *@paramrequest the request send by the client to the server39 *@paramresponse the response send by the server to the client40 *@throwsServletException if an error occurred41 *@throwsIOException if an error occurred42 */

43 public voiddoGet(HttpServletRequest request, HttpServletResponse response)44 throwsServletException, IOException {45 doPost(request,response);46 }47

48 /**

49 * The doPost method of the servlet.
50 *51 * This method is called when a form has its tag value method equals to post.52 *53 *@paramrequest the request send by the client to the server54 *@paramresponse the response send by the server to the client55 *@throwsServletException if an error occurred56 *@throwsIOException if an error occurred57 */

58 public voiddoPost(HttpServletRequest request, HttpServletResponse response)59 throwsServletException, IOException {60

61 response.setContentType("text/html");62 PrintWriter out =response.getWriter();63 out64 .println(""-//W3C//DTD HTML 4.01 Transitional//EN\">");65 out.println("");66 out.println("

A Servlet");67 out.println(" ");68

69 //根据id 查找到所有字段返回给exit.jsp

71 HttpSession session=request.getSession(true);72 String sid=request.getParameter("userid");73 int id=Integer.parseInt(sid);74 UserDAO udao=newUserDAO();75 User u=udao.getUser(id);76 //传递给session 对象

77 session.setAttribute("user", u);78 response.sendRedirect("../edit.jsp");79

80 out.println(" ");81 out.println("");82 out.flush();83 out.close();84 }85

86 /**

87 * Initialization of the servlet.
88 *89 *@throwsServletException if an error occurs90 */

91 public void init() throwsServletException {92 //Put your code here

93 }94

95 }96

根据id 查找到所有字段返回给exit.jsp

edit.jsp 修改好后的传递给

EditServlet.java

1 packageservlet;2

3 importjava.io.IOException;4 importjava.io.PrintWriter;5

6 importjavax.servlet.ServletException;7 importjavax.servlet.http.HttpServlet;8 importjavax.servlet.http.HttpServletRequest;9 importjavax.servlet.http.HttpServletResponse;10

11 importpo.User;12 importdao.UserDAO;13

14 public class EditServlet extendsHttpServlet {15

16 /**

17 * Constructor of the object.18 */

19 publicEditServlet() {20 super();21 }22

23 /**

24 * Destruction of the servlet.
25 */

26 public voiddestroy() {27 super.destroy(); //Just puts "destroy" string in log28 //Put your code here

29 }30

31 /**

32 * The doGet method of the servlet.
33 *34 * This method is called when a form has its tag value method equals to get.35 *36 *@paramrequest the request send by the client to the server37 *@paramresponse the response send by the server to the client38 *@throwsServletException if an error occurred39 *@throwsIOException if an error occurred40 */

41 public voiddoGet(HttpServletRequest request, HttpServletResponse response)42 throwsServletException, IOException {43

44 doPost(request,response);45 }46

47 /**

48 * The doPost method of the servlet.
49 *50 * This method is called when a form has its tag value method equals to post.51 *52 *@paramrequest the request send by the client to the server53 *@paramresponse the response send by the server to the client54 *@throwsServletException if an error occurred55 *@throwsIOException if an error occurred56 */

57 public voiddoPost(HttpServletRequest request, HttpServletResponse response)58 throwsServletException, IOException {59

60 response.setContentType("text/html");61 PrintWriter out =response.getWriter();62 out63 .println(""-//W3C//DTD HTML 4.01 Transitional//EN\">");64 out.println("");65 out.println("

A Servlet");66 out.println(" ");67 //获取edit 传递的字段

69 String userid=request.getParameter("userid");70 String username=request.getParameter("username");71 String userpwd=request.getParameter("userpwd");72 String sex=request.getParameter("sex");73 String age=request.getParameter("age");74 String address=request.getParameter("address");75

76 User u=newUser();77 u.setId(Integer.parseInt(userid));78 u.setUsername(username);79 u.setPassword(userpwd);80 u.setSex(sex);81 u.setAge(Integer.parseInt(age));82 u.setAddress(address);83

84 //调用方法

85 UserDAO udao=newUserDAO();86

87 int n=udao.edit(u);88 if(n>0) {89 //成功就调用查询方法看修改完后的

90 response.sendRedirect("../servlet/GetUsersServlet");91 }92 else

93 {94 out.println("修改失败");95 }96

99 out.println(" ");100 out.println("");101 out.flush();102 out.close();103 }104

105 /**

106 * Initialization of the servlet.
107 *108 *@throwsServletException if an error occurs109 */

110 public void init() throwsServletException {111 //Put your code here

112 }113

114 }

从edit.jsp获取数据在调用修改函数给数据库

1 packageservlet;2

3 importjava.io.IOException;4 importjava.io.PrintWriter;5 import java.sql.*;6

7 importjavax.servlet.ServletException;8 importjavax.servlet.http.HttpServlet;9 importjavax.servlet.http.HttpServletRequest;10 importjavax.servlet.http.HttpServletResponse;11

12 importdao.UserDAO;13 importdb.DbConn;14

15 public class LoginServlet extendsHttpServlet {16

17 /**

18 * Constructor of the object.19 */

20 publicLoginServlet() {21 super();22 }23

24 /**

25 * Destruction of the servlet.
26 */

27 public voiddestroy() {28 super.destroy(); //Just puts "destroy" string in log29 //Put your code here

30 }31

32 /**

33 * The doGet method of the servlet.
34 *35 * This method is called when a form has its tag value method equals to get.36 *37 *@paramrequest the request send by the client to the server38 *@paramresponse the response send by the server to the client39 *@throwsServletException if an error occurred40 *@throwsIOException if an error occurred41 */

42 public voiddoGet(HttpServletRequest request, HttpServletResponse response)43 throwsServletException, IOException {44 doPost(request,response);45 }46

47 /**

57 public voiddoPost(HttpServletRequest request, HttpServletResponse response)58 throwsServletException, IOException {59

60 response.setContentType("text/html");61 PrintWriter out =response.getWriter();62 out63 .println(""-//W3C//DTD HTML 4.01 Transitional//EN\">");64 out.println("");65 out.println("

A Servlet");66 out.println(" ");67

68 String username=(String)request.getParameter("username");69 String userpwd=(String)request.getParameter("password");70

71 UserDAO udao=newUserDAO();72 boolean flag=udao.login(username, userpwd);73

74 if(flag==true){75 out.print("success");76

77 response.sendRedirect("../servlet/GetUsersServlet");78

79 }80 else

81 out.print("false");82

84 out.println(" ");85 out.println("");86 out.flush();87 out.close();88 }89

90 /**

91 * Initialization of the servlet.
92 *93 *@throwsServletException if an error occurs94 */

95 public void init() throwsServletException {96 //Put your code here

97 }98

99 }

LoginServlet.java

查询所有GetUsersServlet.java

1 packageservlet;2

3 importjava.io.IOException;4 importjava.io.PrintWriter;5 importjava.util.List;6

13 importpo.User;14

15 importdao.UserDAO;16

17 public class GetUsersServlet extendsHttpServlet {18

19 /**

20 * Constructor of the object.21 */

22 publicGetUsersServlet() {23 super();24 }25

26 /**

27 * Destruction of the servlet.
28 */

29 public voiddestroy() {30 super.destroy(); //Just puts "destroy" string in log31 //Put your code here

32 }33

34 /**

35 * The doGet method of the servlet.
36 *37 * This method is called when a form has its tag value method equals to get.38 *39 *@paramrequest the request send by the client to the server40 *@paramresponse the response send by the server to the client41 *@throwsServletException if an error occurred42 *@throwsIOException if an error occurred43 */

44 public voiddoGet(HttpServletRequest request, HttpServletResponse response)45 throwsServletException, IOException {46 doPost(request,response);47 }48

49 /**

50 * The doPost method of the servlet.
51 *52 * This method is called when a form has its tag value method equals to post.53 *54 *@paramrequest the request send by the client to the server55 *@paramresponse the response send by the server to the client56 *@throwsServletException if an error occurred57 *@throwsIOException if an error occurred58 */

59 public voiddoPost(HttpServletRequest request, HttpServletResponse response)60 throwsServletException, IOException {61

62 response.setContentType("text/html");63 PrintWriter out =response.getWriter();64 out65 .println(""-//W3C//DTD HTML 4.01 Transitional//EN\">");66 out.println("");67 out.println("

A Servlet");68 out.println(" ");69 HttpSession session=request.getSession(true);70

71 UserDAO udao=newUserDAO();72 List ls=udao.getUserList();73 session.setAttribute("userlist", ls);74 response.sendRedirect("../main.jsp");75

76 out.println(" ");77 out.println("");78 out.flush();79 out.close();80 }81

82 /**

83 * Initialization of the servlet.
84 *85 *@throwsServletException if an error occurs86 */

87 public void init() throwsServletException {88 //Put your code here

89 }90

91 }

获取所有数据

DelServlet.java

1 packageservlet;2

3 importjava.io.IOException;4 importjava.io.PrintWriter;5 import java.sql.*;6

7 importjavax.servlet.ServletException;8 importjavax.servlet.http.HttpServlet;9 importjavax.servlet.http.HttpServletRequest;10 importjavax.servlet.http.HttpServletResponse;11

12 importdb.DbConn;13

14 public class DelServlet extendsHttpServlet {15

16 /**

17 * Constructor of the object.18 */

19 publicDelServlet() {20 super();21 }22

23 /**

24 * Destruction of the servlet.
25 */

26 public voiddestroy() {27 super.destroy(); //Just puts "destroy" string in log28 //Put your code here

29 }30

31 /**

41 public voiddoGet(HttpServletRequest request, HttpServletResponse response)42 throwsServletException, IOException {43 doPost(request,response);44 }45

46 /**

47 * The doPost method of the servlet.
48 *49 * This method is called when a form has its tag value method equals to post.50 *51 *@paramrequest the request send by the client to the server52 *@paramresponse the response send by the server to the client53 *@throwsServletException if an error occurred54 *@throwsIOException if an error occurred55 */

56 public voiddoPost(HttpServletRequest request, HttpServletResponse response)57 throwsServletException, IOException {58

59 response.setContentType("text/html");60 PrintWriter out =response.getWriter();61 out62 .println(""-//W3C//DTD HTML 4.01 Transitional//EN\">");63 out.println("");64 out.println("

A Servlet");65 out.println(" ");66

67 String userid=request.getParameter("userid");68 out.println(userid);69

71 try{72 //创建连接 testdb是数据库名称

73 Connection con =DbConn.getConn();74 //创建声明SQL对象

75 Statement stm =con.createStatement();76 //执行SQL语句，得到结果集，结果集放到ResultSet对象中

78 int n=stm.executeUpdate(sql);79 if(n>0) {80 response.sendRedirect("../servlet/GetUsersServlet");81

82 }83 else

84 {85 out.println("删除失败");86 }87

88 } catch(SQLException e) {89

90 System.out.println("数据库操作出错" +e.getMessage());91 }92

96 out.println(" ");97 out.println("");98 out.flush();99 out.close();100 }101

102 /**

103 * Initialization of the servlet.
104 *105 *@throwsServletException if an error occurs106 */

107 public void init() throwsServletException {108 //Put your code here

109 }110

111 }

删除

1.main.jsp

My JSP 'main.jsp' starting page

12 序号

13 姓名

14 密码

15 年龄

16 性别

17 地址

18 删除

19 修改

23 Listls=(List)session.getAttribute("userlist");24 for(inti=0;i

">删除

">修改

39 }40 %>

42 添加

查询界面和修改界面

2.修改 edit.jsp

2 pageEncoding="utf-8"%>

Insert title here

14 User u=(User)session.getAttribute("user");15 %>

用户添加

姓名：19

20 "/>

21 "/>

密码：24 "/>

年龄：27 "/>

性别：30 "/>

地址：33 "/>

edit.jsp

3.Login

3 String path =request.getContextPath();4 String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";5 %>

My JSP 'Login.jsp' starting page