前端是leyui后端sqlserver和maraDB进行分页

项目场景：

前端是leyui后端sqlserver和maraDB进行分页,两种数据库在后端分页的不同写法

---

解决方案：

前端:

定义table,表格的格式在接口返回时进行创建,根据id进行绑定

 <div class="layui-tab-item layui-show" style="padding-top: 10px">
        <div class="layui-card-body" style="padding:20px 0px;width: 100%">
          <table class="layui-hide" id="Distribution" lay-filter="Distribution">table>
        div>
      div>

此处用于定于表格的表头处的按钮

<script type="text/html" id="toolbarDemo">
  <div class="layui-btn-container">
    <button type="button" class="layui-btn layui-btn-sm" style="background-color: #1E9FFF" id="button1" lay-event="day">今日配送
    button>
    <button type="button" class="layui-btn layui-btn-normal layui-btn-sm" id="button2" style="background-color: #FFB800"  lay-event="week">本周配送
    button>
    <button type="button" class="layui-btn layui-btn-normal layui-btn-sm" id="button3"  style="background-color: #FF5722"  lay-event="month">本月配送
    button>
    <button type="button" class="layui-btn layui-btn-normal layui-btn-sm" id="button4" lay-event="all">查看全部button>
  div>
script>

 定义url和参数以及table和表头处的id选择集

toolbar: '#toolbarDemo', 接收表头id

elem: '#Distribution', 接收table的id

function loadBottledGasHouse (companyID,distributionPerson,customerName,carNum,distributionType,distributionAddress,startTime,endTime){
  table.render({
    elem: '#Distribution',
    url: 'xxx/xxx',
    toolbar: '#toolbarDemo',
    page: true,
    method: 'post',
    limit: 10,
    limits: [10, 30, 50,100,300,500],
    cellMinWidth: 110,
    where: {"comID": companyID,"distributionPerson": distributionPerson,"customerName": customerName,"carNum": carNum,
      "distributionType": distributionType,"distributionAddress": distributionAddress,"startTime": startTime,"endTime": endTime},
    cols: [[
      {field: 'id', sort: true, title: 'ID', hide: true},
      {field: 'companyName', sort: true, title: '所属公司',  align: "center",width:200},
      {field: 'carNum', sort: true, title: '车牌号',  align: "center",width:120},
      {field: 'customerName', sort: true, title: '客户姓名',  align: "center",width:120},
      {field: 'customerPhone', sort: true, title: '客户电话',  align: "center",width:120},
      {field: 'distributionAddress', sort: true, title: '配送地址',  align: "center",width:150},
      {field: 'distributionType', sort: true, title: '配送方式',  align: "center",width:120,templet : function(d) {
          var distributionType = d.distributionType;
          if(distributionType==1){
             return '整罐换气'
          }
          if(distributionType==2){
            return '自有钢瓶'
          }
          if(distributionType==3){
            return '现场充气'
          }
      }},
      {field: 'inflationVolumeTotal', sort: true, title: '总加气量',  align: "center",width:120},
      {field: 'inflationVolume', sort: true, title: '钢瓶标签/加气量',  align: "center",width:300},
      {field: 'distributionTime', sort: true, title: '配送时间',  align: "center",width:200},
      {field: 'cylinderTotal', sort: true, title: '钢瓶数量',  align: "center",width:120},
      {field: 'distributionPersonName', sort: true, title: '配送人员',  align: "center",width:120},
      {align: 'center', toolbar: '#roleTableBarStreet',  title: '操作', align: "center",fixed:'right',width:200}
    ]],
    done(res){
      console.log(res)
    }
  });
  return false;
}

以下是生成的表格样式和分页效果

 

下面是后端分页

同样需要将page和limit传入后端并接收

sqlserver和maraDB一样只是业务层和sql不一样

 

@RequestMapping("/url")
public List selectAlarmDisposeRecord(Integer page, Integer limit) {
   return villageManageService.selectAlarmDisposeRecord(page,limit);
}

业务层

sqlserver直接传就行

public List selectAlarmDisposeRecord( Integer page, Integer limit) {
   return villageManageMapper.selectAlarmDisposeRecord(page,limit);
}

maraDB则需要计算一下

public List selectAlarmDisposeRecord(Integer comId, Integer page, Integer limit, String alarmCause) {
   page = (page-1)*limit; // 修改MariaDB 分页
   return villageManageMapper.selectAlarmDisposeRecord(comId,page,limit,alarmCause);
}

最后是sql的不同

sqlserver

select top ${limit} * from (
            SELECT
                ISNULL( CAST ( a.disposeStatus AS VARCHAR ), '--' ) AS disposeStatus,
                COALESCE(CONVERT(VARCHAR(100), a.alarmTime, 120), '--') AS alarmTime,
                ISNULL( CAST ( b.concNumber AS VARCHAR ), '--' ) AS concNumber,
                ISNULL( CAST ( a.alarmValue AS VARCHAR ), '--' ) AS alarmValue,
                ISNULL( CAST ( a.alarmCause AS VARCHAR ), '--' ) AS alarmCause
            FROM
                DKGasRun.dbo.AlarmDis AS a,
                DKGovtGas.dbo.Concentration AS b
            WHERE
                a.equipID = b.ID
                AND b.companyID = #{comId}
                <if test="alarmCause=''||alarmCause!=null">
                    AND a.alarmCause LIKE'%' + #{alarmCause} + '%'
                </if>
        ) n
        where rownumber > ((${page} - 1)*${limit})

maraDB

  SELECT
            ISNULL( CAST ( a.disposeStatus AS VARCHAR ), '--' ) AS disposeStatus,
            COALESCE(CONVERT(VARCHAR(100), a.alarmTime, 120), '--') AS alarmTime,
            ISNULL( CAST ( b.concNumber AS VARCHAR ), '--' ) AS concNumber,
            ISNULL( CAST ( a.alarmValue AS VARCHAR ), '--' ) AS alarmValue,
            ISNULL( CAST ( a.alarmCause AS VARCHAR ), '--' ) AS alarmCause
            FROM
             dkgasrun.alarmd AS a,
             dkgovtgas.concentration AS b
            WHERE
            a.equipID = b.ID
            AND b.companyID = #{comId}
            <if test="alarmCause=''||alarmCause!=null">
                AND a.alarmCause LIKE CONCAT('%', #{alarmCause}, '%')
            </if>
             limit #{page},#{limit};