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>