Datatables学习（三）后台分页 + 条件查询

ゝ一纸荒年。 2022-02-22 02:13 768阅读 0赞

前两讲谈了 datatables 的基本用法，以及使用 ajax 获取数据。这一讲介绍 ajax 获取数据，对接后台服务器端分页查询，及条件查询

本文后台使用 springboot + mybatis 提供数据接口

### 1、数据库建表 ###

建表 sql 语句如下

SET FOREIGN_KEY_CHECKS=0;
    
    -- ----------------------------
    -- Table structure for user
    -- ----------------------------
    DROP TABLE IF EXISTS `user`;
    CREATE TABLE `user` (
      `user_id` int(11) NOT NULL AUTO_INCREMENT,
      `user_name` varchar(20) DEFAULT NULL,
      `user_code` varchar(20) DEFAULT NULL,
      `user_pwd` varchar(20) DEFAULT NULL,
      `user_image` varchar(50) DEFAULT NULL,
      `role_id` int(11) DEFAULT NULL,
      `status` varchar(20) DEFAULT NULL,
      PRIMARY KEY (`user_id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8;
    
    -- ----------------------------
    -- Records of user
    -- ----------------------------
    INSERT INTO `user` VALUES ('14', '管理员', '0', '0', 'songjiang', '1', '通过');
    INSERT INTO `user` VALUES ('15', '小明', 'a1001', 'fd', '2', '2', '驳回');
    INSERT INTO `user` VALUES ('16', '小红', 'b1002', 'fdfd', null, null, '通过');
    INSERT INTO `user` VALUES ('17', '小张', 'c1003', 'fsd', null, null, '驳回');
    INSERT INTO `user` VALUES ('18', '小明', 'fffg', 'fd', null, null, '通过');
    INSERT INTO `user` VALUES ('19', '小红', 'e', 'fdfd', null, null, '通过');
    INSERT INTO `user` VALUES ('20', '小张', 'c1003', 'fsd', null, null, '通过');
    INSERT INTO `user` VALUES ('21', '小明', 'a1001', 'fd', null, null, '通过');
    INSERT INTO `user` VALUES ('22', '小红', 'b1002', 'fdfd', null, null, '通过');
    INSERT INTO `user` VALUES ('23', '小张', 'c1003', 'fsd', null, null, '通过');
    INSERT INTO `user` VALUES ('24', '小明', 'a1001', 'fd', null, null, '通过');
    INSERT INTO `user` VALUES ('25', '小红', 'b1002', 'fdfd', null, null, '通过');
    INSERT INTO `user` VALUES ('26', '小张', 'c1003', 'fsd', null, null, '通过');

建表成功后如下图

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dzanp6Y2Jx_size_16_color_FFFFFF_t_70][]

## ##

### 2、编写后台代码 ###

**pom 文件引入依赖**

<?xml version="1.0" encoding="UTF-8"?>
    <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    	<modelVersion>4.0.0</modelVersion>
    	<parent>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-parent</artifactId>
                <version>2.0.4.RELEASE</version>
                <relativePath/> 
    	</parent>
    	<groupId>com.demo</groupId>
    	<artifactId>demo</artifactId>
    	<version>0.0.1-SNAPSHOT</version>
    	<name>demo</name>
    	<description>Demo project for Spring Boot</description>
    
    	<properties>
                <java.version>1.8</java.version>
    	</properties>
        
    	<dependencies>
                <dependency>
                    <groupId>org.springframework.boot</groupId>
                    <artifactId>spring-boot-starter-jdbc</artifactId>
                </dependency>
                <dependency>
                    <groupId>org.springframework.boot</groupId>
                    <artifactId>spring-boot-starter-web</artifactId>
                </dependency>
                <dependency>
                    <groupId>org.mybatis.spring.boot</groupId>
                    <artifactId>mybatis-spring-boot-starter</artifactId>
                    <version>2.0.1</version>
                </dependency>
    
                <dependency>
                    <groupId>mysql</groupId>
                    <artifactId>mysql-connector-java</artifactId>
                    <scope>runtime</scope>
                </dependency>
                <dependency>
                    <groupId>org.springframework.boot</groupId>
                    <artifactId>spring-boot-starter-test</artifactId>
                    <scope>test</scope>
                </dependency>
    		
                <dependency>
                    <groupId>com.alibaba</groupId>
                    <artifactId>fastjson</artifactId>
                    <version>1.2.7</version>
                </dependency>
    		
                <dependency>
                    <groupId>commons-lang</groupId>
                    <artifactId>commons-lang</artifactId>
                    <version>2.5</version>
                </dependency>
    	</dependencies>
    
    	<build>
                <plugins>
                    <plugin>
                        <groupId>org.springframework.boot</groupId>
                        <artifactId>spring-boot-maven-plugin</artifactId>
                    </plugin>
                </plugins>
    	</build>
    
    </project>

**配置 application.yml 文件**

server:
      port:  8900
      
    spring:
      datasource:
        url:  jdbc:mysql://localhost:3306/approval?useUnicode=true&characterEncoding=utf-8&useSSL=false
        driver-class-name:  com.mysql.jdbc.Driver  
        username:  root
        password:  123456 
        
    mybatis:
      mapper-locations:  classpath:mapper/*Mapper.xml
      config-location:  classpath:mapper/config/sqlMapConfig.xml 
      
    logging:
      level:
        com.demo: debug

**Page 实体**

此 Page 实体的三个参数是使用 datatables 表格必须用到的，将其定义为父类，其他实体继承 Page 实体

package com.demo;
    
    public class Page {
    	
    	private int draw;
    	
    	private int offset;
    	
    	private int pageSize;
    
    	public int getDraw() {
    		return draw;
    	}
    
    	public void setDraw(int draw) {
    		this.draw = draw;
    	}
    
    	public int getOffset() {
    		return offset;
    	}
    
    	public void setOffset(int offset) {
    		this.offset = offset;
    	}
    
    	public int getPageSize() {
    		return pageSize;
    	}
    
    	public void setPageSize(int pageSize) {
    		this.pageSize = pageSize;
    	}
    }

**用户实体**

package com.demo;
    
    public class User extends Page{
    	
    	private String userName;    //用户名
    	
    	private String userCode;    //账号
    	
    	private String userPwd;     //密码
    	
    	private String status;      //状态
    
    	public String getUserName() {
    		return userName;
    	}
    
    	public void setUserName(String userName) {
    		this.userName = userName;
    	}
    
    	public String getUserCode() {
    		return userCode;
    	}
    
    	public void setUserCode(String userCode) {
    		this.userCode = userCode;
    	}
    
    	public String getUserPwd() {
    		return userPwd;
    	}
    
    	public void setUserPwd(String userPwd) {
    		this.userPwd = userPwd;
    	}
    
    	public String getStatus() {
    		return status;
    	}
    
    	public void setStatus(String status) {
    		this.status = status;
    	}
    
    	@Override
    	public String toString() {
    		return "User [userName=" + userName + ", userCode=" + userCode + ", userPwd=" + userPwd + ", status=" + status
    				+ "]";
    	}
    
    }

**mapper 层**

package com.demo;
    
    import java.util.List;
    import java.util.Map;
    import org.apache.ibatis.annotations.Mapper;
    import org.apache.ibatis.session.RowBounds;
    
    @Mapper
    public interface UserMapper {
    	
    	List<User> getUserList(RowBounds rowBounds, Map<String, Object> map);  //查询分页数据
    	
    	int getUserListCount(Map<String, Object> map);   //查询数据总条数
    
    }

**mapper.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.demo.UserMapper">
        <resultMap id="BaseResultMap" type="com.demo.User">
            <result column="user_code" property="userCode" jdbcType="VARCHAR" />
            <result column="user_name" property="userName" jdbcType="VARCHAR" />
            <result column="user_pwd"  property="userPwd" jdbcType="VARCHAR" />
            <result column="status"  property="status" jdbcType="VARCHAR" />
        </resultMap>
    
       
       <select id="getUserList" resultMap="BaseResultMap">
          select * from user where 1=1 
          <if test="userName != null ">
    	    and INSTR(user_name, #{userName}) > 0
          </if>
          <if test="userCode != null ">
    	    and INSTR(user_code, #{userCode}) > 0
          </if>
          <if test="status != null ">
    	    and INSTR(status, #{status}) > 0
          </if> 
       </select>
       
       
       <select id="getUserListCount" resultType="int">
          select count(*) from user where 1=1 
          <if test="userName != null ">
    	    and INSTR(user_name, #{userName}) > 0
          </if>
    	  <if test="userCode != null ">
    	    and INSTR(user_code, #{userCode}) > 0
          </if>
          <if test="status != null ">
    	    and INSTR(status, #{status}) > 0
          </if> 
       </select>
    </mapper>

**controller 层**

package com.demo;
    
    import java.util.HashMap;
    import java.util.Map;
    import org.apache.commons.lang.StringUtils;
    import org.apache.ibatis.session.RowBounds;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RestController;
    import com.alibaba.fastjson.JSONObject;
    
    @RestController
    @RequestMapping("/user")
    public class UserController {
    	
    	@Autowired
    	private UserMapper userMapper;
    	
    	@RequestMapping("/asyncList")
    	public Object getUserList(User user) {
    		//查询条件
    		Map<String, Object> searchCondition = new HashMap<String, Object>();
    		if(StringUtils.isNotBlank(user.getUserName())) {
    			searchCondition.put("userName", user.getUserName());
    		}else {
    			searchCondition.put("userName", null);
    		}
    		if(StringUtils.isNotBlank(user.getUserCode())) {
    			searchCondition.put("userCode", user.getUserCode());
    		}else {
    			searchCondition.put("userCode", null);
    		}
    		if(StringUtils.isNotBlank(user.getStatus())) {
    			searchCondition.put("status", user.getStatus());
    		}else {
    			searchCondition.put("status", null);
    		}
    		
    		JSONObject res = new JSONObject();
    		res.put("draw", user.getDraw());
    		res.put("recordsTotal", userMapper.getUserListCount(searchCondition));
    		res.put("recordsFiltered", userMapper.getUserListCount(searchCondition));
    		res.put("data", userMapper.getUserList(new RowBounds(user.getOffset(), user.getPageSize()), searchCondition));
    		return res;
    	}
    
    }

**编写 index.html 页面代码**

<!DOCTYPE html>
    <html>
    <head>
    <meta charset="UTF-8">
    <title>Insert title here</title>
    <script type="text/javascript" src="https://code.jquery.com/jquery-3.1.1.min.js" ></script>
    <link rel="stylesheet" href="https://cdn.bootcss.com/bootstrap/3.3.7/css/bootstrap.min.css" integrity="sha384-BVYiiSIFeK1dGmJRAkycuHAHRg32OmUcww7on3RYdg4Va+PmSTsz/K68vbdEjh4u" crossorigin="anonymous">
    <link href="http://cdn.datatables.net/plug-ins/28e7751dbec/integration/bootstrap/3/dataTables.bootstrap.css" rel="stylesheet" type="text/css"  />
    	
    <script src="https://cdn.datatables.net/1.10.19/js/jquery.dataTables.js" type="text/javascript"></script>
    <script src="https://cdn.bootcss.com/bootstrap/3.3.7/js/bootstrap.min.js" integrity="sha384-Tc5IQib027qvyjSMfHjOMaLkfuWVxZxUPnCJA7l2mCWNIpG9mGCD8wGNIcPD7Txa" crossorigin="anonymous"></script>
    <script src="http://cdn.datatables.net/plug-ins/28e7751dbec/integration/bootstrap/3/dataTables.bootstrap.js" type="text/javascript"></script>	
    </head>
    <body>
        <div class="row" style="margin-top:20px;">
            <div class="col-lg-12 col-lg-offset-3">
                <form class="form-inline">
                    <div class="form-group">
                        <input type="text" class="form-control" id="userName" placeholder="用户名">
                    </div>
    				  
                    <div class="form-group">
                        <input type="text" class="form-control" id="userCode" placeholder="用户账号">
                    </div>
    				  
                    <div class="input-group">
                        <select id="status" class="form-control">
                            <option value="">状态</option>
                            <option value="通过">通过</option>
                            <option value="驳回">驳回</option>
                        </select> 
                    </div>
                    <button type=button class="btn btn-success" id="searchButton"> 条 件 查 询 </button>
                    <button type=button class="btn btn-warning" id="clearSearchButton"> 清 空 条 件 </button>
                </form>
            </div>
              
            <div class="table-responsive col-lg-10 col-lg-offset-1">
                <table id="table_id" class="table table-bordered table-hover dataTable">
                    <thead>
                        <tr>
                            <th class="text-center">序号</th>
                            <th class="text-center">用户名</th>
                            <th class="text-center">用户账号</th>
                            <th class="text-center">状态</th>
                        </tr>
                    </thead>
                    <tbody>
                    </tbody>
                </table>
            </div>
        </div>
          		      		
    <script type="text/javascript">
        var datatables_options = {
            language : {
                "lengthMenu" : '每页显示<select class="form-control input-xsmall">' 
                                                + '<option value="10">10</option>'
                                                + '<option value="20">20</option>'
                                                + '<option value="30">30</option>'
                                                + '<option value="40">40</option>'
                                                + '<option value="50">50</option>' 
                                + '</select>条',
                "paginate" : {
                                "first" : "首页",
                                "last" : "尾页",
                                "previous" : "上一页",
                                "next" : "下一页"
                             },
                "processing" : "加载中...",  //DataTables载入数据时，是否显示‘进度’提示  
                "emptyTable" : "暂无数据",
                "info" : "共 _PAGES_ 页  _TOTAL_ 条数据  ",
                "infoEmpty" : "暂无数据",
                "emptyTable" : "暂无要处理的数据...",  //表格中无数据
                "search": "搜索:",
                "infoFiltered" : " —— 从  _MAX_ 条数据中筛选",
                "zeroRecords":    "没有找到记录"				
                        },
            ordering:  false,//排序显示控制
            searching : false,
            paging: true,//开启分页
            processing : true,
            serverSide : true,//是否开启服务器模式
            ajax : {
                    url: '/user/asyncList',
                    type: 'post',
                    cache: false,  //禁用缓存
                    data : function(d){
        				        	
                        var  userName = $("#userName").val();	
                        var userCode = $("#userCode").val();
                        var status = $("#status").val();
        				        	
                        var user = {};	
                        user.userName = userName;
                        user.userCode = userCode;
                        user.status = status;
        						
                        user.pageSize = d.length;
                        user.draw = d.draw;
                        user.offset = d.start;
                        return user;
                    },
                    dataFilter : function(json){
                        //后台返回字段不同于前台，使用此属性进行修改
                        console.log(json)
                        json = JSON.parse(json);
        				        	
                        console.log(json.draw)
                        console.log(json.recordsTotal)
                        console.log(json.recordsFiltered)
                        console.log(json.data)
        				        	
                        var returnData = {};
                        returnData.draw = json.draw;
                        returnData.recordsTotal = json.recordsTotal;  //返回数据全部记录  
                        returnData.recordsFiltered = json.recordsFiltered;  //后台不实现过滤功能，每次查询均视作全部结果
                        returnData.data = json.data;  //返回的数据列表
                        return JSON.stringify(returnData); //这几个参数都是datatable需要的，必须要
                    }
                },	
            columns: [
                       {
                         "data" : null,
                         "orderable" : false,
                         "sClass": "text-center"
                       },
                       {
                         "data": "userName",
                         "name" : "id",
                         "orderable" : true,
                         "sDefaultContent":"",  //默认空字符串
                         "sClass": "text-center"
                       },
                       {
                         "orderable" : false,
                         "data": "userCode",	        	
                         'sClass': "text-center"	 	         	       	
                       },
                       {
                         "orderable" : false,
                         "data": "status",	        	
                         'sClass': "text-center"	 	         	       	
                       }
                     ],
            fnDrawCallback :function () {
                    let api = this.api();
                    let startIndex = api.context[0]._iDisplayStart;//获取本页开始的条数
                    api.column(0).nodes().each(function(cell, i) {
                        cell.innerHTML = startIndex + i + 1;
                    });
            }
        };
        	  
        var firstTable;
            firstTable = $('#table_id').DataTable(datatables_options);
        		
        $("#searchButton").click(function(){
            firstTable.ajax.reload(null,false); // 刷新表格数据，分页信息不会重置                         
            firstTable.draw(false);
        });	
        		
        $("#clearSearchButton").click(function(){
            $("#userName").val("");	
            $("#userCode").val("");
            $("#status").val("");
        });
    </script>
    </body>
    </html>

**项目结构如下图**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dzanp6Y2Jx_size_16_color_FFFFFF_t_70 1][]

### 3、运行程序，查看效果 ###

项目启动后，浏览器访问 [http://localhost:8900/html/index.html][http_localhost_8900_html_index.html]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dzanp6Y2Jx_size_16_color_FFFFFF_t_70 2][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dzanp6Y2Jx_size_16_color_FFFFFF_t_70 3][]

条件查询

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dzanp6Y2Jx_size_16_color_FFFFFF_t_70 4][]

用户名查询

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dzanp6Y2Jx_size_16_color_FFFFFF_t_70 5][]

多条件查询

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dzanp6Y2Jx_size_16_color_FFFFFF_t_70 6][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dzanp6Y2Jx_size_16_color_FFFFFF_t_70]: /images/20220222/5bbf9dad6be941ba9f260b1989e26152.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dzanp6Y2Jx_size_16_color_FFFFFF_t_70 1]: /images/20220222/71c07992712a43a4be060e75f84a4a0a.png
[http_localhost_8900_html_index.html]: http://localhost:8900/html/index.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dzanp6Y2Jx_size_16_color_FFFFFF_t_70 2]: /images/20220222/597b7a4be1284e4d966f704f675bde9b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dzanp6Y2Jx_size_16_color_FFFFFF_t_70 3]: /images/20220222/c38f912282c043a78d07f9ddcef75002.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dzanp6Y2Jx_size_16_color_FFFFFF_t_70 4]: /images/20220222/d5fad5ef110243d2b56cd9b8242ae480.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dzanp6Y2Jx_size_16_color_FFFFFF_t_70 5]: /images/20220222/91fc8741b27a4c5fb0e7b5727a4f6b65.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dzanp6Y2Jx_size_16_color_FFFFFF_t_70 6]: /images/20220222/6e1fc94c158e4a5ea320067c8c000871.png