Javaweb03

使用DAO层对学生管理系统进行处理
在这里插入图片描述

1.准备工作

创建DAOUtil工具类，封装JDBC

package com.grg.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Author Grg
 * @Date 2023/8/23 13:23
 * @PackageName:com.grg.util
 * @ClassName: DAOUtil
 * @Description: 又是码代码的一天
 * @Version plus max 宇宙无敌终极版本
 */
public class DAOUtil {
    //消除魔法值
    private static final String CLASSNAME = "com.mysql.cj.jdbc.Driver";
    private static final String URL = "jdbc:mysql:///jdbctest";
    private static final String USERNAME = "root";
    private static final String PASSWORDS = "123456";

    //加载驱动
    static {
        try {
            Class.forName(CLASSNAME);
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        }
    }

    //建立连接
    public static Connection getConnection() throws Exception {
        return DriverManager.getConnection(URL, USERNAME, PASSWORDS);
    }


    //增删改
    public static int executeUpdate(String sql,Object[] data){
        Connection conn = null;
        try {
            conn = DAOUtil.getConnection();
            PreparedStatement ps = conn.prepareStatement(sql);

            for (int i = 0; i < data.length; i++) {
                ps.setObject(i+1,data[i]);
            }

            return ps.executeUpdate();

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if(conn != null) {
                    conn.close();
                }
            }catch (Exception e){
                e.printStackTrace();
            }
        }
        return 0;
    }

    //查
    public static List<Map<String,Object>> executeQuery(String sql, Object... arr){

        List<Map<String,Object>> data = new ArrayList<>();
        Connection conn = null;
        try {
            conn = DAOUtil.getConnection();
            PreparedStatement ps = conn.prepareStatement(sql);
            for (int i = 0; i < arr.length; i++) {
                ps.setObject(i+1,arr[i]);
            }
            ResultSet set = ps.executeQuery();
            int columnCount = set.getMetaData().getColumnCount();
            while (set.next()){
                HashMap<String, Object> map = new HashMap<>();
                for( int i=0;i<columnCount;i++){
                    map.put( set.getMetaData().getColumnLabel(i+1) , set.getObject(i+1)   );
                }
                data.add(map);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if(conn != null) {
                    conn.close();
                }
            }catch (Exception e){
                e.printStackTrace();
            }
        }
        return  data;
    }
}

创建学生类，当进行多参数传递时，利用对象传参

package com.grg.dto;

import java.io.Serializable;

/**
 * @Author Grg
 * @Date 2023/8/23 13:22
 * @PackageName:com.grg.dto
 * @ClassName: Student
 * @Description: 又是码代码的一天
 * @Version plus max 宇宙无敌终极版本
 */
public class Student implements Serializable {
    private Integer id ;
    private String name;
    private Integer age;
    private String address;

    public Student(Integer id, String name, Integer age, String address) {
        this.id = id;
        this.name = name;
        this.age = age;
        this.address = address;
    }

    public Student() {
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    @Override
    public String toString() {
        return "student{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", age=" + age +
                ", address='" + address + '\'' +
                '}';
    }
}

POM文件

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>org.example</groupId>
  <artifactId>StudentDAO</artifactId>
  <packaging>war</packaging>
  <version>1.0-SNAPSHOT</version>
  <name>StudentDAO Maven Webapp</name>
  <url>http://maven.apache.org</url>
  <dependencies>
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>8.0.33</version>
    </dependency>
    <dependency>
      <groupId>javax.servlet</groupId>
      <artifactId>javax.servlet-api</artifactId>
      <version>4.0.1</version>
    </dependency>
    <dependency>
      <groupId>javax.servlet</groupId>
      <artifactId>jstl</artifactId>
      <version>1.2</version>
    </dependency>
    <dependency>
      <groupId>taglibs</groupId>
      <artifactId>standard</artifactId>
      <version>1.1.2</version>
    </dependency>
  </dependencies>
  <build>
    <finalName>StudentDAO</finalName>
  </build>
</project>

2.DAO

创建StudentDAO接口,其中写了所需要用到的方法

package com.grg.dao;

import com.grg.dto.Student;

import java.util.List;
import java.util.Map;

/**
 * @Author Grg
 * @Date 2023/8/23 13:20
 * @PackageName:com.grg.dao
 * @ClassName: StudentDAO
 * @Description: 又是码代码的一天
 * @Version plus max 宇宙无敌终极版本
 */
public interface StudentDAO {
    /**
     * 查询所有学生
     * @return
     */
    List<Map<String, Object>> listAllStudents();

    /**
     * 删除学生
     * @param id
     * @return
     */
    int delStudentById(String id);

    /**
     * 添加学生
     * @param s
     * @return
     */
    int addStudent(Student s);

    /**
     * 更新学生
     * @param s
     * @return
     */
    int updateStudent(Student s);

    /**
     * 获取需要更新数据
     * @param id
     * @return
     */
    List<Map<String, Object>> getStudentId(String id);
}

创建StudentDAOImpl实现类

package com.grg.dao.impl;

import com.grg.dao.StudentDAO;
import com.grg.dto.Student;
import com.grg.util.DAOUtil;

import java.util.List;
import java.util.Map;

/**
 * @Author Grg
 * @Date 2023/8/23 13:19
 * @PackageName:com.grg.dao.impl
 * @ClassName: studentDAOImpl
 * @Description: 又是码代码的一天
 * @Version plus max 宇宙无敌终极版本
 */
public class StudentDAOImpl implements StudentDAO {
    @Override
    public List<Map<String, Object>> listAllStudents() {
        String sql = "select * from student";
        return DAOUtil.executeQuery(sql);
    }

    @Override
    public int delStudentById(String id) {
        String sql = "delete from student where id = ?";
        Object[] data = {id};
        return DAOUtil.executeUpdate(sql, data);

    }

    @Override
    public int addStudent(Student s) {
        String sql = "insert into student values (null,?,?,?)";
        Object[] data = {s.getName(), s.getAge(), s.getAddress()};
        return DAOUtil.executeUpdate(sql, data);
    }

    @Override
    public int updateStudent(Student s) {
        String sql = "update student set  name=?,age=?,address=? where id = ?";
        Object[] data = { s.getName(), s.getAge(), s.getAddress(),s.getId()};
        return DAOUtil.executeUpdate(sql, data);
    }

    @Override
    public List<Map<String, Object>> getStudentId(String id) {
        String sql = "select * from student where id = " + id;
        return DAOUtil.executeQuery(sql);
    }
}

3.Servlet

展示数据创建StudentServlet

@WebServlet("/stu")
public class StudentServlet extends HttpServlet {
    @Override
    protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        StudentDAO studentDAO = new StudentDAOImpl();
        //查找所有学生
        List<Map<String, Object>> data = studentDAO.listAllStudents();

        //传递
        req.setAttribute("aaa",data);
        req.getRequestDispatcher("student.jsp").forward(req,resp);
    }
}

添加数据创建AddStuServlet

@WebServlet("/addStu")
public class AddStuServlet extends HttpServlet {
    @Override
    protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        //1.接收请求参数
        req.setCharacterEncoding("UTF-8");

        String name = req.getParameter("name");
        String age = req.getParameter("age");
        String address = req.getParameter("address");

        //用student对象作为参数传递
        Student student = new Student(null, name, Integer.parseInt(age), address);
        StudentDAO studentDAO = new StudentDAOImpl();
        int i = studentDAO.addStudent(student);

        //传递
        req.setAttribute("msg", i > 0 ? "添加成功" : "添加失败");
        req.getRequestDispatcher("addStu.jsp").forward(req, resp);

    }
}

删除数据创建DelStuServlet

@WebServlet("/delStu")
public class DelStuServlet extends HttpServlet {
    @Override
    protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        //接收参数
        String id = req.getParameter("id");

        //删除数据
        StudentDAO studentDAO = new StudentDAOImpl();
        int i = studentDAO.delStudentById(id);

        //传递
        req.getRequestDispatcher("stu").forward(req,resp);
    }
}

修改数据创建ToUpdateServlet和UpdateStuServlet

@WebServlet("/toUpdate")
public class ToUpdateServlet extends HttpServlet {
    @Override
    protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        //接收参数
        String id = req.getParameter("id");

        //查询学生
        StudentDAO studentDAO = new StudentDAOImpl();
        List<Map<String, Object>> data = studentDAO.getStudentId(id);

        //传递
        if(data.size()>0){
            req.setAttribute("student",data.get(0));
        }
        req.getRequestDispatcher("updateStu.jsp").forward(req,resp);


    }
}

@WebServlet("/updateStu")
public class UpdateStuServlet extends HttpServlet {
    @Override
    protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        //接收参数
        req.setCharacterEncoding("UTF-8");

        String id = req.getParameter("id");
        String name = req.getParameter("name");
        String age = req.getParameter("age");
        String address = req.getParameter("address");

        //修改数据 使用student对象来传递参数
        Student student = new  Student(Integer.parseInt(id), name, Integer.parseInt(age), address);
        StudentDAO studentDAO = new StudentDAOImpl();
        int i = studentDAO.updateStudent(student);

        //传递
        req.setAttribute("msg", i>0 ? "修改成功" : "修改失败");
        req.getRequestDispatcher("updateStu.jsp").forward(req,resp);
    }
}

4.JSP

student.jsp

<%--
  Created by IntelliJ IDEA.
  User: asus
  Date: 2023/8/23
  Time: 13:39
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>Title</title>
    <link rel="stylesheet" href="https://cdn.bootcdn.net/ajax/libs/twitter-bootstrap/3.4.1/css/bootstrap.min.css"
          integrity="sha384-HSMxcRTRxnN+Bdg0JdbxYKrThecOKuH5zCYotlSAcp1+c8xmyTe9GYg1l9a69psu" crossorigin="anonymous">
    <style>
        .jumbotron {
            text-align: center;
            background: linear-gradient(45deg, #020031, #6d3353);
            color: white;
        }
    </style>
</head>
<body>
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>

<div class="jumbotron">
    <h1>欢迎来到学生管理系统</h1>
    <p>欢迎您</p>
    <p><a class="btn btn-primary btn-lg" href="/day01/addStu.jsp" role="button">添加学生</a></p>
</div>

<table class="table table-striped table-bordered table-hover table-condensed">
    <tr>
        <th>编号</th>
        <th>姓名</th>
        <th>年龄</th>
        <th>住址</th>
        <th style="width: 200px;">操作</th>
    </tr>

    <c:forEach var="s" items="${aaa}">
        <tr>
            <td>${s.id}</td>
            <td>${s.name}</td>
            <td>${s.age}</td>
            <td>${s.address}</td>
            <td>
                <button class="btn btn-danger" οnclick="delStu(${s.id})">删除</button>
                <a href="/day01/toUpdate?id=${s.id}" class="btn btn-primary">修改</a>
            </td>
        </tr>

    </c:forEach>
</table>

<script>
    function delStu(a){
        if(confirm("确定删除吗")){
            window.location.href = "/day01/delStu?id="+ a;
        }
    }
</script>
</body>
</html>

addStu.jsp

<%--
  Created by IntelliJ IDEA.
  User: asus
  Date: 2023/8/23
  Time: 13:41
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>Title</title>
    <!-- 最新版本的 Bootstrap 核心 CSS 文件 -->
    <link rel="stylesheet" href="https://cdn.bootcdn.net/ajax/libs/twitter-bootstrap/3.4.1/css/bootstrap.min.css"
          integrity="sha384-HSMxcRTRxnN+Bdg0JdbxYKrThecOKuH5zCYotlSAcp1+c8xmyTe9GYg1l9a69psu" crossorigin="anonymous">
    <style>
        fieldset {
            width: 500px;
            margin: 100px auto;
        }
    </style>
</head>
<body>
<fieldset>
    <legend>添加学生
        <span class="label label-primary">${msg}</span>
    </legend>
    <form class="form-horizontal" action="/day01/addStu" method="post">
        <div class="form-group">
            <label for="inputEmail3" class="col-sm-2 control-label">学生姓名</label>
            <div class="col-sm-10">
                <input name="name" type="text" class="form-control" id="inputEmail3" placeholder="请输入学生姓名">
            </div>
        </div>
        <div class="form-group">
            <label for="inputPassword3" class="col-sm-2 control-label">学生年龄</label>
            <div class="col-sm-10">
                <input name="age" type="text" class="form-control" id="inputPassword3" placeholder="请输入学生年龄">
            </div>
        </div>
        <div class="form-group">
            <label class="col-sm-2 control-label">学生住址</label>
            <div class="col-sm-10">
                <select name="address" id="">
                    <option>北京</option>
                    <option>上海</option>
                    <option>郑州</option>
                </select>
            </div>
        </div>

        <div class="form-group">
            <div class="col-sm-offset-2 col-sm-10">
                <button type="submit" class="btn btn-default btn-success">添加</button>
                <a href="/day01/stu" class="btn btn-default btn-danger">返回</a>
            </div>
        </div>
    </form>
</fieldset>
</body>
</html>
</body>
</html>

updateStu.jsp

<%--
  Created by IntelliJ IDEA.
  User: asus
  Date: 2023/8/23
  Time: 13:57
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>Title</title>
    <!-- 最新版本的 Bootstrap 核心 CSS 文件 -->
    <link rel="stylesheet" href="https://cdn.bootcdn.net/ajax/libs/twitter-bootstrap/3.4.1/css/bootstrap.min.css"
          integrity="sha384-HSMxcRTRxnN+Bdg0JdbxYKrThecOKuH5zCYotlSAcp1+c8xmyTe9GYg1l9a69psu" crossorigin="anonymous">

    <style>
        fieldset {
            width: 500px;
            margin: 100px auto;
        }
    </style>
</head>
<body>

<fieldset>
    <legend>修改学生</legend>

    <span class="label label-primary">${msg}</span>
    <form class="form-horizontal" action="/day01/updateStu" method="post">
        <input value="${student.id}" name="id" type="hidden">
        <div class="form-group">
            <label for="inputEmail3" class="col-sm-2 control-label">学生姓名</label>
            <div class="col-sm-10">
                <input value="${student.name}" name="name" type="text" class="form-control" id="inputEmail3"
                       placeholder="请输入学生姓名">
            </div>
        </div>
        <div class="form-group">
            <label class="col-sm-2 control-label">学生年龄</label>
            <div class="col-sm-10">
                <input name="age" value="${student.age}" type="text" class="form-control" id="inputPassword3"
                       placeholder="请输入学生年龄">
            </div>
        </div>
        <div class="form-group">
            <label class="col-sm-2 control-label">学生住址</label>
            <div class="col-sm-10">
                <select name="address">
                    <option value="北京" id="北京">北京</option>
                    <option value="上海" id="上海">上海</option>
                    <option value="郑州" id="郑州">郑州</option>
                </select>
            </div>
        </div>

        <div class="form-group">
            <div class="col-sm-offset-2 col-sm-10">
                <button type="submit" class="btn btn-default btn-success">修改</button>
                <a href="/day01/stu" class="btn btn-default btn-danger">返回</a>
            </div>
        </div>
    </form>
</fieldset>

<script>
    document.getElementById(${student.address}).selected = true;
</script>
</body>
</html>