JavaWeb10(用Java语言对MySql数据库中学生信息进行增删改，批量删除，模糊查询等操作的案例）

Part01:功能截图

1、主页菜单：

2、查询所有学生信息：

3、点击1017学生后面的修改操作：

• 修改后：

4、点击1022学生后面的删除操作：

• 点击确认后
  

5、点击添加学生：

• 输入要添加的学生信息
  
• 点击添加按钮后：
  

6、批量删除：

• 点击确认后：
  

7、模糊查找：

Part02:主要功能代码

* StudentServlet

package Servlet;

import Bean.Student;
import org.apache.commons.beanutils.BeanUtils;
import service.StudentService;

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 java.io.IOException;
import java.lang.reflect.InvocationTargetException;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;
import java.util.Map;

@WebServlet(name = "StudentServlet",urlPatterns = {"/student"})
public class StudentServlet 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 {
        request.setCharacterEncoding("utf-8");
        //获取前台传进来的method参数
        String method = request.getParameter("method");
        //若method值为queryAll
        if("queryAll".equals(method)){
            //调用queryAll方法
            queryAll(request,response);

            //若method值为add
        }else if("add".equals(method)){
            //跳转到添加学生页面
            request.getRequestDispatcher("/add.jsp").forward(request,response);

            //若method值为addStudent
        }else if("addStudent".equals(method)){
            //调用addStudent方法
            addStudent(request,response);

            //若method值为modify
        }else if("modify".equals(method)){
            //调用queryStudent方法
             queryStudent(request,response);

            //若method值为modifyStudent
        }else if("modifyStudent".equals(method)){
            //调用modifyStudent方法
            modifyStudent(request,response);

            //若method值为delete
        }else if("delete".equals(method)){
            //调用deleteStudent方法
            deleteStudent(request,response);

            //若method值为delete
        }else if ("delCheck".equals(method)){
            //调用deleteCheck方法
            delCheck(request,response);

            //若method值为search
        }else if("search".equals(method)){
            //调用search方法
            searchStudent(request,response);
        }
    }

    /**
     * 模糊查找
     * @param request
     * @param response
     */
    private void searchStudent(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        try {
            String name = request.getParameter("name");
            String sex = request.getParameter("sex");
            //创建studentService对象
            StudentService studentService = new StudentService();
            //调用studentService对象的delCheck方法
            List<Student> students = studentService.searchStudent(name,sex);
            request.setAttribute("students",students);
            request.setAttribute("name",name);
            request.setAttribute("sex",sex);
            request.getRequestDispatcher("/student.jsp").forward(request,response);
        } catch (SQLException e) {
            e.printStackTrace();
            String msg = "查询学生信息错误";
            //把错误信息放入Request域中
            request.setAttribute("msg",msg);
            //请求转发到error.jsp页面
            request.getRequestDispatcher("/error.jsp").forward(request,response);
        }
    }

    /*
    删除勾选的学生
     */
    private void delCheck(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        try {
            //获取勾选的学生的id
            String[] ids = request.getParameterValues("id");
            //创建studentService对象
            StudentService studentService = new StudentService();
            //调用studentService对象的delCheck方法
            studentService.delCheck(ids);
            //请求转发到查询所有学生页面
            request.getRequestDispatcher("/student?method=queryAll").forward(request,response);
        } catch (SQLException e) {
            e.printStackTrace();
            String msg = "删除勾选学生信息错误";
            //把错误信息放入Request域中
            request.setAttribute("msg",msg);
            //请求转发到error.jsp页面
            request.getRequestDispatcher("/error.jsp").forward(request,response);
        }
    }

    /*
     根据sid删除学生信息
    */
    private void deleteStudent(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        try {
            //获取传进来的sid参数
            String sid = request.getParameter("sid");
            //根据sid删除学生信息
            //创建studentService对象
            StudentService studentService = new StudentService();
            //调用studentService的deleteStudent方法
            studentService.deleteStudent(sid);
            //请求转发到查询所有学生页面
            request.getRequestDispatcher("/student?method=queryAll").forward(request,response);
        } catch (Exception e) {
            e.printStackTrace();
            String msg = "删除学生信息错误";
            //把错误信息放入Request域中
            request.setAttribute("msg",msg);
            //请求转发到error.jsp页面
            request.getRequestDispatcher("/error.jsp").forward(request,response);
        }
    }

    /*
      修改学生信息
    */
    private void modifyStudent(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        try {
            //获取前台参数的Map集合
            Map<String, String[]> map = request.getParameterMap();
            //创建学生对象
            Student student = new Student();
            //拷贝Map集合
            BeanUtils.populate(student,map);
            //创建studentService对象
            StudentService studentService = new StudentService();
            //调用studentService的modifyStudent方法
            studentService.modifyStudent(student);
            //请求转发到查询所有学生页面
            request.getRequestDispatcher("/student?method=queryAll").forward(request,response);
        } catch (Exception e) {
            e.printStackTrace();
            String msg = "修改学生信息错误";
            //把错误信息放入Request域中
            request.setAttribute("msg",msg);
            //请求转发到error.jsp页面
            request.getRequestDispatcher("/error.jsp").forward(request,response);
        }

    }

    /*
       根据ID查询学生信息
    */
    private void queryStudent(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        try {
            //获取前台传进来的sid参数
            String sid = request.getParameter("sid");
            //创建studentService对象
            StudentService studentService = new StudentService();
            //调用studentService的queryStudent方法
            Student student = studentService.queryStudent(sid);
            //把查询到的学生信息放入request域中
            request.setAttribute("student",student);
            //请求转发到modify页面
            request.getRequestDispatcher("/modify.jsp").forward(request,response);
        } catch (SQLException e) {
            e.printStackTrace();
            String msg = "根据ID查询学生信息错误";
            //把错误信息放入Request域中
            request.setAttribute("msg",msg);
            //请求转发到error.jsp页面
            request.getRequestDispatcher("/error.jsp").forward(request,response);
        }
    }

    /*
    添加学生信息
     */
    private void addStudent(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

        try {
            //创建studentService对象
            StudentService studentService = new StudentService();
            //获取前台参数的Map集合
            Map<String, String[]> map = request.getParameterMap();
            //创建学生对象
            Student student = new Student();
            //拷贝Map集合
            BeanUtils.populate(student,map);
            //调用studentService对象的addStudent方法
            studentService.addStudent(student);
            //请求转发到查询所有学生页面
            request.getRequestDispatcher("/student?method=queryAll").forward(request,response);
        } catch (Exception e) {
            e.printStackTrace();
            String msg = "添加学生信息错误";
            //把错误信息放入Request域中
            request.setAttribute("msg",msg);
            //请求转发到error.jsp页面
            request.getRequestDispatcher("/error.jsp").forward(request,response);
        }
    }

    /*
    查询所有学生信息
     */
    private void queryAll(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        try {
            //创建studentService对象
            StudentService studentService = new StudentService();
            //调用studentService对象的queryAll方法
            List<Student> students = studentService.queryAll();
            //System.out.println(students);
            //把查询到的结果放入request域中
            request.setAttribute("students",students);
            //请求转发到student.jsp页面
            request.getRequestDispatcher("/student.jsp").forward(request,response);
        } catch (SQLException e) {
            e.printStackTrace();
            String msg = "查询所有学生错误";
            //把错误信息放入Request域中
            request.setAttribute("msg",msg);
            //请求转发到error.jsp页面
            request.getRequestDispatcher("/error.jsp").forward(request,response);
        }
    }
}

* StudentService

package service;

import Bean.Student;
import Dao.StudentDao;
import Utils.ConnectionManager;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

public class StudentService {
    //创建StudentDao对象
    StudentDao studentDao = new StudentDao();
    /*
    查询所有学生信息
     */
    public List<Student> queryAll() throws SQLException {
        //调用StudentDao的queryAll方法
        List <Student> students = studentDao.queryAll();
        return students;
    }

    /*
   添加学生信息
    */
    public void addStudent(Student student) throws SQLException {
        //调用StudentDao的addStudent方法
        studentDao.addStudent(student);
    }

    /*
      根据id查询学生信息
    */
    public Student queryStudent(String sid) throws SQLException {
        //调用StudentDao的queryStudent方法
        Student student = studentDao.queryStudent(sid);
        return student;
    }

    /*
      修改学生信息
    */
    public void modifyStudent(Student student) throws SQLException {
        //调用StudentDao的modifyStudent方法
        studentDao.modifyStudent(student);
    }

    /*
     根据sid删除学生信息
    */
    public void deleteStudent(String sid) throws SQLException {
        //调用StudentDao的deleteStudent方法
        studentDao.deleteStudent(sid);
    }

    /*
   删除勾选的学生
    */
    public void delCheck(String[] ids) throws SQLException {
        try {
            //开启事务
            ConnectionManager.start();
            if(ids != null){
                //遍历ids
                for (String id:ids
                        ) {
                    //调用StudentDao的delCheck方法
                    studentDao.delCheck(id);
                }
            }
            //提交事务
            ConnectionManager.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                //回滚事务
                ConnectionManager.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            throw e;
        }finally {
            try {
                ConnectionManager.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }

    /**
     * 模糊查找
     * @param name
     * @param sex
     * @return
     */
    public List<Student> searchStudent(String name, String sex) throws SQLException {
        return studentDao.searchStudent(name,sex);
    }
}

* StudentDao

package Dao;

import Bean.Student;
import Utils.C3P0Utils;
import Utils.ConnectionManager;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class StudentDao {
    //创建QueryRunner对象
    QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());

    /*
    查询所有学生信息
     */
    public List<Student> queryAll() throws SQLException {
        //编写SQL
        String sql = "select * from student";
        //执行sql
        List<Student> students = queryRunner.query(sql, new BeanListHandler<Student>(Student.class));
        return students;
    }
    /*
    添加学生信息
     */
    public void addStudent(Student student) throws SQLException {
        //编写SQL
        String sql = "insert into student(sname,sage,ssex) values(?,?,?)";
        //执行SQL
        queryRunner.update(sql,student.getSname(),student.getSage(),student.getSsex());
    }

    /*
     根据id查询学生信息
    */
    public Student queryStudent(String sid) throws SQLException {
        //编写SQL
        String sql = "select * from student where sid=?";
        //执行SQL
        Student student = queryRunner.query(sql, new BeanHandler<Student>(Student.class), sid);
        return student;
    }

    /*
     根据id修改学生信息
   */
    public void modifyStudent(Student student) throws SQLException {
        //编写SQL
        String sql = "update student set sname=?,sage=?,ssex=? where sid=? ";
        //执行SQL
        queryRunner.update(sql,student.getSname(),student.getSage(),student.getSsex(),student.getSid());
    }

    /*
     根据sid删除学生信息
    */
    public void deleteStudent(String sid) throws SQLException {
        //编写SQL
        String sql = "delete from student where sid =? ";
        //执行SQL
        queryRunner.update(sql,sid);
    }

    /*
      删除勾选的学生
       */
    public void delCheck(String id) throws SQLException {
        //创建QueryRunner
        QueryRunner queryRunner = new QueryRunner();
        //编写sql
        String sql = "delete from student where sid =?";
        //执行sql
        queryRunner.update(ConnectionManager.getConnection(),sql,id);
    }

    /**
     * 模糊查找
     * @param name
     * @param sex
     * @return
     */
    public List<Student> searchStudent(String name, String sex) throws SQLException {
        List<String> list = new ArrayList<String>();
        //创建StringBuilder,编写SQL语句
        StringBuilder sql = new StringBuilder("select * from student where 1=1");
        //若查询的姓名不为空
        if(name != ""){
            sql.append(" and sname like ?");
            list.add("%"+name+"%");
        }
        //若查询的性别不为空
        if(sex != ""){
            sql.append(" and ssex = ?");
            list.add(sex);
        }
        //执行sql
        List<Student> students = queryRunner.query(sql.toString(), new BeanListHandler<Student>(Student.class), list.toArray());
        return students;
    }
}

* Student

package Bean;

public class Student {
    private int sid;
    private String sname;
    private int sage;
    private String ssex;

    public Student() {
    }

    public Student(int sid, String sname, int sage, String ssex) {
        this.sid = sid;
        this.sname = sname;
        this.sage = sage;
        this.ssex = ssex;
    }

    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 int getSage() {
        return sage;
    }

    public void setSage(int sage) {
        this.sage = sage;
    }

    public String getSsex() {
        return ssex;
    }

    public void setSsex(String ssex) {
        this.ssex = ssex;
    }

    @Override
    public String toString() {
        return "Student{" +
                "sid=" + sid +
                ", sname='" + sname + '\'' +
                ", sage=" + sage +
                ", ssex='" + ssex + '\'' +
                '}';
    }
}

* add.jsp

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>Title</title>
</head>
<body>
<h3>请输入要添加学生的信息：</h3>
<form action="/student" method="post" >
    <input type="hidden" name="method" value="addStudent">
    姓名：<input type="text" name="sname">
    年龄：<input type="text" name="sage">
    性别：<input type="radio" value="男" name="ssex">男<input type="radio" value="女" name="ssex">女
    <input type="submit" value="添加" align="center">
</form>
</body>
</html>

* error.jsp

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>Title</title>
</head>
<body>
<h2>${msg}</h2>
</body>
</html>

* index.jsp

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
  <head>
    <title>$Title$</title>
  </head>
  <body>
  <a href="${pageContext.request.contextPath}/student?method=queryAll">查询所有学生信息</a>
  <a href="${pageContext.request.contextPath}/student?method=add">添加学生</a>
  </body>
</html>

* modify.jsp

<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>Title</title>
</head>
<body>
<h3>请修改该学生信息：</h3>
<form action="/student" method="post" >
    <input type="hidden" name="method" value="modifyStudent">
    <input type="hidden" name="sid" value="${student.sid}">
    姓名：<input type="text" name="sname" value="${student.sname}">
    年龄：<input type="text" name="sage" value="${student.sage}">
    性别：<input type="radio" value="男" name="ssex"
          <c:if test="${student.ssex eq '男'}">
          checked="checked"  </c:if>
          >男
    <input type="radio" value="女" name="ssex"
    <c:if test="${student.ssex eq '女'}">
           checked="checked"  </c:if>
    >女
    <input type="submit" value="确认修改" align="center">
</form>
</body>
</html>

* student.jsp

<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>Title</title>
    <script type="text/javascript" src="js/jquery-1.11.0.js"></script>
</head>
<body>
<table  border="1px solid #F00" align="center" style="text-align: center" cellpadding="10px">
    <tr align="center">
        <form action="${pageContext.request.contextPath}/student" method="post">
            <input type="hidden" name="method" value="search">
        <td colspan="6">
            姓名<input type="text" name="name" value="${name}">
            性别<input type="text" name="sex" value="${sex}">
            <input type="submit" value="搜索?">
        </td>
        </form>
    </tr>
    <tr>
        <th><input type="checkbox" id="checkID"></th>
        <th>学号</th>
        <th>姓名</th>
        <th>年龄</th>
        <th>性别</th>
        <th>操作</th>
    </tr>

        <c:if test="${empty students}">
         <tr>
            <td colspan="6">数据库中暂无学生信息，请及时添加</td>
         </tr>
        </c:if>
        <c:if test="${not empty students}">
            <form id="formID" action="${pageContext.request.contextPath}/student" method="post">
                <input type="hidden" name="method" value="delCheck">
           <c:forEach items="${students}" var="student">
               <tr>
               <td><input type="checkbox" name="id" value="${student.sid}"></td>
               <td>${student.sid}</td>
               <td>${student.sname}</td>
               <td>${student.sage}</td>
               <td>${student.ssex}</td>
               <td>
                   <a href="${pageContext.request.contextPath}/student?method=modify&sid=${student.sid}">修改</a>
                   <%--注意：在JS中写EL表达式的时候要加上''单引号--%>
                   <a href="#" onclick="del('${student.sid}')">删除</a>
               </td>
               </tr>
           </c:forEach>
           </form>
            <tr align="right">
                <td colspan="6"><input type="button" value="删除勾选" id="btnID"></td>
            </tr>
    </c:if>
</table>
</body>
<script type="text/javascript">
    function del(id) {
        var flag = confirm("确认要删除吗？");
        if(flag){
            location.href = "${pageContext.request.contextPath}/student?method=delete&sid="+id;
        }
    }
    //实现全选全不选
    $("#checkID").click(function () {
        // 使列表中复选框状态和表头一致
        $("[name=id]").prop("checked",$(this).prop("checked"))
    });
    //提交表单
    $("#btnID").click(function () {
        var flag = confirm("确认要删除全部勾选的学生的信息吗？");
        if(flag){
            $("#formID").submit();
        }

    })
</script>
</html>

Part03:用到的工具类、Jar包和配置文件

1、jar包

2、工具类和配置文件：

- C3P0Utils

package Utils;


import com.mchange.v2.c3p0.ComboPooledDataSource;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;

public class C3P0Utils {
    private static ComboPooledDataSource ds = new ComboPooledDataSource();
    //获取连接池对象
    public static DataSource getDataSource(){
        return ds;
    }
    //获取连接
    public static Connection getConnection() throws SQLException {
        return ds.getConnection();
    }
}

- ConnectionManager

package Utils;

import Utils.C3P0Utils;

import java.sql.Connection;
import java.sql.SQLException;

//连接管理类，负责获取连接，开启事务，提交事务，回滚事务
public class ConnectionManager {
    //定义一个ThreadLocal类对象来保存当前线程的连接
    private static ThreadLocal<Connection> tl = new ThreadLocal<Connection>();
    //获取连接
    public static Connection getConnection() throws SQLException {
        //先从tl中获取连接
        Connection connection = tl.get();
        //判断连接是否为空，
        if (connection == null){
            connection = C3P0Utils.getConnection();
            tl.set(connection);
        }
        return connection;
    }
    //开启事务
    public static void start() throws SQLException {
        ConnectionManager.getConnection().setAutoCommit(false);
    }
    //提交事务
    public static void commit() throws SQLException {
        ConnectionManager.getConnection().commit();
    }
    //回滚事务
    public static void rollback() throws SQLException {
        ConnectionManager.getConnection().rollback();
    }
    //关闭连接
    public static void close() throws SQLException {
        ConnectionManager.getConnection().close();
    }

}

- c3p0-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
  <default-config>
    <property name="driverClass">com.mysql.cj.jdbc.Driver</property>
    <property name="jdbcUrl">jdbc:mysql://localhost:3306/数据库名</property>
    <property name="user”>用户名</property>
    <property name="password">密码</property>
    <property name="initialPoolSize">10</property>
  </default-config>
</c3p0-config>