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 }
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 }
页面构建 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"></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>
后台代码开发--
控制器
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 }
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 }
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 }
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 }
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>
启动项目--debug
