SET NAMES utf8mb4;SET FOREIGN_KEY_CHECKS =0;-- ------------------------------ Table structure for t_pers-- ----------------------------DROPTABLEIFEXISTS`t_pers`;CREATETABLE`t_pers`(`id`int(6)NOTNULLAUTO_INCREMENT,`name`varchar(80)DEFAULTNULL,`url`varchar(255)DEFAULTNULL,PRIMARYKEY(`id`))ENGINE=InnoDBDEFAULTCHARSET=utf8;-- ------------------------------ Table structure for t_role-- ----------------------------DROPTABLEIFEXISTS`t_role`;CREATETABLE`t_role`(`id`int(6)NOTNULLAUTO_INCREMENT,`name`varchar(60)DEFAULTNULL,PRIMARYKEY(`id`))ENGINE=InnoDBDEFAULTCHARSET=utf8;-- ------------------------------ Table structure for t_role_perms-- ----------------------------DROPTABLEIFEXISTS`t_role_perms`;CREATETABLE`t_role_perms`(`id`int(6)NOTNULL,`roleid`int(6)DEFAULTNULL,`permsid`int(6)DEFAULTNULL,PRIMARYKEY(`id`))ENGINE=InnoDBDEFAULTCHARSET=utf8;-- ------------------------------ Table structure for t_user-- ----------------------------DROPTABLEIFEXISTS`t_user`;CREATETABLE`t_user`(`id`int(6)NOTNULLAUTO_INCREMENT,`username`varchar(40)DEFAULTNULL,`password`varchar(40)DEFAULTNULL,`salt`varchar(255)DEFAULTNULL,PRIMARYKEY(`id`))ENGINE=InnoDBAUTO_INCREMENT=2DEFAULTCHARSET=utf8;-- ------------------------------ Table structure for t_user_role-- ----------------------------DROPTABLEIFEXISTS`t_user_role`;CREATETABLE`t_user_role`(`id`int(6)NOTNULL,`userid`int(6)DEFAULTNULL,`roleid`int(6)DEFAULTNULL,PRIMARYKEY(`id`))ENGINE=InnoDBDEFAULTCHARSET=utf8;SET FOREIGN_KEY_CHECKS =1;
5. 创建dao方法
//根据用户名查询所有角色
User findRolesByUserName(String username);
//根据角色id查询权限集合
List<Perms> findPermsByRoleId(String id);
**<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd"><mapper namespace="com.baizhi.springboot_jsp_shiro.dao.UserDAO"><insert id="save"parameterType="User"useGeneratedKeys="true"keyProperty="id">
insert into t_user values(#{id},#{username},#{password},#{salt})</insert><select id="findByUserName"parameterType="String"resultType="User">select id,username,password,salt from t_user
where username =#{username}</select><resultMap id="userMap"type="User"><id column="uid"property="id"/><result column="username"property="username"/><!--角色信息--><collection property="roles"javaType="list"ofType="Role"><id column="id"property="id"/><result column="rname"property="name"/></collection></resultMap><select id="findRolesByUserName"parameterType="String"resultMap="userMap">
SELECT u.id uid,u.username,r.id,r.NAME rname
FROM t_user u
LEFT JOIN t_user_role ur
ON u.id=ur.userid
LEFT JOIN t_role r
ON ur.roleid=r.id
WHERE u.username=#{username}</select><select id="findPermsByRoleId"parameterType="String"resultType="Perms">
SELECT p.id,p.NAME,p.url,r.NAME
FROM t_role r
LEFT JOIN t_role_perms rp
ON r.id=rp.roleid
LEFT JOIN t_perms p ON rp.permsid=p.id
WHERE r.id=#{id}</select></mapper>**