简单的增删查改系统

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"/>
                &nbsp;
                按性别查询:<select name="sgender">
                <option value="">--请选择--
                <option value="男">男
                <option value="女">女
            </select>
                &nbsp;&nbsp;&nbsp;
                <input type="submit" value="查询">
                &nbsp;&nbsp;&nbsp;
                <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>&nbsp;<a href="#" onclick="doDelete(${student.sid})">删除</a></td>
            </tr>

        </c:forEach>
        <tr class="active">
            <td colspan="8">
                第${pageStudent.currentPage}页/总页数为${pageStudent.totalPage}页 &nbsp; &nbsp; &nbsp;
                每页显示${pageStudent.pageSize}条, 总记录数为${pageStudent.totalSize} &nbsp; &nbsp; &nbsp;
                <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>