简单的增删改查及数据分页

简单的增删改查及数据分页

• 一、结构图
• 二、相关代码
• • • ① User类
    • ② Page类
    • ③ UserDao类
    • ④ CodeFilter类
    • ⑤ UserService 类
    • ⑥ IndexServlet 类
    • ⑦ UserDeleteServlet 类
    • ⑧ UserEditServlet 类
    • ⑨ edit.jsp
    • ⑩ index.jsp
    • ⑩① web.xml
    • ⑩② pom.xml
• 三、效果截图

一、结构图

二、相关代码

① User类

package com.allen.entity;
import java.util.Date;
public class User {

    private Integer id;
    private String trueName;
    private String loginName;
    private String loginPassword;
    private Integer role;
    private Date createTime;
    private Date updateTime;

    public Integer getId() {
        return id;
    }

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

    public String getTrueName() {
        return trueName;
    }

    public void setTrueName(String trueName) {
        this.trueName = trueName;
    }

    public String getLoginName() {
        return loginName;
    }

    public void setLoginName(String loginName) {
        this.loginName = loginName;
    }

    public String getLoginPassword() {
        return loginPassword;
    }

    public void setLoginPassword(String loginPassword) {
        this.loginPassword = loginPassword;
    }

    public Integer getRole() {
        return role;
    }

    public void setRole(Integer role) {
        this.role = role;
    }

    public Date getCreateTime() {
        return createTime;
    }

    public void setCreateTime(Date createTime) {
        this.createTime = createTime;
    }

    public Date getUpdateTime() {
        return updateTime;
    }

    public void setUpdateTime(Date updateTime) {
        this.updateTime = updateTime;
    }
}

② Page类

package com.allen.entity.ao;

import java.util.List;

public class Page<T> {
    private List<T> list;
    private long count;
    private int pageCount;
    private int page=1;
    private int size=10;

    public List<T> getList() {
        return list;
    }

    public void setList(List<T> list) {
        this.list = list;
    }

    public long getCount() {
        return count;
    }

    public void setCount(long count) {
        this.count = count;
    }

    public int getPageCount() {
        return pageCount;
    }

    public void setPageCount(int pageCount) {
        this.pageCount = pageCount;
    }

    public int getPage() {
        return page;
    }

    public void setPage(int page) {
        this.page = page;
    }

    public int getSize() {
        return size;
    }

    public void setSize(int size) {
        this.size = size;
    }
}

③ UserDao类

package com.allen.dao;

import com.allen.entity.User;

import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class UserDao {

    /**
     * 分页查询数据，模糊查询名字的数据
     *
     * @param page
     * @param size
     * @param name
     * @return
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public List<User> find(int page, int size, String name) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        Connection connection = DriverManager.getConnection("jdbc:mysql:///user-test?useSSL=false", "root", "123456");
        PreparedStatement statement;
        if (name != null && name != "") {
            statement = connection.prepareStatement("select * from user where true_name like ? limit ?,?");
            statement.setObject(1, "%" + name + "%");
            statement.setObject(2, (page - 1) * size);
            statement.setObject(3, size);
        } else {
            statement = connection.prepareStatement("select * from user limit ?,?");
            statement.setObject(1, (page - 1) * size);
            statement.setObject(2, size);
        }
        ResultSet resultSet = statement.executeQuery();
        List<User> list = new ArrayList<>();
        while (resultSet.next()) {
            User user = new User();
            user.setId(resultSet.getInt("id"));
            user.setTrueName(resultSet.getString("true_name"));
            user.setLoginName(resultSet.getString("login_name"));
            user.setLoginPassword(resultSet.getString("login_password"));
            user.setRole(resultSet.getInt("role"));
            //这里需要使用时间戳，才会显示年月日，时分秒
            user.setCreateTime(resultSet.getTimestamp("create_time"));
            user.setUpdateTime(resultSet.getTimestamp("update_time"));
            list.add(user);
        }
        resultSet.close();
        statement.close();
        connection.close();
        return list;
    }

    /**
     * 查询特定名字的人数或全部数据的数量
     *
     * @param name
     * @return
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public long count(String name) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        Connection connection = DriverManager.getConnection("jdbc:mysql:///user-test?useSSL=false", "root", "123456");
        PreparedStatement statement;
        if (name != null && name != "") {
            statement = connection.prepareStatement("select count(*) from user where true_name like ?");
            statement.setObject(1, "%" + name + "%");
        } else {
            statement = connection.prepareStatement("select count(*) from user");
        }
        ResultSet resultSet = statement.executeQuery();
        long count = 0;
        while (resultSet.next()) {
            count = resultSet.getLong(1);
        }
        resultSet.close();
        statement.close();
        connection.close();
        return count;
    }

    /**
     * 根据ID删除对应的数据
     *
     * @param id
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public void deleteById(int id) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        Connection connection = DriverManager.getConnection("jdbc:mysql:///user-test?useSSL=false", "root", "123456");
        PreparedStatement statement = connection.prepareStatement("delete from user where id=?");
        statement.setObject(1, id);
        int i = statement.executeUpdate();
        statement.close();
        connection.close();
    }

    /**
     * 增加新用户
     * @param user
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public void insert(User user) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        Connection connection = DriverManager.getConnection("jdbc:mysql:///user-test?useSSL=false", "root", "123456");
        PreparedStatement statement = connection.prepareStatement("insert into user(true_name,login_name,login_password,role,create_time)"+" values (?,?,?,?,now())");
        statement.setObject(1, user.getTrueName());
        statement.setObject(2, user.getLoginName());
        statement.setObject(3, user.getLoginPassword());
        statement.setObject(4, user.getRole());
        int i = statement.executeUpdate();
        statement.close();
        connection.close();
    }

    /**
     * 根据ID查找用户
     * @param id
     * @return
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public User findById(int id) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        Connection connection = DriverManager.getConnection("jdbc:mysql:///user-test?useSSL=false", "root", "123456");
        PreparedStatement statement = connection.prepareStatement("select * from user where id=?");
        statement.setObject(1,id);
        ResultSet resultSet = statement.executeQuery();
        User user=null;
        if (resultSet.next()) {
          user = new User();
          user.setId(id);
          user.setTrueName(resultSet.getString("true_name"));
          user.setLoginName(resultSet.getString("login_name"));
          user.setLoginPassword(resultSet.getString("login_password"));
          user.setRole(resultSet.getInt("role"));
          user.setCreateTime(resultSet.getTimestamp("create_time"));
          user.setUpdateTime(resultSet.getTimestamp("update_time"));
        }
        resultSet.close();
        statement.close();
        connection.close();
        return user;
    }

    /**
     * 修改用户信息
     * @param user
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public void update(User user) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        Connection connection = DriverManager.getConnection("jdbc:mysql:///user-test?useSSL=false", "root", "123456");
        PreparedStatement statement = connection.prepareStatement("update user set true_name=?,login_name=?,login_password=?,role=?,update_time=now()"+" where id=?");
        statement.setObject(1, user.getTrueName());
        statement.setObject(2, user.getLoginName());
        statement.setObject(3, user.getLoginPassword());
        statement.setObject(4, user.getRole());
        statement.setObject(5, user.getId());
        int i = statement.executeUpdate();
        statement.close();
        connection.close();
    }
}

④ CodeFilter类

package com.allen.filter;

import javax.servlet.*;
import javax.servlet.annotation.WebFilter;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;

@WebFilter("/*")
public class CodeFilter implements Filter {
    @Override
    public void init(FilterConfig filterConfig) throws ServletException {

    }

    @Override
    public void doFilter(ServletRequest servletRequest, ServletResponse servletResponse, FilterChain filterChain) throws IOException, ServletException {
        HttpServletRequest request = (HttpServletRequest) servletRequest;
        HttpServletResponse respons= (HttpServletResponse) servletResponse;
        request.setCharacterEncoding("UTF-8");
        respons.setCharacterEncoding("UTF-8");
        filterChain.doFilter(request,respons);
    }

    @Override
    public void destroy() {

    }
}

⑤ UserService 类

package com.allen.service;

import com.allen.dao.UserDao;
import com.allen.entity.User;
import com.allen.entity.ao.Page;

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

public class UserService {
    UserDao userDao = new UserDao();
    public Page<User> page(Page<User> page, String name) throws SQLException, ClassNotFoundException {
        int size = page.getSize();
        List<User> list = userDao.find(page.getPage(), size, name);
        page.setList(list);
        long count = userDao.count(name);
        page.setCount(count);
        page.setPageCount((int)((count%size==0)?(count/size):((count/size)+1)));
        return page;
    }

    public void delete(int id) throws SQLException, ClassNotFoundException {
        userDao.deleteById(id);
    }

    public void keep(User user) throws SQLException, ClassNotFoundException {
        if(user.getId()!=null){
            userDao.update(user);
        }else{
            userDao.insert(user);
        }
    }

    public User findById(int id) throws SQLException, ClassNotFoundException {
        return userDao.findById(id);
    }
}

⑥ IndexServlet 类

package com.allen.servlet;

import com.allen.entity.User;
import com.allen.entity.ao.Page;
import com.allen.service.UserService;

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;

@WebServlet("/index")
public class IndexServlet extends HttpServlet {
    UserService userService = new UserService();
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        try {
            request.setCharacterEncoding("UTF-8");
            Page<User> pageAo = new Page<>();
            String page = request.getParameter("page");
            if (page!=null && page!="") {
                pageAo.setPage(Integer.parseInt(page));
            }
            String size = request.getParameter("size");
            if (size!=null && size!="") {
                pageAo.setSize(Integer.parseInt(size));
            }
            Page<User> userPage = userService.page(pageAo, request.getParameter("name"));
            request.setAttribute("userPage",userPage);
            request.getRequestDispatcher("/WEB-INF/jsp/index.jsp").forward(request,response);
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

    }
}

⑦ UserDeleteServlet 类

package com.allen.servlet;

import com.allen.service.UserService;

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;
@WebServlet("/userDelete")
public class UserDeleteServlet extends HttpServlet {
    UserService userService = new UserService();
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        try {
            String id = request.getParameter("id");
            if (id!=null && id!="") {
                userService.delete(Integer.parseInt(id));
            }
            response.sendRedirect("index");
        }catch (Exception e){
            e.printStackTrace();
        }
    }
}

⑧ UserEditServlet 类

package com.allen.servlet;

import com.allen.entity.User;
import com.allen.service.UserService;

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;

@WebServlet("/userEdit")
public class UserEditServlet extends HttpServlet {
    private UserService userService = new UserService();
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        try {
            String id = request.getParameter("id");
            if (id!=null && id!="") {
                User user = userService.findById(Integer.parseInt(id));
                request.setAttribute("user",user);
            }
            request.getRequestDispatcher("/WEB-INF/jsp/edit.jsp").forward(request,response);
        }catch(Exception e){
            e.printStackTrace();
        }
    }

    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        try {
            String loginName = request.getParameter("loginName");
            String loginPassword = request.getParameter("loginPassword");
            String role = request.getParameter("role");
            String trueName = request.getParameter("trueName");
            String id = request.getParameter("id");
            User user = new User();
            user.setLoginName(loginName);
            user.setLoginPassword(loginPassword);
            user.setRole(Integer.parseInt(role));
            user.setTrueName(trueName);
            if (id!=null && id!="") {
                user.setId(Integer.parseInt(id));
            }
            userService.keep(user);
            response.sendRedirect(request.getContextPath()+"/index");
        }catch (Exception e){
            e.printStackTrace();
        }
    }
}

⑨ edit.jsp

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>Title</title>
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">
</head>
<body>
<div class="container">
    <div class="row">
        <div class="col-xs-12">
            <h1 class="text-center">用户编辑</h1>
            <form class="form-horizontal" method="post">
                <div class="form-group">
                    <label for="inputEmail3" class="col-sm-2 control-label">登录名</label>
                    <div class="col-sm-10">
                        <input type="text" name="loginName" class="form-control" id="inputEmail3" placeholder="登录名" value="${user.loginName}">
                    </div>
                </div>
                <div class="form-group">
                    <label for="inputPassword3" class="col-sm-2 control-label">密码</label>
                    <div class="col-sm-10">
                        <input type="text" name="loginPassword" class="form-control" id="inputPassword3" placeholder="密码" value="${user.loginPassword}">
                    </div>
                </div>
                <div class="form-group">
                    <label for="inputTrueName" class="col-sm-2 control-label">姓名</label>
                    <div class="col-sm-10">
                        <input type="text" name="trueName" class="form-control" id="inputTrueName" placeholder="姓名" value="${user.trueName}">
                    </div>
                </div>
                <div class="form-group">
                    <label for="inputRole" class="col-sm-2 control-label">角色</label>
                    <div class="col-sm-10">
                        <select name="role" class="form-control" id="inputRole" placeholder="角色" >
                            <option ${user.role==1?"selected":""} value="1">超级管理员</option>
                            <option ${user.role==2?"selected":""} value="2">普通用户</option>
                            <option ${user.role==3?"selected":""} value="3">用户管理员</option>
                        </select>
                    </div>
                </div>
                <div class="form-group">
                    <div class="col-sm-offset-2 col-sm-10">
                        <button type="submit" class="btn btn-primary">保存</button>
                        <a href="${pageContext.request.contextPath}/index" class="btn btn-default">取消</a>
                    </div>
                </div>
            </form>
        </div>
    </div>
</div>
</body>
</html>

⑩ index.jsp

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<html>
<head>
    <title>用户管理</title>
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">
</head>
<body>
<div class="container">
    <div class="row">
        <div class="col-xs-12"><h1>用户管理</h1></div>
        <div class="col-xs-9">
            <a href="${pageContext.request.contextPath}/userEdit" class="btn btn-success">用户添加</a>
        </div>
        <div class="col-xs-3">
            <div class="input-group">
                <input type="text" class="form-control" placeholder="请输入姓名" id="name" value="${param.name}" }>
                <span class="input-group-addon" onclick="findByName()">搜索</span>
            </div>
        </div>
        <div class="col-xs-12">
            <table class="table">
                <thead>
                <tr>
                    <th>ID</th>
                    <th>姓名</th>
                    <th>登录名</th>
                    <th>密码</th>
                    <th>角色</th>
                    <th>创建时间</th>
                    <th>修改时间</th>
                    <th>编辑</th>
                </tr>
                </thead>
                <tbody>
                <c:forEach items="${userPage.list}" var="u">
                    <tr>
                        <td>${u.id}</td>
                        <td>${u.trueName}</td>
                        <td>${u.loginName}</td>
                        <td>${u.loginPassword}</td>
                        <td>${u.role==1?"超级管理员":u.role==2?"普通用户":u.role==3?"用户管理员":""}</td>
                        <td>${u.createTime}</td>
                        <td>${u.updateTime}</td>
                        <td>
                            <a href="${pageContext.request.contextPath}/userDelete?id=${u.id}" class="btn btn-danger btn-sm">删除</a>
                            <a href="${pageContext.request.contextPath}/userEdit?id=${u.id}" class="btn btn-info btn-sm">修改</a>
                        </td>
                    </tr>
                </c:forEach>

                </tbody>
            </table>
            <%--            ${userPage.pageCount}--%>
<%--            &name=${param.name}信息回显并查询列表--%>
            <nav aria-label="Page navigation">
                <ul class="pagination">
                    <li>
                        <a href="?page=${userPage.page>1?(userPage.page-1):1}&name=${param.name}" aria-label="Previous">
                            <span aria-hidden="true">上一页</span>
                        </a>
                    </li>
                    <c:forEach begin="1" end="${userPage.pageCount}" var="u">
                        <li class="${u==userPage.page?"active":""}"><a href="?page=${u}&name=${param.name}">${u}</a></li>
                    </c:forEach>

                    <li>
                        <a href="?page=${userPage.page<userPage.pageCount?(userPage.page+1):userPage.pageCount}&name=${param.name}"
                           aria-label="Next">
                            <span aria-hidden="true">下一页</span>(${userPage.count})
                        </a>
                    </li>
                </ul>
            </nav>
        </div>
    </div>
</div>
<script>
    function findByName() {
        let name = document.getElementById("name").value;
        location.href="?name="+name;
    }
</script>
</body>
</html>

⑩① web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns="http://xmlns.jcp.org/xml/ns/javaee"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_4_0.xsd"
         version="4.0">
    <welcome-file-list>
        <welcome-file>index</welcome-file>
    </welcome-file-list>
</web-app>

⑩② pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.example</groupId>
    <artifactId>user-test</artifactId>
    <version>1.0-SNAPSHOT</version>
    <name>user-test</name>
    <packaging>war</packaging>

    <properties>
        <maven.compiler.target>1.8</maven.compiler.target>
        <maven.compiler.source>1.8</maven.compiler.source>
        <junit.version>5.7.1</junit.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>javax.servlet-api</artifactId>
            <version>4.0.1</version>
            <scope>provided</scope>
        </dependency>
        <dependency>
            <groupId>org.junit.jupiter</groupId>
            <artifactId>junit-jupiter-api</artifactId>
            <version>${junit.version}</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.junit.jupiter</groupId>
            <artifactId>junit-jupiter-engine</artifactId>
            <version>${junit.version}</version>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-war-plugin</artifactId>
                <version>3.3.1</version>
            </plugin>
        </plugins>
    </build>
</project>

三、效果截图

用户添加如下：

用户修改如下：

用户修改成功如下：

数据分页如下：

模糊查询结果如下：