1.准备好数据库
2.编写实体类People
package com.qf.info;
import java.sql.Date;
import java.sql.Timestamp;
public class People{
private Integerid;
private Stringpassword;
private Stringname;
private Stringgender;
private Stringemail;
private Datebirthday;
private TimestampcreateTime;
private TimestampupdateTime;
public People(){
}
public People(int id, String name, String gender, String email, Date birthday) {
this.id = id;
this.name = name;
this.gender = gender;
this.email = email;
this.birthday = birthday;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
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 String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public Timestamp getCreateTime() {
return createTime;
}
public void setCreateTime(Timestamp createTime) {
this.createTime = createTime;
}
public Timestamp getUpdateTime() {
return updateTime;
}
public void setUpdateTime(Timestamp updateTime) {
this.updateTime = updateTime;
}
}
3.添加接口方法
3.1.删除
public interface PeopleDao{
PageBean<People> getPageDate(String name, String gender, Date begin, Date end,int currentPage);
void deletePeople(Integer id);
}
3.2.编辑修改
public interface PeopleDao{
PageBean<People> getPageDate(String name, String gender, Date begin, Date end,int currentPage);
void deletePeople(Integer id);
People getPeopleById(Integer id);
void editPeople(Integer id, String name, String gender, Date birthday, String email);
}
3.3.添加
4.实现接口PeopleDaoImpl
package com.qf.dao.impl;
import com.qf.dao.PeopleDao;
import com.qf.info.People;
import com.qf.utils.DataUtils;
import com.qf.utils.DbUtils;
import com.qf.utils.PageBean;
import java.sql.*;
import java.util.Date;
import java.util.List;
public class PeopleDaoImplimplements PeopleDao{
@Override
public PageBean<People> getPageDate(String name, String gender, Date begin, Date end,int currentPage) {
PageBean<People> pageBean =new PageBean<>();
/**
* 分页的时候索引的位置：如果为第1页，开始索引为0
* 如果为第5页，开始索引为40
* 如果为第8页，开始索引为70
* 目前先考虑正常情况，代码这里的currentPage可以是一个有误的值，有误的值是因为用户的恶意操作：
* http://localhost:8081/web_project_war_exploded/people?currentPage=-20
**/
int total = getTotal(name, gender, begin, end);
if (total ==0) {
pageBean.setDatas(null);
return pageBean;
}
pageBean.setTotal(total);
int pageSize = pageBean.getPageSize();//pageSize每页的数量，默认为10
StringBuffer commonSql =new StringBuffer("select id, name, gender, email, birthday, " +
"createTime, updateTime from people where 1 = 1 ");
if (null != name && !"".equals(name.trim())) {
commonSql.append(" and name like ? ");
}
if (!"-1".equals(gender)) {
commonSql.append(" and gender = ? ");
}
if (null != begin) {
commonSql.append(" and birthday > ? ");
}
if (null != end) {
commonSql.append(" and birthday < ? ");
}
commonSql.append(" limit ?,? ");
// select id, name, gender, email, birthday, createTime, updateTime
// from people where 1 = 1 and gender = ? and birthday < ? limit ?, ?
Connection connection = DbUtils.getConnection();
try {
PreparedStatement ps = connection.prepareStatement(commonSql.toString());
int i =0;//由于不知道那个蚕食是第一个，通过 ++i 来确定顺序
//与上面的的顺序必须保持一致
if (null != name && !"".equals(name.trim())) {
ps.setObject(++i,"%" + name +"%");
}
if (!"-1".equals(gender)) {
ps.setObject(++i, gender);
}
if (null != begin) {
ps.setObject(++i, begin);
}
if (null != end) {
ps.setObject(++i, end);
}
int totalPage = pageBean.getTotalPage();//获取总页数
//设置当前页数
pageBean.setCurrentPage(currentPage);
//取正常值
currentPage = pageBean.getCurrentPage();
int beginIndex =(currentPage -1) * pageSize;//limit ?
ps.setObject(++i, beginIndex);//设置分页的开始索引位置
ps.setObject(++i, pageSize);// 每页的数量
ResultSet rs = ps.executeQuery();
List<People> list = DataUtils.getAll(People.class, rs);
pageBean.setDatas(list);//往PageBean中设置当前分页的数据
DbUtils.colse(rs, ps, connection);
} catch (Exception ex) {
ex.printStackTrace();
}
return pageBean;
}
private int getTotal(String name, String gender, Date begin, Date end) {
int total =0;
StringBuffer countCommonSql =new StringBuffer("select count(*) from people where 1 = 1 ");
if (null != name && !"".equals(name.trim())) {
countCommonSql.append(" and name like ? ");
}
if (!"-1".equals(gender)) {
countCommonSql.append(" and gender = ? ");
}
if (null != begin) {
countCommonSql.append(" and birthday > ? ");
}
if (null != end) {
countCommonSql.append(" and birthday < ? ");
}
Connection connection = DbUtils.getConnection();
try {
PreparedStatement ps = connection.prepareStatement(countCommonSql.toString());
int i =0;
if (null != name && !"".equals(name.trim())) {
ps.setObject(++i,"%" + name +"%");
}
if (!"-1".equals(gender)) {
ps.setObject(++i, gender);
}
if (null != begin) {
ps.setObject(++i, begin);
}
if (null != end) {
ps.setObject(++i, end);
}
ResultSet rs = ps.executeQuery();//执行查询
rs.next();//原因是查询的数据就一行一列的数据, 绝对不会产生多行, 因为是count(*)
total = rs.getInt(1);
DbUtils.colse(rs, ps, connection);
} catch (Exception e) {
e.printStackTrace();
}
return total;
}
@Override
public void deletePeople(Integer id) {
Connection conn = DbUtils.getConnection();
String sql ="delete from people where id = ?";
try {
PreparedStatement ps = conn.prepareStatement(sql);
ps.setObject(1, id);
ps.executeUpdate();
DbUtils.colse(null, ps, conn);
} catch (Exception e) {
e.printStackTrace();
}
}
@Override
public People getPeopleById(Integer id) {
Connection connection = DbUtils.getConnection();
String sql ="select id, name, gender, email, birthday from people where id = ?";
People people =null;
try {
PreparedStatement ps = connection.prepareStatement(sql);
ps.setObject(1, id);
ResultSet rs = ps.executeQuery();
rs.next();
people =new People(rs.getInt("id"), rs.getString("name"),
rs.getString("gender"), rs.getString("email"), rs.getDate("birthday"));
DbUtils.colse(rs, ps, connection);
} catch (Exception e) {
e.printStackTrace();
}
return people;
}
@Override
public void editPeople(Integer id, String name, String gender, Date birthday, String email) {
Connection conn = DbUtils.getConnection();
String sql ="update people set name = ?, birthday = ?, gender = ?, email = ?, updateTime =? where id = ?";
try {
PreparedStatement ps = conn.prepareStatement(sql);
Timestamp timestamp =new Timestamp(new Date().getTime());
ps.setObject(1, name);
ps.setObject(2, birthday);
ps.setObject(3, gender);
ps.setObject(4, email);
ps.setObject(5, timestamp);
ps.setObject(6, id);
ps.executeUpdate();
DbUtils.colse(null,ps,conn);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
5.编写service
5.1.编写接口PeopleService
package com.qf.service;
import com.qf.info.People;
import com.qf.utils.PageBean;
import java.util.Date;
public interface PeopleService{
//获取分页的数据
PageBean<People> getPageDate(String name, String gender, Date begin, Date end,int currentPage);
void deletePeople(Integer id);
People getPeopleById(Integer id);
void editPeople(Integer id, String name, String gender, Date birthday, String email);
}
5.2.实现接口PeopleServiceImpl
package com.qf.service.impl;
import com.qf.dao.PeopleDao;
import com.qf.dao.impl.PeopleDaoImpl;
import com.qf.info.People;
import com.qf.service.PeopleService;
import com.qf.utils.PageBean;
import java.util.Date;
public class PeopleServiceImplimplements PeopleService{
private PeopleDaopeopleDao =new PeopleDaoImpl();
@Override
public PageBean<People> getPageDate(String name, String gender, Date begin, Date end,int currentPage) {
return peopleDao.getPageDate(name, gender, begin, end, currentPage);
}
@Override
public void deletePeople(Integer id) {
peopleDao.deletePeople(id);
}
@Override
public People getPeopleById(Integer id) {
return peopleDao.getPeopleById(id);
}
@Override
public void editPeople(Integer id, String name, String gender, Date birthday, String email) {
peopleDao.editPeople(id,name,gender,birthday,email);
}
}
6.编写Servlet
package com.qf.servlet;
import com.qf.info.People;
import com.qf.service.PeopleService;
import com.qf.service.impl.PeopleServiceImpl;
import com.qf.utils.PageBean;
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.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
@WebServlet(value ="/people", name ="PeopleServlet")
public class PeopleServletextends HttpServlet{
private PeopleServicepeopleService =new PeopleServiceImpl();
@Override
protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException{
String method = req.getParameter("method");
if (null == method ||"".equals(method)) {
getList(req, resp);
} else if ("del".equals(method)) {
delete(req, resp);
} else if ("edit".equals(method)) {
edit(req, resp);
} else if ("update".equals(method)) {
} else if ("add".equals(method)) {
} else if ("toEdit".equals(method)) {
toEdit(req, resp);
} else {
resp.sendRedirect("error.jsp");
}
}
private void getList(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException{
String name = req.getParameter("name");
String gender = req.getParameter("gender");
String beginDate = req.getParameter("beginDate");
String endDate = req.getParameter("endDate");
String currentPage = req.getParameter("currentPage");
gender =null == gender ||"".equals(gender) ?"-1" : gender;//如果gender为空，查询所有
Date begin =null;
if (null != beginDate && !"".equals(beginDate)) {
try {
begin = dateFormat(beginDate,"yyyy-MM-dd");
} catch (ParseException e) {
resp.sendRedirect("error.jsp");
e.printStackTrace();
}
}
Date end =null;
if (null != endDate && !"".equals(endDate)) {
try {
end = dateFormat(endDate,"yyyy-MM-dd");
} catch (ParseException e) {
resp.sendRedirect("error.jsp");
e.printStackTrace();
}
}
int curPage =1;
if (null != currentPage && !"".equals(currentPage)) {
try {
curPage = Integer.valueOf(currentPage);
} catch (NumberFormatException e) {
resp.sendRedirect("error.jsp");
return;
}
}
PageBean<People> pageBean =peopleService.getPageDate(name, gender, begin, end, curPage);
req.setAttribute("pageBean", pageBean);
req.getRequestDispatcher("people.jsp").forward(req, resp);
}
private void edit(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException{
String method = req.getMethod();
if ("GET".equals(method)) {
resp.sendRedirect("error.jsp");
return;
}
String id = req.getParameter("id");
String name = req.getParameter("name");
String email = req.getParameter("email");
String gender = req.getParameter("gender");
String birthday = req.getParameter("birthday");
try {
Integer peopleId = Integer.parseInt(id);
Date date = dateFormat(birthday,"yyyy-MM-dd");
peopleService.editPeople(peopleId, name, gender, date, email);
resp.sendRedirect("people");
} catch (Exception e) {
e.printStackTrace();
}
}
private void toEdit(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException{
String id = req.getParameter("id");
try {
Integer peopleId = Integer.parseInt(id);
People people =peopleService.getPeopleById(peopleId);
req.setAttribute("people", people);
req.getRequestDispatcher("edit.jsp").forward(req, resp);
} catch (Exception e) {
e.printStackTrace();
}
}
private void delete(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException{
//&gender=${param.gender}&name=${param.name}&beginDate=${param.beginDate}
// &endDate=${param.endDate}
String id = req.getParameter("id");
String name = req.getParameter("name");
String gender = req.getParameter("gender");
gender =null == gender ||"".equals(gender) ?"-1" : gender;
String beginDate = req.getParameter("beginDate");
String endDate = req.getParameter("endDate");
Date begin =null;
if (null != beginDate && !"".equals(beginDate)) {
try {
begin = dateFormat(beginDate,"yyyy-MM-dd");
} catch (ParseException e) {
resp.sendRedirect("error.jsp");
e.printStackTrace();
}
}
Date end =null;
if (null != endDate && !"".equals(endDate)) {
try {
end = dateFormat(endDate,"yyyy-MM-dd");
} catch (ParseException e) {
resp.sendRedirect("error.jsp");
e.printStackTrace();
}
}
try {
Integer peopleId = Integer.parseInt(id);
peopleService.deletePeople(peopleId);
} catch (Exception e) {
resp.sendRedirect("error.jsp");
return;
}
resp.sendRedirect("people?name=" + name +"&gender=" + gender +"&beginDate=" + beginDate +"&endDate=" + endDate);
}
private Date dateFormat(String dayeStr, String pattern) throws ParseException{
SimpleDateFormat simpleDateFormat =new SimpleDateFormat(pattern);
Date date = simpleDateFormat.parse(dayeStr);
return date;
}
}
7.JSP设计
7.1.信息展示页面people.jsp
<%--
Created by IntelliJ IDEA.
User: Administrator
Date: 2020/4/16
Time: 11:09
To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %><%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %><%@ taglib uri="http://java.sun.com/jsp/jstl/fmt" prefix="fmt" %><head> <meta charset="UTF-8"> <title>Title</title> <link rel="stylesheet" href="css/bootstrap.min.css"> <style> .btn-content {
margin:20px 0 5px;
}
.user-info {
margin-right:30px;
}
.logout-link {
padding-left:10px;
}
table tfoot td {
text-align:center;
}
table tfoot .foot-pager {
display:inline-block;
}
table tfoot .page-info {
padding-top:10px;
float:left;
}
.foot-pager ul {
margin:0;
}
</style>
</head>
<body>
<div class="navbar navbar-inverse navbar-static-top">
<div class="container-fluid">
<div class="navbar-header">
<a class="navbar-brand active">用户信息展示</a>
</div>
<p class="navbar-text navbar-right user-info">
欢迎您，${sessionScope.user.realname} <a href="#" class="navbar-link logout-link">退出登录</a>
</p>
</div></div><div class="container">
<div class="col-xs-10 col-xs-push-1">
<form class="form-inline" action="people">
<div class="form-group">
<label for="name">用户名:</label>
<input type="text" autocomplete="off" value="${param.name}" class="form-control" name="name" id="name">
</div>
<div class="form-group">
<label for="gender">性 别:</label>
<select class="form-control" id="gender" name="gender">
param.gender出现null是在登陆成功之后重定向到people对应Serclet的时候
-->
<option
<c:if test="${empty param.gender || param.gender =='-1'}">selected</c:if> value="-1">所有
</option> <option
<c:if test="${param.gender =='F'}">selected</c:if> value="F">女
</option> <option
<c:if test="${param.gender =='M'}">selected</c:if> value="M">男
</option>
</select>
</div>
<div class="form-group">
<label for="birthday">生 日</label>
<input value="${param.beginDate}" type="text" id="birthday" class="form-control" name="beginDate">-
<input value="${param.endDate}" type="text" class="form-control" name="endDate">
</div>
<div class="form-group">
<button class="btn btn-danger">搜索</button>
</div>
</form>
</div>
<div class="col-xs-10 col-xs-push-1 btn-content">
<a href="#" class="btn btn-primary">添加</a>
</div>
<div class="col-xs-10 col-xs-push-1 data-content">
<table class="table table-bordered table-hover table-striped">
<thead>
<tr>
<th>ID</th>
<th>姓名</th>
<th>性别</th>
<th>邮件</th>
<th>生日</th>
<th>更新时间</th>
<th>创建时间</th>
<th>操作</th>
</tr>
</thead>
<c:if test="${empty requestScope.pageBean.datas}">
<tr>
<td colspan="8">没有更多数据了</td>
</tr>
</c:if>
<c:if test="${not empty requestScope.pageBean.datas}">
<tbody>
<c:forEach items="${requestScope.pageBean.datas}" var="p">
<tr>
<td>${p.id}</td>
<td>${p.name}</td>
<td>${p.gender == 'F' ? '女' : '男'}</td>
<td>${p.email}</td>
<td>${p.birthday}</td>
<td>${p.updateTime}</td>
<td>${p.createTime}</td>
<td>
<a href="people?id=${p.id}&method=toEdit" class="btn btn-sm btn-success">编辑</a> <a href="people?id=${p.id}&method=del&name=${param.name}&gender=${param.gender}&beginDate=${param.beginDate}&endDate=${param.endDate}" class="btn btn-sm btn-danger">删除</a>
</td>
</tr>
</c:forEach>
</tbody>
<tfoot>
<tr>
<td colspan="8">
<span class="page-info">
当前第 ${requestScope.pageBean.currentPage}/${requestScope.pageBean.totalPage} 页
</span>
<nav class="foot-pager">
<ul class="pagination">
<c:if test="${requestScope.pageBean.hasPre}">
<li>
<a href="people?currentPage=1&name=${param.name}&gender=${param.gender}&beginDate=${param.beginDate}&endDate=${param.endDate}">首页</a>
</li>
<li>
<a href="people?currentPage=${requestScope.pageBean.currentPage - 1}&name=${param.name}&gender=${param.gender}&beginDate=${param.beginDate}&endDate=${param.endDate}">上一页</a>
</li>
</c:if>
<c:if test="${requestScope.pageBean.hasNext}">
<li>
<a href="people?currentPage=${requestScope.pageBean.currentPage + 1}&name=${param.name}&gender=${param.gender}&beginDate=${param.beginDate}&endDate=${param.endDate}">下一页</a>
</li>
<li>
<a href="people?currentPage=${requestScope.pageBean.totalPage}&name=${param.name}&gender=${param.gender}&beginDate=${param.beginDate}&endDate=${param.endDate}">尾页</a>
</li>
</c:if>
</ul>
</nav>
</td>
</tr>
</tfoot>
</c:if>
</table>
</div>
</div>
</body>
</html>
7.2.编辑页面
<%--
Created by IntelliJ IDEA.
User: Administrator
Date: 2020/4/17
Time: 11:31
To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/fmt" prefix="fmt" %><html><head>
<title>修改用户信息</title>
<link rel="stylesheet" href="css/bootstrap.min.css"></head><body><div class="container-fluid">
<div class="row">
<div class="col-xs-6 col-xs-push-3 edit-panel">
<div class="panel panel-primary">
<div class="panel-heading">
<h3 class="panel-title">编辑用户</h3>
</div>
<div class="panel-body">
<form action="people" method="post">
type为hidden的时候，那么该input输入依然为表单的数据，但是不展示.
但是提交的时候，还是会作为表单的数据提交：people?id=34&name=zhangs&gedner=F
-->
<input type="hidden" name="id" value="${requestScope.people.id}">
<input type="hidden" name="method" value="edit">
<div class="form-group">
<label for="name">用户名:</label>
<input name="name" value="${requestScope.people.name}" autocomplete="off" id="name" class="form-control">
</div>
<div class="form-group">
<label for="gender">性 别:</label>
<select class="form-control" id="gender" name="gender">
<option <c:if test="${requestScope.people.gender == 'F'}">selected</c:if> value="F">女</option>
<option <c:if test="${requestScope.people.gender == 'M'}">selected</c:if> value="M">男</option> </select>
</div>
<div class="form-group">
<label for="email">邮 件:</label>
<input value="${requestScope.people.email}" name="email" autocomplete="off" id="email" class="form-control">
</div>
<div class="form-group">
<label for="birthday">生 日:</label> <input name="birthday"
value="<fmt:formatDate value='${requestScope.people.birthday}' pattern='yyyy-MM-dd'></fmt:formatDate>"
id="birthday" autocomplete="off" id="birthday" class="form-control">
</div>
<div class="form-group">
<button class="btn btn-block btn-success">提交</button>
</div>
</form>
</div>
</div>
</div>
</div>
</div>
</body>
</html>
7.3.error.jsp页面
<%--
Created by IntelliJ IDEA.
User: jingjing
Date: 2020/4/18
Time: 20:03
To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %><html><head> <title>error</title></head><body>非法操作，您的账号已被锁定
</body></html>
8.效果展示
