mybatis使用resultMap实现多表的简单查询

首先给出结果图

主要使用到了collection这个字段

ddl语句

CREATE TABLE `user` (
   id INT(11) PRIMARY KEY AUTO_INCREMENT  COMMENT '主键',
  `name` VARCHAR(255)  COMMENT '用户名',
  `password` VARCHAR(255)  COMMENT '密码'
)
 
CREATE TABLE `role` (
   id INT(11) PRIMARY KEY AUTO_INCREMENT NOT NULL  COMMENT '主键',
   role_name VARCHAR(255) DEFAULT NULL COMMENT '角色名称',
  user_id VARCHAR(255) DEFAULT NULL COMMENT '用户id'
)
 
CREATE TABLE permission (
   id INT(11) PRIMARY  KEY AUTO_INCREMENT NOT NULL  COMMENT '主键',
   permission VARCHAR(255) DEFAULT NULL COMMENT '权限',
  role_id VARCHAR(255) DEFAULT NULL COMMENT '角色id'
)

　dml语句

INSERT INTO `user`(`name`,`password`) VALUES('test','123456')
 
INSERT INTO `role`(role_name,user_id) VALUES('admin','1')
 
INSERT INTO permission(permission,role_id) VALUES
('create','1'),
('query','1')

　项目代码

<?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 http://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.3.4.RELEASE</version>
        <relativePath/>
    </parent>
 
    <groupId>com.java</groupId>
    <artifactId>shiro-service</artifactId>
    <version>1.0-SNAPSHOT</version>
 
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <!-- shiro -->
        <dependency>
            <groupId>org.apache.shiro</groupId>
            <artifactId>shiro-spring</artifactId>
            <version>1.6.0</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-aop</artifactId>
        </dependency>
 
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <!--springboot整合mybatis-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.2</version>
        </dependency>
        <!--mysql驱动5.6.17-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.6</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.4</version>
        </dependency>
        <!--页面模板依赖-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-thymeleaf</artifactId>
        </dependency>
        <!--热部署依赖-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <scope>runtime</scope>
        </dependency>
    </dependencies>
 
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>
 
 
</project>

server.port=8001
 
logging.level.com.java=debug
logging.level.web=debug
spring.devtools.add-properties=false
 
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
#spring.datasource.url=jdbc:mysql://127.0.0.1:3306/spring_shiro?useUnicode=true&characterEncoding=utf-8&useSSL=false
#spring.datasource.username=root
#spring.datasource.password=root
 
 
mybatis.mapper-locations=classpath:mapping/*.xml
mybatis.configuration.map-underscore-to-camel-case=true
 
spring.aop.proxy-target-class=true

package com.java;
 
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
 
/**
 * @author yourheart
 * @Description
 * @create 2022-05-03 20:30
 */
@SpringBootApplication
public class ShiroApplication {
    public static void main(String[] args) {
        SpringApplication.run(ShiroApplication.class,args);
    }
}
 
 
package com.java.bean;
 
import lombok.Data;
 
/**
 * @author yourheart
 * @Description
 * @create 2022-06-15 1:54
 */
@Data
public class Permission {
 
 
    private Integer id;
 
    private String permission;
 
    private Integer roleId;
}
 
 
package com.java.bean;
 
import lombok.Data;
 
import java.util.List;
 
/**
 * @author yourheart
 * @Description
 * @create 2022-06-15 1:55
 */
@Data
public class Role {
 
 
    private Integer id;
 
    private String roleName;
 
   private Integer userId;
 
    private List<Permission> permissions;
}
 
 
package com.java.bean;
 
import lombok.Data;
 
import java.util.List;
 
/**
 * @author yourheart
 * @Description
 * @create 2022-06-15 1:53
 */
@Data
public class User {
 
 
    private Integer id;
 
    private String name;
 
    private Integer password;
 
    private List<Role> roles;
}

package com.java.mapper;
 
import com.java.bean.Permission;
import com.java.bean.Role;
import com.java.bean.User;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
 
import java.util.List;
import java.util.Map;
 
/**
 * @author yourheart
 * @Description
 * @create 2022-06-25 8:10
 */
@Mapper
public interface UserMapper {
 
    List<User> getListByBean();
 
 
}

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.java.mapper.UserMapper">
 
    <resultMap id="userMap" type="com.java.bean.User">
 
        <result property="name" column="name"/>
        <result property="password" column="password"/>
        <collection property="roles" ofType="com.java.bean.Role">
            <result property="roleName" column="role_name"/>
            <collection property="permissions" ofType="com.java.bean.Permission">
                <result property="permission" column="permission"/>
            </collection>
        </collection>
    </resultMap>
 
      <select id="getListByBean" resultMap="userMap">
            SELECT  a.`name`,a.`password`,b.`role_name`,c.`permission`
         FROM  `user` a INNER JOIN  `role` b ON a.id=b.user_id INNER JOIN permission c ON b.id=c.role_id
      </select>
 
</mapper>

package com.java;
 
 
import com.java.bean.User;
import com.java.mapper.UserMapper;
import lombok.extern.slf4j.Slf4j;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import java.util.List;
 
/**
 * @author yourheart
 * @Description
 * @create 2022-06-19 0:31
 */
@RunWith(SpringRunner.class)
@SpringBootTest
@Slf4j
public class ShiroApplicationTests {
 
 
    @Autowired
    private UserMapper userMapper;
 
    @Test
    public void test(){
        List<User> listByBean = userMapper.getListByBean();
        listByBean.forEach(b->{
            log.info(b.toString());
        });
 
 
    }
}