devbox
盐析白兔
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

jsp+servlet+jdbc实现对数据库简单的增删改查_jsp连接数据库增删改查

作者：盐析白兔 | 2024-05-15 23:08:07

赞

踩

jsp连接数据库增删改查

      入职以来（来京六整月），一直在学习JavaWeb的知识，分享下最近几个月（断断续续）用jsp+servlet+jdbc实现的一个demo，这个demo借鉴了网上很多前辈的经验，希望自己能够不断进步。

效果图如下：

1、首先介绍下开发所需要工具 ，以及相应的开发环境：
      eclipse+tomcat+mysql+jdk；

2、创建数据库userdemo，然后创建userdemo及userinfo表

3、接下来在eclipse中搭建web工程，相应目录如下图所示，并导入相应的包（需要mysql连接jdbc的jar包，以及jstl的jar包）


4、对应jsp页面的代码
（1）登陆界面logon.jsp：

  1. <%@ page import="java.sql.*" language="java" import="java.util.*" pageEncoding="utf-8" %>
  2. <%
  3. String path = request.getContextPath();
  4. String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
  5. %>   
  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=ISO-8859-1">
  10. <title>用户登陆界面</title>
  11.  
  12. <style type="text/css">
  13. body {
  14.     background-attachment: fixed;
  15.     background-repeat: no-repeat;
  16.     background-size: 100% 100%;
  17.     color: #000;
  18.     font-family: "微软雅黑";
  19.     font-size: 1.6em;
  20. }
  21. #div1 {
  22.     text-align: center;
  23.     width: 200px;
  24.     height: 200px;
  25. }
  26. #span {
  27.     font-size: 9px;
  28. }
  29. </style>
  30.  
  31. <script>
  32. function myFunction(){
  33. 	var x = document.getElementById("psw").value;
  34. 	if(x==""||isNaN(x)){
  35. 		 alert("密码不能为空!");
  36. 		 return;
  37. 	}
  38. 	var y = document.getElementById("usn").value;
  39. 	if(y==""){
  40. 		alert("用户名不能为空!");
  41. 		return;
  42. 	}
  43. 	document.getElementById("form1").submit();
  44. }
  45. </script>
  46. </head>
  47.  
  48. <body>
  49.  
  50. <div id="div1">
  51. 	<form id="form1" action="logon" method="post">
  52. 		<p>
  53. 			<label for="username">用户名</label>
  54. 			<input id="usn" type="text" name="username">
  55. 		</p>
  56. 		<p>
  57. 			<label for="password">密码</label>
  58. 			<input id="psw" type="password" name="password">
  59. 		</p>
  60. 		<input type="button" onclick="myFunction()" value="登陆" >
  61. 	</form>
  62. </div>
  63.  
  64. </body>
  65. </html>

（2）主界面home.jsp

  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.  
  5. <html>
  6. <head>
  7. 	<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
  8. 	<title>人员信息</title>
  9. </head>
  10. 	<!-- frame对应的导航框架 -->
  11. 	<frameset rows="93%,*" >	
  12. 		<frameset cols="20%, *" noresize="noresize" >
  13. 			<frame src="left.jsp" frameborder="1" name="leftFrame" />
  14. 			<frame src="userlistright" frameborder="1" name="rightFrame" />
  15. 		</frameset>
  16. 	<frame src="footer.jsp" frameborder="1" name="footerFrame"/>
  17. 	</frameset>
  18. </html>

（3）左侧部分left.jsp

  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.  
  6. <!-- head部分 -->
  7. <head>
  8. <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  9. <title>人员信息</title>
  10. 	<style type="text/css">
  11. 		a:link,a:visited {
  12. 			text-decoration: none;  /*超链接无下划线*/
  13. 		}
  14. 		a:hover{
  15. 			text-decoration:underline;  /*鼠标放上去有下划线*/
  16. 		}
  17. 		li{
  18. 			list-style: none; /*去除li前面的小黑点*/
  19. 		}
  20. 	</style>
  21. </head>
  22. <!--js:对应跳转部分 -->
  23. <script type="text/javascript">
  24. 	function add(){
  25. 		window.parent.rightFrame.location.href='addright.jsp'
  26. 	}
  27. 	function userList(){
  28. 		window.parent.rightFrame.location.href='userlistright' 
  29. 	}
  30. </script>
  31. <body>
  32. 	<div> 
  33. 		<h3 style="margin-center">人员信息</h3>
  34. 		<ul>
  35. 			<li>
  36. 				<a href="#" onclick="add()">新增</a>
  37. 			</li>
  38. 			<li>
  39. 				<a href="#" onclick="userList()">人员列表</a> 
  40. 			</li>
  41. 		</ul>
  42. 	</div>
  43. </body>
  44. </html>

(4)右侧部分(人员列表)userlistright

  1. <%@ page language="java" contentType="text/html; charset=UTF-8"
  2.     pageEncoding="UTF-8"%>
  3. <%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
  4. <%@ page import="dao.UserInfoDaoMrg" %>
  5. <%@ page import="entity.UserInfo" %>
  6. <%@ page import="java.util.*" %>
  7. <%@ page import="bean.Page" %>
  8. <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
  9. <html>
  10. <!-- head部分 -->
  11. <head>
  12. 	<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  13. 	<title>人员列表</title>	
  14. 	<style type="text/css">
  15. 		a:link,a:visited {
  16. 			text-decoration: none;  /*超链接无下划线*/
  17. 		}
  18. 		a:hover{
  19. 			text-decoration:underline;  /*鼠标放上去有下划线*/
  20. 		}
  21. 		td{ 
  22. 			text-align:center;
  23. 		}		
  24. 				
  25. 	</style>
  26. </head>
  27.  
  28. <!--js:对应跳转部分 -->
  29. <script>
  30. 	function refreshList(){
  31. 		window.parent.rightFrame.location.href='userlistright' //servlet
  32. 	}
  33. 	
  34. 	function add(){
  35. 		window.parent.rightFrame.location.href='addright.jsp' //
  36. 	}
  37. 	
  38. 	function delFun(){
  39. 		window.parent.rightFrame.location.href='userlistright' //
  40. 	}	
  41. 	
  42. 	function editFun(){
  43. 		window.parent.rightFrame.location.href='addright.jsp' //
  44. 	}	
  45. 	
  46. </script>
  47. <body>
  48. 	<div style="margin-top:10px; margin-bottom:10px;"> 
  49. 		<button id="refreshbutton" style="width:80px;height:40px;margin-left:10px" type="button" onclick="refreshList()">刷新界面</button>
  50. 		<button id="addbutton" style="width:80px;height:40px;" type="button" onclick="add()">新增</button>
  51. 	</div>
  52. 	
  53. 	<div>
  54. 		<table id="user_list"  border="1"  cellspacing="0" cellpadding="0" style="border:1px solid gray; border-radius:4px">
  55. 			<thead>
  56. 				<tr>
  57. 			     	<th style="width:100px;text-align:center">工号</th>
  58. 					<th style="width:80px;text-align:center">姓名</th>
  59. 					<th style="width:120px;text-align:center">技术水平</th>
  60. 					<th style="width:120px;text-align:center">所在部门</th>
  61. 					<th style="width:160px;text-align:center">角色</th>
  62. 					<th style="width:180px;text-align:center">操作</th>
  63. 				</tr>		
  64. 			</thead>
  65. 		
  66. 			<tbody>
  67. 				<%
  68. 					List<UserInfo> list = (List<UserInfo>)request.getAttribute("list");					
  69. 					for(UserInfo user : list){
  70. 				%>
  71. 				<tr>
  72. 				    <td style="width:100px;text-align:center"><%=user.getJobNumber()%></td>
  73. 					<td style="width:80px;text-align:center"><%=user.getName()%></td>
  74. 					<td style="width:120px;text-align:center"><%=user.getLevel()%></td>
  75. 					<td style="width:120px;text-align:center"><%=user.getDepartment()%></td>
  76. 					<td style="width:160px;text-align:center"><%=user.getRole()%></td>
  77. 					<td><a href="deleteUserServlet?id=<%=user.getId()%>" onclick="return confirm('是否确认删除<%=user.getName()%>信息?')">删除</a>  
  78. 					<a href="selectUserServlet?id=<%=user.getId()%>">修改</a></td>
  79. 				</tr>	
  80. 				<%
  81. 					}
  82. 				%>
  83. 				
  84. 			</tbody>		
  85. 		</table>
  86. 	</div>
  87. 	
  88. 	<div style="width:800px">	    
  89. 		<c:choose>
  90. 			<c:when test="${page.pageNum<=1}">
  91. 				<a href="userlistright?pageNum = 1 ">前一页</a>
  92. 			</c:when>
  93. 			<c:otherwise>
  94. 				<a href="userlistright?pageNum=${page.pageNum-1}">前一页</a>
  95. 			</c:otherwise>
  96. 		</c:choose>
  97. 	
  98.     	<c:forEach var="i" begin="1" end="${page.pageTotal}" step="1">
  99. 			<a href="userlistright?pageNum=${i}">${i}</a>
  100. 		</c:forEach>
  101. 		
  102. 		<c:choose>
  103. 			<c:when test="${page.pageNum >= page.pageTotal}%>">
  104. 				<a href="userlistright?pageNum=${page.pageTotal}">后一页</a>
  105. 			</c:when>
  106. 			<c:otherwise>
  107. 				<a href="userlistright?pageNum=${page.pageNum + 1}">后一页</a>
  108. 			</c:otherwise>
  109. 		</c:choose>		
  110. 		<span style="margin-left:10px">一共${page.pageTotal}页</span>
  111. 	</div>
  112. </body>
  113. </html>

(5)footer.jsp

  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. <div class="footer">
  5. 	<div class="container_1"> 
  6. 		<p>
  7. 			Designed by <a href="http://www.baidu.com" target="_blank">百度</a>		
  8. 		</p>
  9. 	</div>
  10. </div>

(6)增加人员界面addright.jsp

  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>新增</title>
  8. </head>
  9. <script>
  10. 	function confirmFun(){		
  11. 		var x1 = document.getElementById("name").value;
  12. 		var x2 = document.getElementById("jobNumber").value;
  13. 		var x3 = document.getElementById("level").value;
  14. 		var x4 = document.getElementById("department").value;
  15. 		var x5 = document.getElementById("role").value;
  16. 		if(x1==""||isNaN(x1)||x2==""||isNaN(x2)||x3==""||isNaN(x3)||x4==""||isNaN(x4)
  17. 				||x5==""||isNaN(x5)){
  18. 			 alert("添加信息不完整");
  19. 			 return;
  20. 		}		
  21. 		document.getElementById("form").submit();
  22. 	}
  23. 	
  24. 	function resetFun(){
  25. 		var spans = document.getElementsByTagName("input");
  26. 		for(var j=0;j<spans.length;j++){  
  27. 			var span = spans[j]; // input标签的数组
  28. 			document.getElementById(span.id).value ="";//
  29. 			console.info(span.id);
  30. 		}
  31. 	}
  32. </script>
  33.  
  34. <body>
  35.  <h3>新增页面</h3>
  36.     <form  id="form" action="addright" method="post">    
  37.              工号：<input id="jobNumber" name="jobNumber"><br>
  38. 	    姓名：<input id="name" name="name"><br>
  39. 	    技术水平：<input id="level" name="level"><br>
  40. 	    所在部门：<input id="department" name="department"><br>
  41. 	   角色：<input id="role" name="role"><br>
  42.      <input type="button" value="确定" onclick="confirmFun()"> <input type="reset" value="重置" onclick="resetFun()">
  43.     </form>
  44. </body>
  45.  
  46. </html>

(7)编辑人员信息界面updateuser.jsp

  1. <%@ page language="java" contentType="text/html; charset=UTF-8"
  2.     pageEncoding="UTF-8"%>
  3. <%@ page import="entity.UserInfo" %>
  4. <%@ page import="dao.UserInfoDaoMrg" %>
  5.  
  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>修改</title>
  11. </head>
  12. <script>
  13. 	function confirmFun(){
  14. 		var x1 = document.getElementById("name").value;
  15. 		var x2 = document.getElementById("jobNumber").value;
  16. 		var x3 = document.getElementById("level").value;
  17. 		var x4 = document.getElementById("department").value;
  18. 		var x5 = document.getElementById("role").value;
  19.  
  20. 		if(x1==""||isNaN(x1)||x2==""||isNaN(x2)||x3==""||isNaN(x3)||x4==""||isNaN(x4)
  21. 				||x5==""||isNaN(x5)){
  22. 			 alert("添加信息不完整");
  23. 			 return;
  24. 		}		
  25. 		document.getElementById("updform").submit();
  26. 	}
  27. 	
  28. 	function resetFun(){
  29. 		var spans = document.getElementsByTagName("input");
  30. 		for(var i=0;j<spans.length;i++){  
  31. 			var span = spans[i]; // input标签的数组
  32. 			document.getElementById(span.id).value ="";//
  33. 			console.info(span.id);
  34. 		}
  35. 	}
  36. </script>
  37. <body>
  38.  <h3>修改页面</h3>
  39. 	<%
  40. 	UserInfo user = (UserInfo)request.getAttribute("user");	
  41. 	%>
  42.     <form id="updform" action="updateuser" method="post">
  43.                <input type="hidden" name="id" value="<%=user.getId()%>">
  44. 	               工号：<input name="jobNumber" value="<%=user.getJobNumber()%>"><br>
  45. 		    姓名：<input name="name" value="<%=user.getName()%>"> <br>
  46. 		    技术水平：<input name="level" value="<%=user.getLevel()%>"><br>
  47. 		    所在部门：<input name="department" value="<%=user.getDepartment()%>"><br>
  48. 		   角色：<input name="role" value="<%=user.getRole()%>"><br>
  49. 	    <input type="submit" value="确定"  onclick="confirmFun()">  <input type="reset" value="重置"  onclick="resetFun()">
  50.     </form>
  51. </body>
  52. </html>

5、配置web.xml文件的信息如下：

  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://xmlns.jcp.org/xml/ns/javaee" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_3_1.xsd" id="WebApp_ID" version="3.1">
  3.   <display-name>webexample</display-name>
  4.   <welcome-file-list>
  5.     <welcome-file>/logon.jsp</welcome-file>
  6.   </welcome-file-list>
  7.   
  8.   <servlet>
  9.     <servlet-name>s1</servlet-name>
  10.     <servlet-class>servlet.ActionServlet</servlet-class>
  11.   </servlet>
  12.   <servlet-mapping>
  13.     <servlet-name>s1</servlet-name>
  14.     <url-pattern>/logon</url-pattern>
  15.   </servlet-mapping>
  16.   
  17.   <servlet>
  18.     <servlet-name>s2</servlet-name>
  19.     <jsp-file>/home.jsp</jsp-file>
  20.   </servlet>
  21.   <servlet-mapping>
  22.     <servlet-name>s2</servlet-name>
  23.     <url-pattern>/home</url-pattern>
  24.   </servlet-mapping>
  25.     
  26.   <servlet>
  27.     <servlet-name>s3</servlet-name>
  28.     <servlet-class>servlet.UserListServlet</servlet-class>
  29.   </servlet>
  30.   <servlet-mapping>
  31.     <servlet-name>s3</servlet-name>
  32.     <url-pattern>/userlistright</url-pattern>
  33.   </servlet-mapping>
  34.   
  35.   <servlet>
  36.     <servlet-name>s4</servlet-name>
  37.     <servlet-class>servlet.AddUserServlet</servlet-class>
  38.   </servlet>
  39.   <servlet-mapping>
  40.     <servlet-name>s4</servlet-name>
  41.     <url-pattern>/addright</url-pattern>
  42.   </servlet-mapping>
  43.   
  44.     <servlet>
  45.     <servlet-name>s5</servlet-name>
  46.     <servlet-class>servlet.UpdateUserServlet</servlet-class>
  47.   </servlet>
  48.    <servlet-mapping>
  49.     <servlet-name>s5</servlet-name>
  50.     <url-pattern>/updateuser</url-pattern>
  51.   </servlet-mapping>
  52.    
  53.    <servlet>
  54.     <servlet-name>s6</servlet-name>
  55.     <servlet-class>servlet.DeleteUserServlet</servlet-class>
  56.   </servlet>
  57.   <servlet-mapping>
  58.     <servlet-name>s6</servlet-name>
  59.     <url-pattern>/deleteUserServlet</url-pattern>
  60.   </servlet-mapping>
  61.   
  62.    <servlet>
  63.     <servlet-name>s7</servlet-name>
  64.     <servlet-class>servlet.SelectUserServlet</servlet-class>
  65.   </servlet>
  66.   <servlet-mapping>
  67.     <servlet-name>s7</servlet-name>
  68.     <url-pattern>/selectUserServlet</url-pattern>
  69.   </servlet-mapping>
  70.   
  71. </web-app>

6、对应的Java代码如下：
（1）util包下的DBUtil.java

  1. package util;
  2.  
  3. import java.sql.Connection;
  4. import java.sql.DriverManager;
  5. import java.sql.ResultSet;
  6. import java.sql.SQLException;
  7. import java.sql.Statement;
  8.  
  9. import org.junit.Test;
  10.  
  11. /**
  12.  *  JDBC工具类：
  13.  *  提供了获得连接，关闭连接的相关的方法
  14.  * @author Administrator
  15.  *
  16.  */
  17. public class DBUtil {	
  18. 	static Connection conn = null;			 
  19. 	static Statement stmt = null;
  20.  
  21. 	//建立连接
  22. 	public static Connection getConnection() throws Exception{	
  23. 	    try {		
  24. 	    	Class.forName("com.mysql.jdbc.Driver");		
  25. 		    System.out.println("成功加载驱动程序");
  26. 		    conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/userdemo", "root", "root");
  27. 			System.out.println("数据库连接成功");
  28. 		} catch (Exception e) {
  29. 			System.out.println("未连接成功");
  30. 			e.printStackTrace();
  31. 		}
  32. 		return conn;
  33. 	}
  34. }


（2）entity包下的User.java、UserInfo.java

（2.1） User.java类

  1. package entity;
  2.  
  3. /**
  4.  * 实体类
  5.  * @author Administrator
  6.  * 对应登陆界面
  7.  */
  8.  
  9. public class User {
  10. 	private int id;
  11.     private String username;
  12.     private String pwd;
  13.     private String name;
  14.     private String gender;
  15.  
  16. 	public int getId() {
  17. 		return id;
  18. 	}
  19. 	
  20. 	public void setId(int id) {
  21. 		this.id = id;
  22. 	}
  23. 	
  24. 	public String getUsername() {
  25. 		return username;
  26. 	}
  27. 	
  28. 	public void setUsername(String username) {
  29. 		this.username = username;
  30. 	}
  31. 	
  32. 	public String getPwd() {
  33. 		return pwd;
  34. 	}
  35. 	
  36. 	public void setPwd(String pwd) {
  37. 		this.pwd = pwd;
  38. 	}
  39. 	
  40. 	public String getName() {
  41. 		return name;
  42. 	}
  43. 	
  44. 	public void setName(String name) {
  45. 		this.name = name;
  46. 	}
  47. 	
  48. 	public String getGender() {
  49. 		return gender;
  50. 	}
  51. 	
  52. 	public void setGender(String gender) {
  53. 		this.gender = gender;
  54. 	}
  55.     
  56. 	@Override
  57. 	public String toString() {
  58. 		return "User [id=" + id + ", username=" + username + ", pwd=" + pwd
  59. 				+ ", name=" + name + ", gender=" + gender + "]";
  60. 	}
  61. }

（2.2）UserInfo.java类 

  1. package entity;
  2.  
  3. /**
  4.  * @author
  5.  * 人员信息的实体类
  6.  * 对应人员信息界面
  7.  */
  8. public class UserInfo {
  9. 	private Integer id;
  10.     private String jobNumber;
  11.     private String name;
  12.     private String level;
  13.     private String department;
  14.     private String role;
  15.  
  16. 	//初始化
  17.     public UserInfo(Integer id ,String jobNumber,String name, String level, String department, String role) {
  18.     	this.id = id;
  19.     	this.jobNumber = jobNumber;
  20.     	this.name = name;
  21. 		this.level = level;
  22. 		this.department = department;
  23. 		this.role = role;
  24. 	}
  25.     
  26. 	//初始化
  27.     public UserInfo(String jobNumber,String name, String level, String department, String role) {
  28. 		this.jobNumber = jobNumber;
  29.     	this.name = name;
  30. 		this.level = level;
  31. 		this.department = department;
  32. 		this.role = role;
  33. 	}
  34.     
  35.     public int getId() {
  36. 		return id;
  37. 	}
  38.  
  39. 	public void setId(int id) {
  40. 		this.id = id;
  41. 	}
  42. 	public String getJobNumber() {
  43. 		return jobNumber;
  44. 	}
  45. 	public void setJobNumber(String jobNumber) {
  46. 		this.jobNumber = jobNumber;
  47. 	}
  48. 	public String getName() {
  49. 		return name;
  50. 	}
  51. 	public void setName(String name) {
  52. 		this.name = name;
  53. 	}	
  54. 	public String getLevel() {
  55. 		return level;
  56. 	}
  57. 	public void setLevel(String level) {
  58. 		this.level = level;
  59. 	}
  60. 	public String getDepartment() {
  61. 		return department;
  62. 	}
  63. 	public void setDepartment(String department) {
  64. 		this.department = department;
  65. 	}
  66. 	public String getRole() {
  67. 		return role;
  68. 	}
  69. 	public void setRole(String role) {
  70. 		this.role = role;
  71. 	}
  72. 	
  73. 	@Override
  74. 	public String toString() {
  75. 		return "UserInfo [id=" + id + ", jobNumber=" + jobNumber + ", name="
  76. 				+ name + ", level=" + level + ", department=" + department
  77. 				+ ", role=" + role + "]";
  78. 	}
  79. }

（3）dao包下的UserDao.java、UserInfoDaoMrg.java

（3.1） UserDao.java类

  1. package dao;
  2.  
  3. import java.sql.Connection;
  4. import java.sql.PreparedStatement;
  5. import java.sql.ResultSet;
  6.  
  7. import util.DBUtil;
  8. import entity.User;
  9.  
  10. /**
  11.  * @author
  12.  * 对应登陆界面
  13.  */
  14. public class UserDao {
  15.  
  16. 	public User findByUsername(String username){
  17. 		User user = null;       
  18. 		Connection conn = null;
  19. 		
  20. 		//获取连接    	
  21.         try {
  22.         	conn = DBUtil.getConnection();
  23.         	//预处理语句
  24.         	PreparedStatement prep = conn.prepareStatement("select * from userdemo where username=?");//username
  25. 			prep.setString(1, username);//查询sql语句中第一个“？”的位置			
  26. 			//遍历数据库
  27. 			ResultSet rst = prep.executeQuery();//rst.next()指针游标，输出之后其值会发生变化	
  28. 			if(rst.next()){
  29. 				System.out.println(rst.getInt("id"));
  30. 				user = new User();
  31. 				user.setId(rst.getInt("id"));//id
  32. 				user.setUsername(username);//username
  33. 				user.setPwd(rst.getString("pwd"));//pwd
  34. 				user.setName(rst.getString("name"));//name
  35. 				user.setGender(rst.getString("gender"));//gender
  36. 			}			
  37. 		} catch (Exception e) {			
  38. 			e.printStackTrace();//抛出异常
  39. 			System.out.println("查询失败");
  40. //			DBUtil.close(conn);
  41. 		}
  42. 		return user;	
  43. 	}
  44. }

（3.2）UserInfoDaoMrg.java 类

  1. package dao;
  2.  
  3. import java.sql.Connection;
  4. import java.sql.PreparedStatement;
  5. import java.sql.ResultSet;
  6. import java.util.*;
  7.  
  8. import bean.Page;
  9. import util.DBUtil;
  10. import entity.User;
  11. import entity.UserInfo;
  12.  
  13. /**
  14.  * @author
  15.  * 负责访问数据库
  16.  */
  17. public class UserInfoDaoMrg {
  18. 	
  19. 	List<UserInfo> userList = new ArrayList<>();  	
  20. 	UserInfo user = null;   
  21. 	
  22. 	//查找所有员工
  23. 	public List<UserInfo> getUserInfo(){		
  24.         try {
  25.         	//获取连接
  26.         	Connection conn = DBUtil.getConnection();
  27.         	//预处理语句
  28.         	PreparedStatement prep = conn.prepareStatement("select * from userinfo");
  29. 			//遍历数据库
  30. 			ResultSet rst = prep.executeQuery();
  31. 			while(rst.next()){
  32. 				int id = rst.getInt("id");
  33. 				System.out.println("-----" + id);
  34. 				String jobNumber = rst.getString("jobNumber");
  35. 				String name = rst.getString("name");
  36. 				String technicalLevel = rst.getString("level");
  37. 				String department = rst.getString("department");
  38. 				String role = rst.getString("role");
  39. 				user = new UserInfo(id, jobNumber, name, technicalLevel, department, role);				
  40. 				userList.add(user);
  41. 			}			
  42. 			prep.close();
  43. 			conn.close();			
  44. 		} catch (Exception e) {	
  45. 			e.printStackTrace();//抛出异常  
  46. 			System.out.println("查询失败");
  47. 		}
  48.         return userList;	
  49. 	}
  50. 	
  51. 	//添加
  52. 	 public void addUserInfo(String jobNumber, String name, String technicalLevel, String department, String role) {
  53. 		 try {
  54.         	//获取连接
  55.         	Connection conn = DBUtil.getConnection();
  56.         	//预处理语句
  57.         	String sql = "insert into userinfo (jobNumber,name,level,department,role) values (?, ?, ?, ?, ?)";
  58.         	PreparedStatement prep = conn.prepareStatement(sql);
  59.         	prep.setString(1, jobNumber);
  60.         	prep.setString(2, name);
  61.         	prep.setString(3, technicalLevel);
  62.         	prep.setString(4, department);
  63.         	prep.setString(5, role);
  64.         	prep.executeUpdate();
  65.         	prep.close();
  66.         	conn.close();
  67. 		} catch (Exception e) {	
  68. 			e.printStackTrace();//抛出异常 （检查出现的问题)
  69. 			System.out.println("增加失败");
  70. 		}
  71. 	 }
  72. 	 
  73. 	 
  74. 	//加载员工信息，用于更新界面
  75. 	public UserInfo selectUserInfo(int id){
  76. 		UserInfo user = null;
  77. 		Connection con = null;
  78. 		PreparedStatement prep = null;
  79. 		ResultSet rst = null;
  80. 		try {
  81. 			con=DBUtil.getConnection();
  82. 			String sql = "select * from userinfo where id =?";
  83. 			prep = con.prepareStatement(sql);
  84. 			prep.setInt(1, id);
  85. 			rst = prep.executeQuery();
  86. 			while(rst.next()){
  87. 				String jobNumber = rst.getString("jobNumber");
  88. 				String name = rst.getString("name");
  89. 				String technicalLevel = rst.getString("level");
  90. 				String department = rst.getString("department");
  91. 				String role = rst.getString("role");
  92. 				user = new UserInfo(id, jobNumber, name, technicalLevel, department, role);				
  93. 			}
  94. 		} catch (Exception e) {
  95. 			e.printStackTrace();
  96. 		}
  97. 		return user;
  98. 	}	
  99.  	
  100. 	//更新数据库
  101. 	 public void updateUserInfo(Integer id, String jobNumber, String name, String level,String department, String role) {
  102. 		 try {
  103. 	        //获取连接
  104. 			Connection conn = DBUtil.getConnection();
  105. 			String sql = "update userinfo set jobnumber= ?, name = ?, level= ?, department= ?, role= ? where id= ?";
  106. 			//预处理语句
  107. 			PreparedStatement prep = conn.prepareStatement(sql);
  108.         	prep.setString(1, jobNumber);
  109. 			prep.setString(2, name);
  110.         	prep.setString(3, level);
  111.         	prep.setString(4, department);
  112.         	prep.setString(5, role);
  113.         	prep.setInt(6, id);
  114.      
  115.         	//更新数据库
  116.         	prep.executeUpdate();        	
  117.         	prep.close();
  118.         	conn.close();
  119.         	
  120. 		} catch (Exception e) {	
  121. 			e.printStackTrace();//抛出异常
  122. 			System.out.println("更新失败");
  123. 		} 
  124. 	}
  125. 		 
  126. 	//删除数据库
  127. 	 public void deleteUserInfo(Integer id) {
  128. 		 try {
  129. 	        //获取连接
  130. 			Connection conn = DBUtil.getConnection();
  131. 			//预处理语句
  132. 			PreparedStatement prep = conn.prepareStatement("delete from userinfo where id=?");
  133. 			prep.setInt(1, id);
  134. 			prep.executeUpdate();
  135. 			prep.close();
  136.         	conn.close();
  137. 		} catch (Exception e) {	
  138. 			e.printStackTrace();//抛出异常  //by tongshi
  139. 			System.out.println("删除失败");
  140. 		}
  141. 	}
  142. 	 
  143. 	//分页查找
  144. 	public List<UserInfo> findByPageNum(Page page){
  145. 		int begin = (page.getPageNum()-1)*page.getPageSize();//分页算法：确定起始页第一条记录，pageSize = 6  PageNum： 页码数	
  146. 		 System.out.println(page.getPageNum());
  147. 		 System.out.println(page.getPageSize());
  148. 		 System.out.println(page.getPageTotal());
  149. 		try {
  150. 			//获取连接
  151. 	    	Connection conn = DBUtil.getConnection();
  152. 	    	//预处理语句
  153. 	    	String sql = "select * from userinfo limit ?,?";
  154. 	    	PreparedStatement prep = conn.prepareStatement(sql);
  155. 			prep.setInt(1, begin); 
  156. 			prep.setInt(2, page.getPageSize());
  157. 			
  158. 			//遍历数据库
  159. 			ResultSet rst = prep.executeQuery();
  160. 			while(rst.next()){
  161. 				int id = rst.getInt("id");
  162. 				String jobNumber = rst.getString("jobNumber");
  163. 				String name = rst.getString("name");
  164. 				String technicalLevel = rst.getString("level");
  165. 				String department = rst.getString("department");
  166. 				String role = rst.getString("role");
  167. 				user = new UserInfo(id, jobNumber, name, technicalLevel, department, role);	
  168. 				userList.add(user);
  169. 			}
  170. 		} catch (Exception e) {
  171. 			// TODO Auto-generated catch block
  172. 			e.printStackTrace();
  173. 		}
  174. 		return userList;
  175. 	}
  176. 	
  177. 	//获取总记录数量
  178. 	public int getRecordsNum(){
  179. 		Connection con = null;
  180. 		PreparedStatement ps=null;
  181. 		ResultSet rs = null;
  182. 		int recordsNum = 0;
  183. 		try {
  184. 			con = DBUtil.getConnection();
  185. 			String sql = "select count(*) count from userinfo"; //获得表中记录数
  186. 			ps = con.prepareStatement(sql);
  187. 			rs = ps.executeQuery();
  188. 			while(rs.next()){
  189. 				recordsNum = rs.getInt("count");
  190. 			}
  191. 		} catch (Exception e) {
  192. 			
  193. 			e.printStackTrace();
  194. 		}
  195. 		return recordsNum;
  196. 	}
  197. }

（4）bean包下的Page.java

  1. package bean;
  2.  
  3. import dao.UserInfoDaoMrg;
  4.  
  5. public class Page {	   
  6. 	private int pageNum;//页码数		
  7. 	private int pageSize = 6;//每页显示数量,默认为3
  8. 	private int pageTotal;//总页数
  9. 	private int recordsNum;//总记录数
  10.  
  11. 	UserInfoDaoMrg userDaoInfo = new UserInfoDaoMrg();
  12. 		
  13. 	public int getPageNum() {
  14. 		return pageNum;
  15. 	}
  16.  
  17. 	public void setPageNum(int pageNum) {
  18. 		this.pageNum = pageNum;
  19. 	}
  20.  
  21. 	public int getPageSize() {
  22. 		return pageSize;
  23. 	}
  24.  
  25. 	public void setPageSize(int pageSize) {
  26. 		this.pageSize = pageSize;
  27. 	}
  28.  
  29. 	public int getRecordsNum() {
  30. 		return recordsNum;
  31. 	}
  32.  
  33. 	public void setRecordsNum(int recordsNum) {
  34. 		this.recordsNum = recordsNum;
  35. 	}
  36.  
  37. 	public void setPageTotal(int pageTotal) {
  38. 		this.pageTotal = pageTotal;
  39. 	}
  40.  
  41. 	public int getPageTotal(){
  42. 		recordsNum = userDaoInfo.getRecordsNum();
  43. 		int mod = recordsNum % pageSize;
  44. 		if(mod == 0){
  45. 			pageTotal=recordsNum/pageSize;
  46. 		}else{
  47. 			pageTotal=recordsNum/pageSize+1;
  48. 		}
  49. 		return pageTotal;
  50. 	}	     
  51. }

（5）servlet包下的ActionServlet.java、AddUserServlet.java、DeleteUserServlet.java、SelectUserServlet.java、UpdateUserServlet.java、UserListServlet.java

（5.1）ActionServlet.java类：

  1. package servlet;
  2.  
  3. import java.io.IOException;
  4. import java.io.PrintWriter;
  5.  
  6. import javax.servlet.ServletException;
  7. import javax.servlet.http.HttpServlet;
  8. import javax.servlet.http.HttpServletRequest;
  9. import javax.servlet.http.HttpServletResponse;
  10. import javax.servlet.http.HttpSession;
  11.  
  12. import dao.UserDao;
  13. import entity.User;
  14.  
  15. public class ActionServlet extends HttpServlet {
  16. 	private static final long serialVersionUID = 3624434001049002202L;
  17.  
  18. 	protected void doGet(HttpServletRequest request, HttpServletResponse response)
  19.             throws ServletException, IOException {
  20. 		
  21.         request.setCharacterEncoding("utf-8");
  22.         response.setContentType("text/html;charset=utf-8");
  23.         
  24.         PrintWriter out = response.getWriter();//输出在控制台
  25.         
  26.         //分享请求资源路径
  27.         String uri = request.getRequestURI();
  28.         String action = uri.substring(uri.lastIndexOf("/"));//截取子字符串
  29.         System.out.println("URI为：" + action);
  30.         
  31.         if("/logon".equals(action)){
  32.             //读取用户名和密码
  33.             String userName = request.getParameter("username");//username为jsp中字段，即是：接收到前台传来的数据
  34.             String password = request.getParameter("password");//password为jsp中字段
  35.             
  36.             //根据用户名和密码查询数据库中是否有这个用户
  37.             UserDao ud = new UserDao();
  38.             try {
  39.                 User user = ud.findByUsername(userName);
  40.                 if(user != null && password.equals(user.getPwd())){                	
  41.                 	//创建Session对象保存User对象
  42.                     HttpSession session = request.getSession();
  43.                     session.setAttribute("user", userName);
  44.  
  45.                     //登陆成功跳转指定页面
  46.                     request.getRequestDispatcher("home").forward(request,response);
  47.                 }else{
  48.                     out.print("用户名或者密码为空");
  49.                     request.getRequestDispatcher("/webdexample").forward(request,response);
  50.                 }
  51.             } catch (Exception e) {
  52.                 e.printStackTrace();
  53.                 throw new ServletException(e);
  54.             }
  55.         }
  56.         out.close();
  57.     }
  58. 	
  59. 	 protected void doPost(HttpServletRequest request, HttpServletResponse response)
  60. 			  throws ServletException, IOException {
  61. 	        doGet(request, response);
  62. 	 }
  63. }

 （5.2）AddUserServlet.java类

  1. package servlet;
  2.  
  3. import java.io.IOException;
  4. import java.util.List;
  5.  
  6. import javax.servlet.ServletException;
  7. import javax.servlet.http.HttpServlet;
  8. import javax.servlet.http.HttpServletRequest;
  9. import javax.servlet.http.HttpServletResponse;
  10.  
  11. import com.sun.xml.internal.bind.v2.model.core.ID;
  12.  
  13. import dao.UserInfoDaoMrg;
  14. import entity.UserInfo;
  15.  
  16. /**
  17.  * @author
  18.  * 对应“增加”部分的按钮
  19.  */
  20. public class AddUserServlet extends HttpServlet{
  21.  
  22. 	private static final long serialVersionUID = 1L;
  23.  
  24. 	@Override
  25. 	protected void doGet(HttpServletRequest request, HttpServletResponse response)
  26. 			throws ServletException, IOException {		
  27. 		 request.setCharacterEncoding("utf-8");
  28. 	     response.setContentType("text/html;charset=utf-8");	
  29. 	     
  30. 	     //获得form表单属性
  31. 	     String jobNumber = request.getParameter("jobNumber");
  32. 	     String name = request.getParameter("name");
  33. 	     String level = request.getParameter("level");
  34. 	     String department = request.getParameter("department");
  35. 	     String role = request.getParameter("role");
  36. 	     
  37. 	     //servlet
  38. 	     request.setAttribute("jobNumber", jobNumber);
  39. 	     request.setAttribute("name", name);
  40. 	     request.setAttribute("technicalLevel", level);
  41. 	     request.setAttribute("department", department);
  42. 	     request.setAttribute("role", role);
  43. 	     
  44. 	     //更新数据库 
  45. 	     UserInfoDaoMrg ud = new UserInfoDaoMrg();
  46. 		 ud.addUserInfo(jobNumber, name, level, department, role);   
  47. 	     request.getRequestDispatcher("userlistright").forward(request,response);// 对应servlet
  48. 	}
  49. 	
  50. 	@Override
  51. 	protected void doPost(HttpServletRequest request, HttpServletResponse response)
  52. 			throws ServletException, IOException {
  53. 		doGet(request, response);
  54. 	}
  55. }

（5.3）DeleteUserServlet.java类

  1. package servlet;
  2.  
  3. import java.io.IOException;
  4. import java.util.List;
  5.  
  6. import javax.servlet.ServletException;
  7. import javax.servlet.http.HttpServlet;
  8. import javax.servlet.http.HttpServletRequest;
  9. import javax.servlet.http.HttpServletResponse;
  10.  
  11. import util.DBUtil;
  12. import dao.UserInfoDaoMrg;
  13. import entity.UserInfo;
  14.  
  15. public class DeleteUserServlet extends HttpServlet{
  16.  
  17. 	private static final long serialVersionUID = 1L;
  18.  
  19. 	@Override
  20. 	protected void doGet(HttpServletRequest request, HttpServletResponse response)
  21. 			throws ServletException, IOException {		
  22. 		 request.setCharacterEncoding("utf-8");
  23. 	     response.setContentType("text/html;charset=utf-8");//response解决乱码问题	     
  24. 	     System.out.println("进入deleteservlet");
  25. 	     int id = Integer.parseInt(request.getParameter("id"));//从table（前端）中得到的id
  26. 	     UserInfoDaoMrg ud = new UserInfoDaoMrg();
  27. 	     ud.deleteUserInfo(id);	 
  28. 		 request.getRequestDispatcher("userlistright").forward(request,response);
  29. 	}
  30. 	    	
  31. 	@Override
  32. 	protected void doPost(HttpServletRequest request, HttpServletResponse response)
  33. 			throws ServletException, IOException {
  34. 		doGet(request, response);
  35. 	}
  36. }

（5.4）SelectUserServlet.java类

  1. package servlet;
  2.  
  3. import java.io.IOException;
  4. import java.util.List;
  5.  
  6. import javax.servlet.ServletException;
  7. import javax.servlet.http.HttpServlet;
  8. import javax.servlet.http.HttpServletRequest;
  9. import javax.servlet.http.HttpServletResponse;
  10.  
  11. import dao.UserInfoDaoMrg;
  12. import entity.UserInfo;
  13.  
  14. public class SelectUserServlet extends HttpServlet{
  15. 	private static final long serialVersionUID = 1L;
  16.  
  17. 	public void doGet(HttpServletRequest request, HttpServletResponse response)
  18.             throws ServletException, IOException {
  19. 		
  20. 		 request.setCharacterEncoding("utf-8");
  21. 	     response.setContentType("text/html;charset=utf-8");
  22. 	     
  23. 	     int id = Integer.parseInt(request.getParameter("id"));//从table（前端）中得到的id
  24. 	     System.out.println("-----------" + id);
  25. 	     
  26. 	     UserInfoDaoMrg ud = new UserInfoDaoMrg();
  27. 		 UserInfo user = ud.selectUserInfo(id);
  28. 		 request.setAttribute("user", user);
  29. 	     request.getRequestDispatcher("updateuser.jsp").forward(request,response);	
  30. 	}
  31. 	
  32. 	 protected void doPost(HttpServletRequest request, HttpServletResponse response)
  33. 			  throws ServletException, IOException {
  34. 	        doGet(request, response);
  35. 	 }
  36. }

（5.5）UpdateUserServlet.java类

  1. package servlet;
  2.  
  3. import java.io.IOException;
  4. import java.util.List;
  5.  
  6. import javax.servlet.ServletException;
  7. import javax.servlet.http.HttpServlet;
  8. import javax.servlet.http.HttpServletRequest;
  9. import javax.servlet.http.HttpServletResponse;
  10.  
  11. import com.sun.xml.internal.bind.v2.model.core.ID;
  12.  
  13. import dao.UserInfoDaoMrg;
  14. import entity.UserInfo;
  15.  
  16. public class UpdateUserServlet extends HttpServlet{
  17. 	
  18. 	private static final long serialVersionUID = 1L;
  19.  
  20. 	@Override
  21. 	protected void doGet(HttpServletRequest request, HttpServletResponse response)
  22. 			throws ServletException, IOException {		
  23. 		 request.setCharacterEncoding("utf-8");
  24. 	     response.setContentType("text/html;charset=utf-8");
  25. 	     
  26. 	     UserInfoDaoMrg ud = new UserInfoDaoMrg();
  27. 	     Integer id = Integer.parseInt(request.getParameter("id"));
  28. 	     String name = request.getParameter("name");
  29. 	     String jobNumber = request.getParameter("jobNumber");
  30. 	     String technicalLevel = request.getParameter("level");
  31. 	     String department = request.getParameter("department");
  32. 	     String role = request.getParameter("role"); 
  33. 	     
  34. 	     //更新数据库
  35. 	     ud.updateUserInfo(id, jobNumber, name, technicalLevel, department, role);   
  36. 	     request.getRequestDispatcher("userlistright").forward(request,response);
  37. 	}
  38. 	
  39. 	@Override
  40. 	protected void doPost(HttpServletRequest request, HttpServletResponse response)
  41. 			throws ServletException, IOException {
  42. 		doGet(request, response);
  43. 	}
  44. }

（5.6） UserListServlet.java类

  1. package servlet;
  2.  
  3. import java.io.IOException;
  4. import java.util.List;
  5.  
  6. import javax.servlet.ServletException;
  7. import javax.servlet.http.HttpServlet;
  8. import javax.servlet.http.HttpServletRequest;
  9. import javax.servlet.http.HttpServletResponse;
  10.  
  11. import bean.Page;
  12. import dao.UserInfoDaoMrg;
  13. import entity.UserInfo;
  14.  
  15. public class UserListServlet extends HttpServlet{
  16.  
  17. 	private static final long serialVersionUID = 1L;
  18.  
  19. 	public void doGet(HttpServletRequest request, HttpServletResponse response)
  20.             throws ServletException, IOException {
  21. 		
  22. 		 request.setCharacterEncoding("utf-8");
  23. 	     response.setContentType("text/html;charset=utf-8");
  24. 	     	     
  25. 	     //分页显示对应的代码
  26. 	     Page page = new Page();
  27. 	     String pageNum = request.getParameter("pageNum");
  28. 	     if(pageNum == null){
  29. 			page.setPageNum(1);
  30. 		 }else{
  31. 			page.setPageNum(Integer.parseInt(pageNum));
  32. 		 }	
  33. 	     
  34. 	     UserInfoDaoMrg ud = new UserInfoDaoMrg();
  35. 	     
  36. 		 List<UserInfo> listUser = ud.findByPageNum(page);
  37. 		 request.setAttribute("list", listUser);//对应列表
  38. 		 request.setAttribute("page", page);//对应分页
  39. 	     request.getRequestDispatcher("userlistright.jsp").forward(request,response); //"userlistright.jsp" by workmate	
  40. 	     
  41. //	     //不分页显示对应的代码
  42. //	     UserInfoDaoMrg ud = new UserInfoDaoMrg();
  43. //		 List<UserInfo> userList = ud.getUserInfo();		 
  44. //		 request.setAttribute("list", userList);
  45. //	     request.getRequestDispatcher("userlistright.jsp").forward(request,response); //"userlistright.jsp" by workmate		
  46. 	}
  47. 	
  48. 	 protected void doPost(HttpServletRequest request, HttpServletResponse response)
  49. 			  throws ServletException, IOException {
  50. 	        doGet(request, response);
  51. 	 }
  52. }

 

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/盐析白兔/article/detail/575411
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号