项目场景：

前端是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<AlarmDisposeRecordVO> selectAlarmDisposeRecord(Integer page, Integer limit) {return villageManageService.selectAlarmDisposeRecord(page,limit);
}

业务层

sqlserver直接传就行

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

maraDB则需要计算一下

public List<AlarmDisposeRecordVO> 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 (SELECTISNULL( 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 alarmCauseFROMDKGasRun.dbo.AlarmDis AS a,DKGovtGas.dbo.Concentration AS bWHEREa.equipID = b.IDAND b.companyID = #{comId}<if test="alarmCause=''||alarmCause!=null">AND a.alarmCause LIKE'%' + #{alarmCause} + '%'</if>) nwhere rownumber > ((${page} - 1)*${limit})

maraDB

  SELECTISNULL( 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 alarmCauseFROMdkgasrun.alarmd AS a,dkgovtgas.concentration AS bWHEREa.equipID = b.IDAND b.companyID = #{comId}<if test="alarmCause=''||alarmCause!=null">AND a.alarmCause LIKE CONCAT('%', #{alarmCause}, '%')</if>limit #{page},#{limit};