MyBatis关联映射(二)

准备工作：mybatis的环境搭建

目标：实现一对多查询

问题：查询用户以及关联的订单，一个用户有多个订单

一对多：查询的单个对象中的一个属性包含多个其他对象，使用collection标签嵌套

结果映射<resultMap>–主要使用这个标签

association – 一个复杂类型的关联；许多结果将包装成这种类型
嵌套结果映射 – 关联可以是 resultMap 元素，或是对其它结果映射的引用
collection – 一个复杂类型的集合
嵌套结果映射 – 集合可以是 resultMap 元素，或是对其它结果映射的引用

javaType为实体类中属性的类型，为常用基本数据类型时可以省略

ofType则是用来指定到List或集合中的实体类类型，泛型中的约束类型

1.数据库准备

USE mybatis;
# 创建一个名称为tb_user的表
CREATE TABLE tb_user (
  id int(32) PRIMARY KEY AUTO_INCREMENT,
  username varchar(32),
  address varchar(256)
 );
# 插入3条数据
INSERT INTO tb_user VALUES ('1', '小明', '北京');
INSERT INTO tb_user VALUES ('2', '李华', '上海');
INSERT INTO tb_user VALUES ('3', '李刚', '上海');
# 创建一个名称为tb_orders的表
CREATE TABLE tb_orders (
  id int(32) PRIMARY KEY AUTO_INCREMENT,
  number varchar(32) NOT NULL,
  user_id int(32) NOT NULL,
  FOREIGN KEY(user_id) REFERENCES tb_user(id)
);
# 插入3条数据
INSERT INTO tb_orders VALUES ('1', '1000011', '1');
INSERT INTO tb_orders VALUES ('2', '1000012', '1');
INSERT INTO tb_orders VALUES ('3', '1000013', '2');

2.实体类

2.1 Orders.java

package com.biem.pojo;

import lombok.*;

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class Orders {
    private Integer id;
    private String number;
    private Integer user_id;
}

2.2 Users.java

package com.biem.pojo;

import lombok.*;

import java.util.List;

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class Users {
    private Integer id;
    private String username;
    private String address;
    private List<Orders> ordersList;
}

3.用户配置接口类

3.1 OrdersMapper.java

package com.biem.mapper;

public interface OrdersMapper {
}

3.2 UsersMapper.java

package com.biem.mapper;

public interface UsersMapper {
}

4. 用户配置文件

4.1 OrdersMapper.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.biem.mapper.OrdersMapper">

</mapper>

4.2 UsersMapper.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.biem.mapper.UsersMapper">

</mapper>

5.解决方法

5.1嵌套查询方式

5.1.1 接口类写入方法

OrdersMapper.java

package com.biem.mapper;

import com.biem.pojo.Orders;

import java.util.List;

public interface OrdersMapper {
    public List<Orders> selectOrders(Integer id);
}

UsersMapper.java

package com.biem.mapper;

import com.biem.pojo.Users;

public interface UsersMapper {
    public Users findUserById(Integer id);
}

5.1.2 映射文件写入sql语句

OrdersMapper.xm

<select id="selectOrders" parameterType="Integer" resultType="Orders">
        select * from tb_orders where user_id=#{id}
</select>

UsersMapper.xml

<select id="findUserById" parameterType="Integer" resultMap="map1">
        select * from tb_user where id = #{id}
    </select>
    <resultMap id="map1" type="Users">
        <id property="id" column="id"></id>
        <result property="username" column="username"/>
        <result property="address" column="address"/>
        <collection property="ordersList" javaType="ArrayList" ofType="orders"
                    select="com.biem.mapper.OrdersMapper.selectOrders" column="id"/>
    </resultMap>

5.1.3 测试类

UsersTest.java

import com.biem.mapper.UsersMapper;
import com.biem.pojo.Users;
import com.biem.util.MybatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

public class UsersTest {
    @Test
    public void testFindUserById(){
        SqlSession session = MybatisUtil.openSession();
        UsersMapper mapper = session.getMapper(UsersMapper.class);
        Users user = mapper.findUserById(1);
        System.out.println("user = " + user);
        session.close();
    }
}

5.1.4测试结果

5.2嵌套结果方式

5.2.1 Usersmapper.java 中添加方法

public Users findUserById2(Integer id);

5.2.2 UsersMapper.xml 添加sql语句

<select id="findUserById2" parameterType="Integer" resultMap="map2">
        select *
        from tb_user,tb_orders
        where tb_user.id=tb_orders.user_id and tb_user.id = #{id}
    </select>
    <resultMap id="map2" type="Users">
        <id property="id" column="id"></id>
        <result property="username" column="username"/>
        <result property="address" column="address"/>
        <collection property="ordersList"  ofType="orders">
            <id property="id" column="orders_id"></id>
            <result property="number" column="number"/>
            <result property="user_id" column="user_id"/>
        </collection>
    </resultMap>

5.2.3 测试类

UsersTest.java

@Test
    public void testFindUserById2(){
        SqlSession session = MybatisUtil.openSession();
        UsersMapper mapper = session.getMapper(UsersMapper.class);
        Users user = mapper.findUserById2(1);
        System.out.println("user = " + user);
        session.close();
    }

5.2.4测试结果