使用用MVC制作的客户信息管理系统，有点难度的就是模糊查询和分页了~
模糊查询需求
1.各个查询条件必须包含。
2.当条件为空时，查询所有的数据。
分页需求
1.以表格显示。
2.每页数据8条。
3.当页码达到6以后，起始的页码后移。如图
4.当页码为1时，上一页不显示&页码为最后一页时，下一页不显示（后端控制）
模糊查询难点
1.在数据查询时，对数据的分析匹配，SQl语句的拼接。
分页难点
1.查询后翻页后查询条件丢失的问题。
2.页码的控制
3.页码的url条件拼接
代码较多~想要代码的可以下载项目包和Sql包的：https://download.csdn.net/download/wangzijian121/11504219
也可以加我的QQ：1219116968
前端代码
find.jsp
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%--
Created by IntelliJ IDEA.
User: wangzijian
Date: 2019/8/5
Time: 20:45
To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>Title</title>
</head>
<body>
<h2>高级搜索</h2>
<form action="<c:url value='/CustomerServlet?method=query'/>" method="get">
<input type="hidden" name="method" value="query">
姓名：<input type="text" name="name"><br>
年龄：<input type="text" name="age"><br>
手机：<input type="text" name="phone">
<input type="submit" value="提交">
</form>
</body>
</html>
list.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</title>
<style>
table{
border: 1px solid red;
}
table tr{
border: 1px solid red;
}
table td{
border: 1px solid red;
}
</style>
</head>
<body>
<center>
<table>
<tr>
<td>UUID</td>
<td>姓名</td>
<td>年龄</td>
<td>电话号</td>
<td>操作</td>
</tr>
<c:forEach items="${pb.beanList}" var="obj">
<tr>
<td>${obj.id}</td>
<td>${obj.name}</td>
<td>${obj.age}</td>
<td>${obj.phone}</td>
<td><a href="<%request.getContextPath();%>CustomerServlet?method=findById&id=${obj.id}">编辑</a></td>
<td><a href="<%request.getContextPath();%>CustomerServlet?method=deleteById&id=${obj.id}">删除</a></td>
</tr>
</c:forEach>
</table>
第${pb.pageCode}页/共${pb.pageTotal}页
<a href="${pb.url}&pageCode=1">首页</a>
<c:if test="${pb.pageCode>1}">
<a href="${pb.url}&pageCode=${pb.pageCode-1}">上一页</a>
</c:if>
<c:choose>
<%--如果总的页码数不足10页，那么把所有的页面都显示出来--%>
<c:when test="${pb.pageTotal<=10}">
<c:set var="begin" value="1"/>
<c:set var="end" value="${pb.pageTotal}"/>
</c:when>
<%--当总页大于10 时，通过公式计算出begin 和 end --%>
<c:otherwise>
<%--如果设置页码是10页，保留6个位置时，左面的距离5个，右面的距离4个。--%>
<c:set var="begin" value="${pb.pageCode-5}"/>
<c:set var="end" value="${pb.pageCode+4}"/>
<%--头溢出：当左面的页码少于5个时，页码会为负数。--%>
<c:if test="${begin<1}" >
<c:set var="begin" value="1"/>
<c:set var="end" value="10"/>
</c:if>
<%--尾溢出：当页码达到最大后，加4后尾部会超出4个，当尾部超出后，设置尾部的页码为最大的页码--%>
<c:if test="${end>pb.pageTotal}">
<c:set var="begin" value="${pb.pageTotal-9}"/>
<c:set var="end" value="${pb.pageTotal}"/>
</c:if>
</c:otherwise>
</c:choose>
<%--循环显示所有的列表--%>
<c:forEach var="i" begin= "${begin}" end="${end}">
<%--在超链接中保留参数，在条件查询后也可以通过链接跳转--%>
<a href="${pb.url}&pageCode=${i}"><c:out value="${i}"/></a>
</c:forEach>
<c:if test="${pb.pageCode<pb.pageTotal}">
<a href="${pb.url}&pageCode=${pb.pageCode+1}">下一页</a>
</c:if>
<a href="${pb.url}&pageCode=${pb.pageTotal}">尾页</a>
</center>
</body>
</html>
后端代码
Customer.java
package cn.wang.domain;
public class Customer {
private String id;
private String name;
private String age;
private String phone;
public Customer() {
super();
}
public Customer(String id, String name, String age, String phone) {
this.id = id;
this.name = name;
this.age = age;
this.phone = phone;
}
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public java.lang.String getAge() {
return age;
}
public void setAge(String age) {
this.age = age;
}
public java.lang.String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
}
pageBean.java
package page;
import java.util.List;
public class PageBean<T> {
private int pageCode;//当前页码
//private int pageTotal;//总页数
private int listnum;//每页的记录数
private int listTotal;//总记录数
private List<T> beanList;//当前页的记录
private String url;//url后的条件
public String getUrl() {
return url;
}
public void setUrl(String url) {
this.url = url;
}
public int getPageCode() {
return pageCode;
}
public void setPageCode(int pageCode) {
this.pageCode = pageCode;
}
public int getPageTotal() {
//根据总记录数/每页的记录数得出总页数。
int pageTotal=listTotal/listnum;
return listTotal%listnum==0 ? pageTotal :pageTotal+1;
}
public int getListnum() {
return listnum;
}
public void setListnum(int listCode) {
this.listnum = listCode;
}
public int getListTotal() {
return listTotal;
}
public void setListTotal(int listTotal) {
this.listTotal = listTotal;
}
public List<T> getBeanList() {
return beanList;
}
public void setBeanList(List<T> beanList) {
this.beanList = beanList;
}
@Override
public String toString() {
return "PageBean{" +
"pageCode=" + pageCode +
", listnum=" + listnum +
", listTotal=" + listTotal +
", beanList=" + beanList +
'}';
}
}
CustomerServlet.java
package cn.wang.servlet;
import cn.itcast.commons.CommonUtils;
import cn.itcast.servlet.BaseServlet;
import cn.wang.domain.Customer;
import cn.wang.service.CustomerService;
import page.PageBean;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
public class CustomerServlet<query> extends BaseServlet {
private CustomerService cs = new CustomerService();
/**
* 添加客户
*/
public String add(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
Customer customer = CommonUtils.toBean(req.getParameterMap(), Customer.class);
customer.setId(CommonUtils.uuid());
cs.add(customer);
req.setAttribute("msg", "插入用户成功！");
System.out.printf("插入用户成功！");
return "f:/succ.jsp";
}
/**
* 查询所有客户
* 分页
*/
public String select(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
/**
* 1.获取页面传递的pageCode 和listnum 调用Service方法
* 得到Pagebean保存到request域
* 2.给定page
* 3.转发到list域
*/
/**
* 得到pagecode
* 如果pageCode为空，默认为 1
* 如果参数存在，转为 int类型
*/
int pageCode=getPageCode(req);
/**
* 定义listnum
* 每一页显示数据的数量
*/
int listnNum =8;
PageBean<Customer> pb=cs.select(pageCode,listnNum);
pb.setUrl(getUrl(req));
req.setAttribute("pb",pb);
return "f:/list.jsp";
}
/**
* 获得pageCode（当前页数的方法）的方法
* @param request
* @return
*/
private int getPageCode(HttpServletRequest request){
String value=request.getParameter("pageCode");
if(value==null){
return 1;
}else{
return Integer.parseInt(value);
}
}
/**
* 根据id查找
*/
public String findById(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
String id = req.getParameter("id");
Customer customer = cs.findById(id);
req.setAttribute("customer", customer);
return "f:/updateCustomer.jsp";
}
public String update(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
Customer customer = CommonUtils.toBean(req.getParameterMap(), Customer.class);
cs.update(customer, customer.getId());
req.setAttribute("msg", "更新成功！");
return "f:/succ.jsp";
}
/**
* 删除（根据 UUID删除）
*/
public String deleteById(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
String id = req.getParameter("id");
cs.deleteById(id);
/* List<Customer> list = cs.select(1,1);
req.setAttribute("list", list);*/
return "f:/list.jsp";
}
/**
* 高级查找
* 分页处理
*/
public String query(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
/**
* 把条件封装发到Customer 对象中。
*/
Customer criteria =CommonUtils.toBean(req.getParameterMap(),Customer.class);
/**
* 1.得到pagecode
* 2.给定pageTotal
* 3.使用pageCode 和PageTotal和条件对象调用service 方法
* 4.把pagebean保存在request域中
* 5.转发到list.jsp
*/
int pageCode=getPageCode(req);
int listnNum =8;
PageBean<Customer> pb= cs.query(criteria,pageCode,listnNum);
pb.setUrl(getUrl(req));
req.setAttribute("pb",pb);
return "/list.jsp";
}
/**
* 将当前页码的url提取出来
* Servlet层中只控制条件，不控制pageCode ,pageCode由页面控制，传到Servlet
* @param request
* @return
*/
private String getUrl(HttpServletRequest request){
String contextPath=request.getContextPath();
String servletpath=request.getServletPath();
String queryString=request.getQueryString();
/**
* 判断是否包含pageCode
*/
if(queryString.contains("&pageCode=")){
int index =queryString.lastIndexOf("&pageCode");
queryString=queryString.substring(0,index);
}
return contextPath+servletpath+"?"+queryString;
}
/**
* 处理GET请求方式的编码问题
*/
private Customer encoding(Customer criteria) throws UnsupportedEncodingException {
/**
* 处理三样数据
*/
String name=criteria.getName();
String age=criteria.getAge();
String phone=criteria.getPhone();
if(name!=null&& !name.trim().isEmpty()){
name=new String(name.getBytes("ISO-8859-1"),"utf-8");
criteria.setName(name);
}
if(age!=null&& !age.trim().isEmpty()){
age=new String(age.getBytes("ISO-8859-1"),"utf-8");
criteria.setName(age);
}
if(phone!=null&& !phone.trim().isEmpty()){
phone=new String(phone.getBytes("ISO-8859-1"),"utf-8");
criteria.setName(phone);
}
return criteria;
}
}
CustomerService.java
package cn.wang.service;
import cn.wang.dao.CustomerDao;
import cn.wang.domain.Customer;
import page.PageBean;
import java.util.List;
public class CustomerService {
private CustomerDao customerDao=new CustomerDao();
/**
* 添加客户
* @param customer
*/
public void add(Customer customer){
customerDao.add(customer);
}
/**
* 查询用户
*/
public PageBean<Customer> select(int pageCode , int listNum){
return customerDao.select(pageCode,listNum);
}
/**
* 通过uuid寻找用户
* @return
*/
public Customer findById(String id){
Customer customer =customerDao.findById( id );
return customer;
}
/**
* 更新用户信息
* @param customer
* @param id
*/
public void update(Customer customer,String id){
customerDao.update(customer,id);
}
/**
* 根据uuid删除用户
*/
public void deleteById(String id){
customerDao.deleteById(id);
}
/**
* 多条件组合查询
* @param criteria
* @return
*/
public PageBean<Customer> query(Customer criteria,int pageCode,int listNum) {
return customerDao.query(criteria,pageCode,listNum);
}
}
CustomerDao.java
package cn.wang.dao;
import cn.wang.domain.Customer;
import jdbc_tools.TxQueryRunner;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import page.PageBean;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class CustomerDao {
private QueryRunner qr = new TxQueryRunner();
/**
* 添加客户
*
* @return
*/
public void add(Customer customer) {
String sql = " insert into customer values(?,?,?,?)";
Object[] params = {customer.getId(), customer.getName(), customer.getAge(), customer.getPhone()};
try {
qr.update(sql, params);
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
/**
* 查找所有的客户信息
*
* @return
*/
public PageBean<Customer> select(int pageCode, int listum) {
try {
/**
* 1.创建Pagebea对象
* 2.设置pb的 pageCode 和listnum
* 3.得到ListTotal 和beanList
* 4.根据getPageTotal 获取到分页的页数。
*/
PageBean<Customer> pb = new PageBean<Customer>();
pb.setPageCode(pageCode);
pb.setListnum(listum);
/**
* 得到listTotal
*/
String sql = "select Count(*) from customer";
Number num = (Number) qr.query(sql, new ScalarHandler());
pb.setListTotal(num.intValue());
/**
* 得到beanlist
*/
sql = "select * from customer limit ?,?";
List<Customer> list = qr.query(sql, new BeanListHandler<Customer>(Customer.class), (pageCode-1)*listum, listum);
pb.setBeanList(list);
return pb;
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
/**
* 通过id查找对象
* @param id
* @return
*/
public Customer findById(String id){
String sql="select * from customer where id=?";
Object [] params={id};
Customer customer = null;
try {
customer=qr.query( sql,new BeanHandler<Customer>(Customer.class ),params );
} catch (SQLException e) {
e.printStackTrace();
}
return customer;
}
/**
* 更新用户信息
*/
public void update(Customer customer,String id){
String sql="update Customer set name=?,age=?,phone=? where id=?";
Object [] params={customer.getName(),customer.getAge(),customer.getPhone(),id};
try{
qr.update(sql,params);
}catch(Exception e){
e.printStackTrace();
}
}
/**
* 通过uuid 找到并删除指定用户
*/
public void deleteById(String id){
String sql="delete from customer where id =?";
System.out.println(sql);
Object [] params={id};
try{
qr.update(sql,params);
}catch(Exception e){
e.printStackTrace();
}
}
/**
* 多条件组合查询并分页
* @param criteria
* @return
*/
public PageBean<Customer> query(Customer criteria,int pageCode,int listNum) {
try {
/**
* 1.创建pageBean对象
* 2.设置已有的属性
* 3.得到listTotal(总记录数)
* 4.得到beanlist
*/
PageBean<Customer> pb=new PageBean<Customer>();
pb.setPageCode(pageCode);
pb.setListnum(listNum);
StringBuilder cntsql = new StringBuilder("select count(*) from customer ");
StringBuilder wheresql = new StringBuilder(" where 1=1");
//2.创建一个Arraylist,用来装载参数值
List<Object> params = new ArrayList<Object>();
// 3.判断条件，完成sql中追加where子句。
String name = String.valueOf(criteria.getName());
String age = criteria.getAge();
String phone = criteria.getPhone();
if (name != null && !name.trim().isEmpty()) {
wheresql.append(" and name like ?");
params.add("%"+name+"%");
}
if (age != null && !age.trim().isEmpty()) {
wheresql.append(" and age like ?");
params.add("%"+age+"%");
}
if (phone != null && !phone.trim().isEmpty()) {
wheresql.append(" and phone like ?");
params.add("%"+phone+"%");
}
Number number=(Number) qr.query(cntsql.append(wheresql).toString(),new ScalarHandler(),params.toArray());
//获取总记录数量
int listTotal=number.intValue();
pb.setListTotal(listTotal);
/**
* beanlist
*/
StringBuilder sql = new StringBuilder("select * from customer ");
/**
* 为sql添加 limit 子句
*/
StringBuilder limitsql = new StringBuilder(" limit ?,?");
params.add((pageCode-1)*listNum);
params.add(listNum);
List<Customer> beanlist=qr.query(sql.append(wheresql).append(limitsql).toString(),
new BeanListHandler<Customer>(Customer.class),
params.toArray());
pb.setBeanList(beanlist);
return pb;
}catch (SQLException e){
throw new RuntimeException();
}
}
}
扫一扫
1262
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
