MyBatis多表联查

前言

创建数据库表（user和message），并插入数据：

CREATE TABLE `user` (
`id`  varchar(64) NOT NULL ,
`name`  varchar(64) NULL ,
PRIMARY KEY (`id`)
);

CREATE TABLE `info` (
`id`  varchar(64) NOT NULL ,
`uid`  varchar(64) NULL ,
`msg`  varchar(255) NULL ,
PRIMARY KEY (`id`)
);


INSERT INTO `user` (`id`, `name`) VALUES ('1001', 'ahzoo');
INSERT INTO `user` (`id`, `name`) VALUES ('1002', 'ouo');
INSERT INTO `info` (`id`, `uid`, `msg`) VALUES ('2001', '1001', '测试信息');
INSERT INTO `info` (`id`, `uid`, `msg`) VALUES ('2002', '1001', '第二条信息');

再创建两个数据库表（role和user_role），并插入数据，用于多对多操作：

CREATE TABLE `role` (
`id`  varchar(64) NOT NULL ,
`name`  varchar(64) NULL ,
PRIMARY KEY (`id`)
);


CREATE TABLE `user_role` (
`user_id`  varchar(64) NOT NULL ,
`role_id`  varchar(64) NOT NULL 
);


INSERT INTO `role` (`id`, `name`) VALUES ('3001', '用户');
INSERT INTO `role` (`id`, `name`) VALUES ('3002', '管理员');

INSERT INTO `user_role` (`user_id`, `role_id`) VALUES ('1001', '3001');
INSERT INTO `user_role` (`user_id`, `role_id`) VALUES ('1001', '3002');
INSERT INTO `user_role` (`user_id`, `role_id`) VALUES ('1002', '3002');

创建一个springboot项目，并生成增删改查，快速开始。
项目结构：

依赖：

<dependencies>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
    </dependency>
    <dependency>
        <groupId>org.mybatis.spring.boot</groupId>
        <artifactId>mybatis-spring-boot-starter</artifactId>
        <version>2.2.2</version>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter</artifactId>
    </dependency>
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <optional>true</optional>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
    </dependency>
</dependencies>

一对一

一条信息对应一个发送者，查询信息的发送者。
在Info实体类中增加User对象，这样在对user和info进行联查时，将User对象一起返回：
entity/Info

import lombok.Data;
@Data
public class Info {
    private String id;
    private String uid;
    private String msg;
//    接收一对一查询到的User对象
    private User user;
}

映射文件：
mapper/InfoDao.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="com.example.demo.mapper.InfoMapper">

<!--    使用Map对象作为返回结果-->
    <select id="selectListToMap" resultType="Map">
        SELECT u.id,u.name,i.id,i.uid,i.msg 
        FROM user u,info i 
        WHERE u.id=i.uid
    </select>


    <resultMap id="BaseResultMap" type="com.example.demo.entity.Info">
        <id property="id" column="id" jdbcType="VARCHAR"/>
        <result property="uid" column="uid" jdbcType="VARCHAR"/>
        <result property="msg" column="msg" jdbcType="VARCHAR"/>
        <association property="user" javaType="com.example.demo.entity.User">
<!--            这里为id字段取个别名（user_id），方便与上面的表的id字段进行区分，然后在下面对别名使用AS进行映射即可-->
            <id property="id" column="user_id" jdbcType="VARCHAR"/>
            <result property="name" column="name" jdbcType="VARCHAR"/>
        </association>
    </resultMap>


<!--    使用Info对象作为返回结果-->
    <select id="selectListByMap" resultMap="BaseResultMap">
        SELECT u.id AS user_id,u.name,i.id,i.uid,i.msg 
        FROM user u,info i 
        WHERE u.id=i.uid
    </select>

</mapper>

持久层：
mapper/InfoMapper

import com.example.demo.entity.Info;
import org.apache.ibatis.annotations.Mapper;
import java.util.List;
import java.util.Map;

@Mapper
public interface InfoMapper {
    List<Map<String,String>> selectListToMap();
    List<Info> selectListByMap();
}

测试：
查询目标信息对应的用户信息

import com.example.demo.entity.Info;
import com.example.demo.mapper.InfoMapper;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.List;
import java.util.Map;


@SpringBootTest
class InfoTests {

    @Autowired
    InfoMapper infoMapper;

    /**
     * 使用Map对象作为返回结果
     */
    @Test
    public void toGetMapResult(){
        List<Map<String, String>> infos = infoMapper.selectListToMap();
        infos.forEach(System.out::println); 
        /*
        {msg=测试信息, uid=1001, name=ahzoo, id=1001}
        {msg=第二条信息, uid=1001, name=ahzoo, id=1001}
         */
    }

    /**
     * 使用Info对象作为返回结果
     */
    @Test
    public void toGetInfoResult(){
        List<Info> infos = infoMapper.selectListByMap();
        infos.forEach(System.out::println); 
        /*
        Info(id=2001, uid=1001, msg=测试信息, user=User(id=1001, name=ahzoo))
        Info(id=2002, uid=1001, msg=第二条信息, user=User(id=1001, name=ahzoo))
         */
    }


}

一对多

一个用户可以发送多条信息，查询用户下的所有信息。
在User实体类中增加Info的List对象，用于返回查询到的多个Info对象：
entity/User

import lombok.Data;
import java.util.List;

@Data
public class User{
    private String id;
    private String name;
//    接收一对多查询到的Info对象集合
    private List<Info> infos;
}

持久层：
mapper/UserMapper

import com.example.demo.entity.User;
import org.apache.ibatis.annotations.Mapper;
import java.util.List;
@Mapper
public interface UserMapper {
    List<User> findAllByMap();
}

映射文件：
mapper/UserMapper.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="com.example.demo.mapper.UserMapper">

    <resultMap id="BaseResultMap" type="com.example.demo.entity.User">
        <id property="id" column="id" jdbcType="VARCHAR"/>
        <result property="name" column="name" jdbcType="VARCHAR"/>
        <!--配置集合信息
        property：自定义集合的名称
        ofType：集合的数据类型
        -->
        <collection property="infos" ofType="com.example.demo.entity.Info">
            <result property="id" column="info_id" jdbcType="VARCHAR"/>
            <result property="uid" column="uid" jdbcType="VARCHAR"/>
            <result property="msg" column="msg" jdbcType="VARCHAR"/>
        </collection>
    </resultMap>


    <!-- 查询目标用户所有发布的信息（这里使用左外连接查询） -->
    <select id="findAllByMap" resultMap="BaseResultMap">
        SELECT u.id,u.name,i.id AS info_id,i.uid,i.msg
        FROM user u
        LEFT OUTER JOIN info i
             ON u.id = i.uid;
    </select>

</mapper>

测试：

import com.example.demo.entity.User;
import com.example.demo.mapper.UserMapper;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.List;
@SpringBootTest
public class UserTests {

    @Autowired
    UserMapper userMapper;

    @Test
    public void toGetAll(){
        List<User> allByMap = userMapper.findAllByMap();
        allByMap.forEach(System.out::println);
        /*
        User(id=1001, name=ahzoo, infos=[Info(id=2001, uid=1001, msg=测试信息, user=null), Info(id=2002, uid=1001, msg=第二条信息, user=null)])
         */
    }

}

可以看到所有用户下的所有信息都被查询到

集合中元素的复杂规则设置

在一对多的查询中，有时可能会需要对集合中的数据做额外的限制。此时可以在连接查询时对集合对象对应的表构建为临时表，在临时表中添加限制条件实现。

示例：

在一对多中的集合数据（List<User>对象内嵌套的List<Info>对象）设置排序规则：

<resultMap id="BaseResultMap" type="com.example.demo.entity.User">
    <id property="id" column="id" jdbcType="VARCHAR"/>
    <result property="name" column="name" jdbcType="VARCHAR"/>
    <collection property="infos" ofType="com.example.demo.entity.Info">
        <result property="id" column="info_id" jdbcType="VARCHAR"/>
        <result property="uid" column="uid" jdbcType="VARCHAR"/>
        <result property="msg" column="msg" jdbcType="VARCHAR"/>
    </collection>
</resultMap>

<select id="findAllByMap" resultMap="BaseResultMap">
    SELECT u.id, u.name, i.id AS info_id, i.uid, i.msg
    FROM user u
             LEFT OUTER JOIN 
    						# 这里将连接查询的info表构建为自定义的临时表，来实现限制查询
    						(SELECT id AS info_id, i.uid, i.msg
                              FROM info
                              ORDER BY id ASC) i
                             ON u.id = i.uid;
</select>

多对多

一个用户可以有多个角色，一个角色可以属于多个用户，查询用户对应的角色，及角色对应的用户。
在User实体类中增加Role的List对象，用于返回查询到的多个Role对象：
entity/User

import lombok.Data;
import java.util.List;

@Data
public class User{
    private String id;
    private String name;
    private List<Role> roles;
}

同理：
entity/Role

import lombok.Data;
import java.util.List;

@Data
public class Role {

    private String id;
    private String name;
    private List<User> users;
}

持久层：
mapper/RoleMapper

import com.example.demo.entity.Role;
import org.apache.ibatis.annotations.Mapper;
import java.util.List;
@Mapper
public interface RoleMapper {
    List<Role> getAllUser();
}

mapper/UserMapper

import com.example.demo.entity.User;
import org.apache.ibatis.annotations.Mapper;
import java.util.List;
@Mapper
public interface UserMapper {
    List<User> getAllRole();
}

映射文件：
mapper/RoleMapper

<resultMap id="roleMap" type="com.example.demo.entity.Role">
    <id property="id" column="id" jdbcType="VARCHAR"/>
    <result property="name" column="name" jdbcType="VARCHAR"/>
    <collection property="users" ofType="com.example.demo.entity.User">
        <result property="id" column="role_id" jdbcType="VARCHAR"/>
        <result property="name" column="name" jdbcType="VARCHAR"/>
    </collection>
</resultMap>

<select id="getAllUser" resultMap="roleMap">
    SELECT u.id,u.name,r.id AS role_id,r.name
    FROM user u
    LEFT OUTER JOIN user_role ur ON u.id = ur.user_id
    LEFT OUTER JOIN role r ON r.id = ur.role_id
</select>

mapper/UserMapper

<resultMap id="userMap" type="com.example.demo.entity.User">
    <id property="id" column="id" jdbcType="VARCHAR"/>
    <result property="name" column="name" jdbcType="VARCHAR"/>
    <collection property="roles" ofType="com.example.demo.entity.Role">
        <result property="id" column="role_id" jdbcType="VARCHAR"/>
        <result property="name" column="name" jdbcType="VARCHAR"/>
    </collection>
</resultMap>

<select id="getAllRole" resultMap="userMap">
    SELECT u.id,u.name,r.id AS role_id,r.name
    FROM user u
    LEFT OUTER JOIN user_role ur ON u.id = ur.user_id
    LEFT OUTER JOIN role r ON r.id = ur.role_id
</select>

测试：

    @Autowired
    RoleMapper roleMapper;
    @Autowired
    UserMapper userMapper;

    @Test
    public void toGetAllUser(){
        List<Role> allUser = roleMapper.getAllUser();
        allUser.forEach(System.out::println);
/*
        Role(id=1001, name=ahzoo, users=[User(id=3001, name=ahzoo, roles=null), User(id=3002, name=ahzoo, roles=null)])
        Role(id=1002, name=ouo, users=[User(id=null, name=ouo, roles=null)])
*/

    }
    @Test
    public void toGetAllRole(){
        List<User> allRole = userMapper.getAllRole();
        allRole.forEach(System.out::println);

/*
        User(id=1001, name=ahzoo, roles=[Role(id=3001, name=ahzoo, users=null), Role(id=3002, name=ahzoo, users=null)])
        User(id=1002, name=ouo, roles=[Role(id=null, name=ouo, users=null)])

 */
    }

**