servelet+jsp+jdbc实现简单增删改查_综合

servelet+jsp+jdbc实现简单增删改查

1、sql建表语句
2、目录结构

3、实体类

4、servlet类
5、utils工具类
6、jsp页面

1、sql建表语句


SET FOREIGN_KEY_CHECKS=0;DROP TABLE IF EXISTS `employee`;
CREATE TABLE `employee` (`Operater_id` int(20) DEFAULT NULL COMMENT ,`Name` varchar(400) DEFAULT NULL,`Password` varchar(400) DEFAULT NULL,`Status` tinyint(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

2、目录结构

在这里插入图片描述
#web.xml配置

<?xml version="1.0" encoding="UTF-8"?>
<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" version="3.1"><display-name>mianshi</display-name><servlet><servlet-name>FindAll</servlet-name><servlet-class>com.demo.servlet.FindAll</servlet-class></servlet><servlet><servlet-name>AddServlet</servlet-name><servlet-class>com.demo.servlet.AddServlet</servlet-class></servlet><servlet><servlet-name>DeleteServlet</servlet-name><servlet-class>com.demo.servlet.DeleteServlet</servlet-class></servlet><servlet-mapping><servlet-name>FindAll</servlet-name><url-pattern>/FindAll</url-pattern></servlet-mapping><welcome-file-list><welcome-file>home.jsp</welcome-file></welcome-file-list>
</web-app>

3、实体类

package com.demo.pojo;public class Employee {private int Operater_id;private String Name;private String Password;private int Status;public Employee() {super();}public Employee(int operater_id, String name, String password, int status) {super();Operater_id = operater_id;Name = name;Password = password;Status = status;}public int getOperater_id() {return Operater_id;}public void setOperater_id(int operater_id) {Operater_id = operater_id;}public String getName() {return Name;}public void setName(String name) {Name = name;}public String getPassword() {return Password;}public void setPassword(String password) {Password = password;}public int getStatus() {return Status;}public void setStatus(int status) {Status = status;}@Overridepublic String toString() {return "Employee [Operater_id=" + Operater_id + ", Name=" + Name + ", Password=" + Password + ", Status="+ Status + "]";}
}

4、servlet类

1、AddServlet.java

package com.demo.servlet;import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;import com.demo.pojo.Employee;
import com.demo.utils.IsPureDigital;
import com.demo.utils.JdbcUtil;/*** Servlet implementation class AddServlet*/
@WebServlet("/addServlet")
public class AddServlet extends HttpServlet {private static final long serialVersionUID = 1L;/*** @see HttpServlet#HttpServlet()*/public AddServlet() {super();// TODO Auto-generated constructor stub}/*** @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse* response)*/protected void doGet(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {// TODO Auto-generated method stubConnection conn;PreparedStatement pst1;PreparedStatement pst2;response.setCharacterEncoding("utf-8"); PrintWriter out = response.getWriter();try {conn = JdbcUtil.getConn();System.out.println("数据库连接成功");String sql = "insert into employee(Operater_id,Name,Password,Status) values(?,?,?,?)";pst1 = (PreparedStatement) conn.prepareStatement(sql);String id = request.getParameter("Operater_id");String name = request.getParameter("Name");String password = request.getParameter("Password");String status = request.getParameter("Status");String sql1="select * from employee where Name= ?";pst2 = (PreparedStatement) conn.prepareStatement(sql1);pst2.setString(1, name);ResultSet res=pst2.executeQuery();int count=0;while(res.next()) {count++;}if(!IsPureDigital.isPureDigital(String.valueOf(id))) {out.print("{\"msg\":\"idWrong\"}");}else if(count>0) {out.print("{\"msg\":\"dup\"}");System.out.println("查询出 "+count+"条数据");}else {pst1.setInt(1, Integer.valueOf(id));pst1.setString(2, name);pst1.setString(3, password);pst1.setInt(4, Integer.valueOf(status));int rows = pst1.executeUpdate();System.out.println("插入： " + rows+ " 条 ");out.print("{\"msg\":\"success\"}");pst1.close();}pst2.close();conn.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}/*** @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse* response)*/protected void doPost(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {// TODO Auto-generated method stubdoGet(request, response);}}

2、DeleteServlet.java

package com.demo.servlet;import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;import com.demo.utils.JdbcUtil;/*** Servlet implementation class DeleteServlet*/
@WebServlet("/deleteServlet")
public class DeleteServlet extends HttpServlet {private static final long serialVersionUID = 1L;/*** @see HttpServlet#HttpServlet()*/public DeleteServlet() {super();// TODO Auto-generated constructor stub}/*** @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)*/protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {// TODO Auto-generated method stubConnection conn;response.setCharacterEncoding("utf-8"); PrintWriter out = response.getWriter();String id=request.getParameter("id");System.out.println(id);String sql="delete from Employee where Operater_id="+id;try {conn=JdbcUtil.getConn();PreparedStatement ps=conn.prepareStatement(sql);int i=ps.executeUpdate();if(i>0) {out.print("{\"msg\":\"success\"}");}} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}/*** @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)*/protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {// TODO Auto-generated method stubdoGet(request, response);}}

3、FindAll.java

package com.demo.servlet;import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;import com.demo.pojo.Employee;
import com.demo.utils.JdbcUtil;/*** Servlet implementation class findAll*/
@WebServlet("/findAll")
public class FindAll extends HttpServlet {private static final long serialVersionUID = 1L;/*** @see HttpServlet#HttpServlet()*/public FindAll() {super();// TODO Auto-generated constructor stub}/*** @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)*/protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {// TODO Auto-generated method stubrequest.setCharacterEncoding("utf-8");   //防止中文乱码try {Connection conn=JdbcUtil.getConn();System.out.println("数据库连接成功");String sql1="select * from employee";PreparedStatement st=conn.prepareStatement(sql1);ResultSet res=st.executeQuery();List<Employee> emList=new ArrayList<>();while(res.next()) {Employee em=new Employee();em.setOperater_id(res.getInt("Operater_id"));em.setName(res.getString("Name"));em.setPassword(res.getString("Password"));em.setStatus(res.getByte("Status"));emList.add(em);}// System.out.println(emList);request.setAttribute("emList", emList);res.close();st.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}request.getRequestDispatcher("home.jsp").forward(request, response);}protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {// TODO Auto-generated method stubdoGet(request, response);}}

4、SearchServlet.java

package com.demo.servlet;import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;import com.demo.pojo.Employee;
import com.demo.utils.JdbcUtil;
import com.mysql.cj.xdevapi.JsonArray;import java.io.PrintWriter;
/*** Servlet implementation class SearchServlet*/
@WebServlet("/searchServlet")
public class SearchServlet extends HttpServlet {private static final long serialVersionUID = 1L;/*** @see HttpServlet#HttpServlet()*/public SearchServlet() {super();// TODO Auto-generated constructor stub}/*** @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)*/protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {// TODO Auto-generated method stubConnection conn;response.setCharacterEncoding("utf-8"); PrintWriter out = response.getWriter();try {conn = JdbcUtil.getConn();PreparedStatement pst;String name=request.getParameter("name");String status=request.getParameter("status");String sql="select * from Employee where Name like \"%"+name+"%\" and Status = "+status;PreparedStatement ps=conn.prepareStatement(sql);ResultSet res=ps.executeQuery();List<Employee> list=new ArrayList<>();while(res.next()) {Employee em=new Employee();em.setOperater_id(res.getInt("Operater_id"));em.setName(res.getString("Name"));em.setPassword(res.getString("Password"));em.setStatus(res.getInt("Status"));list.add(em);}List<String> json=new ArrayList<>();String str="";for(Employee em:list) {System.out.println(em);str="{\"Operater_id\":\""+em.getOperater_id()+"\",\"Name\":\""+em.getName()+"\",\"Password\":\""+em.getPassword()+"\",\"Status\":\""+em.getStatus()+"\"}";json.add(str);}System.out.println(json);out.print(json);} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}/*** @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)*/protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {// TODO Auto-generated method stubdoGet(request, response);}}

5、utils工具类

1、正则表达式匹配类: IsPureDigital.java

package com.demo.utils;import java.util.regex.Matcher;
import java.util.regex.Pattern;public class IsPureDigital {public static boolean isPureDigital(String str) {if (str == null || "".equals(str)){return false;}Pattern p;Matcher m;p = Pattern.compile("[0-9]*{10}");m = p.matcher(str);if (m.matches()){return true;}else{return false;}}
}

2、数据库连接类：JdbcUtil.java

package com.demo.utils;import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;import com.mysql.cj.MysqlConnection;
public class JdbcUtil {public static Connection getConn() throws SQLException {Connection conn=null;String url="jdbc:mysql://localhost:3306/mianshiDemo?serverTimezone=CST&zeroDateTimeBehavior=CONVERT_TO_NULL";String username="root";String password="123456";try {Class.forName("com.mysql.cj.jdbc.Driver");conn=(Connection)DriverManager.getConnection(url,username,password);} catch (ClassNotFoundException e) {// TODO Auto-generated catch blocke.printStackTrace();}return conn;}
}

6、jsp页面

1、home.jsp

<%@ page language="java" contentType="text/html; charset=utf-8" pageEncoding="utf-8"%>
<%@page import="java.util.List"%>
<%@page import="com.demo.pojo.Employee"%>
<!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=ISO-8859-1">
<title>Insert title here</title>
</head>
<style>
table,tbody,thead,tr,td{
border:1px solid black;
}
</style>
<script src="http://code.jquery.com/jquery-latest.js"></script>
<script>function addWin(){window.open("add.jsp", "_blank",'height=200,width=600,status=yes,top=200,left=400,toolbar=no,menubar=no,location=no')}$(document).ready(function(){ //alert("页面加载完成！");$("#delete").click(function(){var id=$(this).parent().find("#id").val();var name=$(this).parent().find("#name").val();alert("确认删除用户 "+name);var id=$(this).parent().find("#id").val();console.log(id);$.ajax({url:'deleteServlet',data:{id:id},                 //将表单数据序列化，格式为name=valuetype:'POST',dataType:'json',success:function(data){//successconsole.log(data);window.location.reload();}});});}); function query(){$.ajax({url:'searchServlet',data:$('#form1').serialize(),                 //将表单数据序列化，格式为name=valuetype:'POST',dataType:'json',success:function(data){//successconsole.log(data);var html="";for(var i=0;i<data.length;i++){html+="<tr><td>"+data[i].Operater_id+"</td><td>"+data[i].Name+"</td><td>"+data[i].Password+"</td><td>"+data[i].Status+"</td><td><a href='deleteServlet?"+data[i].Operater+"'>删除</a><a href='updateServlet?"+data[i].Operater+"'>修改</a></td></tr>";}$("tbody").html(html);}});}
</script>
<body><div><a href="#" onclick="addWin()">创建用户</a><table><thead><tr><form id="form1" action="" method="post"><td>按姓名查找</td><td colspan="2"><input name="name" type="text" /></td><td><select value="1" name="status" id="status">	<option value="1">有效</option>		<option value="0">无效</option>				</select></td></form><td><input onclick="query()" type="button" value="查询" id="query"></td></tr><tr><td>id</td><td>name</td><td>password</td><td>status</td><td>操作</td></tr></thead><tbody><%List<Employee> list=(List<Employee>)request.getAttribute("emList");for(Employee em:list){%><tr><td><%=em.getOperater_id() %></td><td><%=em.getName() %></td><td><%=em.getPassword() %></td><td><%=em.getStatus() %></td><td><input type="hidden" id="id" value="<%=em.getOperater_id() %>" /><input type="hidden" id="name" value="<%=em.getName() %>" /><a href="javascript:;" id="delete">删除</a><a href="javascript:;" id="update">修改</a></td></tr><%}%></tbody></table></div>
</body>
</html>

2、add.jsp

<%@ page language="java" contentType="text/html; charset=utf-8" pageEncoding="utf-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="ISO-8859-1">
<title>添加员工</title>
</head>
<script src="http://code.jquery.com/jquery-latest.js"></script>
<script type="text/javascript">$(document).ready(function(){ //alert("页面加载完成！");
}); function sub(){if($("input[name='Name']").val()==""||$("input[name='Password']").val()==""){alert("用户名密码必填");}else{$.ajax({url:'addServlet',data:$('#form1').serialize(),                 //将表单数据序列化，格式为name=valuetype:'POST',dataType:'json',success:function(data){//successconsole.log(data);if(data.msg=="idWrong"){alert("ID必须为正整数，且不能过长");}else if(data.msg=="dup"){//alert("该用户已存在");$("#warnning").html("用户名已存在");$("#warnning").css("color","red");}else if(data.msg=="success"){alert("恭喜您，用户添加成功！");window.parent.opener.location.reload();window.close();}},});}}
</script>
<body>
<form id="form1" action="" method="post"><table><tr><td rospan="2"><span id="warnning"></span></td></tr><tr><td>账号：</td><td><input name="Operater_id" type="text" /></td></tr><tr><td>姓名：</td><td><input name="Name" type="text" /></td></tr><tr><td>密码：</td><td><input name="Password" type="text" /></td></tr><tr><td>状态：</td><td><select name="Status" id="s1"><option value="1">有效</option>	<option value="0">无效</option>				</select></td></tr></table>
</form><input id="btnsub" onclick="sub()" type="button" value="提交" />
</body>
</html>