JSP增删改查及分页

我的界面大概是这个样子，简单版本，没美化，数据库用的sqlserver，建立三个表学生表、教员表、班级表

1.增加

1.1增加是个a标签，下面是增加界面

1.2确定按钮提交表单，增加成功存到数据库，否则回到主界面

1.3重置按钮可清空表格所填信息按钮属性type="reset" 即可做到

2.删除

2.1根据学生id删除

2.2删除时弹出一个提示框，可取消删除，用到js的comfirm提示框

2.3取消和删除后会回到主界面，并且删除后提示删除成功

3.修改

3.1修改界面与增加界面一致

3.2点击修改时需要把id传到servlet中查询对应的学生

3.3然后将查询到的学生传到界面显示信息

3.4复选框的回显：将爱好根据逗号切割（split（））成为一个数组，遍历该数组，对每个复选框进行判断，遍历的值与复选框的值一致则默认选中，具体操作看下面代码

servlet：

package com.Servlet;

import java.io.IOException;

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.dao.StudentDao;
import com.entity.student;
@WebServlet("/updshow.do")
public class UpdShowServlet extends HttpServlet{

	protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
       doPost(req, resp);
	}
	protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {

		req.setCharacterEncoding("utf-8");
		resp.setContentType("text/html;charset=utf-8");
		int sid = Integer.parseInt(req.getParameter("sid"));
		student s = new StudentDao().byids(sid);
		String sah = s.getSah();
        //切割
		String[] split = sah.split(",");
		
		req.setAttribute("s", s);
		req.setAttribute("split", split);
		req.getRequestDispatcher("upd.jsp").forward(req, resp);
	}
	
}

jsp：

4.分页模糊查询

sqlserver的分页语句：

select * from (select *,row_number() over(order by sid) as rowid from student
)as b where rowid between 1 and 3

4.1模糊查询的关键字有三个：教员班级爱好

4.2教员和班级为下拉框默认选中第一个，爱好可勾选可不勾选不勾选时默认为空字符串，勾选时通过逗号拼接起来进行模糊查询

4.3查询后默认分页，我这里是两条一页，点下一页时将下一页的页数和模糊查询的关键字传到servlet中

4.4输入框分页：根据输入的页码跳转界面，我这里是用表单将输入框的GO按钮装起来，点击go提交表单，关键字通过隐藏的输入框带过去进行查询，具体操作看下面代码

4.5首页和尾页的跳转，就是将1和最大的页码数和关键字信息传到servlet中


<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
    <%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<style type="text/css">
#t1{
margin: auto;
text-align: center;
width: 600px;
height: 300px
}
table,tr,td{
border-collapse: collapse;
}
#inp{
text-align: center;
}
#p{
text-align: center;
}
#but{
background-color: orange;
}
#go{
border: none;
}
</style>
</head>
<body>
<form action="key.do">
<div id="inp"><button id="but">查询</button>&nbsp;<a href="add.jsp">新增</a></div>
<table border="" id="t1">
<tr>
<td colspan="6">
教员:
<select name="teacher">
<c:forEach var="i" items="${ allT }">
<option value="${ i.tname }" ${ i.tname==teacher?"selected":"" }>${ i.tname }</option>
</c:forEach>
</select>
班级:
<select name="sclass">
<c:forEach var="i" items="${ allC }">
<option value="${ i.sclazz }" ${ i.sclazz==sclass?"selected":"" }>${ i.sclazz }</option>
</c:forEach>
</select>
爱好:
<input type="checkbox" name="ah" value="篮球" <c:forEach var="i" items="${ split }">
<c:if test="${ i=='篮球' }"> checked
</c:if>
</c:forEach>
>篮球
<input type="checkbox" name="ah" value="唱歌" <c:forEach var="i" items="${ split }">
<c:if test="${ i=='唱歌' }"> checked
</c:if>
</c:forEach>
>唱歌
<input type="checkbox" name="ah" value="足球" <c:forEach var="i" items="${ split }">
<c:if test="${ i=='足球' }"> checked
</c:if>
</c:forEach>
>足球
<input type="checkbox" name="ah" value="跳舞" <c:forEach var="i" items="${ split }">
<c:if test="${ i=='跳舞' }"> checked
</c:if>
</c:forEach>
>跳舞
</td>
</tr>
</form>
<tr>
<td>学生ID</td>
<td>学生姓名</td>
<td>学生教员</td>
<td>班级</td>
<td>学生爱好</td>
<td>操作</td>
</tr>
<c:forEach var="i" items="${ list }">
<tr>
<td>${ i.sid }</td>
<td>${ i.sname }</td>
<td>${ i.tname }</td>
<td>${ i.sclass }</td>
<td>${ i.sah }</td>
<td><a href="del.do?sid=${ i.sid }">删除</a><a href="updshow.do?sid=${ i.sid }">修改</a></td>
</tr>
</c:forEach>
</table>
<div id="p">第${ pages }页，共${ max }页，总记录${ m }条，
<a href="key.do?aid=1&teacher=${ teacher }&sclass=${ sclass }&ah=${ ah }">首页</a>，
<a href="key.do?aid=${ pages-1<1?1:pages-1 }&teacher=${ teacher }&sclass=${ sclass }&ah=${ ah }">上一页</a>，
<form action="key.do"><input style="width: 20px" name="aid">
<input style="width: 20px" name="teacher" value="${ teacher }" type="hidden">
<input style="width: 20px" name="sclass" value="${ sclass }" type="hidden">
<input style="width: 20px" name="ah" value="${ ah }" type="hidden">
<button id="go">Go</button></form>,
<a href="key.do?aid=${ pages+1>max?max:pages+1 }&teacher=${ teacher }&sclass=${ sclass }&ah=${ ah }">下一页</a>，
<a href="key.do?aid=${ max }&teacher=${ teacher }&sclass=${ sclass }&ah=${ ah }">尾页</a></div>
</body>
</html>

注意：模糊查询的分页和进入主界面的没有模糊查询的分页参数不能相同，不然会分页错误哦！我用的是pid和aid

5.dao包所有方法

每个方法都有注释

package com.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.entity.clazz;
import com.entity.student;
import com.entity.teacher;
import com.util.DBHelper;

/**
 * 方法类
 * @author zjjt
 *
 */
public class StudentDao {

	/**
	 * 查询全部学生
	 * @return
	 */
	public List<student> getAll(){
		List<student> list = new ArrayList<student>();
		Connection con=DBHelper.getCon();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = con.prepareStatement("select * from student");
			rs = ps.executeQuery();
			while (rs.next()) {
				student s=new student();
				s.setSid(rs.getInt(1));
				s.setSname(rs.getString(2));
				s.setSah(rs.getString(3));
				s.setSclass(rs.getString(4));
				s.setTname(rs.getString(5));
				list.add(s);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBHelper.myClose(con, ps, rs);
		}
		return list;
	}
	
	

	/**
	 * 查询所有教员
	 */
	public List<teacher> getAllT(){
		List<teacher> list = new ArrayList<teacher>();
		Connection con=DBHelper.getCon();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = con.prepareStatement("select * from teacher");
			rs = ps.executeQuery();
			while (rs.next()) {
				teacher t=new teacher();
				t.setTid(rs.getInt(1));
				t.setTname(rs.getString(2));
				list.add(t);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBHelper.myClose(con, ps, rs);
		}
		return list;
	}
	
	
	/**
	 * 查询所有班级
	 */
	public List<clazz> getAllC(){
		List<clazz> list = new ArrayList<clazz>();
		Connection con=DBHelper.getCon();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = con.prepareStatement("select * from clazz");
			rs = ps.executeQuery();
			while (rs.next()) {
				clazz c=new clazz();
				c.setCid(rs.getInt(1));
				c.setSclazz(rs.getString(2));
				list.add(c);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBHelper.myClose(con, ps, rs);
		}
		return list;
	}
	
	/**
	 * 主界面的分页
	 * @param pages
	 * @param pagesize
	 * @return
	 */
	public List<student> getpage(int pages,int pagesize){
		List<student> list = new ArrayList<student>();
		Connection con=DBHelper.getCon();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = con.prepareStatement("select * from (select *,row_number() over(order by sid) as rowid from student)as b where rowid between ? and ?");
			ps.setInt(1, pages);
			ps.setInt(2, pagesize);
			rs = ps.executeQuery();
			while (rs.next()) {
				student s=new student();
				s.setSid(rs.getInt(1));
				s.setSname(rs.getString(2));
				s.setSah(rs.getString(3));
				s.setSclass(rs.getString(4));
				s.setTname(rs.getString(5));
				list.add(s);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBHelper.myClose(con, ps, rs);
		}
		return list;
	}
	
	/**
	 * 模糊查询的分页
	 * @param pages
	 * @param pagesize
	 * @param key1
	 * @param key2
	 * @param key3
	 * @return
	 */
	//爱好  班级  教员
	public List<student> getpage(int pages,int pagesize,String key1,String key2,String key3){
		List<student> list = new ArrayList<student>();
		Connection con=DBHelper.getCon();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = con.prepareStatement("\r\n" + 
					"select * from (select *,row_number() over(order by sid) as rowid from student\r\n" + 
					"where sah like ? \r\n" + 
					"and sclass like ? and tname like ?\r\n" + 
					")as b where rowid between ? and ?");
			ps.setInt(4, pages);
			ps.setInt(5, pagesize);
			ps.setString(1, "%"+key1+"%");
			ps.setString(2, "%"+key2+"%");
			ps.setString(3, "%"+key3+"%");
			rs = ps.executeQuery();
			while (rs.next()) {
				student s=new student();
				s.setSid(rs.getInt(1));
				s.setSname(rs.getString(2));
				s.setSah(rs.getString(3));
				s.setSclass(rs.getString(4));
				s.setTname(rs.getString(5));
				list.add(s);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBHelper.myClose(con, ps, rs);
		}
		return list;
	}
	
	/**
	 * 删除学生
	 * @param sid
	 * @return
	 */
	public int del(int sid) {
		Connection con=DBHelper.getCon();
		PreparedStatement ps=null;
		try {
			ps=con.prepareStatement("delete from student where sid=?");
			ps.setInt(1, sid);
			return ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			DBHelper.myClose(con, ps, null);
		}
		return 0;
	}
	/**
	 * 增加学生
	 * @param s
	 * @return
	 */
	public int add(student s) {
		Connection con=DBHelper.getCon();
		PreparedStatement ps=null;
		try {
			ps=con.prepareStatement("insert into student values(?,?,?,?)");
			ps.setString(1, s.getSname());
			ps.setString(2, s.getSah());
			ps.setString(3, s.getSclass());
			ps.setString(4, s.getTname());
			return ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			DBHelper.myClose(con, ps, null);
		}
		return 0;
	}
	/**
	 * 根据ID查询学生
	 * @param sid
	 * @return
	 */
	public student byids(int sid) {
		Connection con = DBHelper.getCon();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = con.prepareStatement("select * from student where sid=?");
			ps.setInt(1, sid);
			rs = ps.executeQuery();
			while (rs.next()) {
				student s=new student();
				s.setSid(rs.getInt(1));
				s.setSname(rs.getString(2));
				s.setSah(rs.getString(3));
				s.setSclass(rs.getString(4));
				s.setTname(rs.getString(5));
				return s;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBHelper.myClose(con, ps, rs);
		}
		return null;
	}
	
	/**
	 * 修改学生信息
	 * @param s
	 * @return
	 */
	public int upd(student s) {
		Connection con=DBHelper.getCon();
		PreparedStatement ps=null;
		try {
			ps=con.prepareStatement("update student set sname=?,sah=?,sclass=?,tname=? where sid=?");
			ps.setString(1, s.getSname());
			ps.setString(2, s.getSah());
			ps.setString(3, s.getSclass());
			ps.setString(4, s.getTname());
			ps.setInt(5, s.getSid());
			return ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			DBHelper.myClose(con, ps, null);
		}
		return 0;
	}
	/**
	 * 模糊查询
	 * @param key1
	 * @param key2
	 * @param key3
	 * @return
	 */
	public List<student> getkey(String key1,String key2,String key3){
		List<student> list = new ArrayList<student>();
		Connection con=DBHelper.getCon();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = con.prepareStatement("select * from student where tname like ? and sclass like ? and sah like ?");
			ps.setString(1, "%"+key1+"%");
			ps.setString(2, "%"+key2+"%");
			ps.setString(3, "%"+key3+"%");
			rs = ps.executeQuery();
			while (rs.next()) {
				student s=new student();
				s.setSid(rs.getInt(1));
				s.setSname(rs.getString(2));
				s.setSah(rs.getString(3));
				s.setSclass(rs.getString(4));
				s.setTname(rs.getString(5));
				list.add(s);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBHelper.myClose(con, ps, rs);
		}
		return list;
	}
	
	
	
}

模糊查询的servlet

package com.Servlet;

import java.io.IOException;
import java.io.PrintWriter;
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.dao.StudentDao;
import com.entity.student;
@WebServlet("/key.do")
public class KeyServlet extends HttpServlet{
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
	doPost(req, resp);
}
@Override
	protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {

	req.setCharacterEncoding("utf-8");
	resp.setContentType("text/html;charset=utf-8");
	String teacher= req.getParameter("teacher");
	String sah="";
	String[] ah = req.getParameterValues("ah");
	if(ah==null) {
		sah="";
	}else {
	for (String s : ah) {
		sah+=s+",";
	}
	}
	String[] split = sah.split(",");
	String sclass = req.getParameter("sclass");
	
	String aid = req.getParameter("aid");
	int pages=1;
	if(aid!=null) {
		pages=Integer.parseInt(aid);
	}
	int pagesize=2;
	int begin=(pages-1)*pagesize+1;
	int end=pages*pagesize;
	int m=new StudentDao().getkey(teacher, sclass, sah).size();

	
	List<student> list2 = new StudentDao().getkey(teacher,sclass,sah);
	List<student> list = new StudentDao().getpage(begin,end,sah,sclass,teacher);
	//最大页码数
	int max=(int)Math.ceil(m*1.0/pagesize);
	req.setAttribute("list", list);
	req.setAttribute("max", max);
	req.setAttribute("pages", pages);
	req.setAttribute("teacher", teacher);
	req.setAttribute("sah", sah);
	req.setAttribute("m", m);
	req.setAttribute("split", split);
	req.setAttribute("sclass", sclass);
	req.getRequestDispatcher("mohu.jsp").forward(req, resp);
}
}

JSP增删改查大概就涉及到这些内容，不懂的可私信哦！

原文链接：https://blog.csdn.net/weixin_52639224/article/details/119335892