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