简单的增删改查及数据分页
简单的增删改查及数据分页
一、结构图
二、相关代码
① 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>
三、效果截图
用户添加如下:
用户修改如下:
用户修改成功如下:
数据分页如下:
模糊查询结果如下:
本图文内容来源于网友网络收集整理提供,作为学习参考使用,版权属于原作者。
THE END
二维码