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')

　项目代码

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

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

}

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

});

}

}

相关阅读