以一个简单的学生信息系统为例
1.功能简介
实现学生信息的简单操作:分页查看以及增删改。
2.使用的基本框架
用户的请求由Jsp界面接受,然后转发给具体的Servlet,功能实现由Service层实现,具备业务逻辑的数据操作,例如:要修改一个学生的信息则必须保证这个学生的信息已经存在,若不存在则进行添加,所以要先进行查询操作。这些基本的数据操作由数据访问层来完成并将具体结果依次向上返回到Servlet,将结果处理后再由Jsp展示
3.具体项目结构
4.代码实现
在代码注释中有具体的逻辑描述
JavaBean
学生信息:
package org.studentinfo.entity;
public class Student {
private int id;// 学生Id
private String no;// 学号
private String name;// 姓名
private String address;// 家庭住址
public Student(int id, String no, String name, String address) {
super();
this.id = id;
this.no = no;
this.name = name;
this.address = address;
}
public Student(String no, String name, String address) {
super();
this.no = no;
this.name = name;
this.address = address;
}
public Student() {
super();
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getNo() {
return no;
}
public void setNo(String no) {
this.no = no;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
@Override
public String toString() {
return "Student [id=" + id + ", no=" + no + ", name=" + name + ", address=" + address + "]";
}
}
页面信息:(用于分页显示)
package org.studentinfo.entity;
import java.util.List;
public class PageInfo {
private int currentPage;// 当前页面
private int count;// 总数据数
private int pageSize;// 当前页面大小
private int totalPage;// 总页数
private List<Student> students;// 当前页面具体数据
public PageInfo() {
}
public PageInfo(int currentPage, int count, int pageSize, List<Student> students) {
super();
this.currentPage = currentPage;
this.count = count;
this.pageSize = pageSize;
this.students = students;
this.totalPage = (this.count % this.pageSize == 0) ? (this.count / this.pageSize)
: (this.count / this.pageSize + 1);
}
@Override
public String toString() {
return "PageInfo [当前页:" + currentPage + ", 数据总数:" + count + ", 页面大小:" + pageSize + ", 总页数:" + totalPage
+ "学生信息:" + students + "]";
}
public int getCurrentPage() {
return currentPage;
}
public void setCurrentPage(int currentPage) {
this.currentPage = currentPage;
}
public int getCount() {
return count;
}
public void setCount(int count) {
this.count = count;
}
public int getPageSize() {
return pageSize;
}
public void setPageSize(int pageSize) {
this.pageSize = pageSize;
this.totalPage = (this.count % this.pageSize == 0) ? (this.count / this.pageSize)
: (this.count / this.pageSize + 1);
}
public List<Student> getStudents() {
return students;
}
public void setStudents(List<Student> students) {
this.students = students;
}
public int getTotalPage() {
return totalPage;
}
}
数据库操作工具类
将JDBC的重复操作抽象出来形成一个工具类,实现泛化的查询与修改操作
在这个类中将数据库连接固定了下来,也可以以get、set方式将这个具体连接信息作为参数传入。
另外,在加载驱动前要导入依赖的jar包
这里使用的是MySQL数据库,不同的数据库依赖的驱动jar包不同
package org.studentinfo.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class DBUtil {
private static final String URL = "jdbc:mysql://localhost:3306/test?serverTimezone=GMT";// 数据库位置,位置信息必须添加!
private static final String USERNAME = "root";// 用户名
private static final String PASSWORD = "helloworld";// 用户密码
public static Connection connection = null;
public static PreparedStatement pstm = null;
public static ResultSet rs = null;
public static void createConnection() throws ClassNotFoundException, SQLException {// 创建连接
Class.forName("com.mysql.cj.jdbc.Driver");
connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
}
public static void createPreparedStatement(String sql, Object[] param) throws SQLException {// 替换占位符
pstm = connection.prepareStatement(sql);
if (param != null) {
for (int i = 0; i < param.length; i++) {
pstm.setObject(i + 1, param[i]);
}
}
}
public static void destory() throws SQLException {// 回收资源
/*
*将Connection、PreparedStatement、ResultSet做为工具类的属性。也可以传入参数进行回收
*/
if (rs != null)
rs.close();
if (pstm != null)
pstm.close();
if (connection != null)
connection.close();
}
public static ResultSet query(String sql, Object[] param) {// 通用查询
try {
createConnection();// 创建连接
createPreparedStatement(sql, param);// 填充sql语句
rs = pstm.executeQuery();// 执行操作
return rs;
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
// 因为要返回具体结果集,所以不能在工具类内部进行资源回收
return rs;
}
public static boolean update(String sql, Object[] param) throws SQLException {// 通用修改
boolean flag = false;
try {
createConnection();
createPreparedStatement(sql, param);
if (pstm.executeUpdate() > 0) {// 修改成功返回true
flag = true;
} else {
flag = false;
}
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
} finally {
// 修改操作只返回结果表示不使用结果集,所以可以在此处直接进行回收
destory();
}
return flag;
}
}
Jsp页面
首页:
截图展示:
用户进入页面第一次默认访问首页。点击删除链接可以直接删除对应的学生信息,并重新加载首页。点击增加按钮,进入学生信息增加页面,输入具体学生信息,进行添加。点击修改按钮将根据学生学号进行修改。点击下一页或上一页进行分页跳转。点击首页或尾页直接回到首页或尾页。点击学号可以查看更多该学生信息
<body>
<%
if (request.getAttribute("pageinfo") == null) {
request.getRequestDispatcher("QueryStudentInfoByPageServlet").forward(request, response);
request.setAttribute("error", "");
if (request.getAttribute("error").equals("操作失败!")) {
out.print("操作失败!");
}
}
%>
<table border="1px">
<tr>
<th>学号</th>
<th>姓名</th>
<th>点击对应链接删除</th>
</tr>
<%
PageInfo pageinfo = (PageInfo) request.getAttribute("pageinfo");
ArrayList<Student> students = (ArrayList<Student>) pageinfo.getStudents();
for (Student student : students) {
%>
<tr>
<td><a
href='QueryStudentInfoBySnoServlet?sno=<%=student.getNo()%>'> <%=student.getNo()%>
</a></td>
<td><%=student.getName()%></td>
<td><a href='DeleteStudentInfoServlet?sno=<%=student.getNo()%>'>
删除 </a></td>
</tr>
<%
}
%>
<tr>
<td>
<form action="addstudent.jsp">
<input type="submit" value="增加学生信息">
</form>
</td>
<td>
<form action="updatestudent.jsp">
<input type="submit" value="修改学生信息">
</form>
</td>
</tr>
</table>
<a href='QueryStudentInfoByPageServlet'>首页</a>
<a
href='QueryStudentInfoByPageServlet?currentPage=<%=pageinfo.getCurrentPage() - 1%>'>上一页</a>
<a
href='QueryStudentInfoByPageServlet?currentPage=<%=pageinfo.getCurrentPage() + 1%>'>下一页</a>
<a
href='QueryStudentInfoByPageServlet?currentPage=<%=pageinfo.getTotalPage()%>'>尾页</a>
</body>
学生具体信息页面:不能直接访问该页面,因为没有学生具体信息的来源。
<%@page import="org.studentinfo.entity.PageInfo"%>
<%@page import="org.studentinfo.entity.Student"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<%
//PageInfo pageinfo = (PageInfo) request.getAttribute("pageinfo");
Student student = (Student) request.getAttribute("student");
%>
<table border="1px">
<tr>
<th>学号</th>
<th>姓名</th>
<th>地址</th>
</tr>
<tr>
<td><%=student.getNo()%></td>
<td><%=student.getName()%></td>
<td><%=student.getAddress()%></td>
</tr>
<tr>
<td>
<form action="QueryStudentInfoByPageServlet">
<input type="submit" value="返回主页">
</form>
</td>
</tr>
</table>
</body>
</html>
增加学生信息页面:
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<form action="AddStudentInfoServlet">
<table>
<tr>
<td>id</td>
<td><input type="text" name=id /></td>
</tr>
<tr>
<td>学号</td>
<td><input type="text" name=sno /></td>
</tr>
<tr>
<td>姓名</td>
<td><input type="text" name=sname /></td>
</tr>
<tr>
<td>住址</td>
<td><input type="text" name=saddress /></td>
</tr>
<tr>
<td><input type="submit" value="确认增加" /></td>
</tr>
</table>
</form>
</body>
</html>
修改学生信息页面:
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<form action="UpdateStudentInfoServlet">
<table>
<tr>
<td>原学号</td>
<td><input type="text" name=sno /></td>
</tr>
<tr>
<td>id</td>
<td><input type="text" name=id /></td>
</tr>
<tr>
<td>新学号</td>
<td><input type="text" name=no /></td>
</tr>
<tr>
<td>姓名</td>
<td><input type="text" name=sname /></td>
</tr>
<tr>
<td>住址</td>
<td><input type="text" name=saddress /></td>
</tr>
<tr>
<td><input type="submit" value="确认修改" /></td>
</tr>
</table>
</form>
</body>
</html>
Servlet
每一个页面操作对应一个Servlet(主要是doGet()与doPost()方法)
增加:
@WebServlet("/AddStudentInfoServlet")
public class AddStudentInfoServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
public AddStudentInfoServlet() {
super();
}
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
StudentInfoService dao = new StudentInfoService();
int id = Integer.parseInt(request.getParameter("id"));
String sno = request.getParameter("sno");
String sname = request.getParameter("sname");
String saddress = request.getParameter("saddress");
Student student = new Student(id,sno,sname,saddress);
if(!dao.addStudent(student)) {
request.setAttribute("error", "操作失败!");
}else {
request.getRequestDispatcher("QueryStudentInfoByPageServlet").forward(request, response);
}
}
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
doGet(request, response);
}
}
删除:
@WebServlet("/DeleteStudentInfoServlet")
public class DeleteStudentInfoServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
public DeleteStudentInfoServlet() {
super();
}
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
StudentInfoService dao = new StudentInfoService();
String sno = request.getParameter("sno");
if(!dao.deleteStudent(sno)) {
request.setAttribute("error", "操作失败!");
}else {
request.getRequestDispatcher("QueryStudentInfoByPageServlet").forward(request, response);
}
}
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
doGet(request, response);
}
}
修改:
@WebServlet("/UpdateStudentInfoServlet")
public class UpdateStudentInfoServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
public UpdateStudentInfoServlet() {
super();
}
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
StudentInfoService dao = new StudentInfoService();
int id = Integer.parseInt(request.getParameter("id"));
String sno = request.getParameter("sno");
String no = request.getParameter("no");
String sname = request.getParameter("sname");
String saddress = request.getParameter("saddress");
Student student = new Student(id, no, sname, saddress);
// System.out.println(student);
boolean flag = dao.updateStudent(sno, student);
if (!flag) {
request.setAttribute("error", "操作失败!");
}
request.getRequestDispatcher("QueryStudentInfoByPageServlet").forward(request, response);
}
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
doGet(request, response);
}
}
查询所有:
@WebServlet("/QueryAllStudentsServlet")
public class QueryAllStudentsServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
public QueryAllStudentsServlet() {
super();
}
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
StudentInfoService dao = new StudentInfoService();
List<Student> students = dao.queryAllStudent();
if (students != null) {
request.setAttribute("students", students);
} else {
// System.out.println("结果集为空 !");
request.setAttribute("error", "操作失败!");
}
request.getRequestDispatcher("index.jsp").forward(request, response);
}
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
doGet(request, response);
}
}
查询单个:
@WebServlet("/QueryStudentInfoBySnoServlet")
public class QueryStudentInfoBySnoServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
public QueryStudentInfoBySnoServlet() {
super();
}
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
String sno = request.getParameter("sno");
StudentInfoService dao = new StudentInfoService();
Student student = dao.queryBySno(sno);
if (student != null) {
request.setAttribute("student", student);
} else {
request.setAttribute("error", "查询失败");
}
request.getRequestDispatcher("showstudent.jsp").forward(request, response);// 涉及到数据,请求转发到个人信息显示页面
}
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
doGet(request, response);
}
}
分页查询:
步骤:
1.数据总数 (查询数据库获得)
2.页面大小(页面容量,每页显示的数据条数,由用户自定义)
3.总页数 (自动计算) 总页数= 数据总数 % 页面大小==0 ?数据总数 /页面大小:数据总数 /页面大小 + 1;
注意:自动计算的时机:当数据总数和页面大小都被赋值以后,自动计算总页数。
4.当前页码(用户自定义)
5.实体类对象集合(当前页的数据集合):依赖于数据库
所以将这些包装成一个Page类JavaBean
@WebServlet("/QueryStudentInfoByPageServlet")
public class QueryStudentInfoByPageServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
public QueryStudentInfoByPageServlet() {
super();
}
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
StudentInfoService dao = new StudentInfoService();
PageInfo pageinfo = new PageInfo();
int count = dao.getCount();// 数据总数
pageinfo.setCount(count);
int pageSize = 3;// 当前页面大小,可以写死,也可以通过用户请求设置
pageinfo.setPageSize(pageSize);
int currentPage = 1;
if (request.getParameter("currentPage") != null) {// 第一次请求时默认访问首页
currentPage = Integer.parseInt(request.getParameter("currentPage"));// 当前页面
}
if (currentPage > pageinfo.getTotalPage() || currentPage < 1) {// 当页码太大时自动回滚到首页,太小时留在首页
currentPage = 1;
}
// System.out.println("当前传递到来的当前页面为:"+ currentPage);
List<Student> students = dao.queryByPage(currentPage, pageSize);// 具体数据集合
pageinfo.setCurrentPage(currentPage);
pageinfo.setStudents(students);
if (students != null) {
request.setAttribute("pageinfo", pageinfo);
} else {
request.setAttribute("error", "操作失败!");
}
request.getRequestDispatcher("index2.jsp").forward(request, response);
}
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
doGet(request, response);
}
}
Service层
实现业务逻辑,主要负责基本操作的组织。
package org.studentinfo.service;
import java.util.List;
import org.studentinfo.dao.StudentInfoDao;
import org.studentinfo.entity.Student;
public class StudentInfoService {
private StudentInfoDao dao = null;
public StudentInfoService() {
super();
dao = new StudentInfoDao();
}
public Student queryBySno(String sno) {
return dao.queryBySno(sno);
}
public List<Student> queryAllStudent() {
return dao.queryAllStudent();
}
public boolean addStudent(Student student) {//增加
if(dao.queryBySno(student.getNo()) != null) {
return false;
}else {
dao.addStudent(student);
return true;
}
}
public boolean deleteStudent(String sno) {//删除
if(dao.queryBySno(sno) != null) {
dao.deleteStudent(sno);
return true;
}else {
return false;
}
}
public boolean updateStudent(String sno,Student student) {//修改
if(dao.queryBySno(sno) != null) {
//System.out.println(student);
dao.updateStudent(sno, student);
return true;
}else {
return false;
}
}
public int getCount() {
return dao.getCount();
}
public List<Student> queryByPage(int currentPage, int pageSize) {
return dao.queryByPage(currentPage, pageSize);
}
}
Dao层
数据访问层,直接对数据库进行操作。将SQL语句和补齐占位符数组传入。
package org.studentinfo.dao;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.studentinfo.entity.Student;
import org.studentinfo.util.DBUtil;
public class StudentInfoDao {
private ResultSet rs = null;
private Student student = null;
private List<Student> students = null;
// 根据学号查单个学生
public Student queryBySno(String sno) {
try {
String sql = "SELECT sno,sname,saddress FROM studeninfo WHERE sno = ? ";
Object[] param = { sno };
rs = DBUtil.query(sql, param);
if (rs.next()) {
String no = (String) (rs.getObject("sno"));
String name = (String) (rs.getObject("sname"));
String address = (String) (rs.getObject("saddress"));
student = new Student(no, name, address);
} else {
return null;
}
} catch (SQLException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
DBUtil.destory();
} catch (SQLException e) {
e.printStackTrace();
}
}
return student;
}
// 查询所有学生信息
public List<Student> queryAllStudent() {
students = new ArrayList<Student>();
try {
String sql = "SELECT id,sno,sname,saddress FROM studeninfo ";
Object[] param = null;
rs = DBUtil.query(sql, param);
while (rs.next()) {
int id = (int) (rs.getObject("id"));
String no = (String) (rs.getObject("sno"));
String name = (String) (rs.getObject("sname"));
String address = (String) (rs.getObject("saddress"));
student = new Student(id, no, name, address);
students.add(student);
}
} catch (SQLException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
DBUtil.destory();
} catch (SQLException e) {
e.printStackTrace();
}
}
return students;
}
// 增加学生信息
public boolean addStudent(Student student) {
boolean flag = false;
try {
String sql = "INSERT INTO studeninfo VALUES ( ?, ? , ? , ? ) ";
Object[] param = { student.getId(), student.getNo(), student.getName(), student.getAddress() };
flag = DBUtil.update(sql, param);
} catch (SQLException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
DBUtil.destory();
} catch (SQLException e) {
e.printStackTrace();
}
}
return flag;
}
// 删除学生信息
public boolean deleteStudent(String sno) {
boolean flag = false;
try {
String sql = "DELETE FROM studeninfo WHERE sno = ?";
Object[] param = { sno };
flag = DBUtil.update(sql, param);
} catch (SQLException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
DBUtil.destory();
} catch (SQLException e) {
e.printStackTrace();
}
}
return flag;
}
// 修改学生信息
public boolean updateStudent(String sno, Student student) {
boolean flag = false;
try {
String sql = "UPDATE studeninfo SET id = ? , sno = ? , sname = ? , saddress = ? WHERE sno = ? ";
Object[] param = { student.getId(), student.getNo(), student.getName(), student.getAddress(), sno };
flag = DBUtil.update(sql, param);
} catch (SQLException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
DBUtil.destory();
} catch (SQLException e) {
e.printStackTrace();
}
}
return flag;
}
// 查询列表大小
public int getCount() {
String sql = "SELECT count(1) from studeninfo";
int count = -1;
try {
rs = DBUtil.query(sql, null);
if(rs.next()) {
count = rs.getInt(1);
}
} catch (SQLException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
DBUtil.destory();
} catch (SQLException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
return count;
}
public List<Student> queryByPage(int currentPage, int pageSize) {
students = new ArrayList<Student>();
try {
String sql = "SELECT * FROM studeninfo ORDER BY id LIMIT ?, ? ";
Object[] param = { (currentPage - 1) * pageSize, pageSize };
rs = DBUtil.query(sql, param);
while (rs.next()) {
int id = (int) (rs.getObject("id"));
String no = (String) (rs.getObject("sno"));
String name = (String) (rs.getObject("sname"));
String address = (String) (rs.getObject("saddress"));
student = new Student(id, no, name, address);
students.add(student);
}
} catch (SQLException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
DBUtil.destory();
} catch (SQLException e) {
e.printStackTrace();
}
}
return students;
}
}
5.具体数据库示例
6.优化改进方向
(1)增加登录界面,使用拦截器进行登录验证与信息筛选
(2)设置监听器统计网站访问人数
(3)优化界面显示
(4)在用户删除具体信息时应设置提示语句,防止误操作
(5)用户权限分级
(6)设置文件上传与下载