多条件分页查询 - PageHelper

1. Maven配置插件

<!-- 分页插件 -->
<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper</artifactId>
    <version>${pagehelper.version}</version>
</dependency>

2. 准备数据库信息

在这里插入图片描述

3. 准备实体类

Drug.java

public class Drug {
    private Integer dr_id;

    private String dr_url;

    private Long dr_inPrice;

    private Long dr_outPrice;

    private String dr_name;

    private String dr_type;

    private String dr_simpleDesc;

    private String dr_expiration;

    private String dr_detaDesc;

    private String dr_factory;

    private String dr_direction;

    private String dr_remark;

    private Integer dr_number;

    private Integer dr_state;

    @Override
    public String toString() {
        return "Drug{" +
                "dr_id=" + dr_id +
                ", dr_url='" + dr_url + '\'' +
                ", dr_inPrice=" + dr_inPrice +
                ", dr_outPrice=" + dr_outPrice +
                ", dr_name='" + dr_name + '\'' +
                ", dr_type='" + dr_type + '\'' +
                ", dr_simpleDesc='" + dr_simpleDesc + '\'' +
                ", dr_expiration='" + dr_expiration + '\'' +
                ", dr_detaDesc='" + dr_detaDesc + '\'' +
                ", dr_factory='" + dr_factory + '\'' +
                ", dr_direction='" + dr_direction + '\'' +
                ", dr_remark='" + dr_remark + '\'' +
                ", dr_number=" + dr_number +
                ", dr_state=" + dr_state +
                '}';
    }

    // ------------- 此处省略Get / Set方法 -------------

4. 封装结果类

ResultVo.java

/**
 * 返回结果的统一封装
 *
 * @author murphy
 * @since 2021/7/27 4:45 下午
 */
public class ResultVo<T> {
    /**
     * 返回的是分页对象
     */
    private PageInfo<T> pageInfo;

    /**
     * 返回的是集合
     */
    private List<T> list;

    /**
     * 返回的是单个对象
     */
    private T obj;

    /**
     * 表示返回的状态码
     */
    private Integer code = 200;

    /**
     * 表示可以展示给用户的信息
     */
    private String msg = "OK";

    public ResultVo() {
    }

    public ResultVo(PageInfo<T> pageInfo) {
        this.pageInfo = pageInfo;
    }

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

    public ResultVo(T obj) {
        this.obj = obj;
    }

    public ResultVo(Integer code, String msg) {
        this.code = code;
        this.msg = msg;
    }
  
    // ------------- 此处省略Get / Set方法 -------------

5. 封装查询类

注意：这里封装的信息是查找的关键词或者编号

/**
 * 封装的查询类 - 药品管理
 *
 * @author murphy
 * @since 2021/8/8 2:19 下午
 */
public class QueryDrugVo {
    private Integer dr_id;
    private String dr_name;
    private String dr_type;

    // ------------- 此处省略Get / Set方法 -------------

6. 在 DrugMapper.java / DrugMapper.xml 文件中编写SQL语句

DrugMapper.java - 方法接口

/**
 * 动态SQL
 * @param vo
 * @return
 */
List<Drug> queryByVo(QueryDrugVo vo);

DrugMapper.xml - 方法实现

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.murphy.mapper.DrugMapper">
    <resultMap id="BaseResultMap" type="com.murphy.pojo.Drug">
        <id column="dr_id" jdbcType="INTEGER" property="dr_id"/>
        <result column="dr_url" jdbcType="VARCHAR" property="dr_url"/>
        <result column="dr_inPrice" jdbcType="DECIMAL" property="dr_inPrice"/>
        <result column="dr_outPrice" jdbcType="DECIMAL" property="dr_outPrice"/>
        <result column="dr_name" jdbcType="VARCHAR" property="dr_name"/>
        <result column="dr_type" jdbcType="VARCHAR" property="dr_type"/>
        <result column="dr_simpleDesc" jdbcType="VARCHAR" property="dr_simpleDesc"/>
        <result column="dr_expiration" jdbcType="VARCHAR" property="dr_expiration"/>
        <result column="dr_detaDesc" jdbcType="VARCHAR" property="dr_detaDesc"/>
        <result column="dr_factory" jdbcType="VARCHAR" property="dr_factory"/>
        <result column="dr_direction" jdbcType="VARCHAR" property="dr_direction"/>
        <result column="dr_remark" jdbcType="VARCHAR" property="dr_remark"/>
        <result column="dr_number" jdbcType="INTEGER" property="dr_number"/>
        <result column="dr_state" jdbcType="INTEGER" property="dr_state"/>
    </resultMap>
    
  	<!-- 动态SQL -->
    <select id="queryByVo" parameterType="com.murphy.vo.query.QueryDrugVo" resultMap="BaseResultMap">
        select * from SSM_Hosp.hosp_drug
        <where>
            <if test="dr_id != null">dr_id = #{dr_id}</if>
            <if test="dr_name != null">and dr_name like concat(concat('%',#{dr_name},'%'))</if>
            <if test="dr_type != null">and dr_type like concat(concat('%',#{dr_type},'%'))</if>
        </where>
    </select>
</mapper>

7. 编写Service层 - DrugService

/**
 * 药品管理 - Service层
 *
 * @author murphy
 * @since 2021/8/8 2:20 下午
 */
@Service
public class DrugService {

    @Resource
    private DrugMapper drugMapper;

    /**
     * 多条件分页查询
     * @param pageNum
     * @param pageSize
     * @param vo
     * @return
     */
    @Transactional(propagation = Propagation.REQUIRED, readOnly = true)
    public PageInfo<Drug> queryByPage(Integer pageNum, Integer pageSize, QueryDrugVo vo) {
        // 通过PageHelper进行分页
      	PageHelper.startPage(pageNum, pageSize);
        List<Drug> drugs = drugMapper.queryByVo(vo);
        return new PageInfo<>(drugs);
    }
}

8. 编写Controller层 - DrugController

/**
 * 药品管理 - Controller层
 *
 * @author murphy
 * @since 2021/8/8 2:21 下午
 */
@Controller
@RequestMapping("drug")
@ResponseBody
public class DrugController {

    @Resource
    private DrugService drugService;

    /**
     * 多条件分页查询
     *
     * @param pageNum
     * @param pageSize
     * @param vo
     * @return
     */
    @RequestMapping(value = "list", method = RequestMethod.GET)
    public ResultVo<Drug> queryByPage(Integer pageNum, Integer pageSize, QueryDrugVo vo) {
        if (pageNum == null || pageNum <= 0) {
            pageNum = 1;
        }
        if (pageSize == null || pageSize <= 0) {
            pageSize = 5;
        }
        PageInfo<Drug> drugPageInfo = drugService.queryByPage(pageNum, pageSize, vo);
        return new ResultVo<>(drugPageInfo);
    }
}

9. 前端页面

<!DOCTYPE html>
<html>
<head>
    <title>药品管理</title>
    <meta charset="UTF-8">
    <link rel="stylesheet" type="text/css" href="/css/bootstrap.css"/>
    <link rel="stylesheet" type="text/css" href="/css/bootstrap-responsive.css"/>
    <link rel="stylesheet" type="text/css" href="/css/style.css"/>
    <script type="text/javascript" src="/js/jquery.js"></script>
    <script type="text/javascript" src="/js/bootstrap.js"></script>
    <script type="text/javascript" src="/js/ckform.js"></script>
    <script type="text/javascript" src="/js/common.js"></script>

    <style type="text/css">
        body {
            padding-bottom: 40px;
        }

        .correct:before {
            content: '\2714';
            color: #008100;
        }
        .incorrect:before {
            content: '\2716';
            color: #b20610;
        }
        .loading:before {
            content: '\27F2';
            color: #faa732;
        }
    </style>
</head>
<body>
<form id="myForm" method="post" class="definewidth m20">
    <table class="table table-bordered table-hover definewidth m10">
        <tr>
            <td width="10%" class="tableleft">药品编号：</td>
            <td><input type="text" name="dr_id" value=""/></td>

            <td width="10%" class="tableleft">药品名称：</td>
            <td><input type="text" name="dr_name" value=""/></td>

            <td width="10%" class="tableleft">药品类型：</td>
            <td>
                <select id="dr_type" name="dr_type">
                    <option value="">请选择</option>
                    <option value="中药">中药</option>
                    <option value="西药">西药</option>
                    <option value="中西药">中西药</option>
                    <option value="处方药">处方药</option>
                    <option value="非处方药">非处方药</option>
                </select>
            </td>
            <td colspan="4">
                <center>
                    <button class="btn btn-success" type="button" onclick="loadData()">查询</button>
                    <button class="btn btn-danger" type="reset">清空</button>
                </center>
            </td>
        </tr>
    </table>

    <table class="table table-bordered table-hover definewidth m10">
        <thead>
        <tr>
            <th><input type="checkbox" id="check_All" onChange="check_All();"></th>
            <th>药品编号</th>
            <th>药品名称</th>
            <th>药品类型</th>
            <th>简单描述</th>
            <th>状态</th>
            <th>剩余量</th>
            <th>操作</th>
        </tr>
        </thead>
        <tbody id="tbody">
        
        </tbody>
    </table>

    <table class="table table-bordered table-hover definewidth m10">
        <tr>
            <th colspan="5">
                <div class="inline pull-right page">
                    共 <span id="total" class="current"></span> 条数据，共 <span id="pages" class="current"></span> 页&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                    每页显示
                    <select class="form-control" name="pageSize" style="width: 55px;height: 25px">
                        <option value="5">5</option>
                        <option value="10">10</option>
                        <option value="15">15</option>
                    </select>
                    条数据&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                    跳转到第
                    <input class="form-control" name="pageNum" value="1"
                           style="height: 20px;width: 40px;border-radius: 8px;text-align: center"/>
                    页&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                    <a class="btn btn-default" id="firstPage" name="pageLi">首页</a>
                    <a class="btn btn-default" id="prePage" name="pageLi">上一页</a>
                    <a class="btn btn-default" id="nextPage" name="pageLi">下一页</a>
                    <a class="btn btn-default" id="lastPage" name="pageLi">末页</a>
                </div>
                <div>
                    <button type="button" class="btn btn-success" id="newNav">添加新药</button>
                    <button type="button" class="btn btn-success" id="outExcel">导出Excel</button>
                </div>
            </th>
        </tr>
    </table>
</form>
</body>
</html>
<script type="text/javascript">  
    $(function () {
        loadData();
        $("#firstPage").click(function () {
            if (($(this).attr("class")) == 'disabled')
                return;
            let num = $(this).attr("name");
            $("input[name='pageNum']").val(num);
            loadData();
        });

        $("#prePage").click(function () {
            if (($(this).attr("class")) == 'disabled')
                return;
            let num = $(this).attr("name");
            $("input[name='pageNum']").val(num);
            loadData();
        });

        $("#nextPage").click(function () {
            if (($(this).attr("class")) == 'disabled')
                return;
            let num = $(this).attr("name");
            $("input[name='pageNum']").val(num);
            loadData();
        });

        $("#lastPage").click(function () {
            if (($(this).attr("class")) == 'disabled')
                return;
            let num = $(this).attr("name");
            $("input[name='pageNum']").val(num);
            loadData();
        });

    });

    //填充分页信息
    function fillPageData(pageInfo) {
        //填充信息
        $("#total").html(pageInfo.total);
        $("#pages").html(pageInfo.pages);
        $("input[name='pageNum']").val(pageInfo.pageNum);
        $("select[name='pageSize']").val(pageInfo.pageSize);
        //设置按钮的属性值和样式
        $("#firstPage").attr("name", 1);
        $("#prePage").attr("name", pageInfo.prePage);
        $("#nextPage").attr("name", pageInfo.nextPage);
        $("#lastPage").attr("name", pageInfo.pages);
        $("#firstPage").removeClass("disabled");
        $("#prePage").removeClass("disabled");
        $("#nextPage").removeClass("disabled");
        $("#lastPage").removeClass("disabled");
        if (pageInfo.isFirstPage) {//如果是第一页：上一页和首页禁用
            $("#firstPage").addClass("disabled");
            $("#prePage").addClass("disabled");
        }
        if (pageInfo.isLastPage) {//如果已经是最后一页：下一页和末页禁用
            $("#nextPage").addClass("disabled");
            $("#lastPage").addClass("disabled");
        }
    }

    String.prototype.GetValue = function (para) {
        let reg = new RegExp("(^|&)" + para + "=([^&]*)(&|$)");
        let r = this.substr(this.indexOf("\?") + 1).match(reg);
        if (r != null) return unescape(r[2]);
        return null;
    }

    //加载数据
    function loadData() {
        let url = document.location.toString();
        let pageNum = url.GetValue("pageNum");
        let pageSize = url.GetValue("pageSize");
        if (pageNum != null && $("input[name='pageNum']").val() != null) {
            $("input[name='pageNum']").val(pageNum);
        }
        if (pageSize != null && $("select[name='pageSize']").val() != null) {
            $("select[name='pageSize']").val(pageSize);
        }

        $.ajax({
            type: "GET",
            url: "/drug/list",
            data: $("#myForm").serialize(),//主要是分页和多条件参数数据
            dataType: "json",
            success: function (vo) {
                let list = vo.pageInfo.list;
                let str = "";
                for (let i = 0; i < list.length; i++) {
                    let obj = list[i];

                    let dr_id = obj.dr_id;
                    let dr_name = obj.dr_name == null ? '' : obj.dr_name;
                    let dr_type = obj.dr_type == null ? '' : obj.dr_type;
                    let dr_simpleDesc = obj.dr_simpleDesc == null ? '' : obj.dr_simpleDesc;
                    let dr_state = obj.dr_state == null ? 1 : obj.dr_state;
                    let dr_number = obj.dr_number == null ? '' : obj.dr_number;
                    let state = '';
                  
                    let btnStr = '<button type="button" name="infoBtn" class="btn btn-sm btn-info" οnclick="drugInfo(' + dr_id + ')" >详情</button>&nbsp;';

                    if (dr_state == 0 && dr_number != 0) {
                        state = '销售中 ' + '<span class="correct"></span>';
                    } else if (dr_state == 1) {
                        state = '已下架 ' + '<span class="incorrect"></span>';
                    } else if (dr_state == 0 && dr_number == 0) {
                        state = '缺货中 ' + '<span class="loading"></span>';
                    }

                    str += '<tr>' +
                        '            <td><input type="checkbox" name="check" value="' + dr_id + '"/></td>' +
                        '            <td>' + dr_id + '</td>' +
                        '            <td>' + dr_name + '</td>' +
                        '            <td>' + dr_type + '</td>' +
                        '            <td>' + dr_simpleDesc + '</td>' +
                        '            <td>' + state + '</td>' +
                        '            <td>' + dr_number + '</td>' +
                        '            <td>' + btnStr + '</td>' +
                        '</tr>'
                }
                $("#tbody").html(str);
                //填充分页数据
                fillPageData(vo.pageInfo);
            }
        });
    }
</script>