java查询数据库绑定到layui表格上,含有分页

更多具体说明请看官方文档:layui.com
效果如下

这里把我的pom全上来,有的用不到自行筛选

  <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

        <!--mybatis得依赖-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.3</version>
        </dependency>
        <!--mysql得依赖-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>

        <!-- 引入jstl标签，配套jsp使用，可以不导入 -->
        <dependency>
            <groupId>jstl</groupId>
            <artifactId>jstl</artifactId>
            <version>1.2</version>
        </dependency>


        <!-- 配置内嵌的tomcat解析jsp -->
        <dependency>
            <groupId>org.apache.tomcat.embed</groupId>
            <artifactId>tomcat-embed-jasper</artifactId>
        </dependency>

        <!--devtools热部署-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <optional>true</optional>
            <scope>true</scope>
        </dependency>


        <!-- easypoi的支持 -->
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>4.1.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-web</artifactId>
            <version>4.1.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-annotation</artifactId>
            <version>4.1.0</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/com.alipay.sdk/alipay-easysdk -->
        <!--阿里沙箱环境-->
        <!-- 支付宝支付jar包 -->
        <dependency>
            <groupId>com.alipay.sdk</groupId>
            <artifactId>alipay-sdk-java</artifactId>
            <version>3.1.0</version>
        </dependency>
        <dependency>
            <groupId>com.vaadin.external.google</groupId>
            <artifactId>android-json</artifactId>
            <version>0.0.20131108.vaadin1</version>
            <scope>compile</scope>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

        <!-- 文件上传 start by zhangyd-c -->
        <dependency>
            <groupId>commons-fileupload</groupId>
            <artifactId>commons-fileupload</artifactId>
            <version>1.3.1</version>
        </dependency>
        <dependency>
            <groupId>commons-io</groupId>
            <artifactId>commons-io</artifactId>
            <version>2.4</version>
        </dependency>
        <!-- 验证码 -->
        <dependency>
            <groupId>com.github.penggle</groupId>
            <artifactId>kaptcha</artifactId>
            <version>2.3.2</version>
        </dependency>

        <!-- 引入redis -->
        <dependency>
            <groupId>redis.clients</groupId>
            <artifactId>jedis</artifactId>
            <type>jar</type>
            <scope>compile</scope>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-redis</artifactId>
            <version>2.3.0.RELEASE</version>
        </dependency>
    </dependencies>

application.properties

#连接数据库
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
#url
spring.datasource.url= jdbc:mysql://localhost:3306/test?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&useSSL=false
#用户名
spring.datasource.username=root
#密码
spring.datasource.password=root
#扫描mapper文件
mybatis.mapper-locations=classpath:mybatis/mapper/*.xml
#开启打印日志
logging.level.com.wyh.dao=debug
#配置视图的前缀和后缀
spring.mvc.view.prefix=/
spring.mvc.view.suffix=.jsp

# 下面为连接池的补充设置，应用到上面所有数据源中
spring.datasource.initialSize=5
spring.datasource.minIdle=5
spring.datasource.maxActive=20
# 配置获取连接等待超时的时间
spring.datasource.maxWait=60000
# 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
spring.datasource.timeBetweenEvictionRunsMillis=60000
# 配置一个连接在池中最小生存的时间，单位是毫秒
spring.datasource.minEvictableIdleTimeMillis=300000
spring.datasource.validationQuery=SELECT 1 FROM DUAL
spring.datasource.testWhileIdle=true
spring.datasource.testOnBorrow=false
spring.datasource.testOnReturn=false
# 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
spring.datasource.filters=stat,wall
#开启慢sql的查询
spring.datasource.logSlowSql=true



#添加那个目录的文件需要restart
spring.devtools.restart.additional-paths=src/main/java
#排除那个目录的文件不需要restart
spring.devtools.restart.exclude=static/**,public/**
#页面不加载缓存  及时生效
spring.devtools.freemarker.cache=false   

首先数据库基本的jar,layui文件夹配置都要有,这些之前都写过
dao(service)

package com.wyh.dao;

import org.apache.ibatis.annotations.Mapper;

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

@Mapper
public interface ITestDao {
    List<Map> queryStudent(Integer page,Integer limit );
}

mapper

<?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.wyh.dao.ITestDao">


    <select id="queryStudent" resultType="java.util.Map">
        select * from student
        <if test="limit !=null and page!=null">
            limit ${page},${limit}
        </if>

    </select>

service和dao一样的,这里直接写实现

package com.wyh.service.impl;

import com.wyh.dao.ITestDao;
import com.wyh.service.ITestService;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.List;
import java.util.Map;
@Service
public class IServiceImpl implements ITestService {
    @Resource
    ITestDao testDao;
    @Override
    public List<Map> queryStudent(Integer page,Integer limit ) {
        return testDao.queryStudent(page,limit);
    }

    


}

controller

package com.wyh.controller;

import com.wyh.service.ITestService;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Controller
public class TestController {
    @Resource
    ITestService testService;
    @RequestMapping("/demo")
    @ResponseBody
    public Map<String, Object> test(Integer page,Integer limit){
        Integer page1 = (page-1)*limit;
        Map<String,Object> map = new HashMap<>();
        map.put("code","0");
        map.put("msg","");
        map.put("count",testService.queryStudent(null,null).size());
        map.put("data", testService.queryStudent(page1, limit));
        return map;
    }
    @RequestMapping("/showData")
    public String showData(){
        return "testJsp";
    }
}

jsp

<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%--
  Created by IntelliJ IDEA.
  User: wx_weiyihe
  Date: 2021/8/19
  Time: 15:51
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<!--引入layui的css-->
<link rel="stylesheet" href="../layui-v2.6.8/layui/css/layui.css">
<!--引入layui的js-->
<script type="text/javascript" src="../layui-v2.6.8/layui/layui.js"></script>


<html>
<head>
    <title>Title</title>
</head>
<body>

<body>
<table  id="demo" lay-filter="test" lay-even>
</table>
<script type="text/html" id="barDemo">
    <a class="layui-btn layui-btn-primary layui-btn-xs" lay-event="detail">查看</a>
    <a class="layui-btn layui-btn-xs" lay-event="more">更多 <i class="layui-icon layui-icon-down"></i></a>
</script>
<script>
    layui.use('table', function(){
        var table = layui.table;
        //第一个实例
        table.render({
            elem: '#demo' //实例化那个table的id
            ,height: 520  //表格高度
            ,url: 'demo' //数据接口
            ,page: true //开启分页
            ,limits:[1,2,3,4,5,10,15,20,30] //分页条数列表
            ,toolbar: 'default' //打开工具栏
            ,limit:3  //默认分页条数
            ,cols: [[ //表头
                {type: 'checkbox', fixed: 'center'}
                ,{field: 'StudentNo', title: '学号', width:140, sort: true, fixed: 'left',align:'center'}
                ,{field: 'StudentName', title: '姓名', width:100,align:'center'}
                ,{field: 'Sex', title: '性别', width:80,align:'center'}
                ,{field: 'Phone', title: '电话', width:130,align:'center'}
                ,{field: 'Address', title: '地址', width: 177,align:'center'}
                ,{field: 'BornDate', title: '出生日期', width: 180, sort: true,align:'center'}
                ,{field: 'Email', title: '邮箱', width: 180,align:'center'}
                ,{fixed: 'right', width: 150, align:'center', toolbar: '#barDemo'}
            ]]

        });
        //监听头工具栏事件
        table.on('toolbar(test)', function(obj){
            var checkStatus = table.checkStatus(obj.config.id)
                ,data = checkStatus.data; //获取选中的数据
            switch(obj.event){
                case 'add':
                    layer.msg('添加');
                    break;
                case 'update':
                    if(data.length === 0){
                        layer.msg('请选择一行');
                    } else if(data.length > 1){
                        layer.msg('只能同时编辑一个');
                    } else {
                        layer.alert('编辑 [id]：'+ checkStatus.data[0].id);
                    }
                    break;
                case 'delete':
                    if(data.length === 0){
                        layer.msg('请选择一行');
                    } else {
                        layer.msg('删除');
                    }
                    break;
            };
        });

        //监听行工具事件
        table.on('tool(test)', function(obj){ //注：tool 是工具条事件名，test 是 table 原始容器的属性 lay-filter="对应的值"
            var data = obj.data //获得当前行数据
                ,layEvent = obj.event; //获得 lay-event 对应的值
            if(layEvent === 'detail'){
                layer.msg('查看操作');
            } else if(layEvent === 'more'){
                //下拉菜单
                dropdown.render({
                    elem: this //触发事件的 DOM 对象
                    ,show: true //外部事件触发即显示
                    ,data: [{
                        title: '编辑'
                        ,id: 'edit'
                    },{
                        title: '删除'
                        ,id: 'del'
                    }]
                    ,click: function(menudata){
                        if(menudata.id === 'del'){
                            layer.confirm('真的删除行么', function(index){
                                obj.del(); //删除对应行（tr）的DOM结构
                                layer.close(index);
                                //向服务端发送删除指令
                            });
                        } else if(menudata.id === 'edit'){
                            layer.msg('编辑操作，当前行 ID:'+ data.id);
                        }
                    }
                    ,align: 'right' //右对齐弹出（v2.6.8 新增）
                    ,style: 'box-shadow: 1px 1px 10px rgb(0 0 0 / 12%);' //设置额外样式
                })
            }
        });

    });
</script>
</body>
</body>
</html>

访问路径

http://localhost:8080/showData

项目目录