<?xmlversion="1.0"encoding="UTF-8"?>
<!DOCTYPEmapper
PUBLIC"-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--
namespace:命名空间，用来唯一标识一个映射文件,命名规范就是当前的文件的包名+mapper的xml文件名
-->
<mapper namespace="com.rl.mapper.PersonMapper">
<!--当前映射文件开启二级缓存-->
<cache type="org.mybatis.caches.ehcache.EhcacheCache"/>
<!--
id:resultMap的唯一标识
type:给哪个实体类做结果的映射
-->
<resultMap type="person" id="BaseResultMap">
<!--
column:数据库中表的字段
property：数据库中表所有映射的实体类javaBean中的属性名
-->
<id column="person_id" property="personId"/>
<result column="name" property="name"/>
<result column="gender" property="gender"/>
<result column="person_addr" property="personAddr"/>
<result column="birthday" property="birthday"/>
</resultMap>
<!--
公用的sql片段,也可以接收参数，动态sql，所有的sql可以使用
-->
<sql id="columns">
PERSON_ID, NAME, GENDER, PERSON_ADDR, BIRTHDAY
</sql>
<!--
根据id来查询一个Person的数据
sql语句接收参数的一个语法#{}，如果接收的是一个{}中的内容任意select * from person_test t where t.ID = ?,使用预编译方式生成sql
id:sql语句的唯一的标识不能重复
parameterType:sql要接收的数据类型
resultType：sql所返回的数据类型
-->
<!--
实际项目中数据库的表的字段一般由多个单词来构成由下划线来分隔多个单词 person_addr
在java的model的实体类中的属性多个单词的命名规范是驼峰模式personAddr
-->
<!--
useCache:控制当前的这个sql是否使用二级缓存
-->
<select id="selectPersonById" parameterType="int" resultMap="BaseResultMap" useCache="true">
select * from person t where t.person_id = #{id}
</select>
<select id="selectPersonCount" resultType="int">
select count(*) from person
</select>
<!--这里引用了上面的sql片段 -->
<select id="selectPersonAll" resultMap="BaseResultMap">
select <includerefid="columns"/> from person
</select>
<!--
可以使用map
map.put("gender",1);
map.put("birthday" new Date());
#{}中的内容使用Map的key来接收参数
-->
<select id="selectPersonByParams" parameterType="map" resultMap="BaseResultMap">
<![CDATA[
select * from person t where t.gender = #{gender} and t.birthday < #{birthday}
]]>
</select>
<!--
使用查询对象的get方法来接收参数（也就是属性名）
-->
<select id="selectPersonByParams1" parameterType="qc" resultMap="BaseResultMap">
<![CDATA[
select * from person t where t.gender = #{gender} and t.birthday < #{birthday}
]]>
</select>
<!--
模糊查询使用${} select * from person t where t.name like '%安%'
parameterType：不能直接使用String,一定要用查询对象或者map
-->
<select id="selectPersonByLike" parameterType="qc" resultMap="BaseResultMap">
select * from person t where t.name like '%${name}%'
</select>
<!--库表变更 -->
<insert id="insert" parameterType="person">
<!--
keyProperty:实体类中主键属性，主键生成后把主键返回给这个属性
order：生成主键的sql和insert执行的顺序，mysql是AFTER， oracle是BEFORE
resultType:主键返回的数据类型
sql：
在mysql中select LAST_INSERT_ID()
在oracle中 select xxx.nextval from dual
selectKey做了两件事：1.主键自增 2.主键返回
-->
<selectKey keyProperty="personId" order="AFTER" resultType="int">
select LAST_INSERT_ID()
</selectKey>
insert into person (person_id, name, gender, person_addr, birthday)
values(#{personId}, #{name}, #{gender}, #{personAddr}, #{birthday})
</insert>
<update id="update" parameterType="person">
update person p set p.name = #{name},
p.gender = #{gender},
p.person_addr = #{personAddr},
p.birthday = #{birthday}
where p.person_id = #{personId}
</update>
<!--
删除的sql不能使用别名
-->
<delete id="delete" parameterType="int">
delete from person where person_id = #{personId}
</delete>
<!-- =============================动态sql================================== -->
<!--
map.put("name", "安");
map.put("gender", "0");
map.put("personAddr", "东京")
map.put("birthday", new Date());
<where>会自动处理and，第一个and可以不写，其他的and必须要写
-->
<select id="selectPersonByCondition" parameterType="map" resultMap="BaseResultMap">
select * from person t
<where>
<if test="name != null">
t.name like '%${name}%'
</if>
<if test="gender != null">
and t.gender = #{gender}
</if>
<if test="personAddr != null">
and t.person_addr like '%${personAddr}%'
</if>
<if test="birthday != null">
<![CDATA[
and t.birthday < #{birthday}
]]>
</if>
</where>
</select>
<!--
动态修改
<set>标签可以去掉最后一个逗号
flushCache：二级缓存的刷新的配置：默认是true：会刷新，如果false就不刷新缓存
-->
<update id="dynamicUpdate" parameterType="person" flushCache="false">
update person t
<set>
<if test="name != null">
t.name = #{name},
</if>
<if test="gender != null">
t.gender = #{gender},
</if>
<if test="personAddr != null">
t.person_addr = #{personAddr},
</if>
<if test="birthday != null">
t.birthday = #{birthday}
</if>
</set>
where t.person_id = #{personId}
</update>
<!--
select * from person t where t.person_id in (1,2,3)
map.put("ids", list);
-->
<select id="selectPersonByIn" parameterType="map" resultMap="BaseResultMap">
select * from person t where t.person_id in
<foreach collection="ids" item="personId" open="(" close=")" separator="," index="index">
#{personId}
</foreach>
</select>
<!--
map.put("pList", pList);
insert into person (person_id, name, gender, person_addr, birthday)
values
(#{personId}, #{name}, #{gender}, #{personAddr}, #{birthday}),
(#{personId}, #{name}, #{gender}, #{personAddr}, #{birthday}),
(#{personId}, #{name}, #{gender}, #{personAddr}, #{birthday}),
(#{personId}, #{name}, #{gender}, #{personAddr}, #{birthday}),
(#{personId}, #{name}, #{gender}, #{personAddr}, #{birthday});
-->
<insert id="insertBatch" parameterType="map">
<selectKey keyProperty="personId" order="AFTER" resultType="int">
select LAST_INSERT_ID()
</selectKey>
insert into person (person_id, name, gender, person_addr, birthday)
values
<foreach collection="pList" item="person" separator=",">
(#{person.personId}, #{person.name}, #{person.gender}, #{person.personAddr}, #{person.birthday})
</foreach>
</insert>
<delete id="deleteBatch" parameterType="map">
delete from person where person_id in
<foreach collection="ids" item="personId" open="(" close=")" separator="," index="index">
#{personId}
</foreach>
</delete>
<!-- ===============================关联查询================== -->
<!--一对多 -->
<resultMap type="person" id="selectPersonAndOrderByPIdRM">
<id column="person_id" property="personId"/>
<result column="name" property="name"/>
<result column="gender" property="gender"/>
<result column="person_addr" property="personAddr"/>
<result column="birthday" property="birthday"/>
<!--
collection:一对多的关联映射
property：一的端集合的属性名
ofType：集合中的泛型
-->
<collection property="ordersList" ofType="com.rl.model1.Orders">
<id column="ORDER_ID" property="orderId" jdbcType="INTEGER"/>
<result column="PERSON_ID" property="personId" jdbcType="INTEGER"/>
<result column="TOTAL_PRICE" property="totalPrice" jdbcType="REAL"/>
<result column="ADDR" property="addr" jdbcType="VARCHAR"/>
</collection>
</resultMap>
<!--
extends:resultMap的继承
-->
<resultMap type="person" id="selectPersonAndOrderByPIdRM1" extends="BaseResultMap">
<collection property="ordersList" ofType="com.rl.model1.Orders">
<id column="ORDER_ID" property="orderId" jdbcType="INTEGER"/>
<result column="PERSON_ID" property="personId" jdbcType="INTEGER"/>
<result column="TOTAL_PRICE" property="totalPrice" jdbcType="REAL"/>
<result column="ADDR" property="addr" jdbcType="VARCHAR"/>
</collection>
</resultMap>
<resultMap type="person" id="selectPersonOrderAndDetailByPIdRM" extends="BaseResultMap">
<collection property="ordersList" ofType="com.rl.model1.Orders">
<id column="ORDER_ID" property="orderId" jdbcType="INTEGER"/>
<result column="PERSON_ID" property="personId" jdbcType="INTEGER"/>
<result column="TOTAL_PRICE" property="totalPrice" jdbcType="REAL"/>
<result column="ADDR" property="addr" jdbcType="VARCHAR"/>
<collection property="detailList" ofType="com.rl.model1.OrderDetail">
<id column="DETAIL_ID" property="detailId" jdbcType="INTEGER"/>
<result column="ORDER_ID" property="orderId" jdbcType="INTEGER"/>
<result column="PRICE" property="price" jdbcType="REAL"/>
<result column="QUANTITY" property="quantity" jdbcType="INTEGER"/>
<result column="ITEM_NAME" property="itemName" jdbcType="VARCHAR"/>
</collection>
</collection>
</resultMap>
<resultMap type="person" id="selectPersonAndRoleByPIdRM" extends="BaseResultMap">
<collection property="roleList" ofType="com.rl.model1.Role">
<id column="ROLE_ID" property="roleId" jdbcType="INTEGER"/>
<result column="ROLE_NAME" property="roleName" jdbcType="VARCHAR"/>
<result column="DESCRIPT" property="descript" jdbcType="VARCHAR"/>
</collection>
</resultMap>
<select id="selectPersonAndOrderByPId" parameterType="int" resultMap="selectPersonAndOrderByPIdRM1">
select * from person p, orders o where p.PERSON_ID = o.PERSON_ID and p.PERSON_ID = #{personId}
</select>
<select id="selectPersonOrderAndDetailByPId" parameterType="int" resultMap="selectPersonOrderAndDetailByPIdRM">
select * from person p,
orders o,
order_detailod where
p.PERSON_ID = o.PERSON_ID
and o.ORDER_ID = od.ORDER_ID
and p.PERSON_ID = #{personId}
</select>
<!--多对多从Person一端看 -->
<select id="selectPersonAndRoleByPId" parameterType="int" resultMap="selectPersonAndRoleByPIdRM">
SELECT p.*, r.* from person p,
person_rolepr,
role r where
p.PERSON_ID = pr.PERSON_ID
and pr.ROLE_ID = r.ROLE_ID
and p.PERSON_ID = #{personId}
</select>
<!-- =========================延迟加载======================== -->
<resultMap type="person" id="selectPersonByIdLazyRM" extends="BaseResultMap">
<!--
column：主sql的一列作为子sql的参数
select：指定子sql的位置
-->
<collection property="ordersList" column="person_id" select="com.rl.mapper.OrdersMapper.selectOrderByPersonId">
</collection>
</resultMap>
<select id="selectPersonByIdLazy" parameterType="int" resultMap="selectPersonByIdLazyRM">
select * from person t where t.person_id = #{personId}
</select>
</mapper>
