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"?>

    4.0.0
    
        org.springframework.boot
        spring-boot-starter-parent
        2.3.4.RELEASE
        
    

    com.java
    shiro-service
    1.0-SNAPSHOT

    
        
            org.springframework.boot
            spring-boot-starter-web
        
        
            org.springframework.boot
            spring-boot-starter-test
            test
        
        
        
            org.apache.shiro
            shiro-spring
            1.6.0
        
        
            org.springframework.boot
            spring-boot-starter-aop
        

        
            org.projectlombok
            lombok
            true
        
        
        
            org.mybatis.spring.boot
            mybatis-spring-boot-starter
            2.1.2
        
        
        
            mysql
            mysql-connector-java
            5.1.6
        
        
            com.alibaba
            druid
            1.1.4
        
        
        
            org.springframework.boot
            spring-boot-starter-thymeleaf
        
        
        
            org.springframework.boot
            spring-boot-devtools
            runtime
        
    

    
        
            
                org.springframework.boot
                spring-boot-maven-plugin
            
        
    

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 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 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 getListByBean();


}

<?xml version="1.0" encoding="UTF-8"?>



    

        
        
        
            
            
                
            
        
    

      
            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
      

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 listByBean = userMapper.getListByBean();
        listByBean.forEach(b->{
            log.info(b.toString());
        });


    }
}