5.0其他WEB技术——分页和模糊查询结合实例

JAVAWEB文章索引点这里
首先分页的核心是写一个PageBean对象，将页面中要使用到的首页、上页、下页、末页、页数、查询出的内容等等进行一个封装。然后转发回到页面中进行一个显示。而模糊查询的核心是对sql语句进行一个拼接，达到模糊匹配需要内容的效果。
数据库Store中有表product(id,name,bar_code,price,producer)
需要用到的jar包邮jstl，sqljdbc4
表sql：

/*
 Navicat Premium Data Transfer

 Source Server         : localhost_3306
 Source Server Type    : MySQL
 Source Server Version : 50721
 Source Host           : localhost:3306
 Source Schema         : store

 Target Server Type    : MySQL
 Target Server Version : 50721
 File Encoding         : 65001

 Date: 15/07/2018 13:22:23
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for product
-- ----------------------------
DROP TABLE IF EXISTS `product`;
CREATE TABLE `product`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `bar_code` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `price` float(10, 2) NULL DEFAULT NULL,
  `producer` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 12 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of product
-- ----------------------------
INSERT INTO `product` VALUES (1, 'name1', '123', 3.50, 'sssss');
INSERT INTO `product` VALUES (2, 'name2', '123', 3.50, 'sssss');
INSERT INTO `product` VALUES (3, 'name3', '123', 3.50, 'sssss');
INSERT INTO `product` VALUES (4, 'name4', '123', 3.00, 'sssss');
INSERT INTO `product` VALUES (5, 'name5', '123', 3.00, 'sssss');
INSERT INTO `product` VALUES (6, 'name6', '123', 3.00, 'sssss');
INSERT INTO `product` VALUES (7, 'name7', '123', 3.00, 'sssss');
INSERT INTO `product` VALUES (8, 'name8', '123', 3.00, 'sssss');
INSERT INTO `product` VALUES (9, 'name9', '123', 3.00, 'sssss');
INSERT INTO `product` VALUES (10, 'name10', '123', 3.00, 'sssss');
INSERT INTO `product` VALUES (11, 'name11', '123', 3.00, 'sssss');

SET FOREIGN_KEY_CHECKS = 1;

比较常规的dbutils

package com.dbutil;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import com.sun.xml.internal.ws.Closeable;
public class DBUtils {

    private static String url = "jdbc:mysql://localhost:3306/Store";
    private static String user = "root";
    private static String password = "123456";
    private static Connection conn = null;
    private static PreparedStatement psttm = null;
    static {
        try {

            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    public static Connection getConnection() {
        try {
            conn = DriverManager.getConnection(url,user,password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }
    public static PreparedStatement getPreparedStatement(String sql) {

        try {
            psttm = getConnection().prepareStatement(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return psttm;
    }
    public static void close() {
        try {
            if(conn != null) {
                conn.close();
            }
            if(psttm != null) {
                psttm.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

ProductDao:主要用与查询操作

package com.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.bean.Product;
import com.dbutil.DBUtils;

public class ProductDao {
    /**
     *查询出指定大小的[模糊]内容 
     * */
    public static List<Product> getList(int startNum, int pageNum,String nameFind){
        List list = new ArrayList<>();
        //进行sql语句拼接
        StringBuffer sql = new StringBuffer("select * from product where 1=1");

        if(nameFind != null && !nameFind.trim().equals("")){//如果nameFind不为空，并且去除前后字符串后且不是空字符串
            sql.append(" and name like '%"+nameFind+"%'");
        }
        sql.append(" limit ?,?");
        //查询结果
        try {
            PreparedStatement psttm = DBUtils.getPreparedStatement(sql.toString());
            psttm.setInt(1, startNum);
            psttm.setInt(2, pageNum);
            ResultSet rs = psttm.executeQuery();
            while(rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String bar_code = rs.getString("bar_code");
                float prive = rs.getFloat("price");
                String producer = rs.getString("producer");
                Product product = new Product(id, name, bar_code, prive, producer);
                list.add(product);
            }
            rs.close();
            DBUtils.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }
    /**
     * 返回[模糊]查询结果的大小
     * */
    public static int getCount(String nameFind) {
        int count = 0;
        //sql拼接
        StringBuffer sql = new StringBuffer("SELECT COUNT(id) FROM product");
        if(nameFind != null && !nameFind.trim().equals("")) {
            sql.append(" where name like '%"+nameFind+"%'");
        }
        //查询结果
        try {
            PreparedStatement psttm = DBUtils.getPreparedStatement(sql.toString());
            ResultSet rs = psttm.executeQuery();
            if(rs.next()) {
                count = rs.getInt(1);
            }
            rs.close();
            DBUtils.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }
}

beans:
PageBean,主要用于封装页面的各信息，以及保存查询结果和请求中的模糊查询条件

package com.bean;

import java.util.*;

public class PageBean {
    private List<Product> list;
    //首页
    private int firstPage;
    //最后一页
    private int lastPage;
    //当前页
    private int nowPage;
    //上页
    private int backPage;
    //下页
    private int nextPage;
    //总共页数
    private int sum;
    //每页数量
    private int pageSize;
    //查找参数
    private FindBean findBean = new FindBean();

    public int getFirstPage() {
        return 1;
    }
    public void setFirstPage(int firstPage) {
        this.firstPage = firstPage;
    }
    //获取末页
    public int getLastPage() {
        if(sum%pageSize==0) {
            lastPage = sum/pageSize;
        }else {
            lastPage = sum/pageSize + 1;
        }
        return lastPage;
    }
    public void setLastPage(int lastPage) {
        this.lastPage = lastPage;
    }
    public int getNowPage() {
        return nowPage;
    }
    public void setNowPage(int nowPage) {
        this.nowPage = nowPage;
    }
    //获取上一页
    public int getBackPage() {
        if(nowPage == 1) {
            backPage = 1;
        }else {
            backPage = nowPage - 1;
        }
        return backPage;
    }
    public void setBackPage(int backPage) {
        this.backPage = backPage;
    }
    //设置下一页
    public int getNextPage() {
        if(nowPage==lastPage) {
            nextPage = lastPage;
        }else {
            nextPage = nowPage+1;
        }
        return nextPage;
    }
    public void setNextPage(int nextPage) {
        this.nextPage = nextPage;
    }
    public int getSum() {
        return sum;
    }
    public void setSum(int sum) {
        this.sum = sum;
    }
    public int getPageSize() {
        return pageSize;
    }
    public void setPageSize(int pageSize) {
        this.pageSize = pageSize;
    }
    public List<Product> getList() {
        return list;
    }
    public void setList(List<Product> list) {
        this.list = list;
    }
    public FindBean getFindBean() {
        return findBean;
    }
    public void setFindBean(FindBean findBean) {
        this.findBean = findBean;
    }
}

对应表的product

package com.bean;

public class Product {
    private int id;
    private String name;
    private String bar_code;
    private float price;
    private String producer;

    public Product() {
    }
    public Product(int id, String name, String bar_code, float price, String producer) {
        super();
        this.id = id;
        this.name = name;
        this.bar_code = bar_code;
        this.price = price;
        this.producer = producer;
    }
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getBar_code() {
        return bar_code;
    }
    public void setBar_code(String bar_code) {
        this.bar_code = bar_code;
    }

    public float getPrice() {
        return price;
    }
    public void setPrice(float price) {
        this.price = price;
    }
    public String getProducer() {
        return producer;
    }
    public void setProducer(String producer) {
        this.producer = producer;
    }
    public void printInfo() {
        System.out.println(this.getId()+","+this.getName()+","+this.getBar_code()+","
                    +this.getPrice()+this.getProducer());
    }
}

FindBean,用于存放模糊查询条件，本例只模糊匹配了name属性，如果要证件匹配项则应该增加本类中的属性

package com.bean;

public class FindBean {
    private String nameFind;

    public String getNameFind() {
        return nameFind;
    }

    public void setNameFind(String nameFind) {
        this.nameFind = nameFind;
    }
}

servlet,用于接受页面的数据，传参方式调用service，然后将封装好的PageBean放入域对象后进行转发

package com.servlet;

import java.io.IOException;
import java.util.ArrayList;

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 com.bean.PageBean;
import com.bean.Product;
import com.dao.ProductDao;
import com.service.PageService;

import java.util.List;

@WebServlet("/PageServlet")
public class PageServlet extends HttpServlet {
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        //1获取页面数据
        PageBean pageBean = new PageBean();
        String nowPage = request.getParameter("nowPage");
        String pageSize = request.getParameter("pageSize");
        String nameFind = request.getParameter("nameFind");
        //设置pageBean属性
        PageService pageService = new PageService();
        pageService.setPageBean(pageBean, nowPage, pageSize,nameFind);
        //2存进域对象
        request.setAttribute("pageBean", pageBean);
        //3转发
        request.getRequestDispatcher("index.jsp").forward(request, response);
    }
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        doGet(request, response);
    }

}

service,将从servlet中得到的参数进行判断后封装到PageBean中并返回给servlet，被封装的内容还包括，调用dao得到的查询结果。

package com.service;

import java.util.List;

import com.bean.PageBean;
import com.bean.Product;
import com.dao.ProductDao;

public class PageService {

    public PageBean setPageBean(PageBean pageBean,String now,String pageSize,String nameFind) {
        //设置当前页
        if(now==null||now.equals("")) {
            now = "1";
        }
        int nowPage = Integer.parseInt(now);
        pageBean.setNowPage(nowPage);
        //设置数据量大小
        int num = ProductDao.getCount(nameFind);
        pageBean.setSum(num);
        //设置分页大小
        if(pageSize == null || pageSize.equals("")) {
            pageSize = "3";//默认大小为3
        }
        int size = Integer.parseInt(pageSize);
        pageBean.setPageSize(size);
        //获取list
        List<Product> list = ProductDao.getList((nowPage-1)*size,size,nameFind);
        pageBean.setList(list);
        //设置FindBean
        pageBean.getFindBean().setNameFind(nameFind);
        return pageBean;
    }
}

index，显示页面和向页面发送请求，其中js部分实现了页数选择和每页显示数量选择。同时也对当前页进行了一个判断，如第一页的时候无法点击首页和末页。每次点击的时候都需要将一些信息通过超链接带入到servlet中以保证分页效果可以持续下去。其中编辑和删除超链接只做显示效果，并未实现。

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
    <%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>.
</head>
<body>
<table border="1" align="center" width="550" height="250" >
<tr>
<td colspan="5">
请输入需要查询的名字
<form action="${pageContext.request.contextPath}/PageServlet?pageSize=${pageBean.pageSize}" method="post" style="margin:0px;display:inline;">
    <input name="nameFind" size="4" value="${pageBean.findBean.nameFind}" />
    <input type="submit" value="提交"/>
</form>
</td>
</tr>
<tr>
<td>名称</td>
<td>条码</td>
<td>单价</td>
<td>产地</td>
<td>操作</td>
</tr>
<c:forEach items="${pageBean.list}" var="a_product">
    <tr>
        <td>${a_product.name}</td>
        <td>${a_product.bar_code}</td>
        <td>${a_product.price }</td>
        <td>${a_product.producer }</td>
        <td><a href="">编辑</a>&nbsp;<a href="">删除</a></td>
    </tr>
</c:forEach>
<tr>
<td colspan="5">第${pageBean.nowPage}页 /共 ${pageBean.lastPage }页,&nbsp;

<c:choose>
    <%--如果是第一页，首页和上一页就不可点击 --%>
    <c:when test="${pageBean.nowPage == 1}">
    首页&nbsp;
    上一页&nbsp;
    </c:when>
    <c:otherwise>
        <a href="${pageContext.request.contextPath}/PageServlet?nowPage=${pageBean.firstPage}&pageSize=${pageBean.pageSize}&nameFind=${pageBean.findBean.nameFind }">首页</a>&nbsp;
        <a href="${pageContext.request.contextPath}/PageServlet?nowPage=${pageBean.backPage}&pageSize=${pageBean.pageSize}&nameFind=${pageBean.findBean.nameFind }">上一页</a>&nbsp;
    </c:otherwise>
</c:choose>
<c:choose>
    <%--如果是最后页，末页和下一页就不可点击 --%>
    <c:when test="${pageBean.nowPage == pageBean.lastPage}">
    下一页&nbsp;
    末页&nbsp;
    </c:when>
    <c:otherwise>
        <a href="${pageContext.request.contextPath}/PageServlet?nowPage=${pageBean.nextPage}&pageSize=${pageBean.pageSize}&nameFind=${pageBean.findBean.nameFind }">下一页</a>&nbsp;
        <a href="${pageContext.request.contextPath}/PageServlet?nowPage=${pageBean.lastPage}&pageSize=${pageBean.pageSize}&nameFind=${pageBean.findBean.nameFind }">末页</a>
    </c:otherwise>
</c:choose>
    <%--实现页面跳转和显示内容大小 --%>
    跳转到第<input id="nowPage" size="2" onblur="changeNowPage()" value="${pageBean.nowPage }"/>页&nbsp;
    显示<input id="pageSize" size="2" onblur="changePageSize()" value="${pageBean.pageSize }"/>条
</td>
</tr>
</table>
<script type="text/javascript">
    <%--实现pageSize带参跳转--%>
    function changePageSize(){
        var pageSize = document.getElementById("pageSize").value;
        var reg = /^[1-9][0-9]?$/;
        if(!reg.test(pageSize)){
            alert("只能输入1-2位的数字");
            return;
        }
        var url = "${pageContext.request.contextPath}/PageServlet?pageSize="+pageSize+"&nameFind=${pageBean.findBean.nameFind }";
        window.location.href=url;
    }
    <%--实现指定页面跳转跳转--%>
    function changeNowPage(){
        var nowPage = document.getElementById("nowPage").value;
        var reg = /^[1-9][0-9]?$/;
        if(!reg.test(nowPage)){
            alert("只能输入1-2位的数字");
            return;
        }
        var lastPage = "${pageBean.lastPage}";
        if(nowPage > lastPage){
            alert("跳转页面超过了最大页面");
            return;
        }
        var url = "${pageContext.request.contextPath}/PageServlet?nowPage="+nowPage+"&pageSize=${pageBean.pageSize}"+"&nameFind=${pageBean.findBean.nameFind }";
        window.location.href=url;
    }
</script>
</body>
</html>

打开浏览器访问http://localhost:8080/ShowPage/PageServlet
效果如下