StudentDao类
import com.bruce.domain.Student;
import java.sql.SQLException;
import java.util.List;
public interface StudentDao {
int PAGE_SIZE = 5;
List<Student> findAll() throws SQLException;
Student findStudentById(int sid) throws SQLException;
List<Student> searchStudentByTag(String sname,String sgender) throws SQLException;
List<Student> findStudentByPage(int currentPage) throws SQLException;
void insert(Student student) throws SQLException;
void update(Student student) throws SQLException;
void delete(int sid) throws SQLException;
int findCount() throws SQLException;
}
StudentDao实现类
package com.bruce.dao;
import com.bruce.domain.Student;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import utils.JDBCUtil;
import utils.TextUtil;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class StudentDaoImpl implements StudentDao{
/**
* 查询所有学生数据
* @return
* @throws SQLException
*/
@Override
public List<Student> findAll() throws SQLException {
QueryRunner queryRunner = new QueryRunner(JDBCUtil.getDataSource());
String sql = "SELECT * FROM STUDENT";
return queryRunner.query(sql,new BeanListHandler<>(Student.class));
}
/**
* 根据ID来查询并返回符合条件的学生
* @param sid
* @return
* @throws SQLException
*/
@Override
public Student findStudentById(int sid) throws SQLException {
QueryRunner queryRunner = new QueryRunner(JDBCUtil.getDataSource());
String sql = "SELECT * FROM STUDENT WHERE sid=?";
//这里只返回单个对象,不是List
return queryRunner.query(sql,new BeanHandler<>(Student.class),sid);
}
/**
* 根据2个符合条件来返回学生数据
* @param sname
* @param sgender
* @return
* @throws SQLException
*/
@Override
public List<Student> searchStudentByTag(String sname, String sgender) throws SQLException {
QueryRunner queryRunner = new QueryRunner(JDBCUtil.getDataSource());
List<String> list = new ArrayList<>();
String sql = "SELECT * FROM STUDENT WHERE 1=1";
//判断有没有姓名, 如果有,就组拼到sql语句里面
if(!TextUtil.isEmpty(sname)){
sql = sql + " and sname like ?";
list.add("%"+sname+"%");
}
//判断有没有性别,有的话,就组拼到sql语句里面。
if(!TextUtil.isEmpty(sgender)){
sql = sql + " and gender = ?";
list.add(sgender);
}
return queryRunner.query(sql,new BeanListHandler<>(Student.class),list.toArray());
}
/**
* 按照页数来返回学生数据
* @param currentPage
* @return
* @throws SQLException
*/
@Override
public List<Student> findStudentByPage(int currentPage) throws SQLException {
QueryRunner queryRunner = new QueryRunner(JDBCUtil.getDataSource());
String sql = "SELECT * FROM STUDENT LIMIT ? OFFSET ?";
return queryRunner.query(sql,
new BeanListHandler<Student>(Student.class), PAGE_SIZE , (currentPage-1)*PAGE_SIZE);
}
/**
* 添加学生数据
* @param student
* @throws SQLException
*/
@Override
public void insert(Student student) throws SQLException {
QueryRunner queryRunner = new QueryRunner(JDBCUtil.getDataSource());
String sql = "INSERT INTO STUDENT VALUES(NULL,?,?,?,?,?,?)";
queryRunner.update(sql,
student.getSname(),
student.getGender(),
student.getPhone(),
student.getBirthday(),
student.getHobby(),
student.getInfo());
}
/**
* 更新学生数据
* @param student
* @throws SQLException
*/
@Override
public void update(Student student) throws SQLException {
QueryRunner queryRunner = new QueryRunner(JDBCUtil.getDataSource());
String sql = "Update STUDENT SET sname=?,gender=?,phone=?,birthday=?,hobby=?,info=? where sid=?";
queryRunner.update(sql,
student.getSname(),
student.getGender(),
student.getPhone(),
student.getBirthday(),
student.getHobby(),
student.getInfo(),
student.getSid()
);
}
/**
* 删除学生数据
* @param sid
* @throws SQLException
*/
@Override
public void delete(int sid) throws SQLException {
QueryRunner queryRunner = new QueryRunner(JDBCUtil.getDataSource());
String sql = "Delete FROM STUDENT WHERE sid=?";
queryRunner.update(sql,sid);
}
/**
* 获取学生数据的总个数
* @return
* @throws SQLException
*/
@Override
public int findCount() throws SQLException {
QueryRunner runner = new QueryRunner(JDBCUtil.getDataSource());
//用于处理 平均值 、 总的个数。
Long result = (Long) runner.query("SELECT COUNT(*) FROM STUDENT" , new ScalarHandler() );
return result.intValue();
}
}
StudentBean类
package com.bruce.domain;
import java.util.Date;
public class Student {
private int sid;
private String sname;
private String gender;
private String phone;
private Date birthday;
private String hobby;
private String info;
public Student() {
}
public Student(String sname, String gender, String phone, Date birthday, String hobby, String info) {
this.sname = sname;
this.gender = gender;
this.phone = phone;
this.birthday = birthday;
this.hobby = hobby;
this.info = info;
}
public Student(int sid,String sname, String gender, String phone, Date birthday, String hobby, String info) {
this.sid = sid;
this.sname = sname;
this.gender = gender;
this.phone = phone;
this.birthday = birthday;
this.hobby = hobby;
this.info = info;
}
public int getSid() {
return sid;
}
public void setSid(int sid) {
this.sid = sid;
}
public String getSname() {
return sname;
}
public void setSname(String sname) {
this.sname = sname;
}
public String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public String getHobby() {
return hobby;
}
public void setHobby(String hobby) {
this.hobby = hobby;
}
public String getInfo() {
return info;
}
public void setInfo(String info) {
this.info = info;
}
@Override
public String toString() {
return "Student{" +
"sid=" + sid +
", sname='" + sname + '\'' +
", gender='" + gender + '\'' +
", phone='" + phone + '\'' +
", birthday=" + birthday +
", hobby='" + hobby + '\'' +
", info='" + info + '\'' +
'}';
}
}
分页Bean类
package com.bruce.domain;
import java.util.List;
public class PageStudent<T> {
private int currentPage;
private int totalPage;
private int pageSize;
private int totalSize;
private List<T> list;
public PageStudent() {
}
public int getCurrentPage() {
return currentPage;
}
public void setCurrentPage(int currendPage) {
this.currentPage = currendPage;
}
public int getTotalPage() {
return totalPage;
}
public void setTotalPage(int totalPage) {
this.totalPage = totalPage;
}
public int getPageSize() {
return pageSize;
}
public void setPageSize(int pageSize) {
this.pageSize = pageSize;
}
public int getTotalSize() {
return totalSize;
}
public void setTotalSize(int totalSize) {
this.totalSize = totalSize;
}
public List<T> getList() {
return list;
}
public void setList(List<T> list) {
this.list = list;
}
}
Service业务类
package com.bruce.service;
import com.bruce.domain.PageStudent;
import com.bruce.domain.Student;
import java.sql.SQLException;
import java.util.List;
public interface StudentService {
public List<Student> findAll() throws SQLException;
Student findStudentById(int sid) throws SQLException;
List<Student> searchStudentByTag(String sname,String sgender) throws SQLException;
PageStudent findStudentByPage(int currentPage) throws SQLException;
void insert(Student student) throws SQLException;
void update(Student student) throws SQLException;
void delete(int sid) throws SQLException;
}
Service业务实现类
package com.bruce.service;
import com.bruce.dao.StudentDao;
import com.bruce.dao.StudentDaoImpl;
import com.bruce.domain.PageStudent;
import com.bruce.domain.Student;
import java.sql.SQLException;
import java.util.List;
public class StudentServiceImpl implements StudentService {
@Override
public List<Student> findAll() throws SQLException {
StudentDao studentDao = new StudentDaoImpl();
return studentDao.findAll();
}
@Override
public Student findStudentById(int sid) throws SQLException {
StudentDao studentDao = new StudentDaoImpl();
return studentDao.findStudentById(sid);
}
@Override
public List<Student> searchStudentByTag(String sname, String sgender) throws SQLException {
StudentDao studentDao = new StudentDaoImpl();
return studentDao.searchStudentByTag(sname,sgender);
}
@Override
public PageStudent findStudentByPage(int currentPage) throws SQLException {
PageStudent<Student> pageStudent = new PageStudent<>();
int pageSize = StudentDao.PAGE_SIZE;
pageStudent.setCurrentPage(currentPage);
pageStudent.setPageSize(pageSize);
StudentDao dao = new StudentDaoImpl() ;
List<Student> list =dao.findStudentByPage(currentPage);
pageStudent.setList(list);
//总的记录数, 总的页数。
int count = dao.findCount();
pageStudent.setTotalSize(count); //设置总的记录数
//200 , 10 ==20 201 , 10 = 21 201 % 10 == 0 ?201 / 10 :201 % 10 + 1
pageStudent.setTotalPage(count % pageSize==0 ? count / pageSize : (count / pageSize) + 1); //总页数
return pageStudent;
}
@Override
public void insert(Student student) throws SQLException {
StudentDao studentDao = new StudentDaoImpl();
studentDao.insert(student);
}
@Override
public void update(Student student) throws SQLException {
StudentDao studentDao = new StudentDaoImpl();
studentDao.update(student);
}
@Override
public void delete(int sid) throws SQLException {
StudentDao studentDao = new StudentDaoImpl();
studentDao.delete(sid);
}
}
简单的查询全部数据的类
package com.bruce.web.servlet;
import com.bruce.service.StudentServiceImpl;
import com.bruce.service.StudentService;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.sql.SQLException;
import java.util.List;
public class StudentListServlet extends HttpServlet {
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request,response);
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
StudentService studentService = new StudentServiceImpl();
try {
List list =studentService.findAll();
request.setAttribute("list",list);
request.getRequestDispatcher("list.jsp").forward(request, response);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
JSP页面
<%--
Created by IntelliJ IDEA.
User: SuperMAX
Date: 2019/1/27
Time: 13:54
To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<html>
<head>
<link rel="stylesheet" href="https://cdn.staticfile.org/twitter-bootstrap/3.3.7/css/bootstrap.min.css">
<script src="https://cdn.staticfile.org/jquery/2.1.1/jquery.min.js"></script>
<script src="https://cdn.staticfile.org/twitter-bootstrap/3.3.7/js/bootstrap.min.js"></script>
<title>所有学生信息页面</title>
</head>
<body>
<form action="SearchStudentServlet" method="post">
<table class="table table-condensed table-striped">
<tr >
<td colspan="8">
按姓名查询:<input type="text" name="sname"/>
按性别查询:<select name="sgender">
<option value="">--请选择--
<option value="男">男
<option value="女">女
</select>
<input type="submit" value="查询">
<a href="add.jsp">添加</a>
</td>
</tr>
<tr align="center" style="font-weight: bold" class="active">
<td>编号</td>
<td>姓名</td>
<td>性别</td>
<td>电话</td>
<td>生日</td>
<td>爱好</td>
<td>简介</td>
<td>操作</td>
</tr>
<c:forEach items="${pageStudent.list }" var="student">
<tr align="center">
<td>${student.sid}</td>
<td>${student.sname}</td>
<td>${student.gender}</td>
<td>${student.phone}</td>
<td>${student.birthday}</td>
<td>${student.hobby}</td>
<td>${student.info}</td>
<td><a href="EditServlet?sid=${student.sid }">更新</a> <a href="#" onclick="doDelete(${student.sid})">删除</a></td>
</tr>
</c:forEach>
<tr class="active">
<td colspan="8">
第${pageStudent.currentPage}页/总页数为${pageStudent.totalPage}页
每页显示${pageStudent.pageSize}条, 总记录数为${pageStudent.totalSize}
<c:if test="${pageStudent.currentPage !=1}">
<a href="StudentPageListServlet?currentPage=1">首页</a>|
<a href="StudentPageListServlet?currentPage=${pageStudent.currentPage-1}">上一页</a>
</c:if>
<c:forEach begin="1" end="${pageStudent.totalPage}" var="i">
<c:if test="${pageStudent.currentPage==i}">
${i}
</c:if>
<c:if test="${pageStudent.currentPage!=i}">
<a href="StudentPageListServlet?currentPage=${i}">${i}</a>
</c:if>
</c:forEach>
<c:if test="${pageStudent.currentPage !=pageStudent.totalPage}">
<a href="StudentPageListServlet?currentPage=${pageStudent.currentPage+1}">下一页</a>|
<a href="StudentPageListServlet?currentPage=${pageStudent.totalPage}">尾页</a>
</c:if>
</td>
</tr>
</table>
<script type="text/javascript">
function doDelete(sid) {
/* 如果这里弹出的对话框,用户点击的是确定,就马上去请求Servlet。
如何知道用户点击的是确定。
如何在js的方法中请求servlet。 */
var flag = confirm("是否确定删除?");
if (flag) {
//表明点了确定。 访问servlet。 在当前标签页上打开 超链接,
//window.location.href="DeleteServlet?sid="+sid;
location.href = "DeleteServlet?sid=" + sid;
}
}
</script>
</form>
</body>
</html>