Spring Boot+MybatisPlus使用JQuery DataTables表格插件展示数据、实现分页和查询功能
一、部分技术选型
前端
Thymeleaf 模板引擎
jquery js框架
layer提示框、弹出层、日期
dataTables表格插件(基于jquery)
后台
Springboot+MybatisPlus
数据库
mysql
开发工具
IDEA
二、项目结构
三、功能需求和效果展示
展示数据、实现分页和查询功能
四、关键代码
4.1页面sc_dyj.html
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<meta name="renderer" content="webkit|ie-comp|ie-stand">
<meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">
<meta http-equiv="Cache-Control" content="no-siteapp" />
<link th:href="@{/erp/assets/css/bootstrap.min.css}" rel="stylesheet" />
<link rel="stylesheet" th:href="@{/erp/css/style.css}"/>
<link th:href="@{/erp/assets/css/codemirror.css}" rel="stylesheet">
<link rel="stylesheet" th:href="@{/erp/assets/css/ace.min.css}" />
<link rel="stylesheet" th:href="@{/erp/assets/css/font-awesome.min.css}" />
<!--[if IE 7]>
<link rel="stylesheet" th:href="@{/erp/assets/css/font-awesome-ie7.min.css}" />
<![endif]-->
<!--[if lte IE 8]>
<link rel="stylesheet" th:href="@{/erp/assets/css/ace-ie.min.css}" />
<![endif]-->
<script th:src="@{/erp/js/jquery-1.9.1.min.js}"></script>
<script th:src="@{/erp/assets/js/bootstrap.min.js}"></script>
<script th:src="@{/erp/assets/js/jquery.dataTables.min.js}"></script>
<script th:src="@{/erp/assets/js/jquery.dataTables.bootstrap.js}"></script>
<script th:src="@{/erp/assets/js/typeahead-bs2.min.js}"></script>
<script th:src="@{/erp/assets/layer/layer.js}" type="text/javascript" ></script>
<script th:src="@{/erp/assets/js/jquery-ui-1.10.3.custom.min.js}"></script>
<script th:src="@{/erp/assets/js/jquery.ui.touch-punch.min.js}"></script>
<script th:src="@{/erp/assets/js/ace-elements.min.js}"></script>
<script th:src="@{/erp/assets/js/ace.min.js}"></script>
<title>激光打印机管理</title>
<script type="text/javascript">
jQuery(function($) {
//表格渲染
var table =$("#sample-table").dataTable( {
"autoWidth": false,
searching: false,
ordering: true,
serverSide: true,
ajax: {
url: "/list",
type: "post",
data: function (d) {
var data = $('form').serialize(); //获取查询条件
//获取分页信息
var searchParams = {
start: d.start,
length: d.length,
};
data = data + '&' + $.param(searchParams);
return data;
}
},
columnDefs: [{
"defaultContent": "",
"targets": "_all"
}],
columns: [
{
"data": "id", "createdCell": function (td, cellData, rowData, row, col) {
$(td).empty().append('<label><input type="checkbox" value="' + rowData.id + '" id="ID" name="yy" class="ace"><span\n' +
' class="lbl"></span></label>');
}
},
{"data":"pnum"},
{"data":"pserver"},
{"data":"pdescribe"},
{"data":"pip"},
{"data":"pline"},
{"data":"premark"},
{
"data": "premark", "createdCell": function (td, cellData, rowData, row, col) {
$(td).empty().append(' <a title="编辑" οnclick="member_edit(this)" href="javascript:;" class="btn btn-xs btn-info" ><i class="icon-edit bigger-120"></i></a>\n' +
' <a title="删除" href="javascript:;" οnclick="member_del(this,\'1\')" class="btn btn-xs btn-warning" ><i class="icon-trash bigger-120"></i></a>');
}
},
]
});
//搜索按钮事件
$("#search").click(function () {
console.log(111111);
table.fnUpdate();
});
$('table th input:checkbox').on('click' , function(){
var that = this;
$(this).closest('table').find('tr > td:first-child input:checkbox')
.each(function(){
this.checked = that.checked;
$(this).closest('tr').toggleClass('selected');
});
});
})
</script>
</head>
<body>
<div class="margin clearfix">
<div class="stystems_style">
<div class="tabbable">
<div class="page-content clearfix">
<div id="Member_Ratings">
<div class="tab-content">
<div id="home" class="tab-pane active">
<!-- 查询功能-->
<div class="search_style">
<form>
<ul class="search_content clearfix">
<li><label class="l_f">查询打印机:</label><input type="text" id="pNum" name="pNum"
class="text_add"
placeholder="输入打印机编号"
style=" width:400px"/></li>
<li style="width:90px;">
<button type="button" class="btn_search" id="search"><i
class="icon-search"></i>查询
</button>
</li>
</ul>
</form>
</div>
<!--表格-->
<table class="table table-striped table-bordered table-hover" id="sample-table">
<thead>
<tr>
<th width="25"><label><input type="checkbox" id="btnAllChk" class="ace"><span class="lbl"></span></label></th>
<th width="150">打印机编号</th>
<th width="80">服务器</th>
<th width="150">描述</th>
<th width="120">IP地址</th>
<th width="80">责任产线</th>
<th width="250">备注</th>
<th width="250">操作</th>
</tr>
</thead>
<tbody>
</tbody>
</table>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</body>
</html>
4.2controller层
package com.bigdata.controller;
import com.bigdata.entity.Printer;
import com.bigdata.service.IPrinterService;
import com.bigdata.util.DataTablePage;
import com.bigdata.util.Page;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import java.util.List;
/**
* <p>
* 激光打印机管理
* </p>
*
* @author bigdata
* @since 2021-03-19
*/
@Controller
public class PrinterController {
@Autowired
private IPrinterService printerService;
@RequestMapping("")
public String page() {
return "laser/sc_dyj";
}
@ResponseBody
@RequestMapping("list")
public Page list(@RequestParam(required = false) String pNum, Integer start, Integer length) {
//当前条件查询到的数据
List<Printer> printerByCompanyId = printerService.getPrinterByCompanyId("2", pNum, start, length);
//当前条件查询到的条数
int count =printerService.getCount(pNum,"2");
//返回datatable对应的json格式
return new DataTablePage(count,count,printerByCompanyId);
}
}
4.3 mapper层
4.3.1PrinterMapper
package com.bigdata.mapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.bigdata.entity.Printer;
import java.util.List;
/**
* <p>
* Mapper 接口
* </p>
*
* @author bigdata
* @since 2021-03-19
*/
public interface PrinterMapper extends BaseMapper<Printer> {
/**
* 根据公司id,查询条件,分页条件查询
* @param companyId
* @param pNum
* @param start
* @param length
* @return
*/
List<Printer> getPrinterByCompanyId(String companyId, String pNum, Integer start, Integer length);
/**
* 根据公司id,查询条件,查询总条数
* @param pNum
* @param companyId
* @return
*/
Integer getCount(String pNum,String companyId);
}
4.3.2PrinterMapper.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.bigdata.mapper.PrinterMapper">
<select id="getPrinterByCompanyId" resultType="Printer">
SELECT * FROM t_printer
WHERE company_id =#{companyId}
<if test="pNum != null and pNum != ''">
<bind name="pNum" value="'%'+pNum+'%'"/>
and p_num LIKE #{pNum}
</if>
LIMIT #{start},#{length}
</select>
<select id="getCount" resultType="Integer">
SELECT count(*) FROM t_printer
WHERE company_id = #{companyId}
<if test="pNum != null and pNum != ''">
<bind name="pNum" value="'%'+pNum+'%'"/>
and p_num LIKE #{pNum}
</if>
</select>
</mapper>
五、案例下载链接
Spring Boot+MybatisPlus使用JQuery DataTables表格插件展示数据、实现分页和模糊查询等功能
六、总结
1.注意controller的接口拿到的分页数值是integer类型,sql语句需要的也是数值。容易写成String类型。
2.数据返回时,注意需要用到page,而且需要@ResponseBody注解。注解容易忘了写。