连表查询

• 创建表

CREATE TABLE user
(
	id 				BIGINT(20) NOT NULL 			COMMENT '主键ID',
	company_id 		BIGINT(20) NOT NULL 			COMMENT '公司ID',
	name 			VARCHAR(30) NULL DEFAULT NULL 	COMMENT '姓名',
	age 			INT(11) NULL DEFAULT NULL 		COMMENT '年龄',
	email 			VARCHAR(50) NULL DEFAULT NULL 	COMMENT '邮箱',
	PRIMARY KEY (id)
);
 
DROP TABLE IF EXISTS company;
 
CREATE TABLE company
(
	id 				BIGINT(20) NOT NULL 			COMMENT '主键ID',
	name 			VARCHAR(30) NULL DEFAULT NULL 	COMMENT '公司名称',
	PRIMARY KEY (id)
);

• pom.xml

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <version>2.6.1</version>
        <scope>test</scope>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-webflux</artifactId>
        <version>2.6.1</version>
    </dependency>
    <dependency>
        <groupId>com.baomidou</groupId>
        <artifactId>mybatis-plus-boot-starter</artifactId>
        <version>3.4.0</version>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.27</version>
    </dependency>
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <version>1.18.18</version>
        <scope>provided</scope>
    </dependency>
    <dependency>
        <groupId>org.assertj</groupId>
        <artifactId>assertj-core</artifactId>
        <version>3.21.0</version>
        <scope>compile</scope>
    </dependency>
</dependencies>

• yml

server:
  port: 80
 
spring:
  application:
    name: demo03
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://127.0.0.1:3306/dbtest?useUnicode=true&characterEncoding=UTF-8
    username: root
    password: 123456
  main:
    allow-circular-references: true
  devtools:
    restart:
      enabled: true
 
mybatis:
  configuration:
    map-underscore-to-camel-case: true
# Mybatis-Plus
mybatis-plus:
  # Maven 多模块项目的扫描路径需以 classpath*: 开头 （即加载多个 jar 包下的 XML 文件）
  mapper-locations: classpath*:/mapper/*.xml
  global-config:
    db-config:
      property-format: "\"%s\""
logging:
  level:
    org:
      example:
        demo03: debug

• 启动类

@SpringBootApplication
@MapperScan("org.example.demo03.mapper")
public class AssociationApplication {
 
    public static void main(String[] args) {
        SpringApplication.run(AssociationApplication.class, args);
    }
 
}

• 配置类

@Configuration
public class MybatisPlusConfig {
 
	/**
     *	 分页插件
     */
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor());
        return interceptor;
    }
 
    /**
     *	 注入主键生成器
     */
    @Bean
    public IKeyGenerator keyGenerator(){
        return new H2KeyGenerator();
    }    
}

• 实体类

@Data
@TableName("company")
public class Company {
 
	private Long id;
 
    private String name;
 
    List<User> userList;
 
}
 
@Setter
@Getter
@ToString
@TableName("user")
public class User {
 
    @TableId(type = IdType.ASSIGN_ID)
    private Long id;
 
    @TableField("company_id")
    private Long company_id;
 
    private String name;
 
    private Integer age;
 
    private String email;
 
}

• 持久层接口

@Mapper
public interface CompanyMapper extends BaseMapper<Company> {
 
    Company testResultMapCollection();
}

• xml映射

<?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="org.example.demo03.mapper.CompanyMapper">
 
    <resultMap id="companyUser" type="org.example.demo03.entity.Company">
        <result property="id" column="id"/>
        <result property="name" column="name"/>
        <collection property="userList" ofType="org.example.demo03.entity.User">
            <result property="id" column="id"/>
            <result property="name" column="name"/>
            <result property="age" column="age"/>
            <result property="email" column="email"/>
        </collection>
    </resultMap>
 
    <select id="testResultMapCollection" resultMap="companyUser">
        SELECT c.id, c.name, u.id AS uid, u.name AS uname, u.age, u.email
        FROM company c JOIN user u ON c.id = u.company_id WHERE c.id=1
    </select>
</mapper>

• 测试方法

@Test
public void testResultMapCollection() {
    Company company = companyMapper.testResultMapCollection();
    System.out.println(company);
}
 
# 控制台
SELECT c.id, c.name, u.id AS uid, u.name AS uname, u.age, u.email FROM company c JOIN user u ON c.id = u.company_id WHERE c.id=1