MyBatis的关联映射

资料下载链接

MyBatis的关联映射- 1V1

MyBatis的关联映射-1Vn

MyBatis的关联映射-nVn

一、需求分析

        掌握一对一关联映射，掌握一对多关联映射， 掌握多对多关联映射 。

二、搭建环境

1）数据库环境

        mybatis数据库，运行mybatistest04.sql

2）引入依赖

        pom.xml文件

3）数据库连接的配置文件

        src/main/resources，数据库连接的配置文件db.properties。

4）MyBatis核心配置文件

        src/main/resources，MyBatis的核心配置文件mybatis-config.xml

5）新建包和目录

        src/main/java，新建com.sw.pojo包

        src/main/java，新建com.sw.mapper包

        src/main/java，新建com.sw.util包，工具类MyBatisUtils

        src/main/resources，新建com/sw/mapper目录

三、一对一关联映射

        一个人只能有一个身份证，同时一个身份证也只会对应一个人。

1）数据封装类

        com.sw.pojo包，新建IdCard类

public class IdCard {
    private Integer id;
    private String code;
    //get、set
    //tostring
}

        com.sw.pojo包，新建Person类

public class Person {
    private Integer id;             
    private String name;         
    private Integer age;          
    private String sex;            
    private IdCard idCard;
    //get、set
    //tostring
}

2）关联映射--嵌套查询方式

        com.sw.mapper包，新建IdCardMapper接口

    IdCard getOne(int id);

        com/sw/mapper目录，IdCardMapper.xml

    <select id="getOneById" parameterType="int" resultType="IdCard">
        select * from tb_idcard where id = #{id};
    </select>

        com.sw.mapper包，新建PersonMapper接口

    Person getOneByQuery(int id);

        com/sw/mapper目录，PersonMapper.xml

    <select id="getOneByQuery" parameterType="int" resultMap="PersonWithIdCardByQuery">
        select * from tb_person where id = #{id};
    </select>
    <resultMap id="PersonWithIdCardByQuery" type="Person">
        <id property="id" column="id" />
        <result property="name" column="name" />
        <result property="age" column="age" />
        <result property="sex" column="sex" />
        <association property="idCard" column="card_id" javaType="IdCard"
                     select="com.sw.mapper.IdCardMapper.getOne"/>
    </resultMap>

3）关联映射--嵌套结果方式

        com.sw.mapper包，新建PersonMapper接口

    Person getOneByResult(int id);

        com/sw/mapper目录，PersonMapper.xml

    <select id="getOneByResult" parameterType="int" resultMap="PersonWithIdCardByResult">
        select * from tb_person,tb_idcard
        where tb_person.card_id=tb_idcard.id
        and tb_person.id = #{id}
    </select>
    <resultMap id="PersonWithIdCardByResult" type="Person">
        <id property="id" column="id"/>
        <result property="name" column="name" />
        <result property="age" column="age" />
        <result property="sex" column="sex" />
        <association property="idCard" javaType="IdCard">
            <id property="id" column="card_id"/>
            <result property="code" column="code"/>
        </association>
    </resultMap>

四、一对多关联映射

        一个用户可以有多个订单，多个订单也可以归一个用户所有。

1）数据封装类

        com.sw.pojo包，新建Order类

public class Order{
    private Integer id;
    private String number;
    //get、set
    //tostring
}

        com.sw.pojo包，新建User类

public class User{
    private Integer id;
    private String username;
    private String address;
    List<Order> orderList;
    //get、set
    //tostring
}

2）关联映射--嵌套查询方式

        com.sw.mapper包，新建OrderMapper接口。

    List<Order> getList(int userId);

        com/sw/mapper目录，OrderMapper.xml

    <select id="getList" parameterType="int" resultType="Order">
        select * from tb_orders where user_id = #{userId};
    </select>

        com.sw.mapper包，新建UserMapper接口。

    User getOneByQuery(int id);

        com/sw/mapper目录，UserMapper.xml

    <select id="getOneByQuery" parameterType="int" resultMap="UserWithOrderListByQuery">
        SELECT * FROM tb_user WHERE id = #{id}
    </select>
    <resultMap id="UserWithOrderListByQuery" type="User">
        <id property="id" column="id"/>
        <result property="username" column="username"/>
        <result property="address" column="address"/>
        <collection property="orderList" ofType="Order" column="id"
                    select="com.sw.mapper.OrderMapper.getList"/>
    </resultMap>

3）关联映射--嵌套结果方式

        com.sw.mapper包，新建UserMapper接口。

    User getOneByResult(int id);

        com/sw/mapper目录，UserMapper.xml

    <select id="getOneByResult" parameterType="int" resultMap="UserWithOrderListByResult">
        SELECT * FROM tb_user WHERE id = #{id}
    </select>
    <resultMap id="UserWithOrderListByResult" type="User">
        <id property="id" column="id"/>
        <result property="username" column="username"/>
        <result property="address" column="address"/>
        <collection property="orderList" ofType="Order" column="id"
                    select="com.sw.mapper.OrderMapper.getList"/>
    </resultMap>

五、多对多关联映射

        以订单和商品为例，一个订单可以包含多种商品，而一种商品又可以属于多个订单，订单和商品属于多对多关联关系。

1）数据封装类

        com.sw.pojo包，新建Product类

public class Product{
    private Integer id;
    private String name;
    private Double price;
    private List<Order> orderList;
    //get、set
    //tostring
}

        com.sw.pojo包，Order类

public class Order{
    private Integer id;
    private String number;
    private List<Product> productList;
    //get、set
    //tostring
}

2）关联映射--嵌套查询方式

        com.sw.mapper包，新建ProductMapper接口。

    List<Product> getList(int orderId);

        com/sw/mapper目录，ProductMapper.xml

    <select id="getList" parameterType="int" resultType="Product">
        select * from tb_product where id in(
        select product_id from tb_ordersitem where orders_id = #{orderId}
        )
    </select>

        com.sw.mapper包，新建OrderMapper接口。

    Order getOneByQuery(int id);

        com/sw/mapper目录，OrderMapper.xml

    <select id="getOneByQuery" parameterType="int" resultMap="OrderWithProductListByQuery">
        select * from tb_orders where id = #{id}
    </select>
    <resultMap id="OrderWithProductListByQuery" type="Order">
        <id property="id" column="id"/>
        <result property="number" column="number"/>
        <collection property="productList" column="id" ofType="Product"
                    select="com.sw.mapper.ProductMapper.getList"/>
    </resultMap>
    <select id="getList" parameterType="int" resultType="Order">
        select * from tb_orders where user_id = #{userId}
    </select>

3）关联映射--嵌套结果方式

        com.sw.mapper包，新建OrderMapper接口。

    Order getOneByResult(int id);

        com/sw/mapper目录，OrderMapper.xml

    <select id="getOneByResult" parameterType="int" resultMap="OrderWithProductListByResult">
        SELECT * FROM tb_orders,tb_ordersitem,tb_product
        WHERE tb_orders.id = tb_ordersitem.orders_id 
        AND tb_ordersitem.product_id = tb_product.id 
        AND tb_orders.id = #{id}
    </select>
    <resultMap id="OrderWithProductListByResult" type="Order">
        <id property="id" column="id"/>
        <result property="number" column="number"/>
        <collection property="productList" ofType="Product">
            <id property="id" column="product_id"/>
            <result property="name" column="name"/>
            <result property="price" column="price"/>
        </collection>
    </resultMap>