MyBatis的关联映射----一对多

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

1.数据库准备

代码如下：

USE mybatis;
# 创建一个名称为tb_idcard的表
CREATE TABLE  tb_idcard( 
     id INT PRIMARY KEY AUTO_INCREMENT,
     CODE VARCHAR(18)
);
# 插入2条数据
INSERT INTO tb_idcard(CODE) VALUES('152221198711020624');
INSERT INTO tb_idcard(CODE) VALUES('152201199008150317');
# 创建一个名称为tb_person的表
CREATE TABLE  tb_person( 
     id INT PRIMARY KEY AUTO_INCREMENT,
     name VARCHAR(32),
     age INT,
     sex VARCHAR(8),
     card_id INT UNIQUE,     
     FOREIGN KEY(card_id) REFERENCES tb_idcard(id)
);
# 插入2条数据
INSERT INTO tb_person(name,age,sex,card_id) VALUES('Rose',22,'女',1);
INSERT INTO tb_person(name,age,sex,card_id) VALUES('jack',23,'男',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 userId;
}
 

2.2Users.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.1OrdersMapper.java

代码如下：

package com.biem.mapper;

public interface OrdersMapper {
}
 

3.2UsersMapper.java

代码如下：

package com.biem.mapper;

public interface UsersMapper {
}
 

4.用户配置文件

4.1rdersMapper.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.2UsersMapper.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.1OrdersMapper.java

package com.biem.mapper;

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

5.1.2UsersMapper.java

package com.biem.mapper;

import com.biem.pojo.User;

public interface UserMapper {
    public User findUserById(int id);
}

5.1.3OrdersMapper.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">
    <!-- public List<Orders> selectOrders(Integer user_id); -->
    <select id="selectOrders" parameterType="Integer" resultType="Orders">
        select * from tb_orders where user_id=#{userId}
    </select>
</mapper>
 

5.1.4UsersMapper.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">
    <resultMap id="usersWithOrders1" type="Users">
        <id property="id" column="id"></id>
        <result property="username" column="username"/>
        <result property="address" column="address"/>
        <!--由于查询老师中有一个属性为所有学生对象集合，所一使用collection标签进行嵌套，
        方法的返回值是List，所以javaType使用ArrayList来接收，而list中泛型约束类型放的
        是orders对象所以使用ofType为orderst来进行映射，使用select进行子查询，并用column
        将查询出的id字段传给子查询需要的id-->
        <!--此处javaType可以省略-->
        <collection property="ordersList" javaType="ArrayList" ofType="orders" select="com.biem.mapper.OrdersMapper.selectOrders" column="id"/>
    </resultMap>

    <!-- public Users findUserById(Integer id); -->
    <select id="findUserById" parameterType="Integer" resultMap="usersWithOrders1">
        select * from tb_user where id = #{id}
    </select>
</mapper>
 

5.2测试方法

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.3测试结果

 6.嵌套结果方式

6.1UsersMapper.java添加

public Users findUserByIdWithNestedResults(Integer id);
 

6.2UserMapper.xml添加

    <!-- public Users findUserByIdWithNestedResults(Integer id); -->
    <!-- 一对多：查看某一个用户及关联的订单信息，
        注意：当关联查询出来的列名相同时，则需要使用别名区分  -->
    <select id="findUserByIdWithNestedResults" parameterType="Integer" resultMap="UsersWithOrdersResult" >
        select u.*, o.id as orders_id , o.number, o.user_id
        from tb_user u, tb_orders o
        where u.id = o.user_id and u.id=#{id}
    </select>
    <resultMap id="UsersWithOrdersResult" 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="Id" column="user_id"/>
        </collection>
    </resultMap>
 

6.3UsersTest.java添加

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

6.4测试结果