mybatis笔记

Sql 元素
这个元素用来定义能够被其它语句引用的可重用 SQL 语句块。例如：
<sql id=”userColumns”> id,username,password </sql>
这个 SQL 语句块能够被其它语句引用，如:
<select id=”selectUsers” parameterType=”int” resultType=”hashmap”>
select <include refid=”userColumns”/>
from some_table
where id = #{id}
</select>


<insert id="insertAuthor" parameterType="domain.blog.Author">
insert into Author (id,username,password,email,bio)
values (#{id},#{username},#{password},#{email},#{bio})
</insert>


<update id="updateAuthor" parameterType="domain.blog.Author">
update Author set
username = #{username},
password = #{password},
email = #{email},
bio = #{bio}
where id = #{id}
</update>


<delete id="deleteAuthor” parameterType="int">
delete from Author where id = #{id}
</delete>


字符串替换
默认的情况下，使用#{}语法会促使MyBatis 生成PreparedStatement并且安全地设置
PreparedStatement 参数（=？）值。尽量这是安全、快捷并且是经常使用的，但有时候您可能想
直接未更改的字符串代入到SQL 语句中。比如说，对于ORDER BY，您可以这样使用
ORDER BY ${columnName}
这样 MyBatis 就不会修改这个字符串了。


<resultMap id="blogResult" type="Blog">
<id property=”id” column="blog_id" />
<result property="title" column="blog_title"/>
<collection property="posts" ofType="Post" resultMap=”blogPostResult”/>
</resultMap>

<resultMap id="blogPostResult" type="Post">
<id property="id" column="post_id"/>
<result property="subject" column="post_subject"/>
<result property="body" column="post_body"/>
</resultMap>



Discriminator定义要指定column和javaType属性。列是MyBatis 将要取出进行比较的值，
javaType 用来确定适当的测试是否正确运行（即使是String 在大部分情况下也可以工作），
例：
<resultMap id="vehicleResult" type="Vehicle">
<id property=”id” column="id" />
<result property="vin" column="vin"/>
<result property="year" column="year"/>
<result property="make" column="make"/>
<result property="model" column="model"/>
<result property="color" column="color"/>
<discriminator javaType="int" column="vehicle_type">
<case value="1" resultMap="carResult"/>
<case value="2" resultMap="truckResult"/>
<case value="3" resultMap="vanResult"/>
<case value="4" resultMap="suvResult"/>
</discriminator>
</resultMap>

<resultMap id="carResult" type="Car" extends=”vehicleResult”>
<result property=”doorCount” column="door_count" />
</resultMap>



<select id=”findActiveBlogLike”
parameterType=”Blog” resultType=”Blog”>
SELECT * FROM BLOG WHERE state = ‘ACTIVE’
<if test=”title != null”>
AND title like #{title}
</if>
<if test=”author != null and author.name != null”>
AND title like #{author.name}
</if>
</select>



<select id=”findActiveBlogLike”
parameterType=”Blog” resultType=”Blog”>
SELECT * FROM BLOG WHERE state = ‘ACTIVE’
<choose>
<when test=”title != null”>
AND title like #{title}
</when>
<when test=”author != null and author.name != null”>
AND title like #{author.name}
</when>
<otherwise>
AND featured = 1
</otherwise>
</choose>
</select>



<select id=”findActiveBlogLike”
parameterType=”Blog” resultType=”Blog”>
SELECT * FROM BLOG
<where>
<if test=”state != null”>
state = #{state}
</if>
<if test=”title != null”>
AND title like #{title}
</if>
<if test=”author != null and author.name != null”>
AND title like #{author.name}
</if>
</where>
</select>



<update id="updateAuthorIfNecessary"
parameterType="domain.blog.Author">
update Author
<set>
<if test="username != null">username=#{username},</if>
<if test="password != null">password=#{password},</if>
<if test="email != null">email=#{email},</if>
<if test="bio != null">bio=#{bio}</if>
</set>
where id=#{id}
</update>



<select id="selectPostIn" resultType="domain.blog.Post">
SELECT *
FROM POST P
WHERE ID in
<foreach item="item" index="index" collection="list"
open="(" separator="," close=")">
#{item}
</foreach>
</select>