MyBatis多表连接查询

使用MyBatis进行单表查询十分简单，多表连接查询相比单表查询操作稍微复杂，不过相较于使用JDBC方式简单得多：

现有八张表，每张表的结构如下图：
users:

image.png

orders:

image.png

addresses:

image.png

pay_type:

image.png

order_item:

image.png

products:

image.png

product_types:

image.png

imgaes:

image.png

1、使用SQL语句将User表作为主表查询id为1的用户的所有订单详情：

SELECT * FROM users u LEFT JOIN orders o ON o.user_id=u.id LEFT JOIN pay_type pty ON pty.id=o.pay_type LEFT JOIN addresses ad ON ad.id=o.address LEFT JOIN order_item oi ON oi.order_id=o.order_id LEFT JOIN products p ON p.id=oi.product_id LEFT JOIN product_types pt ON pt.id=p.type LEFT JOIN images img ON img.product_id=p.id WHERE u.id=1;

查询结果如下：

image.png

2、使用MyBatis以User表开始查询八张表

• 首先，根据一张表对应一个类的原则，我们要创建这八张表的对应类，需要注意的是，从不同的表出发进行多表连接查询每张表的私有成员有一些不同：
  User.java

package com.qianfeng.pojo;

import java.util.List;

public class User {
    private int id;
    private String name;
    private String password;
    private int age;
    private List<Order> orders;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public int getAge() {
        return age;
    }

    public List<Order> getOrders() {
        return orders;
    }

    public void setOrders(List<Order> orders) {
        this.orders = orders;
    }

    public void setAge(int age) {
        this.age = age;
    }

    @Override
    public String toString() {
        final StringBuilder sb = new StringBuilder("User{");
        sb.append("id=").append(id);
        sb.append(", name='").append(name).append('\'');
        sb.append(", password='").append(password).append('\'');
        sb.append(", age=").append(age);
        sb.append(", orders=").append(orders);
        sb.append('}');
        return sb.toString();
    }
}

Order.java

package com.qianfeng.pojo;

import java.util.List;

public class Order {
    private int id;
    private double price;
    private int userId;
    private List<OrderItem> items;
    private Address address;
    private PayType pt;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public double getPrice() {
        return price;
    }

    public void setPrice(double price) {
        this.price = price;
    }

    public int getUserId() {
        return userId;
    }

    public void setUserId(int userId) {
        this.userId = userId;
    }

    public List<OrderItem> getItems() {
        return items;
    }

    public void setItems(List<OrderItem> items) {
        this.items = items;
    }

    public Address getAddress() {
        return address;
    }

    public void setAddress(Address address) {
        this.address = address;
    }

    public PayType getPt() {
        return pt;
    }

    public void setPt(PayType pt) {
        this.pt = pt;
    }

    @Override
    public String toString() {
        final StringBuilder sb = new StringBuilder("Order{");
        sb.append("id=").append(id);
        sb.append(", price=").append(price);
        sb.append(", userId=").append(userId);
        sb.append(", items=").append(items);
        sb.append(", address=").append(address);
        sb.append(", pt=").append(pt);
        sb.append('}');
        return sb.toString();
    }
}

以下省略

• 创建MyBatis配置文件
  mybatis.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <properties resource="db.properties"/>
    <settings>
        <setting name="logImpl" value="STDOUT_LOGGING"/>
    </settings>
    <typeAliases>
        <package name="com.qianfeng.pojo"/>
    </typeAliases>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="com/qianfeng/pojo/OrderMapper.xml"/>
        <mapper resource="com/qianfeng/pojo/UserMapper.xml"/>
        <mapper resource="com/qianfeng/pojo/AddressMapper.xml"/>
        <mapper resource="com/qianfeng/pojo/ImageMapper.xml"/>
        <mapper resource="com/qianfeng/pojo/OrderItemMapper.xml"/>
        <mapper resource="com/qianfeng/pojo/PayTypeMapper.xml"/>
        <mapper resource="com/qianfeng/pojo/ProductMapper.xml"/>
        <mapper resource="com/qianfeng/pojo/ProductTypeMapper.xml"/>
    </mappers>
</configuration>

• 创建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.qianfeng.pojo.User">
    <select id="getUserById" resultType="User">
        select * from users where id = #{id}
    </select>
    <select id="getUserOrdersByUid" resultMap="UserMapper">
        select * from users where id = #{id}
    </select>
    <resultMap id="UserMapper" type="User">
        <id property="id" column="id"/>
        <result column="name" property="name"/>
        <result column="password" property="password"/>
        <result column="age" property="age"/>
        <collection property="orders" column="id" select="com.qianfeng.pojo.Order.getOrderByOId2" ofType="Order"/>
    </resultMap>
</mapper>

OrderMapper.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.qianfeng.pojo.Order">
    <select id="getOrderByOId" resultMap="orderMap">
            select * from orders where order_id = #{id}
       </select>
    <resultMap id="orderMap" type="Order">
        <id property="id" column="order_id"></id>
        <result column="price" property="price"/>
        <association property="user" column="user_id" select="com.qianfeng.pojo.User.getUserById"/>
        <association property="address" column="address" select="com.qianfeng.pojo.Address.getAddressById"/>
        <association property="pt" column="pay_type" select="com.qianfeng.pojo.PayType.getPayTypeById"/>
        <collection property="items" column="order_id" select="com.qianfeng.pojo.OrderItem.getOrderItemByOid" ofType="OrderItem"/>
    </resultMap>

    <select id="getOrderByOId2" resultMap="orderMap2">
            select * from orders where user_id = #{id}
       </select>
    <resultMap id="orderMap2" type="Order">
        <id property="id" column="order_id"></id>
        <result column="price" property="price"/>
        <result column="user_id" property="userId"/>
        <association property="address" column="address" select="com.qianfeng.pojo.Address.getAddressById"/>
        <association property="pt" column="pay_type" select="com.qianfeng.pojo.PayType.getPayTypeById"/>
        <collection property="items" column="order_id" select="com.qianfeng.pojo.OrderItem.getOrderItemByOid" ofType="OrderItem"/>
    </resultMap>
</mapper>

以下省略。

只需要记住：多表查询对一关系使用的是association标签，对多查询使用的是collection标签。因为在MyBatis中只有这两种关系：对一和对多。当然这和传统的数据库有所不同，是由于看问题的角度导致的。传统的数据库有一对一、一对多、多对一、多对多的关系。那用户与订单的关系来看，一个用户对应多个订单，很容易理解这是一对多的关系，但是如果从订单的角度来看，一个订单只对应一个用户，这是对一的关系。