用Springboot+layui+jquery实现前后端分离表格分页查询

前端页面

前端使用layui作为UI框架，使用jquery发起ajax请求获取数据。

首先需要引入layui和jquery的CDN：

<!-- 引入layui -->
<link rel="stylesheet" href="https://cdn.bootcdn.net/ajax/libs/layui/2.5.7/css/layui.min.css" integrity="sha384-4mIT5kNQ+9jz4pK5tXuwkQbK8Sd5k/4vLjRQqJc4nBxHJ5r4eXwFvX1Q9Jr+3I/I" crossorigin="anonymous">
<script src="https://cdn.bootcdn.net/ajax/libs/layui/2.5.7/layui.min.js" integrity="sha384-pI0JXAuRSt8VJrHwKvZKtGpQwX6KnZ1U/4IMpQy4nMlLMsDqV8yqW0UJcY2Qh34I" crossorigin="anonymous"></script>

<!-- 引入jquery -->
<script src="https://cdn.bootcdn.net/ajax/libs/jquery/3.5.1/jquery.min.js"></script>

然后在页面中添加表格和分页组件：

<table id="userTable" class="layui-table" lay-filter="userTable"></table>
<div id="userPage" class="layui-laypage"></div>

接下来就是使用jquery发起ajax请求获取数据，并将数据渲染到表格中：

// 发起ajax请求获取数据
$.ajax({
    url: '/user/list',
    dataType: 'json',
    data: {
        page: page,
        limit: limit,
        keyword: keyword
    },
    success: function (res) {
        // 渲染表格
        table.render({
            elem: '#userTable',
            data: res.data,
            cols: [[
                {field: 'id', title: 'ID'},
                {field: 'username', title: '用户名'},
                {field: 'email', title: '邮箱'},
                {field: 'phone', title: '手机号码'},
                {field: 'status', title: '状态', templet: '#statusTpl'},
                {field: 'createTime', title: '创建时间'},
                {title: '操作', toolbar: '#operationBar'}
            ]]
        });

        // 渲染分页组件
        laypage.render({
            elem: '#userPage',
            count: res.count,
            limit: limit,
            curr: page,
            jump: function (obj, first) {
                if (!first) {
                    page = obj.curr;
                    limit = obj.limit;
                    getUserList();
                }
            }
        });
    }
});

后端接口

后端使用Springboot框架，使用Mybatis作为数据访问层。

首先需要定义一个User实体类，用于映射数据库中的user表：

@Data
public class User {
    private Long id;
    private String username;
    private String password;
    private String email;
    private String phone;
    private Integer status;
    private Date createTime;
    private Date updateTime;
}

然后定义一个UserService接口，用于定义用户相关的服务：

public interface UserService {
    List<User> getUserList(Integer page, Integer limit, String keyword);

    Integer getUserCount(String keyword);
}

接下来实现UserService接口：

@Service
public class UserServiceImpl implements UserService {
    @Autowired
    private UserMapper userMapper;

    @Override
    public List<User> getUserList(Integer page, Integer limit, String keyword) {
        int offset = (page - 1) * limit;
        return userMapper.getUserList(offset, limit, keyword);
    }

    @Override
    public Integer getUserCount(String keyword) {
        return userMapper.getUserCount(keyword);
    }
}

在UserMapper接口中定义对user表的查询操作：

public interface UserMapper {
    List<User> getUserList(@Param("offset") int offset, @Param("limit") int limit, @Param("keyword") String keyword);

    Integer getUserCount(@Param("keyword") String keyword);
}

最后实现UserMapper接口：

<mapper namespace="com.example.demo.mapper.UserMapper">
    <select id="getUserList" resultType="User">
        select * from user
        <where>
            <if test="keyword != null and keyword != ''">
                and (username like concat('%', #{keyword}, '%')
                or email like concat('%', #{keyword}, '%')
                or phone like concat('%', #{keyword}, '%'))
            </if>
        </where>
        order by create_time desc
        limit #{offset}, #{limit}
    </select>

    <select id="getUserCount" resultType="int">
        select count(*) from user
        <where>
            <if test="keyword != null and keyword != ''">
                and (username like concat('%', #{keyword}, '%')
                or email like concat('%', #{keyword}, '%')
                or phone like concat('%', #{keyword}, '%'))
            </if>
        </where>
    </select>
</mapper>

在UserController中定义对用户列表的请求处理：

@RestController
@RequestMapping("/user")
public class UserController {
    @Autowired
    private UserService userService;

    @GetMapping("/list")
    public Result getUserList(@RequestParam(required = false, defaultValue = "1") Integer page,
                              @RequestParam(required = false, defaultValue = "10") Integer limit,
                              @RequestParam(required = false) String keyword) {
        List<User> userList = userService.getUserList(page, limit, keyword);
        Integer count = userService.getUserCount(keyword);
        return Result.success(userList, count);
    }
}

最后返回的数据格式为：

{
    "code": 0,
    "msg": "",
    "count": 100,
    "data": [
        {
            "id": 1,
            "username": "张三",
            "email": "zhangsan@example.com",
            "phone": "13888888888",
            "status": 1,
            "createTime": "2021-01-01 00:00:00"
        },
        {
            "id": 2,
            "username": "李四",
            "email": "lisi@example.com",
            "phone": "13999999999",
            "status": 1,
            "createTime": "2021-01-01 00:00:00"
        }
    ]
}

这样就完成了Springboot+layui+jquery实现前后端分离表格分页查询的示例