mp和mybatis注解实现3表联合查询，从用户id查角色，从角色查资源。springboot 2.4.3，以后，url字符串，可以不加时区了
代码如下，就是多表时用many=@many的时候有些绕，这里的column ，是要传入下一个接口查询的参数
-----------------------------------------------------------------------------
实体类
User
添加属性roleList，作关系映射
package cn.taotao.bean; @Data @AllArgsConstructor @NoArgsConstructor @TableName("sys_user") public class User implements Serializable { @TableId private Integer id; private String username; private String password; private Integer status; @TableField(exist = false) private List<Role> roleList; private static final long serialVersionUID = 1L; }
Role
添加属性permissionList，作关系映射
package cn.taotao.bean;
@Data @AllArgsConstructor @NoArgsConstructor @TableName("sys_role") public class Role implements Serializable { @TableId private Integer id; private String roleName; private String roleDesc; @TableField(exist = false) private List<Permission> permissionList; private static final long serialVersionUID = 1L; }
Permission
package cn.taotao.bean; @Data @AllArgsConstructor @NoArgsConstructor @TableName("sys_permission") public class Permission implements Serializable { @TableId private Integer id; private String permissionName; private String permissionUrl; private Integer parentId; private static final long serialVersionUID = 1L; }
Dao层
UserDao
package cn.taotao.dao; public interface UserDao extends BaseMapper<User> { @Select("select * from sys_user where id=#{id}") @Results({ @Result(id=true, column = "id",property = "id"), @Result(column = "username",property = "username"), @Result(column = "password",property = "password"), @Result(javaType = List.class,property = "roleList",column = "id", // 这里的id，是将要传入下一个接口的入参 many=@Many(select="cn.taotao.dao.RoleDao.findRoleById")) }) User findUserAndRoleById(int id); }
RoleDao
package cn.taotao.dao; public interface RoleDao extends BaseMapper<Role> { @Select("SELECT * FROM sys_role r ,sys_user_role ur WHERE r.`ID`=ur.`RID`AND ur.`UID`=#{id}" ) @Results({ @Result(id = true, property = "id",column = "id"), @Result(property = "roleName",column = "role_name"), @Result(property = "roleDesc",column = "role_desc"), @Result(property = "permissionList",column = "rid",many = @Many(select="cn.taotao.dao.PermissionDao.findPermissionAndRoleById")) // 这里的rid是传入下一个接口的入参 }) List<Role> findRoleById(int id); }
PermissionDao
package cn.taotao.dao; public interface PermissionDao extends BaseMapper<Permission> { @Select("SELECT * FROM sys_permission p ,sys_role_permission rp WHERE p.`ID`=rp.`PID` AND rp.`RID`=#{id}") public List<Permission> findPermissionAndRoleById(int id); }
测试类
@SpringBootTest class Security3ApplicationTests { @Autowired private UserDao userDao; // 通过用户id，查询到所有的角色，然后通过角色，查询到所有的资源。 @Test void findUserAndRole (){ User userAndRoleById = this.userDao.findUserAndRoleById(4); System.out.println("userAndRoleById = " + userAndRoleById); }
application.yml
spring:
datasource:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/security #springboot 2.4.3，以后，url字符串，可以不加时区了
username: root
password: xxxxxx
mybatis-plus: #这里是plus，不是原始的mybatis
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
pom
<?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>2.5.13</version> <relativePath/> <!-- lookup parent from repository --> </parent> <groupId>cn.taotao</groupId> <artifactId>security3</artifactId> <version>0.0.1-SNAPSHOT</version> <name>security3</name> <description>security3</description> <properties> <java.version>1.8</java.version> </properties> <dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-plus-boot-starter</artifactId> <version>3.5.1</version> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-devtools</artifactId> <scope>runtime</scope> <optional>true</optional> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-configuration-processor</artifactId> <optional>true</optional> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <optional>true</optional> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> </dependencies> <build> <plugins> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> <configuration> <excludes> <exclude> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> </exclude> </excludes> </configuration> </plugin> </plugins> </build> </project>
sql语句
/* SQLyog Ultimate v12.08 (64 bit) MySQL - 8.0.16 : Database - security_authority ********************************************************************* */ /*!40101 SET NAMES utf8 */; /*!40101 SET SQL_MODE=''*/; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; /*Table structure for table `sys_permission` */ DROP TABLE IF EXISTS `sys_permission`; CREATE TABLE `sys_permission` ( `ID` int(11) NOT NULL AUTO_INCREMENT COMMENT '编号', `permission_NAME` varchar(30) DEFAULT NULL COMMENT '菜单名称', `permission_url` varchar(100) DEFAULT NULL COMMENT '菜单地址', `parent_id` int(11) NOT NULL DEFAULT '0' COMMENT '父菜单id', PRIMARY KEY (`ID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*Data for the table `sys_permission` */ /*Table structure for table `sys_role` */ DROP TABLE IF EXISTS `sys_role`; CREATE TABLE `sys_role` ( `ID` int(11) NOT NULL AUTO_INCREMENT COMMENT '编号', `ROLE_NAME` varchar(30) DEFAULT NULL COMMENT '角色名称', `ROLE_DESC` varchar(60) DEFAULT NULL COMMENT '角色描述', PRIMARY KEY (`ID`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8; /*Data for the table `sys_role` */ /*Table structure for table `sys_role_permission` */ DROP TABLE IF EXISTS `sys_role_permission`; CREATE TABLE `sys_role_permission` ( `RID` int(11) NOT NULL COMMENT '角色编号', `PID` int(11) NOT NULL COMMENT '权限编号', PRIMARY KEY (`RID`,`PID`), KEY `FK_Reference_12` (`PID`), CONSTRAINT `FK_Reference_11` FOREIGN KEY (`RID`) REFERENCES `sys_role` (`ID`), CONSTRAINT `FK_Reference_12` FOREIGN KEY (`PID`) REFERENCES `sys_permission` (`ID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*Data for the table `sys_role_permission` */ /*Table structure for table `sys_user` */ DROP TABLE IF EXISTS `sys_user`; CREATE TABLE `sys_user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(32) NOT NULL COMMENT '用户名称', `password` varchar(120) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '密码', `status` int(1) DEFAULT '1' COMMENT '1开启0关闭', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8; /*Data for the table `sys_user` */ /*Table structure for table `sys_user_role` */ DROP TABLE IF EXISTS `sys_user_role`; CREATE TABLE `sys_user_role` ( `UID` int(11) NOT NULL COMMENT '用户编号', `RID` int(11) NOT NULL COMMENT '角色编号', PRIMARY KEY (`UID`,`RID`), KEY `FK_Reference_10` (`RID`), CONSTRAINT `FK_Reference_10` FOREIGN KEY (`RID`) REFERENCES `sys_role` (`ID`), CONSTRAINT `FK_Reference_9` FOREIGN KEY (`UID`) REFERENCES `sys_user` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*Data for the table `sys_user_role` */ /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
