重温MyBatis（四、Mapper的关联映射之二：一对多关联映射）

1、ProductInfoMapper.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.springmvc.mapper.ProductInfoMapper">

    <!--    根据id查询信息-->
    <select id="findProductInfoById" parameterType="int" resultType="ProductInfo">
        select * from product_info where id = #{id}
    </select>

    <select id="findProductInfoByPtid" parameterType="int"  resultType="ProductInfo">
        select * from product_info where ptid = #{ptid}
    </select>

    <!--    添加数据-->
    <insert id="addProductInfo" parameterMap="addProductInfoMap">
        insert into product_info (ptid, code, name) values (#{productType.id}, #{code}, #{name});
    </insert>
    <parameterMap id="addProductInfoMap" type="ProductInfo">
        <parameter property="productType.id" />
        <parameter property="code" />
        <parameter property="name"></parameter>
    </parameterMap>

</mapper>

2、ProductTypeMapper.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.springmvc.mapper.ProductTypeMapper">

    <!--    使用引用外部定义好的SQL语句块查询方式-嵌套查询-->
    <select id="findProductTypeById" parameterType="int" resultMap="ProductTypeMap">
        select * from product_type where id = #{id}
    </select>
    <!--    查询映射结果-->
    <resultMap id="ProductTypeMap" type="ProductType">
        <id property="id" column="id" />
        <result property="name" column="name" />
        <!--    一对多关联映射 -->
        <collection property="productInfoList" column="id"
            select="com.springmvc.mapper.ProductInfoMapper.findProductInfoByPtid"
        />
    </resultMap>


    <!--    使用嵌套结果查询方式实现一对多关联查询-->
    <select id="findProductTypeById2" parameterType="int" resultMap="ProductTypeMap2">
        select
            pt.id ptid,
            pt.name ptname,
            pi.*
        from
            product_type pt,
            product_info pi
        where
            pi.ptid = pt.id
        and
            pt.id = #{id}
    </select>
    <resultMap id="ProductTypeMap2" type="ProductType">
        <id property="id" column="ptid" />
        <result property="name" column="ptname" />
        <!--    一对多关联映射 -->
        <collection property="productInfoList" ofType="ProductInfo">
            <id property="id" column="id" />
            <result property="code" column="code" />
            <result property="name" column="name" />
        </collection>
    </resultMap>

    <!--    插入数据    -->
    <insert id="addProductType" parameterType="ProductType">
        <!--
            插入数据，并获得岗插入数据表ProductType的记录id
            细节：MySQL这种语言，order需要设置为AFTER才会取到正确的值。
        -->
        <selectKey keyProperty="id" resultType="int" order="AFTER">
            select last_insert_id() as id
        </selectKey>
        insert into product_type(name) values (#{name});
    </insert>

    <!--    删除数据-->
    <delete id="deleteProductTypeById" parameterType="int">
        delete from product_info where ptid = #{id};
        delete from product_type where id = #{id}
    </delete>

</mapper>