📃 关联查询

一、创建有关系的表

前面我们只使用了一张表实现单表查询，我们再创建一张订单表，用于测试多表查询。

以下是用户与订单两表之间的关系：

• 对于订单表，一个订单，关联一个用户数据
• 对于用户表，一个用户，关联多个订单数据

用户表的创建参考：
语雀内容

添加一张订单表：

CREATE TABLE `order` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '订单id',
  `member_id` bigint(20) NOT NULL,
  `order_sn` varchar(64) DEFAULT NULL COMMENT '订单编号',
  `create_time` datetime DEFAULT NULL COMMENT '提交时间',
  `member_username` varchar(64) DEFAULT NULL COMMENT '用户帐号',
  `total_amount` decimal(10,2) DEFAULT NULL COMMENT '订单总金额',
  `pay_amount` decimal(10,2) DEFAULT NULL COMMENT '应付金额（实际支付金额）',
  `freight_amount` decimal(10,2) DEFAULT NULL COMMENT '运费金额',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8 COMMENT='订单表';

创建模型：

@Data
public class Order implements Serializable {
    private Long id;
    private Long userId;
    private String orderSn;
    private Date createTime;
    private String memberUsername;
    private BigDecimal totalAmount;
    private BigDecimal payAmount;
    private BigDecimal freightAmount;

    private static final long serialVersionUID = 1L;
}

Dao、Mapper和Service的创建就不说了，跟前面的一致，参考：
语雀内容
语雀内容

二、一对一查询

由于订单与用户是一对一的，我们可以修改订单模型，将用户模型包括于其中：

@Data
public class Order implements Serializable {
    private Long id;
    ...

    private User user;
}

然后在OrderDao中添加：

Order query(Long id);

在mapper生产SQL：

<resultMap id="OrderUserResultMap" type="Order" autoMapping="true">
    <association property="user" javaType="User" autoMapping="true">
        <id column="user_id" property="id"/>
    </association>
</resultMap>

<select id="query" resultMap="OrderUserResultMap">
    select * from `order` o
    left join user u on o.user_id=u.id
    where o.id = #{id}
</select>

关于 association 的说明：

• association 完成子对象的映射
• property 子对象在父对象中的属性名
• javaType 子对象的java类型
• autoMapping 完成子对象的自动映射，若开启驼峰，则按驼峰匹配

OrderService接口不多说，其接口实现OrderServiceImpl中添加一个query方法：

@Override
public Order query(Long id) {
    return orderDao.query(id);
}

控制器OrderController：

@GetMapping("/id/{id}")
public Order query(@PathVariable Long id) {
    return orderService.query(id);
}

请求测试：http://localhost:8080/id/1

返回数据：

{
    "id": 1,
    "userId": 13,
    "orderSn": "123",
    "createTime": "2020-03-24T15:00:45.000+0000",
    "memberUsername": "test1",
    "totalAmount": 100.00,
    "payAmount": 80.00,
    "freightAmount": 10.00,
    "user": {
        "id": 13,
        "username": "xiaoyu",
        "password": "123456",
        "nickname": "yu",
        "phone": "18300000000",
        "status": 1,
        "icon": "",
        "gender": 1,
        "createTime": "2020-03-24 15:00:45",
        "birthday": "2020-03-24T00:00:00.000+0000"
    }
}

二、一对多查询

由于用户与订单是一对多的，我们可以修改用户模型，将订单列表包括于其中：

@Data
public class User implements Serializable {
    private Long id;
    ...

    private List<Order> orders;
}

然后在UserDao中添加：

User query(Long id);

在mapper生产SQL：

<resultMap id="BaseResultMap" type="User" autoMapping="true">
    <id column="id" jdbcType="BIGINT" property="id" />
    <collection property="orders" javaType="List" ofType="Order" autoMapping="true">
        <id column="o_id" property="id"/>
    </collection>
</resultMap>

<select id="query" resultMap="BaseResultMap">
    SELECT u.*, o.*, o.id as o_id from user u
    LEFT JOIN `order` o on u.id = o.user_id
    where u.id=#{id}
</select>

关于 collection 的说明：

• collection 定义子对象集合映射
• property 子对象在父对象中的属性名
• javaType 子对象的java类型
• ofType 集合中单个对象的类型
• autoMapping 完成子对象的自动映射，若开启驼峰，则按驼峰匹配

UserService接口不多说，其接口实现UserServiceImpl中添加query方法：

@Override
public User query(Long id) {
    return userDao.query(id);
}

控制器UserController：

@GetMapping("/{id}")
public User query(@PathVariable Long id) {
    return userService.query(id);
}

请求测试：http://localhost:8080/user/13

返回数据：

{
    "id": 13,
    "username": "xiaoyu",
    "password": "123456",
    "nickname": "yu",
    "phone": "18300000000",
    "status": 1,
    "icon": "",
    "gender": 1,
    "createTime": "2020-03-24 11:09:31",
    "birthday": "2020-03-24T00:00:00.000+0000",
    "orders": [
        {
            "id": 1,
            "userId": 13,
            "orderSn": "123",
            "createTime": "2020-03-24T11:09:31.000+0000",
            "memberUsername": "test1",
            "totalAmount": 100.00,
            "payAmount": 80.00,
            "freightAmount": 10.00,
            "user": null
        },
        {
            "id": 2,
            "userId": 13,
            "orderSn": "456",
            "createTime": "2020-03-24T11:09:31.000+0000",
            "memberUsername": "test2",
            "totalAmount": 80.00,
            "payAmount": 80.00,
            "freightAmount": 0.00,
            "user": null
        }
    ]
}

三、使用dto

上面的实现，有个不好的地方，需要修改了模型本身，为其添加数据表中本不存在的属性，因此，我们可以使用dto（数据传输对象 Data Transfer Object）

下面以订单表为例。

首先恢复订单表实体：

@Data
public class Order implements Serializable {
    private Long id;
    private Long userId;
    private String orderSn;
    private Date createTime;
    private String memberUsername;
    private BigDecimal totalAmount;
    private BigDecimal payAmount;
    private BigDecimal freightAmount;

    private static final long serialVersionUID = 1L;
}

然后添加dto，继承自 Order ：

@Data
public class OrderUser extends Order {
    private User user;
}

相应地，dao和service也需要做相应修改，返回类型改为d刚才创建的dto类型OrderUser：

public interface OrderDao {
    OrderUser query(Long id);
}

public interface OrderService {
    OrderUser query(Long id);
}

@Service
public class OrderServiceImpl implements OrderService {
    @Autowired
    private OrderDao orderDao;

    @Override
    public OrderUser query(Long id) {
        return orderDao.query(id);
    }
}

然后修改resultMap，使其返回类型（type）亦为OrderUser：

<resultMap id="OrderUserResultMap" type="com.example.test.dto.OrderUser" autoMapping="true">
    <association property="user" javaType="com.example.test.model.User" autoMapping="true">
        <id column="user_id" property="id"/>
    </association>
</resultMap>

控制器也需要将返回值指定为OrderUser：

@GetMapping("/{id}")
public OrderUser query(@PathVariable Long id) {
    return orderService.query(id);
}

至此，所有修改完成。