工具:eclipse oracle (oracle 表有: 学生表、教员表、班级表。)
需要的jar包
主界面
主页面默认查询全部,可根据老师、班级、爱好 进行模糊查询
增加页面
修改页面
主页面数据显示代码
package com.servlet;
import java.io.IOException;
import java.util.Iterator;
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.impl.Studentdao;
import com.entity.Hobby;
import com.entity.StuClass;
import com.entity.Student;
import com.entity.Teacher;
/**
* 主页面数据加载以及模糊查询和分页
*
* @author T440s
*
*/
@WebServlet("/index.do")
public class indexservlet 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 {
resp.setContentType("text/html;charset=utf-8");
req.setCharacterEncoding("utf-8");
//获取模糊查询的值(默认加载为null)
String key = req.getParameter("stuclass1");
String key1 = req.getParameter("teacher1");
String[] key2 = req.getParameterValues("id");
String hobby1 = "";
if (key2 != null && key2.length > 0) {
for (String string : key2) {
hobby1 += string;
}
}
//如果关键字为null设置为空
if (key == null)
key = "";
if (key1 == null)
key1 = "";
Studentdao s = new Studentdao();
// 获取所有班级
List<StuClass> stuclass = s.getallclass();
// 获取所有教员
List<Teacher> teacher = s.getallcher();
// 获取所有爱好
List<Hobby> hobby = s.getallhobby();
//获取当前页数
String pid = req.getParameter("pid");
//默认页数
int pages = 1;
if (pid != null) {//页数不为null则替换
pages = Integer.parseInt(pid);
}
//每页显示条数
int pagesSize = 3;
int begin = (pages - 1) * pagesSize + 1;
int end = pages * pagesSize;
//数据总条数
int row = s.getrow();
int max = (int) Math.ceil(row * 1.0 / pagesSize);
// 获取学生
List<Student> student = s.getall(key1, key, hobby1, begin, end);
req.setAttribute("stuclass", stuclass);
req.setAttribute("teacher", teacher);
req.setAttribute("hobby", hobby);
req.setAttribute("student", student);
req.setAttribute("max", max);
req.setAttribute("pages", pages);
req.setAttribute("row", row);
req.getRequestDispatcher("index.jsp").forward(req, resp);
}
}
模糊查询
主页面
<%@ 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>
</head>
<script src="js/jquery-3.5.1.js"></script>
<style type="text/css">
table,tr {
border-collapse: collapse;
border-color: red;
}
td{
text-align: center;
}
</style>
<script>
var luy="";
$({
$('input:checkbox[name="id"]:checked').each(function(){
luy=luy+$(this).val()
})
console.log(luy)
})
</script>
<body><form action="">
<table border>
<tr >
<td colspan="6">老师:
<select name="teacher1">
<option>请选择</option>
<c:forEach items="${teacher }" var="t">
<option value="${t.teacher}">${t.teacher}</option>
</c:forEach>
</select>
班级:
<select style="margin-left: 40px" name="stuclass1">
<option>请选择</option>
<c:forEach items="${stuclass}" var="s">
<option value="${s.name}">${s.name}</option>
</c:forEach>
</select>
爱好:
<c:forEach items="${hobby }" var="h">
<input type="checkbox" value="${h.hooby}" name="id"/>${h.hooby}
</c:forEach>
<button onclick="location.href='index.do'">搜索</button>
<a href="add.do">新增</a>
</td>
</tr>
<tr>
<td>学生ID</td> <td>学生姓名</td>
<td>学生教员</td> <td>学生所在班级</td>
<td>学生爱好</td> <td>操作</td>
</tr>
<c:forEach items="${student}" var="stu">
<tr>
<td>${stu.sid }</td> <td>${stu.name }</td>
<td>${stu.teacher}</td> <td>${stu.classid}</td>
<td>${stu.hobby}</td> <td><a href="delstu.do?id=${stu.sid }">删除</a> <a href="updstu.do?id=${stu.sid }">修改</a></td>
</tr>
</c:forEach>
<tr><td colspan="6">第${pages}页 共${max}页 总记录${row}条 <a href="index.do?pid=1">首页</a>
<a href="index.do?pid=${pages-1<1?1:pages-1}">上一页</a> <a href="index.do?pid=${pages+1>max?max:pages+1 }">下一页</a> <a href="index.do?pid=${max}">尾页</a>
</td></tr>
</table>
</form>
</body>
</html>
增加页面代码(先加载页面数据再执行增加)
package com.servlet;
import java.io.IOException;
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.impl.Studentdao;
import com.entity.Hobby;
import com.entity.StuClass;
import com.entity.Teacher;
/**
* 增加学生界面数据显示
* @author T440s
*
*/
@WebServlet("/add.do")
public class addservlet 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 {
resp.setContentType("text/html;charset=utf-8");
req.setCharacterEncoding("utf-8");
Studentdao s = new Studentdao();
// 获取所有班级
List<StuClass> stuclass = s.getallclass();
// 获取所有教员
List<Teacher> teacher = s.getallcher();
// 获取所有爱好
List<Hobby> hobby = s.getallhobby();
req.setAttribute("stuclass", stuclass);
req.setAttribute("teacher", teacher);
req.setAttribute("hobby", hobby);
//跳转到增加界面
req.getRequestDispatcher("add.jsp").forward(req, resp);
}
}
增加页面
<%@ 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>
</head>
<style type="text/css">
table,tr {
border-collapse: collapse;
border-color: red;
}
td{
text-align: center;
}
</style>
<body>
<form action="addstudent.do">
<table border="">
<tr><td><p>名字:<input name="name"/></p></td></tr>
<tr><td><p>教员:<select name="teacher">
<option>请选择</option>
<c:forEach items="${teacher }" var="t">
<option value="${t.teacher}">${t.teacher}</option>
</c:forEach>
</select></p></td></tr>
<tr><td><p>班级:<select name="stuclass">
<option>请选择</option>
<c:forEach items="${stuclass}" var="s">
<option value="${s.name}">${s.name}</option>
</c:forEach>
</select></p></td></tr>
<tr><td><p>爱好:
<c:forEach items="${hobby }" var="h">
<input type="checkbox" value="${h.hooby}" name="hobby"/>${h.hooby}
</c:forEach>
</p></td></tr>
<tr><td><button>提交</button></td></tr>
</table>
</form>
</body>
</html>
删除
通过学生ID进行删除
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.impl.Studentdao;
/**
* 删除学生
* @author T440s
*
*/
@WebServlet("/delstu.do")
public class delstudent 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 {
resp.setContentType("text/html;charset=utf-8");
req.setCharacterEncoding("utf-8");
//获取删除学生id
String id = req.getParameter("id");
Studentdao studao = new Studentdao();
//删除学生
int delstudent = studao.delstudent(Integer.parseInt(id));
resp.sendRedirect("index.do");
}
}
修改
先通过需要修改的学生ID获取次学生再将数据赋值到页面上
package com.servlet;
import java.io.IOException;
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.impl.Studentdao;
import com.entity.Hobby;
import com.entity.StuClass;
import com.entity.Student;
import com.entity.Teacher;
/**
* 修改页面加载数据
* @author T440s
*
*/
@WebServlet("/updstu.do")
public class updstudent 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 {
resp.setContentType("text/html;charset=utf-8");
req.setCharacterEncoding("utf-8");
//获取修改学生id
String id = req.getParameter("id");
Studentdao s = new Studentdao();
//获取需要修改学生
Student student = s.getstudent(Integer.parseInt(id));
// 获取所有班级
List<StuClass> stuclass = s.getallclass();
// 获取所有教员
List<Teacher> teacher = s.getallcher();
// 获取所有爱好
List<Hobby> hobby = s.getallhobby();
//将学生爱好变成数组
String[] split = student.getHobby().split(",");
req.setAttribute("split", split);
req.setAttribute("student", student);
req.setAttribute("stuclass", stuclass);
req.setAttribute("teacher", teacher);
req.setAttribute("hobby", hobby);
req.getRequestDispatcher("upd.jsp").forward(req, resp);
}
}
修改页面赋值
<%@ 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>
</head>
<style type="text/css">
table, tr {
border-collapse: collapse;
border-color: red;
}
td {
text-align: center;
}
</style>
<body>
<form action="stupd.do">
<table border="">
<tr>
<td><p>
名字:<input name="name" value="${student.name}" />
</p></td>
</tr>
<tr>
<td><p>
教员:<select name="teacher">
<option>请选择</option>
<c:forEach items="${teacher }" var="t">
<option ${student.teacher==t.teacher?"selected":""}
value="${t.teacher}">${t.teacher}</option>
</c:forEach>
</select>
</p></td>
</tr>
<tr>
<td><p>
班级:<select name="stuclass">
<option>请选择</option>
<c:forEach items="${stuclass}" var="s">
<option ${student.classid==s.name?"selected":"" }
value="${s.name}">${s.name}</option>
</c:forEach>
</select>
</p></td>
</tr>
<tr>
<td>
<p>
爱好:
<c:forEach items="${hobby }" var="h">
<input type="checkbox" value="${h.hooby}" name="hobby"
<c:forEach items="${split }" var="sp">
<c:if test="${sp==h.hooby }">checked</c:if>
</c:forEach> />${h.hooby}
</c:forEach>
</p>
</td>
</tr>
<tr>
<td><button>修改</button> <input name="id"
value="${student.sid }" hidden="" /></td>
</tr>
</table>
</form>
</body>
</html>
项目所有方法
package com.dao.impl;
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 com.dao.Istudao;
import com.entity.Hobby;
import com.entity.StuClass;
import com.entity.Student;
import com.entity.Teacher;
import com.util.DBHelper;
public class Studentdao implements Istudao{
Connection con;
PreparedStatement ps;
ResultSet rs;
/**
* 查询 模糊查询 分页
*/
@Override
public List<Student> getall(String teacher, String Class, String hobby, int begin, int end) {
List<Student> list=new ArrayList<Student>();
con=DBHelper.getCon();
String sql="SELECT * FROM( SELECT r.* ,ROWNUM rid FROM student r WHERE teacher LIKE ? AND classid LIKE ? AND hobby LIKE ? )t WHERE rid BETWEEN ? AND ? ";
try {
ps=con.prepareStatement(sql);
ps.setString(1, "%"+teacher+"%");
ps.setString(2, "%"+Class+"%");
ps.setString(3,"%"+hobby+"%");
ps.setInt(4, begin);
ps.setInt(5, end);
rs=ps.executeQuery();
while(rs.next()) {
Student s=new Student();
s.setSid(rs.getInt(1));
s.setName(rs.getString(2));
s.setTeacher(rs.getString(3));
s.setClassid(rs.getString(4));
s.setHobby(rs.getString(5));
list.add(s);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
DBHelper.myClose(con, ps, rs);
}
return list;
}
/**
* 查询单个学生
*/
@Override
public Student getstudent(int id) {
con=DBHelper.getCon();
String sql="select * from student where sid=?";
try {
ps=con.prepareStatement(sql);
ps.setInt(1, id);
rs=ps.executeQuery();
if(rs.next()) {
Student s=new Student();
s.setSid(rs.getInt(1));
s.setName(rs.getString(2));
s.setTeacher(rs.getString(3));
s.setClassid(rs.getString(4));
s.setHobby(rs.getString(5));
return s;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
DBHelper.myClose(con, ps, rs);
}
return null;
}
/**
* 获取所有班级
*/
@Override
public List<StuClass> getallclass() {
List<StuClass> list=new ArrayList<StuClass>();
con=DBHelper.getCon();
String sql="select * from class";
try {
ps=con.prepareStatement(sql);
rs=ps.executeQuery();
while(rs.next()) {
StuClass s=new StuClass();
s.setClassid(rs.getInt(1));
s.setName(rs.getString(2));
list.add(s);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
DBHelper.myClose(con, ps, rs);
}
return list;
}
/**
* 获取所有老师
*/
@Override
public List<Teacher> getallcher() {
List<Teacher> list=new ArrayList<Teacher>();
con=DBHelper.getCon();
String sql="select * from teacher";
try {
ps=con.prepareStatement(sql);
rs=ps.executeQuery();
while(rs.next()) {
Teacher s=new Teacher();
s.setTeacherid(rs.getInt(1));
s.setTeacher(rs.getString(2));
list.add(s);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
DBHelper.myClose(con, ps, rs);
}
return list;
}
/**
* 获取所有爱好
*/
@Override
public List<Hobby> getallhobby() {
List<Hobby> list=new ArrayList<Hobby>();
con=DBHelper.getCon();
String sql="SELECT * FROM hobby";
try {
ps=con.prepareStatement(sql);
rs=ps.executeQuery();
while(rs.next()) {
Hobby s=new Hobby();
s.setHobbyid(rs.getInt(1));
s.setHooby(rs.getString(2));
list.add(s);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
DBHelper.myClose(con, ps, rs);
}
return list;
}
/**
* 获取学生表数据条数
*/
@Override
public int getrow() {
con=DBHelper.getCon();
String sql="select count(1) from student";
try {
ps=con.prepareStatement(sql);
rs=ps.executeQuery();
if(rs.next()) {
return rs.getInt(1);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return 0;
}
/**
* 增加学生
*/
@Override
public int addstudent(Student s) {
con=DBHelper.getCon();
String sql="INSERT INTO student VALUES(?,?,?,?,?)";
try {
ps=con.prepareStatement(sql);
ps.setInt(1, s.getSid());
ps.setString(2, s.getName());
ps.setString(3, s.getTeacher());
ps.setString(4, s.getClassid());
ps.setString(5, s.getHobby());
return ps.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
DBHelper.myClose(con, ps, rs);
}
return 0;
}
/**
* 设置学生id
*/
@Override
public int getmaxid() {
con=DBHelper.getCon();
String sql="select nvl(max(sid),0)+1 from student";
try {
ps=con.prepareStatement(sql);
rs=ps.executeQuery();
if(rs.next()) {
return rs.getInt(1);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
DBHelper.myClose(con, ps, rs);
}
return 0;
}
/**
* 删除学生
*/
@Override
public int delstudent(int id) {
con=DBHelper.getCon();
String sql="DELETE FROM student WHERE SID=? ";
try {
ps=con.prepareStatement(sql);
ps.setInt(1, id);
return ps.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
DBHelper.myClose(con, ps, rs);
}
return 0;
}
/**
* 修改学生
*/
@Override
public int updstudent(Student s) {
con=DBHelper.getCon();
String sql="UPDATE student SET NAME=?,teacher=?,classid=?,hobby=? WHERE SID=?";
try {
ps=con.prepareStatement(sql);
ps.setString(1, s.getName());
ps.setString(2, s.getTeacher());
ps.setString(3, s.getClassid());
ps.setString(4, s.getHobby());
ps.setInt(5, s.getSid());
return ps.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
DBHelper.myClose(con, ps, rs);
}
return 0;
}
}