您的位置: 首页  >  IT文章  >  SpringBoot实战项目（三）--用户列表以及分页功能实现

SpringBoot实战项目（三）--用户列表以及分页功能实现

分类: IT文章 • 2023-11-14 08:58:19

sys_user（用户表）sys_role（角色表）sys_user_role(用户角色关联表)



CREATE TABLE `sys_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(255) NOT NULL COMMENT '用户名',
  `nick_name` varchar(255) DEFAULT NULL COMMENT '昵称',
  `password` varchar(255) NOT NULL COMMENT '密码',
  `sex` int(10) DEFAULT NULL COMMENT '性别 1=男',
  `telephone` varchar(255) NOT NULL COMMENT '电话',
  `email` varchar(255) NOT NULL COMMENT '邮箱',
  `birthday` date DEFAULT NULL COMMENT '生日',
  `status` int(10) NOT NULL COMMENT '状态',
  `createTime` datetime NOT NULL COMMENT '创建时间',
  `updateTime` datetime NOT NULL COMMENT '最后更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;




INSERT INTO `sys_user`(`id`, `user_name`, `nick_name`, `password`, `sex`, `telephone`, `email`, `birthday`, `status`, `createTime`, `updateTime`) VALUES (1, '张三', '大不列颠-张三', '123456', 1, '17687206393', '300@qq.com', '2020-04-01', 1, '2020-04-07 13:38:43', '2020-04-07 13:38:52');
INSERT INTO `sys_user`(`id`, `user_name`, `nick_name`, `password`, `sex`, `telephone`, `email`, `birthday`, `status`, `createTime`, `updateTime`) VALUES (2, '李四', '尼古拉斯-赵四', '123456', 1, '17687208353', '333@qq.com', '2020-04-10', 0, '2020-04-10 13:19:58', '2020-04-10 13:20:16');
INSERT INTO `sys_user`(`id`, `user_name`, `nick_name`, `password`, `sex`, `telephone`, `email`, `birthday`, `status`, `createTime`, `updateTime`) VALUES (3, '王五', '隔壁老王', '000000', 1, '13245785000', '22@qq.com', '2020-04-10', 0, '2020-04-10 13:21:06', '2020-04-10 13:21:10');
INSERT INTO `sys_user`(`id`, `user_name`, `nick_name`, `password`, `sex`, `telephone`, `email`, `birthday`, `status`, `createTime`, `updateTime`) VALUES (4, '赵六', '可好看', '000000', 1, '16875236945', 'aa@qq.com', '2019-10-24', 0, '2020-04-10 13:37:18', '2020-04-10 13:37:21');
INSERT INTO `sys_user`(`id`, `user_name`, `nick_name`, `password`, `sex`, `telephone`, `email`, `birthday`, `status`, `createTime`, `updateTime`) VALUES (5, '陈七', '随风起舞', '123456', 2, '17687206982', '66@qq.com', NULL, 1, '2020-04-07 13:38:08', '2020-04-28 13:38:11');
INSERT INTO `sys_user`(`id`, `user_name`, `nick_name`, `password`, `sex`, `telephone`, `email`, `birthday`, `status`, `createTime`, `updateTime`) VALUES (6, '王八', '铁血无双', '8888', 1, '19853687755', '88@qq.com', NULL, 0, '2020-03-31 13:38:55', '2020-04-13 13:38:59');
INSERT INTO `sys_user`(`id`, `user_name`, `nick_name`, `password`, `sex`, `telephone`, `email`, `birthday`, `status`, `createTime`, `updateTime`) VALUES (7, '李九', '死亡眼神', '88888', 1, '18496775309', '77@qq.com', NULL, 0, '2020-04-30 20:14:15', '2020-04-05 20:14:19');
INSERT INTO `sys_user`(`id`, `user_name`, `nick_name`, `password`, `sex`, `telephone`, `email`, `birthday`, `status`, `createTime`, `updateTime`) VALUES (8, 'admin', '逆天而行', '99999', 1, '18523698755', '55@qq.com', '2019-01-17', 1, '2020-04-01 20:15:38', '2020-03-25 20:15:42');
INSERT INTO `sys_user`(`id`, `user_name`, `nick_name`, `password`, `sex`, `telephone`, `email`, `birthday`, `status`, `createTime`, `updateTime`) VALUES (9, '十一', '星星有泪', '11111', 2, '16359874588', 'bb@qq.com', '2020-04-23', 1, '2020-04-09 20:16:36', '2020-04-10 20:16:40');
INSERT INTO `sys_user`(`id`, `user_name`, `nick_name`, `password`, `sex`, `telephone`, `email`, `birthday`, `status`, `createTime`, `updateTime`) VALUES (10, 'user', '穷凶极恶', '88888', 1, '15689321456', '99@qq.com', NULL, 1, '2020-04-29 20:17:26', '2020-04-13 20:17:29');
INSERT INTO `sys_user`(`id`, `user_name`, `nick_name`, `password`, `sex`, `telephone`, `email`, `birthday`, `status`, `createTime`, `updateTime`) VALUES (11, '十三', '好久时光', '00000', 2, '15286767244', 'iuouh@qq.com', NULL, 1, '2020-04-10 20:18:32', '2020-04-10 20:18:35');





CREATE TABLE `sys_role` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `role_name` varchar(255) NOT NULL COMMENT '角色名',
  `remark` varchar(255) DEFAULT NULL COMMENT '角色备注',
  `createTime` datetime DEFAULT NULL COMMENT '创建时间',
  `updateTime` datetime NOT NULL COMMENT '最后修改时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;




INSERT INTO `sys_role`(`id`, `role_name`, `remark`, `createTime`, `updateTime`) VALUES (1, '超级管理员', '拥有本系统的一切权限', '2020-04-11 21:03:08', '2020-04-11 21:03:11');
INSERT INTO `sys_role`(`id`, `role_name`, `remark`, `createTime`, `updateTime`) VALUES (2, '普通用户', '系统权限受限', '2020-04-11 21:03:49', '2020-04-11 21:03:59');
INSERT INTO `sys_role`(`id`, `role_name`, `remark`, `createTime`, `updateTime`) VALUES (3, '测试用户', '测试专用', '2020-04-11 21:04:22', '2020-04-11 21:04:24');





CREATE TABLE `sys_user_role` (
  `userId` int(11) NOT NULL,
  `roleId` int(11) NOT NULL,
  PRIMARY KEY (`userId`),
  KEY `fk_rolerId` (`roleId`),
  CONSTRAINT `fk_rolerId` FOREIGN KEY (`roleId`) REFERENCES `sys_role` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `fk_userId` FOREIGN KEY (`userId`) REFERENCES `sys_user` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8;




INSERT INTO `sys_user_role`(`userId`, `roleId`) VALUES (1, 1);
INSERT INTO `sys_user_role`(`userId`, `roleId`) VALUES (2, 2);
INSERT INTO `sys_user_role`(`userId`, `roleId`) VALUES (3, 3);


 





entity包下新建BaseEntity, SysUser实体类


 BaseEntity--抽取公共基础字段





 1 package com.beilin.entity;
 2 
 3 import com.fasterxml.jackson.annotation.JsonFormat;
 4 import lombok.Data;
 5 
 6 import java.io.Serializable;
 7 import java.util.Date;
 8 
 9 @Data
10 public abstract class BaseEntity<ID extends Serializable> implements Serializable {
11 
12     private static final long serialVersionUID = 8925514045582235838L;
13     private ID id;
14     private Date createTime = new Date();
15     @JsonFormat(pattern = "yyyy-MM-dd  HH:mm:ss")
16     private Date updateTime = new Date();
17 
18 }



BaseEntity



SysUser实体类继承BaseEntity





 1 package com.beilin.entity;
 2 
 3 import com.fasterxml.jackson.annotation.JsonFormat;
 4 import lombok.Data;
 5 import lombok.EqualsAndHashCode;
 6 
 7 import java.util.Date;
 8 
 9 
10 @Data
11 @EqualsAndHashCode(callSuper = true)
12 public class SysUser extends BaseEntity<Long> {
13 
14     private String userName;
15     private String passWord;
16     private String nickName;
17     private Integer sex;
18     private String telephone;
19     private String email;
20     @JsonFormat(pattern = "yyyy-MM-dd")
21     private Date birthday;
22     private Integer status;
23 
24 
25 }



SysUser 



页面构建 user-list.html





  1 <!DOCTYPE html>
  2 <html class="x-admin-sm" xmlns:th="http://www.thymeleaf.org">
  3     <head>
  4         <meta charset="UTF-8">
  5         <title>欢迎页面-X-admin2.2</title>
  6           <header th:replace="header.html"></header>
  7     </head>
  8     <body class="childrenBody">
  9         <div class="x-nav">
 10           <span class="layui-breadcrumb">
 11             <a href="">用户列表</a>
 12             <a>
 13               <cite>【云深不知处】</cite></a>
 14           </span>
 15           <a class="layui-btn layui-btn-small" style="line-height:1.6em;margin-top:3px;float:right" onclick="location.reload()" title="刷新">
 16             <i class="layui-icon layui-icon-refresh" style="line-height:30px"></i></a>
 17         </div>
 18     <div class="layui-card-body ">
 19         <div class="layui-card-header" style="display: inline">
 20              <button class="layui-btn layui-btn-danger" onclick="delAll()"><i class="layui-icon">�</i>批量删除</button>
 21              <button class="layui-btn" onclick="xadmin.open('添加用户','/user/add')"><i class="layui-icon">�</i>添加</button>
 22         </div>
 23            <form class="layui-form  layui-col-space5" style="display: inline">
 24              <div class="layui-inline layui-show-xs-block">
 25                 <input type="text" name="userName"  placeholder="请输入用户名" autocomplete="off" class="layui-input">
 26              </div>
 27              <div class="layui-inline layui-show-xs-block">
 28                  <button class="layui-btn"  lay-submit="" lay-filter="search"><i class="layui-icon">&#xe615;</i></button>
 29              </div>
 30            </form>
 31 
 32 
 33         <div class="layui-card-body ">
 34             <table class="layui-hide" id="table" lay-filter="member"></table>
 35         </div>
 36          <div class="layui-card-body ">
 37              <script type="text/html" id="barDemo">
 38                      <a class="layui-btn layui-btn-xs" lay-event="edit">编辑</a>
 39                      <a class="layui-btn layui-btn-danger layui-btn-xs" lay-event="del" >删除</a>
 40              </script>
 41          </div>
 42     </div>
 43 </body>
 44     <script>
 45             layui.use(['table'], function(){
 46                 var table = layui.table;
 47                 form = layui.form;
 48                 //第一个实例
 49                 table.render({
 50                     elem: '#table'
 51                     ,url: '/user/list' //数据接口
 52                     ,toolbar: '#toolbarDemo' //开启头部工具栏，并为其绑定左侧模板
 53                     ,page: true //开启分页
 54                     ,response: {
 55                         countName: 'count', //规定数据总数的字段名称，默认：count
 56                         dataName: 'datas' //规定数据列表的字段名称，默认：data
 57                     }
 58 
 59                     ,cols: [
 60                         [ //表头
 61                             {type: 'checkbox',fixed: 'left'}
 62                             ,{field: 'id', title: 'ID', align:'center', 60,sort: true}
 63                             ,{field: 'userName', title: '用户名', 80}
 64                             ,{field: 'nickName', title: '昵称',120}
 65                             ,{field: 'sex', title: '性别' ,75,align:'center',sort: true,templet:function (s) {
 66                                 return s.sex == '1'?'男':'女';
 67                             }}
 68                             ,{field: 'telephone', title: '手机', 120}
 69                             ,{field: 'email', title: '邮箱',165}
 70                             ,{field: 'status', title: '状态', 60,align:'center', templet:function (d) {
 71                                 return d.status == '1'?'启动':'禁用';
 72                             }}
 73                             ,{field: 'birthday', title: '生日',105}
 74                             ,{title:'操作', toolbar: '#barDemo'}
 75                         ]
 76                     ],
 77                     done:function() {
 78                         checkPermission()
 79                     }
 80                     });
 81 
 82                  //监听工具条
 83             table.on('tool(member)', function(obj){
 84                 var data = obj.data;
 85                 if(obj.event === 'del'){
 86                     layer.confirm('真的删除行么', function(index){
 87 
 88                         $.ajax({
 89                             url:"/user/delete",
 90                             type:"GET",
 91                             data:{id:data.id},
 92                             dataType:'json',
 93                             success:function(result){
 94                                 layer.alert("删除成功", {icon: 1},function (index1) {
 95                                     layer.close(index1);
 96                                     //xadmin.father_reload();
 97                                     table.reload('table');
 98                                 });
 99                             },
100                         });
101 
102                     });
103                 } else if(obj.event === 'edit'){
104                     xadmin.open('编辑用户信息','/user/edit/?id='+data.id);
105                 }
106             });
107 
108 
109             //搜索
110             form.on('submit(search)', function(data){
111                 var userName = data.field.userName;
112                 table.render({
113                      elem: '#table'
114                     ,url: ''//数据接口
115                     ,type:"GET"
116                     ,page: true //开启分页
117                     ,where:{
118                         "userName":userName
119                     }
120                     ,response: {
121 
122                         countName: 'count', //规定数据总数的字段名称，默认：count
123                         dataName: 'datas' //规定数据列表的字段名称，默认：data
124                     }
125                      ,cols: [
126                         [ //表头
127                             {type: 'checkbox',fixed: 'left'}
128                             ,{field: 'id', title: 'ID', align:'center', 60,sort: true}
129                             ,{field: 'userName', title: '用户名', 80}
130                             ,{field: 'nickName', title: '昵称',120}
131                             ,{field: 'sex', title: '性别' ,75,align:'center',sort: true,templet:function (s) {
132                                 return s.sex == '1'?'男':'女';
133                             }}
134                             ,{field: 'telephone', title: '手机', 120}
135                             ,{field: 'email', title: '邮箱',165}
136                             ,{field: 'status', title: '状态', 60,align:'center', templet:function (d) {
137                                 return d.status == '1'?'启动':'禁用';
138                             }}
139                             ,{field: 'birthday', title: '生日',105}
140                             ,{title:'操作', toolbar: '#barDemo'}
141                         ]
142                     ]
143                 });
144                 return false;
145             });
146             checkPermission();
147         });
148         </script>
149 </html>



user-list.html



后台代码开发--


控制器





 1 package com.beilin.controller;
 2 
 3 import com.beilin.Service.UserService;
 4 import com.beilin.entity.SysUser;
 5 import com.beilin.result.Page;
 6 import com.beilin.result.Results;
 7 import io.swagger.annotations.ApiImplicitParam;
 8 import io.swagger.annotations.ApiOperation;
 9 import org.springframework.beans.factory.annotation.Autowired;
10 import org.springframework.stereotype.Controller;
11 import org.springframework.web.bind.annotation.GetMapping;
12 import org.springframework.web.bind.annotation.RequestMapping;
13 import org.springframework.web.bind.annotation.ResponseBody;
14 
15 
16 @Controller
17 @RequestMapping("/user")
18 public class UserController {
19     @Autowired
20     private UserService userService;
21 
22     /**
23      * 获取用户信息并分页功能实现
24      * @param page
25      * @return
26      */
27     @GetMapping("/list")
28     @ResponseBody
29 
30     public Results<SysUser> list(Page page){
31         page.countOffset();
32         return userService.getAllUser(page.getOffset(),page.getLimit());
33     }
34 
35   
36 }



UserController



service层





 1 package com.beilin.Service;
 2 
 3 import com.beilin.entity.SysUser;
 4 import com.beilin.result.Results;
 5 
 6 
 7 public interface UserService {
 8 
 9     Results<SysUser> getAllUser(Integer pageNum, Integer limit);
10 
11   
12 }



UserService 



service实现类





 1 package com.beilin.Service.Impl;
 2 
 3 import com.beilin.Service.UserService;
 4 import com.beilin.dao.UserDao;
 5 import com.beilin.entity.SysUser;
 6 import com.beilin.result.Results;
 7 import org.springframework.beans.factory.annotation.Autowired;
 8 import org.springframework.stereotype.Service;
 9 import org.springframework.transaction.annotation.Transactional;
10 
11 import java.util.List;
12 
13 @Service
14 @Transactional
15 public class UserServiceImpl implements UserService {
16     @Autowired
17     private UserDao userDao;
18 
19     /**
20      * 查询所有用户并分页
21      * @param pageNum
22      * @param limit
23      * @return
24      */
25     @Override
26     public Results<SysUser> getAllUser(Integer pageNum, Integer limit) {
27 //        Long count = userDao.countAllUsers();
28 //        List<SysUser> userList =userDao.getAllUserByPage(pageNum, limit);
29         return Results.success(userDao.countAllUsers().intValue(),userDao.getAllUserByPage(pageNum, limit));
30     }
31 
32    
33 }



UserServiceImpl 


Dao层





 1 package com.beilin.dao;
 2 
 3 import com.beilin.entity.SysUser;
 4 import org.apache.ibatis.annotations.Param;
 5 
 6 import java.util.List;
 7 
 8 
 9 public interface UserDao {
10 
11     /**
12      * 查询所有用户信息并分页
13      * @param pageNum
14      * @param limit
15      * @return
16      */
17     List<SysUser> getAllUserByPage(@Param("pageNum") Integer pageNum, @Param("limit") Integer limit);
18     Long countAllUsers();
19 
20     
21 }



UserDao 


SQL映射文件





 1 <?xml version="1.0" encoding="utf-8" ?>
 2 <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
 3 <mapper namespace="com.beilin.dao.UserDao">
 4     <resultMap id="UserMap" type="com.beilin.entity.SysUser">
 5         <id property="id" column="id"/>
 6         <result property="userName" column="user_name"/>
 7         <result property="nickName" column="nick_name"/>
 8         <result property="passWord" column="password"/>
 9         <result property="sex" column="sex"/>
10         <result property="telephone" column="telephone"/>
11         <result property="email" column="email"/>
12         <result property="birthday" column="birthday"/>
13         <result property="status" column="status"/>
14         <result property="createTime" column="createTime"/>
15         <result property="updateTime" column="updateTime"/>
16     </resultMap>
17     <sql id="Base_result">
18         id,user_name,nick_name,password,sex,telephone,email,birthday,
19         status,createTime,updateTime
20     </sql>
21     <!--查询所有用户-->
22     <select id="getAllUserByPage" resultType="com.beilin.entity.SysUser">
23         select<include refid="Base_result"/> from sys_user order by id limit #{pageNum},#{limit};
24     </select>
25     <!--总记录数-->
26     <select id="countAllUsers" resultType="java.lang.Long">
27         SELECT count(*) from sys_user
28     </select>
29  
30 
31 </mapper>



UserMapper.xml


启动项目--debug


SpringBoot实战项目（三）--用户列表以及分页功能实现
															
																					

								

									相关推荐