Mybatis——多表查询配置实现

Mybatis多表查询配置方式

• 一对一配置：使用resultMap做配置
• 一对多配置：使用resultMap+collection做配置
• 多对多配置：使用resultMap+collection做配置

数据表与实体类

这里有涉及到java.util.Date类型与数据库中bigint类型的转换器，具体可以参考Mybatis——typeHandlers标签自定义类型处理器
User类和User表

public class User {
    private int id;
    private String name;
    private String password;
    private Date birthday;
    //......注意配置get和set和ToString函数  ![请添加图片描述](https://img-blog.csdnimg.cn/aa6efe13a97a4b0da103c3555a1aa6d9.png)

}

Order类和Orders表

public class Order {
    private int id;
    private Date ordertime;
    private double total;
    private User user;
    //......注意配置get和set和ToString函数  
}

Role类和Role表

public class Role {
    private int id;
    private String roleName;
    //......注意配置get和set和ToString函数    
}

user_role表

一对一配置

需求：完成对每个order订单的查询以及通过订单内的uid属性查询对应的user用户

OrderMapper.xml

利用resultMap手动指定字段与实体属性的映射关系
这里Order类中有User类的属性，有两种方法完成对应的映射
方法一:
column:数据表的字段名称
property:实体的属性名称

<?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.springbootmybatis03.mapper.OrderMapper">
    <resultMap id="orderMap" type="order">
        <id column="oid" property="id"></id>
        <result column="ordertime" property="ordertime"></result>
        <result column="total" property="total"></result>
        <result column="uid" property="user.id"></result>
        <result column="name" property="user.name"></result>
        <result column="password" property="user.password"></result>
        <result column="birthday" property="user.birthday"></result> 
    </resultMap>
    <select id="findAll" resultMap="orderMap">
        SELECT *,o.id oid FROM orders o,user u WHERE o.uid=u.id
    </select>
</mapper>

方法二:
property:当前实体（order）中的属性名称（private User user）
javaType:当前实体（order）中的属性的类型（User）

<?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.springbootmybatis03.mapper.OrderMapper">
    <resultMap id="orderMap" type="order">
        <id column="oid" property="id"></id>
        <result column="ordertime" property="ordertime"></result>
        <result column="total" property="total"></result>
        <association property="user" javaType="user">
            <id column="uid" property="id"></id>
            <id column="name" property="name"></id>
            <id column="password" property="password"></id>
            <id column="birthday" property="birthday"></id>
        </association>
    </resultMap>
    <select id="findAll" resultMap="orderMap">
        SELECT *,o.id oid FROM orders o,user u WHERE o.uid=u.id
    </select>
</mapper>

OrderMapper

@Mapper
@Repository
public interface OrderMapper {
    List<Order> findAll();
}

测试与结果

    @Test
    public void testAllUserAndOrder(){
        List<Order> list = orderMapper.findAll();
        for(Order order:list) {
            System.out.println(order);
        }
    }

一对多配置

需求：完成每个用户及其所有订单的查询，在User类中创建Order类型的List存储映射用户的所有订单信息，联结user表和orders表进行查询

UserMapper.xml

<resultMap id="userMap" type="com.example.springbootmybatis03.domain.User">
        <id column="uid" property="id"></id>
        <result column="name" property="name"></result>
        <result column="password" property="password"></result>
        <result column="birthday" property="birthday"></result>
        <!--配置集合信息
            property:集合名称
            ofType:当前集合中的数据类型-->
        <collection property="orderList" ofType="com.example.springbootmybatis03.domain.Order">
            <!--封装Order的数据-->
            <id column="oid" property="id"></id>
            <result column="ordertime" property="ordertime"></result>
            <result column="total" property="total"></result>
        </collection>
    </resultMap>

    <select id="findAll" resultMap="userMap">
        select *,o.id oid from user u,orders o where u.id=o.id
    </select>

UserMapper

@Mapper
@Repository
public interface UserMapper {
    List<User> findAll();
}

测试与结果

    @Test
    public void testFindAllUser(){
        List<User> list = userMapper.findAll();
        for(User user:list) {
            System.out.println(user);
        }
    }

多对多配置

需求：一个user对应一个或多个role，而一个role也可以有多个user，查询需要用到三个表，与User类对应的user表，与Role类对应的role表，以及利用userId与roleId存储user和role对应关系的中间表user_role

UserMapper.xml

<resultMap id="userRoleMap" type="com.example.springbootmybatis03.domain.User">
        <!--user的信息-->
        <id column="userid" property="id"></id>
        <result column="name" property="name"></result>
        <result column="password" property="password"></result>
        <result column="birthday" property="birthday"></result>
        <!--user内部的roleList信息-->
        <collection property="roleList" ofType="com.example.springbootmybatis03.domain.Role">
            <id column="roleId" property="id"></id>
            <result column="roleName" property="roleName"></result>
        </collection>
    </resultMap>

    <select id="findAllUserAndRole" resultMap="userRoleMap">
        select * from user u,user_role ur,role r where u.id = ur.userId and ur.roleId = r.id
    </select>

UserMapper

@Mapper
@Repository
public interface UserMapper {
    List<User> findAllUserAndRole();
}

测试与结果

    @Test
    public void testFindAllUserAndRole(){
        List<User> list = userMapper.findAllUserAndRole();
        for(User user:list) {
            System.out.println(user);
        }
    }