bootstrap-table插件实现分页(服务端分页)

文章目录

• ​​前言：​​
• ​​前端html部分​​
• ​​js部分​​
• ​​后端​​
• ​​实现效果​​

前言：

前端html部分

<%--商品列表显示--%>
            <div class="ibox-content table-responsive">
                <table id="table" class="table" data-click-to-select="true">
                </table>
            </div>

js部分

$(function () {
        
            var $table=$('#table');
            $table.bootstrapTable({
                url:"/ssm_test/productinfo/list",
                dataType:"json",    //服务器返回的数据类型
                method:'post',      //请求方式
                contentType: "application/x-www-form-urlencoded",//发送到服务器的数据编码类型
                pagination:true,    //是否显示分页
                pageSize:8,         //设置每页的记录行数
                pageList: [8,16,32,64],   //可供选择的每页的行数
                pageNumber:1,       //设置首页页码
                singleSelect:false, //设置是否单选
                checkboxHeader: true,
                sortable:true,      //是否启用排序
                sortOrder:"asc",    //排序方式
                clickToSelect:true, //是否启用点击选中行
                queryParamsType:"undefined",    //设置参数格式
                queryParams:function queryParams(params) {  //设置查询参数page和rows
                    //这里的键的名字和控制器的变量名必须一直，这边改动，控制器也需要改成一样的
                    var param={
                        page:params.pageNumber, //首页页码
                        rows:params.pageSize    //每页的记录行数
                    };
                    return param;
                },
                cache:false,        //禁用AJAX数据缓存
                sidePagination: 'server',   //服务端处理分页
                columns:[{
                    checkbox:true
                },{
                    title:'商品编码',
                    field:'code',
                    valign:'middle'
                },{
                    title: '商品名称',
                    field:'name',
                    valign:'middle'
                },{
                    title:'商品类型',
                    field:'type',
                    formatter:function (value,row,index) {
                        if (row.type){
                            return row.type.name;
                        }else{
                            return value;
                        }
                    }
                },{
                    title:'商品品牌',
                    field:'brand',
                    valign:"middle"
                },{
                    title:'商品数量',
                    field:'num',
                    valign:'middle'
                },{
                    title:'商品价格',
                    field:'price',
                    valign:'middle'
                },{
                    title:'商品介绍',
                    field:'intro',
                    valign:'middle'
                }],
                // onLoadSuccess:function () {
                //     swal('系统提示','数据加载成功','success');
                // },
                // onLoadError:function () {
                //     swal('系统提示','数据加载失败!','warning');
                // }
            });
         });

后端

实体类:

package com.ssm.pojo;

public class ProductInfo {
  // 商品基本信息（部分）
  private int id; // 商品编号
  private String code; // 商品编码
  private String name; // 商品名称
  // 关联属性
  private Type type;    // 商品类型

  private String brand; // 商品品牌
  private String pic; // 商品小图
  private int num; // 商品数量
  private double price; // 商品价格
  private String intro; // 商品介绍
  private int status; // 商品状态 

  private double priceFrom;
  private double priceTo;

  public int getId() {
    return id;
  }

  public void setId(int id) {
    this.id = id;
  }

  public String getCode() {
    return code;
  }

  public void setCode(String code) {
    this.code = code;
  }

  public String getName() {
    return name;
  }

  public void setName(String name) {
    this.name = name;
  }

  public String getIntro() {
    return intro;
  }

  public void setIntro(String intro) {
    this.intro = intro;
  }

  public String getBrand() {
    return brand;
  }

  public void setBrand(String brand) {
    this.brand = brand;
  }

  public double getPrice() {
    return price;
  }

  public void setPrice(double price) {
    this.price = price;
  }

  public int getStatus() {
    return status;
  }

  public void setStatus(int status) {
    this.status = status;
  }

  public String getPic() {
    return pic;
  }

  public void setPic(String pic) {
    this.pic = pic;
  }
  
  public int getNum() {
    return num;
  }

  public void setNum(int num) {
    this.num = num;
  }

  public double getPriceFrom() {
    return priceFrom;
  }

  public void setPriceFrom(double priceFrom) {
    this.priceFrom = priceFrom;
  }

  public double getPriceTo() {
    return priceTo;
  }

  public void setPriceTo(double priceTo) {
    this.priceTo = priceTo;
  }

  public Type getType() {
    return type;
  }

  public void setType(Type type) {
    this.type = type;
  }

  @Override
  public String toString() {
    return "ProductInfo{" +
        "id=" + id +
        ", code='" + code + '\'' +
        ", name='" + name + '\'' +
        ", type=" + type +
        ", brand='" + brand + '\'' +
        ", pic='" + pic + '\'' +
        ", num=" + num +
        ", price=" + price +
        ", intro='" + intro + '\'' +
        ", status=" + status +
        ", priceFrom=" + priceFrom +
        ", priceTo=" + priceTo +
        '}';
  }
}

package com.ssm.pojo;

public class Pager {
  private int curPage;// 待显示页
  private int perPageRows;// 每页显示的记录数
  private int rowCount; // 记录总数
  private int pageCount; // 总页数

  public int getCurPage() {
    return curPage;
  }

  public void setCurPage(int currentPage) {
    this.curPage = currentPage;
  }

  public int getPerPageRows() {
    return perPageRows;
  }

  public void setPerPageRows(int perPageRows) {
    this.perPageRows = perPageRows;
  }

  public int getRowCount() {
    return rowCount;
  }

  public void setRowCount(int rowCount) {
    this.rowCount = rowCount;
  }
  // 根据rowCount和perPageRows计算总页数
  public int getPageCount() {
    return (rowCount + perPageRows - 1) / perPageRows;
  }
  // 分页显示时，获取当前页的第一条记录的索引
  public int getFirstLimitParam() {
    return (this.curPage - 1) * this.perPageRows;
  }

  @Override
  public String toString() {
    return "Pager{" +
        "curPage=" + curPage +
        ", perPageRows=" + perPageRows +
        ", rowCount=" + rowCount +
        ", pageCount=" + pageCount +
        '}';
  }
}

package com.ssm.pojo;

public class Type {
  private int id; // 产品类型编号
  private String name; // 产品类型名称

  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;
  }

  @Override
  public String toString() {
    return "Type{" +
        "id=" + id +
        ", name='" + name + '\'' +
        '}';
  }
}

Controller

package com.ssm.controller;

import com.ssm.pojo.Pager;
import com.ssm.pojo.ProductInfo;
import com.ssm.pojo.Type;
import com.ssm.service.ProductInfoService;
import com.ssm.service.TypeService;
import org.apache.ibatis.annotations.Param;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Controller
@RequestMapping(value = "/productinfo",method = {RequestMethod.GET,RequestMethod.POST})
public class ProductInfoController {

    @Autowired
    ProductInfoService productInfoService;
    @Autowired
    TypeService typeService;

    //后台商品列表分页显示
    @RequestMapping("/list")
    @ResponseBody
    public Map<String,Object> list(Integer page, Integer rows, ProductInfo productInfo){
        //初始化分页类对象
        Pager pager=new Pager();
        pager.setCurPage(page);
        pager.setPerPageRows(rows);
       // System.out.println(pager);
        //创建params对象，封装查询条件
        Map<String,Object> params=new HashMap<>();
        params.put("productInfo",productInfo);
        //获取满足条件的商品总数
        int totalCount=productInfoService.count(params);
        //获取满足条件的商品列表
        List<ProductInfo> productInfos=productInfoService.findProductInfo(productInfo,pager);
        //创建result对象，保存查询结果数据
        Map<String,Object> result=new HashMap<>(2);
        result.put("total",totalCount);
        result.put("rows",productInfos);
        //将结果以JSON格式发送到前端控制器
        return result;
    }
}

Service层

package com.ssm.service;

import com.ssm.pojo.Pager;
import com.ssm.pojo.ProductInfo;

import java.util.List;
import java.util.Map;

public interface ProductInfoService {
    //查询商品总数
    public Integer count(Map<String, Object> params);
    //分页显示商品
    List<ProductInfo> findProductInfo(ProductInfo productInfo, Pager pager);
}

package com.ssm.service.impl;

import com.ssm.dao.ProductInfoDao;
import com.ssm.pojo.Pager;
import com.ssm.pojo.ProductInfo;
import com.ssm.service.ProductInfoService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Isolation;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import java.util.HashMap;
import java.util.List;
import java.util.Map;
@Service("productInfoService")
@Transactional(propagation = Propagation.REQUIRED,isolation = Isolation.DEFAULT)
public class ProductInfoServiceImpl implements ProductInfoService {

    @Autowired
    ProductInfoDao productInfoDao;

    @Override
    public Integer count(Map<String, Object> params) {
        return productInfoDao.count(params);
    }

    @Override
    public List<ProductInfo> findProductInfo(ProductInfo productInfo, Pager pager) {
        //创建对象params
        Map<String,Object> params=new HashMap<>();
        //将封装有查询条件的productInfo对象放入params
        params.put("productInfo",productInfo);
        //根据条件计算商品总数
        int recordCount=productInfoDao.count(params);
        //给pager对象设置rowCount属性值(记录总数)
        pager.setRowCount(recordCount);
        if(recordCount>0){
            //将pager对象放入params
            params.put("pager",pager);
        }
        //分页获取商品信息
        return productInfoDao.selectByPage(params);
    }
}

Dao层

package com.ssm.dao;

import com.ssm.dao.provider.ProductInfoDynaSqlProvider;
import com.ssm.pojo.ProductInfo;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.mapping.FetchType;

import java.util.List;
import java.util.Map;

public interface ProductInfoDao {
    //根据条件查询商品总数
    @SelectProvider(type = ProductInfoDynaSqlProvider.class,method = "count")
    Integer count(Map<String, Object> params);

    // 分页获取商品
    @Results({ @Result(id = true, column = "id", property = "id"), @Result(column = "code", property = "code"),
            @Result(column = "name", property = "name"), @Result(column = "brand", property = "brand"),
            @Result(column = "pic", property = "pic"), @Result(column = "num", property = "num"),
            @Result(column = "price", property = "price"), @Result(column = "intro", property = "intro"),
            @Result(column = "status", property = "status"),
            @Result(column = "tid", property = "type", one = @One(select = "com.ssm.dao.TypeDao.selectById", fetchType = FetchType.EAGER)) })
    @SelectProvider(type = ProductInfoDynaSqlProvider.class, method = "selectWithParam")
    List<ProductInfo> selectByPage(Map<String, Object> params);
}

package com.ssm.dao;

import com.ssm.pojo.Type;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface TypeDao {

    //根据类型编号查询类型对象
    @Select("select * from type where id=#{id}")
    public Type selectById(int id);
}

package com.ssm.dao.provider;

import com.ssm.pojo.Pager;
import com.ssm.pojo.ProductInfo;
import org.apache.ibatis.jdbc.SQL;

import java.util.Map;

public class ProductInfoDynaSqlProvider {

    // 分页动态查询
    public String selectWithParam(final Map<String, Object> params) {
        String sql = new SQL() {
            {
                SELECT("*");
                FROM("product_info");
                if (params.get("productInfo") != null) {
                    ProductInfo productInfo = (ProductInfo) params.get("productInfo");
                    if (productInfo.getCode() != null && !"".equals(productInfo.getCode())) {
                        WHERE(" code = #{productInfo.code} ");
                    }
                    if (productInfo.getName() != null && !productInfo.getName().equals("")) {
                        WHERE("  name LIKE CONCAT ('%',#{productInfo.name},'%') ");
                    }
                    if (productInfo.getBrand() != null && !productInfo.getBrand().equals("")) {
                        WHERE("  brand LIKE CONCAT ('%',#{productInfo.brand},'%') ");
                    }
                    if (productInfo.getType() != null && productInfo.getType().getId() > 0) {
                        WHERE(" tid = #{productInfo.type.id} ");
                    }
                    if (productInfo.getPriceFrom() > 0) {
                        WHERE(" price > #{productInfo.priceFrom} ");
                    }
                    if (productInfo.getPriceTo() > 0) {
                        WHERE(" price <= #{productInfo.priceTo} ");
                    }
                }
            }
        }.toString();
        if (params.get("pager") != null) {
            //第一条记录的索引                     每页显示的记录数
            sql += " limit #{pager.firstLimitParam} , #{pager.perPageRows}  ";
        }
        return sql;
    }



    //根据条件动态查询订单总记录数
    public String count(final Map<String,Object> params){
        return new SQL(){
            {
                SELECT("count(*)");
                FROM("product_info");
                if(params.get("productInfo")!=null){
                    ProductInfo productInfo=(ProductInfo)params.get("productInfo");
                    if(productInfo.getCode()!=null&&!"".equals(productInfo.getCode())){
                        WHERE("code=#{productInfo.code}");
                    }
                    if(productInfo.getName()!=null&&!"".equals(productInfo.getName())){
                        WHERE("name LIKE CONCAT('%',#{productInfo.name},'%')");
                    }
                    if(productInfo.getBrand()!=null&&!"".equals(productInfo.getBrand())){
                        WHERE("brand LIKE CONCAT('%',#{productInfo.brand},'%')");
                    }
                    if(productInfo.getType()!=null&& productInfo.getType().getId()>0){
                        WHERE("tid=#{productInfo.type.id}");
                    }
                    if(productInfo.getPriceFrom()>0){
                        WHERE("price>=#{productInfo.priceFrom}");
                    }
                    if(productInfo.getPriceTo()>0){
                        WHERE("price<=#{productInfo.priceTo}");
                    }
                }
            }

        }.toString();
    }
}

实现效果