Vue+Element UI+c3p0连接池实现学生管理系统的增改以及分页、查询和批量删除

本文链接：https://blog.csdn.net/qq_42662411/article/details/108991117

Vue+Element UI+c3p0连接池实现学生管理系统分页以及批量删除

1-index.html

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>学生列表</title>
    <!--1.引入vue-->
    <script src="plugins/vue.min.js"></script>
    <!--2.引入axios-->
    <script src="plugins/axios.min.js"></script>
    <!-- 3.引入样式 -->
    <link rel="stylesheet" href="plugins/element-ui/lib/theme-chalk/index.css">
    <!-- 4.引入组件库 -->
    <script src="plugins/element-ui/lib/index.js"></script>
    <style>
        *{
            padding: 0px;
            margin: 0px;
        }

        .el-header, .el-footer {
            background-color: #B3C0D1;
            color: #333;
            text-align: center;
            line-height: 60px;
        }

        .el-aside {
            background-color: white;
            color: #333;
            text-align: center;
            line-height: 100px;
            height: 800px;
        }

        .el-container .el-main {
            background-color: #E9EEF3;
            color: #333;
            text-align: center;
            padding: 0px;
        }

        body > .el-container {
            margin-bottom: 40px;
        }
        .el-table{
            margin: 0;
            padding:0;
        }
        #dd{
            text-align: right;
            margin-top: -5px;
        }
    </style>


</head>
<body>
    <el-container id="d1">
        <el-header>学生后台管理系统</el-header>
        <el-container>
            <el-aside width="200px">
                    <el-menu :default-openeds="['1']">
                        <el-submenu index="1">
                            <template slot="title"><i class="el-icon-message"></i>后台管理</template>
                            <el-menu-item-group>
                                <el-menu-item index="1-1">首页</el-menu-item>
                            </el-menu-item-group>
                        </el-submenu>
                        <el-submenu index="2">
                            <template slot="title"><i class="el-icon-menu"></i>学生管理</template>
                            <el-menu-item-group>
                                <el-menu-item index="2-1">学生列表</el-menu-item>
                            </el-menu-item-group>
                        </el-submenu>
                    </el-menu>
            </el-aside>
            <el-main>
                {{studentsListIds}}
                <el-table
                        :data="students"
                        border
                        :header-cell-style="headContent"
                        :cell-style = "contentData"
                        stripe
                        style="width: 100%"
                        @selection-change="handleSelectionChange">
                    <el-table-column
                            type="selection"
                            label="删除">
                    </el-table-column>
                    <el-table-column
                            prop="sid"
                            label="学号">
                    </el-table-column>
                    <el-table-column
                            prop="sname"
                            label="学生姓名"
                           >
                    </el-table-column>
                    <el-table-column
                            prop="sex"
                            label="学生性别"
                            >
                    </el-table-column>
                    <el-table-column
                            prop="age"
                            label="学生年龄"
                            >
                    </el-table-column>
                    <el-table-column
                            prop="addr"
                            label="学生地址"
                            >
                    </el-table-column>
                    <el-table-column
                            prop="cname"
                            label="所在班级">
                    </el-table-column>
                    <el-table-column
                            fixed="right"
                            label="操作"
                            width="200">
                        <template slot-scope="scope">
                            <el-button @click="handleClick(scope.row)" type="primary" size="mini">修改</el-button>
                        </template>
                    </el-table-column>
                </el-table>
                <div style="margin-top: 20px" id="dd">
                    <el-col :span="8">
                        <div class="grid-content bg-purple-light">
                            <!--{{sname}}-->
                            <el-input
                                    placeholder="请输入查询学生的关键字"
                                    v-model="sname"
                                    clearable>
                            </el-input>
                        </div>
                    </el-col>
                    <el-col :span="8">
                        <div class="grid-content bg-purple-light">
                            <!--{{addr}}-->
                            <el-input
                                    placeholder="请输入查询住址的关键字"
                                    v-model="addr"
                                    clearable>
                            </el-input>
                        </div>
                    </el-col>
                    <el-button type="danger" @click="delStu()" icon="el-icon-delete">批量删除</el-button>
                    <el-button @click="addStu()" type="success" icon="el-icon-plus" >添加</el-button>
                    <el-button @click="search()" type="success" icon="el-icon-search">查询列表</el-button>
                    <el-button @click="returnList()" type="success" icon="el-icon-refresh">返回列表</el-button>
                </div>
                <el-pagination
                        background
                        @size-change="handleSizeChange"
                        @current-change="handleCurrentChange"
                        :current-page="page"
                        :page-sizes="[3, 5, 10, 15]"
                        :page-size="pageSize"
                        layout="total, sizes, prev, pager, next, jumper"
                        :total="total">
                </el-pagination>
            </el-main>

        </el-container>
        <!-- 弹出框显示点击修改时 显示当前对应学生对象的值-->
        <el-dialog title="学生信息" :visible.sync="dialogFormVisible">
            <!--{{student}}-->
            <el-form :model="student">
                <el-form-item label="学生姓名" :label-width="formLabelWidth">
                    <el-input v-model="student.sname" autocomplete="off"></el-input>
                </el-form-item>
                <el-form-item label="学生性别" :label-width="formLabelWidth">
                    <el-radio v-model="student.sex" label="男">男</el-radio>
                    <el-radio v-model="student.sex" label="女">女</el-radio>
                </el-form-item>
                <el-form-item label="学生年龄" :label-width="formLabelWidth">
                    <el-input v-model="student.age" autocomplete="off"></el-input>
                </el-form-item>
                <el-form-item label="学生地址" :label-width="formLabelWidth">
                    <el-input v-model="student.addr" autocomplete="off"></el-input>
                </el-form-item>
                <el-form-item label="所在班级" :label-width="formLabelWidth">
                    <el-select v-model="student.cid" placeholder="请选择所在班级" style="width: 100%">
                        <el-option v-for="c in classes" :label="c.cname" :value="c.cid"></el-option>
                    </el-select>
                </el-form-item>
            </el-form>
            <div slot="footer" class="dialog-footer">
                <el-button @click="dialogFormVisible = false">取 消</el-button>
                <el-button type="primary" @click="save()">确 定</el-button>
            </div>
        </el-dialog>

    </el-container>


</body>
<script>
    new Vue({
        el:"#d1",
        data:{
            students:[],
            dialogFormVisible: false, //默认弹出框不弹出
            student: {  //单个学生信息
                sname: '',
                sex: '',
                age: '',
                addr: '',
                cid: '',
            },
            formLabelWidth: '120px',
            classes:[], //查询所有班级信息
            sname:'',  //查询学生姓名关键字
            addr:'', //查询学生住址关键字
            page:1, //当前页面
            pageSize:3, //每页的数量
            total:20,  //学生总数
            studentsListIds:[],
        },
        methods: {
            //1.定义查询所有学生的方法
            findAllStudents() {
                axios.get("student?cmd=list").then(response => {  //找到servlet对应的方法 查询学生数据
                    this.students = response.data; //将后端传来的数据赋值给数组 属性必须一一对应
                })
            },
            //2.设置表头居中以及表格内容居中
            headContent() {
                return "text-align:center;color:blue;background:aqua;";
            },
            contentData() {
                return "text-align:center";
            },
            //3.查询所有班级信息
            findAllClasses() {
                axios.get("class?cmd=list").then(response => {
                    this.classes = response.data;
                })
            },
            //4.查看单个学生对象 点击表格修改
            handleClick(row){  //row表示当前这一行
                //console.log(row) //row表示当前这一行学生对象的值
                this.dialogFormVisible = true; //显示弹出框
                this.student = row; //将当前数据显示在弹出框页面当中
            },
            //5.添加学生
            addStu(){
                this.dialogFormVisible = true; //显示弹出框
                this.student = {}; //清空学生对象
            },
            //6.修改学生或者添加学生(保存学生)
            save(){
                //6.1)关闭对话框
                this.dialogFormVisible = false;
                //6.2)定义url
                let url = "student?cmd=add";
                //6.3)判断此时对话框中学生对象是否存在sid
                if(this.student.sid){
                    //6.4)执行修改
                    url = "student?cmd=update"
                }
                //6.5)执行后台操作
                axios.post(url,"stud="+JSON.stringify(this.student)).then(response=>{
                    console.log(response);
                    //6.5.1)获取后台传送的信息
                    if(response.data.success){
                        this.handleCurrentChange(this.page); //说明数据添加或者修改成功 刷新页面
                    }else{
                        this.$message({
                            showClose: true,
                            message: resp.data.message,
                            type: 'error'
                        }); //说明添加数据或者修改数据失败
                    }
                })
            },
            //7.查询学生
            search(){
                //7.1)将需要查询的关键字传入后端
                axios.get("student?cmd=search&sname="+this.sname+"&addr="+this.addr).then(response=>{
                    console.log(response.data);
                    this.students = response.data; //返回后端查询的数据
                })
            },
            //8.返回列表
            returnList(){
                this.sname = "";
                this.addr = "";
                axios.get("student?cmd=list").then(response=>{  //找到servlet对应的方法 查询学生数据
                    this.students = response.data; //将后端传来的数据赋值给数组 属性必须一一对应
                })
            },
            //9.分页查询
            handleSizeChange(val) {  //每页显示多少条数
                this.pageSize = val;
                this.handleCurrentChange(this.page);
                console.log(`每页 ${this.pageSize} 条`);
            },
            handleCurrentChange(page) {  //处理当前页的数据
                console.log(`当前页: ${page},每页大小：${this.pageSize}`);
                axios.get("student?cmd=page&page="+page+"&pageSize="+this.pageSize).then(resp=>{
                    //console.log(resp.data);
                    this.total = resp.data.total;
                    this.students = resp.data.rows;
                })
            },
            //10.用户点击复选框
            handleSelectionChange(val) {
                this.studentsListIds = val;
            },
            //11.根据选中的学生对象删除对应的学生
            delStu(){
                //11.1)获取对应的id数
                let ids=[];  //定义存放选中的编号数组
                this.studentsListIds.forEach(stud=>{
                    //11.2)添加编号到数组中
                    ids.push(stud.sid);
                });
                console.log(ids);
                //11.3)把数组传入到后端中
                axios.get("student?cmd=deleteStu&ids="+ids).then(resp=>{
                    console.log(resp.data);
                    if(resp.data.success){
                        this.handleCurrentChange(1); //说明数据删除成功 刷新页面
                    }else{
                        this.$message({
                            showClose: true,
                            message: resp.data.message,
                            type: 'error'
                        });
                    }
                })
            }
        },
        created(){  //创建vue实例后调用方法
            //this.findAllStudents();
            this.handleCurrentChange(1);
            this.findAllClasses();
        }
    })
</script>

2-后端代码

package com.zelin.servlet;
import cn.hutool.core.util.StrUtil;
import com.alibaba.fastjson.JSON;
import com.zelin.pojo.PageResult;
import com.zelin.pojo.Result;
import com.zelin.pojo.Student;
import com.zelin.service.StudentService;
import com.zelin.service.impl.StudentServiceImpl;

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.sql.SQLException;
import java.util.List;


/**
 * @author wf
 * @date 2020-10-08 19:57
 */
@WebServlet("/student")
public class StudentServlet extends HttpServlet {
    private StudentService studentService;
    @Override
    public void init() throws ServletException {
        studentService = new StudentServiceImpl();
    }

    @Override
    protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        //1.设置编码格式
        req.setCharacterEncoding("UTF8");
        resp.setContentType("text/html;charset=UTF8");
        //2.获取参数
        String cmd = req.getParameter("cmd");
        //3.判断参数 选择不同的方法
        if(StrUtil.isNotBlank(cmd)){
            if("list".equals(cmd)){
                list(req,resp);    //列表学生
            }else if("add".equals(cmd)){
                addStu(req,resp); //添加学生
            }else if("update".equals(cmd)){
                update(req,resp); //修改学生
            }else if("search".equals(cmd)){
                search(req,resp); //查询所有学生
            }else if("page".equals(cmd)){
                page(req,resp);  //分页查询
            }else if("deleteStu".equals(cmd)){
                deleteStu(req,resp); //批量删除
            }
        }
    }
    //2.查询所有学生
    private void list(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException  {
        try {
            //2.1)查询所有学生
            List<Student> studentList = studentService.findAll();
            //2.2)输出打印
            resp.getWriter().println(JSON.toJSONString(studentList));
            //2.3)刷新
            resp.getWriter().flush();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    //3.添加学生
    private void addStu(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException{
        //3.0)定义结果对象
        Result result = null;
        try {
            //3.1)获取前端传过来的参数
            String stud = req.getParameter("stud");
            //3.2)将json字符串转为学生对象
            Student student = JSON.parseObject(stud, Student.class);
            //3.3)将添加的学生数据添加到数据库中
            studentService.insert(student);
            //3.4)返回结果给前端
            result = new Result(true,"添加学生成功！");
        } catch (Exception e) {
            e.printStackTrace();
            result = new Result(false,"添加学生对象失败！");
        }
        //3.5)返回结果给前端页面
        resp.getWriter().println(JSON.toJSONString(result));
        //3.6)刷新页面
        resp.getWriter().flush();
    }
    //4.修改学生
    private void update(HttpServletRequest req, HttpServletResponse resp)throws ServletException, IOException {
        //4.1)定义结果对象
        Result result = null;
        try {
            //4.2)获取前端传过来的参数
            String stud = req.getParameter("stud");
            //4.3)将json字符串转为学生对象
            Student student = JSON.parseObject(stud, Student.class);
            //4.4)将修改后的学生数据添加到数据库中
            studentService.update(student);
            //4.5)返回结果给前端
            result = new Result(true,"修改学生成功！");
        } catch (Exception e) {
            e.printStackTrace();
            result = new Result(false,"修改学生对象失败！");
        }
        //4.6)返回结果给前端页面
        resp.getWriter().println(JSON.toJSONString(result));
        //4.7)刷新页面
        resp.getWriter().flush();
    }
    //6.查询学生
    private void search(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException{
        try {
            //6.1)获取前端传来的参数
            String sname = req.getParameter("sname");
            String addr = req.getParameter("addr");
            System.out.println("姓名关键字为："+sname+"住址关键字为:"+addr);
            //6.2)传入数据库
            List<Student> students = studentService.search(sname,addr);
            //6.3)输出打印
            resp.getWriter().println(JSON.toJSONString(students));
            //6.4)刷新
            resp.getWriter().flush();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //7.分页查询
    private void page(HttpServletRequest req, HttpServletResponse resp)throws ServletException, IOException {
        try {
            //7.1)获取前端传过来的参数
            String pageStr = req.getParameter("page");
            String pageSizeStr= req.getParameter("pageSize");
            //7.2)优化
            int page = StrUtil.isNotBlank(pageStr)? new Integer(pageStr):1;
            int pageSize = StrUtil.isNotBlank(pageSizeStr)? new Integer(pageSizeStr): 3;
            //7.3)返回查找的PageResult
            PageResult<Student> pageResult = studentService.page(page,pageSize);
            //7.4)将对象传送给前端数据
            resp.getWriter().println(JSON.toJSONString(pageResult));
            //7.5)刷新
            resp.getWriter().flush();
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }
    //8.批量删除
    private void deleteStu(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException{
        Result result = null;
        try{
            //8.1)获取前端参数
            String ids = req.getParameter("ids");
            System.out.println(ids);
            //8.2)传入数据库
            studentService.deleteStu(ids);
            result = new Result(true,"修改学生成功！");
        } catch (Exception e) {
            e.printStackTrace();
            result = new Result(false,"修改学生对象失败！");
        }
            //8.3)返回结果给前端页面
            resp.getWriter().println(JSON.toJSONString(result));
            //8.4)刷新页面
            resp.getWriter().flush();
    }

}

package com.zelin.service;

import com.zelin.pojo.PageResult;
import com.zelin.pojo.Student;

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

/**
 * @author wf
 * @date 2020-10-08 20:06
 */
public interface StudentService {
    List<Student> findAll() throws SQLException;

    void insert(Student student) throws SQLException;

    void update(Student student) throws SQLException;


    List<Student> search(String sname, String addr) throws SQLException;

    PageResult<Student> page(int page, int pageSize) throws SQLException;

    void deleteStu(String ids) throws SQLException;
}

package com.zelin.service.impl;

import com.zelin.dao.StudentDao;
import com.zelin.dao.impl.StudentDaoImpl;
import com.zelin.pojo.PageResult;
import com.zelin.pojo.Student;

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

/**
 * @author wf
 * @date 2020-10-08 20:06
 */
public class StudentServiceImpl implements com.zelin.service.StudentService {
    private StudentDao studentDao = new StudentDaoImpl();
    //1.查询所有学生
    @Override
    public List<Student> findAll() throws SQLException {
        return studentDao.findAll();
    }
    //2.添加学生
    @Override
    public void insert(Student student) throws SQLException {
        studentDao.insert(student);
    }
    //3.修改学生对象
    @Override
    public void update(Student student) throws SQLException {
        studentDao.update(student);
    }


    //4.批量删除学生
    @Override
    public void deleteStu(String ids) throws SQLException {
        String[] split = ids.split(",");
        System.out.println(split);
        for (String sid : split) {
            studentDao.deleteStu(sid);
        }
    }
    //5.关键字查询学生
    @Override
    public List<Student> search(String sname, String addr) throws SQLException {
        return studentDao.search(sname,addr);
    }
    //6.分页查询
    @Override
    public PageResult<Student> page(int page, int pageSize) throws SQLException {
        Long total = studentDao.findCount();  //查询所有学生总数
        List<Student> rows = studentDao.findByPage(page,pageSize);
        return new PageResult(total,rows);
    }
}

package com.zelin.dao;

import com.zelin.pojo.Student;

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

/**
 * @author wf
 * @date 2020-10-08 20:14
 */
public interface StudentDao {
    List<Student> findAll() throws SQLException;

    void insert(Student student) throws SQLException;

    void update(Student student) throws SQLException;



    List<Student> search(String sname, String addr) throws SQLException;

    Long findCount() throws SQLException;

    List<Student> findByPage(int page, int pageSize) throws SQLException;

    void deleteStu(String sid) throws SQLException;
}

package com.zelin.dao.impl;

import cn.hutool.core.util.StrUtil;
import com.zelin.dao.StudentDao;

import com.zelin.pojo.Student;
import com.zelin.util.JdbcUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

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

/**
 * @author wf
 * @date 2020-10-08 20:14
 */
public class StudentDaoImpl implements StudentDao {
    private QueryRunner runner = new QueryRunner(JdbcUtil.getDataSource());
    //1.查询所有学生信息
    @Override
    public List<Student> findAll() throws SQLException {
        return runner.query("select st.*,cname from student st,classes c where c.cid = st.cid",
                new BeanListHandler<>(Student.class));
    }
    //2.添加学生
    @Override
    public void insert(Student student) throws SQLException {
        runner.update("insert into student values(null,?,?,?,?,?)",
                student.getSname(),student.getSex(),student.getAge(),
                student.getAddr(),student.getCid());
    }
    //3.修改学生对象
    @Override
    public void update(Student student) throws SQLException {
        runner.update("update  student set sname=?,sex=?,age=?,addr=?,cid=? where sid=? ",
                student.getSname(),student.getSex(),student.getAge(),
                student.getAddr(),student.getCid(),student.getSid());
    }
    //4.批量删除学生
    @Override
    public void deleteStu(String sid) throws SQLException {
        runner.update("delete from student where sid=?",sid);
    }
    //5.关键字查询学生
    @Override
    public List<Student> search(String sname, String addr) throws SQLException {
        String sql = "select st.*,cname from student st,classes c where c.cid = st.cid";
        if(StrUtil.isNotBlank(sname)){
            sql += " and sname like '%"+sname+"%'";
        }
        if(StrUtil.isNotBlank(addr)){
            sql += " and addr like '%"+addr+"%'";
        }
        System.out.println("SQL = "+sql);
        return runner.query(sql,new BeanListHandler<>(Student.class));
    }
    //6.查询所有学生数量总数
    @Override
    public Long findCount() throws SQLException {
        return runner.query("select count(*) from student s,classes c where c.cid = s.cid",
                new ScalarHandler<>());
    }
    //7.查询当前页面的学生数量
    @Override
    public List<Student> findByPage(int page, int pageSize) throws SQLException {
        return runner.query("select st.*,cname from student st,classes c where c.cid = st.cid limit ?,?",
                new BeanListHandler<>(Student.class),(page-1)*pageSize,pageSize);
    }


}

3-实体类

Student

package com.zelin.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;


@Data
@AllArgsConstructor
@NoArgsConstructor
public class Student {
    private int sid;
    private String sname;
    private String sex;
    private int age;
    private String addr;
    private int cid;
    private String cname;
    public Student(String sname,String sex,int age,String addr,int cid){
        this.sname = sname;
        this.sex = sex;
        this.age = age;
        this.addr = addr;
        this.cid = cid;
    }
    public Student(int sid,String sname,String sex,int age,String addr,int cid){
        this(sname,sex,age,addr,cid);
        this.sid = sid;

    }
}

Classes

package com.zelin.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;


@Data
@AllArgsConstructor
@NoArgsConstructor
public class Classes {
    private int cid;
    private String cname;
}

PageResult

package com.zelin.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.util.List;

/**
 * @author wf
 * @date 2020-10-09 20:05
 */
@NoArgsConstructor
@AllArgsConstructor
@Data
public class PageResult <T>{
    private Long total;  //数据库学生总数
    private List<Student> rows; //当前页面的学生对象
}

Result

package com.zelin.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.beans.Transient;
import java.io.Serializable;

/**
 * @author wf
 * @date 2020-10-08 21:24
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Result {
    private boolean success;
    private String message;
}