Mybatis学习总结（三） 实战

前面两篇文章已经简单介绍了Mybatis并且讲述了如何快速搭建Mybatis环境，本文简单介绍项目开发中主要用到的功能点，包括以下知识点：

1. ResultMap的编写
2. 基本select
3. update语句，<set>标签
4. 单层foreach的用法
5. delete语句
6. <![CDATA[需要转义的段落]]>，转义 <> & 符号
7. <if test="nodeType==1"></if> 动态sql
8. 双层foreach
9. 分层List，一对多映射 collection，一对一映射 association
10. resultMap 中调用外部select单元
11. <sql> 标签定义代码段，<include>调用，实现SQL复用

本项目包括所有文件和数据库脚本本人都已上传到码云：https://gitee.com/nonkey/Mybatis-Practise 

欢迎各位大神批评指正，共同成长。

<?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.jnk.mybatis.practise.service.mapper.IPlayerMapper">
	<resultMap type="com.jnk.mybatis.practise.service.entity.PlayerEntity"
		id="playerInfo">
		<result property="id" column="ID" />
		<result property="pNameCn" column="P_NAME_CN" />
		<result property="pNameEn" column="P_NAME_EN" />
		<result property="pNo" column="P_NO" />
		<result property="birthDay" column="BIRTH_DAY" />
		<result property="age" column="AGE" />
		<result property="teamId" column="TEAM_ID" />
	</resultMap>
	<sql id="commonPlayerInfo">
		select
		ID,
		P_NAME_CN,
		P_NAME_EN,
		P_NO,
		BIRTH_DAY,
		YEAR(CURRENT_DATE()) - YEAR(BIRTH_DAY) as AGE,
		TEAM_ID
		from tbl_player
	</sql>
	<select id="queryPlayerInfo" resultMap="playerInfo">
		<include refid="commonPlayerInfo"></include>
		where id = #{id}
	</select>

	<select id="queryAllPlayerInfo" resultMap="playerInfo">
		<include refid="commonPlayerInfo"></include>
		where id <![CDATA[<>]]>
		6 <!-- CDATA 转义的用法 -->
	</select>

	<update id="updatePlayerById">
		update tbl_player
		<set>
			P_NAME_EN = #{pNameEn}
		</set>
		where id=#{id}
	</update>

	<update id="updatePlayerByIds">
		<foreach collection="players" item="item" index="index" open=""
			close="" separator=";">
			update tbl_player
			<set>
				P_NAME_EN=#{item.pNameEn}
			</set>
			where id=#{item.id}
		</foreach>
	</update>

	<update id="updatePlayerByCondition">
		<foreach collection="players" item="item" separator=";">
			update tbl_player
			<set>
				<if test="condition == 1">
					P_NAME_EN = #{item.pNameEn},
				</if>
				<if test="condition == 2">
					P_NAME_CN = #{item.pNameCn}
				</if>
			</set>
			where id=#{item.id}
		</foreach>
	</update>


	<resultMap id="totalZone"
		type="com.jnk.mybatis.practise.service.vo.TotalZoneInfoVo">
		<result property="zoneId" column="ZONE_ID"></result>
		<result property="zoneName" column="ZONE_NAME_CN"></result>
		<collection property="teams"
			ofType="com.jnk.mybatis.practise.service.vo.TeamVo">
			<result property="teamId" column="TEAM_ID"></result>
			<result property="teamNameCn" column="TEAM_NAME_CN"></result>
			<result property="location" column="LOCATION"></result>
			<collection property="players"
				ofType="com.jnk.mybatis.practise.service.entity.PlayerEntity"
				column="{teamId=TEAM_ID}" select="queryPlayerInfoByTeamId"></collection>
		</collection>
	</resultMap>
	<select id="queryAllZoneInfo" resultMap="totalZone">
		SELECT
		zone.ZONE_ID,
		zone.ZONE_NAME_CN,
		team.TEAM_ID,
		team.TEAM_NAME_CN,
		team.LOCATION
		FROM
		tbl_zone zone
		INNER JOIN tbl_team team ON zone.ZONE_ID = team.ZONE_ID
	</select>

	<!-- association 感觉用处不大，这里有两个分区，返回值也是List，但是只会返回一个 -->
	<resultMap id="zoneMap" type="com.jnk.mybatis.practise.service.vo.ZoneVo">
		<association property="zones"
			javaType="com.jnk.mybatis.practise.service.entity.ZoneEntity">
			<result property="zoneId" column="ZONE_ID"></result>
			<result property="zoneName" column="ZONE_NAME_CN"></result>
		</association>
	</resultMap>
	<select id="queryZoneInfo" resultMap="zoneMap">
		SELECT
			zone.ZONE_ID,
			zone.ZONE_NAME_CN
		FROM
		tbl_zone zone
	</select>

	<select id="queryPlayerInfoByTeamId" resultMap="playerInfo">
		<include refid="commonPlayerInfo"></include>
		where TEAM_ID = #{teamId}
	</select>
	
	<!-- 多层foreach循环 -->
	<update id="updateTeamPlayer">
		<foreach collection="teams" item="items">
			<foreach collection="items.players" item="player">
				update tbl_player
				set P_NAME_EN = CONCAT(#{player.pNameCn},' NEW')
				where ID = #{player.id};
			</foreach>
		</foreach>
	</update>

	<delete id="deletePlayerById">
		delete from tbl_player
		where id = #{id};
	</delete>
	
</mapper>

参考文献：
http://www.yiibai.com/mybatis/
http://www.mybatis.org/mybatis-3/zh/index.html