文章导航

Mapper文件的编写

select

1
2
3
<select id = “ selectPerson” parameterType = “ int” resultType = “ hashmap” >   
  SELECT * FROM PERSON WHERE ID =＃{id}
</ select>

这里有两点需要注意的，一是区分#和$的区别.#的底层是基于预编译语句来实现了，这样可以避免SQL注入的风险。而$在底层是通过字符串的直接拼接来实现了，因此有SQL注入的风险。

上面是常见的select的用法，实际上它还有许多其它的属性供我们选用。

比如：

1
2
3
4
5
6
7
8
9
10
11
12
<select
  id="selectPerson"
  parameterType="int"
  parameterMap="deprecated"
  resultType="hashmap"
  resultMap="personResultMap"
  flushCache="false"
  useCache="true"
  timeout="10"
  fetchSize="256"
  statementType="PREPARED"
  resultSetType="FORWARD_ONLY">

下面列举了一些相对比较常用的属性：

属性 描述
id
parameterType 将传递到该语句中的参数的标准类名或别名
resultType 从该语句返回的结果预期的标准类名或别名
resultMap 对外部resultMap的引用，用于结果集映射
flushCache 若将该属性设置为true，那么调用此语句时会刷新本地和二级缓存
useCache 将此属性设置为true，这该语句的结果会被缓存到二级缓存中。默认值为true
timeout 超时时间
fetchSize 设置成批的返回的行数
statementType 设置statement的类型，有STATEMENT,PREPARED或CALLABLE.默认是第二种，即PreparedStatement

insert，update and delete

这三个标签常见的用法如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
<insert
  id="insertAuthor"
  parameterType="domain.blog.Author"
  flushCache="true"
  statementType="PREPARED"
  keyProperty=""
  keyColumn=""
  useGeneratedKeys=""
  timeout="20">

<update
  id="updateAuthor"
  parameterType="domain.blog.Author"
  flushCache="true"
  statementType="PREPARED"
  timeout="20">

<delete
  id="deleteAuthor"
  parameterType="domain.blog.Author"
  flushCache="true"
  statementType="PREPARED"
  timeout="20">

一些属性的用法补充：

属性 解释
useGeneratedKeys 当设置为true的时候，使用数据库内部生成的主键。默认为false
keyProperty 该属性一般与useGeneratedKeys结合使用，它标识的是Java对象的属性名。配置了该属性之后，会将数据库中自动生成的主键存到对应的java属性中。
keyColumn 这几个属性一般是结合使用的,keyColumn指定数据库主键字段名。

sql片段

sql片段是mybatis动态sql的基础。它的使用方法如下：

1
2
3
4
5
6
7
8
9
10
<sql id="userColumns"> ${alias}.id,${alias}.username,${alias}.password </sql>
    
    
<select id="selectUsers" resultType="map">
  select
    <include refid="userColumns"><property name="alias" value="t1"/></include>,
    <include refid="userColumns"><property name="alias" value="t2"/></include>
  from some_table t1
    cross join some_table t2
</select>

这里通过属性，为sql片段传递数据，使用起来非常的灵活：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
<sql id="sometable">
  ${prefix}Table
</sql>

<sql id="someinclude">
  from
    <include refid="${include_target}"/>
</sql>

<select id="select" resultType="map">
  select
    field1, field2, field3
  <include refid="someinclude">
    <property name="prefix" value="Some"/>
    <property name="include_target" value="sometable"/>
  </include>
</select>

resultMap

结果集映射是mybatis非常重要的特性

它的简单的使用方法如下：

1
2
3
4
5
<resultMap id="userResultMap" type="User">
  <id property="id" column="user_id" />
  <result property="username" column="user_name"/>
  <result property="password" column="hashed_password"/>
</resultMap>

在<resultMap>还可以使用<constructor>标签，通过构造器完成结果集Java对象的映射。

1
2
3
4
5
<constructor>
   <idArg column="id" javaType="int"/>
   <arg column="username" javaType="String"/>
   <arg column="age" javaType="_int"/>
</constructor>

用于一对一关联查询结果映射的<assocation>标签的使用方式：

1
2
3
4
5
6
7
8
9
10
11
<resultMap id="blogResult" type="Blog">
  <association property="author" column="author_id" javaType="Author" select="selectAuthor"/>
</resultMap>

<select id="selectBlog" resultMap="blogResult">
  SELECT * FROM BLOG WHERE ID = #{id}
</select>

<select id="selectAuthor" resultType="Author">
  SELECT * FROM AUTHOR WHERE ID = #{id}
</select>

另一个例子：

1
2
3
4
5
6
7
8
9
10
11
<resultMap id="blogResult" type="Blog">
  <id property="id" column="blog_id" />
  <result property="title" column="blog_title"/>
  <association property="author" javaType="Author">
    <id property="id" column="author_id"/>
    <result property="username" column="author_username"/>
    <result property="password" column="author_password"/>
    <result property="email" column="author_email"/>
    <result property="bio" column="author_bio"/>
  </association>
</resultMap>

一对多的关系映射的标签<collection>标签的使用方式：

1
2
3
4
5
<collection property="posts" ofType="domain.blog.Post">
  <id property="id" column="post_id"/>
  <result property="subject" column="post_subject"/>
  <result property="body" column="post_body"/>
</collection>
1
private List<Post> posts;

另一个例子是这样的：

1
2
3
4
5
6
7
8
9
<resultMap id="blogResult" type="Blog">
  <id property="id" column="blog_id" />
  <result property="title" column="blog_title"/>
  <collection property="posts" ofType="Post">
    <id property="id" column="post_id"/>
    <result property="subject" column="post_subject"/>
    <result property="body" column="post_body"/>
  </collection>
</resultMap>

鉴别器<discriminator>,它可以根据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
<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>

动态SQL

if

1
2
3
4
5
6
7
8
<select id="findActiveBlogWithTitleLike"
     resultType="Blog">
  SELECT * FROM BLOG
  WHERE state = ‘ACTIVE’
  <if test="title != null">
    AND title like #{title}
  </if>
</select>

choose, when, otherwise

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<select id="findActiveBlogLike"
     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 author_name like #{author.name}
    </when>
    <otherwise>
      AND featured = 1
    </otherwise>
  </choose>
</select>

trim, where, set

1
2
3
<trim prefix="WHERE" prefixOverrides="AND |OR ">
  ...
</trim>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<select id="findActiveBlogLike"
     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 author_name like #{author.name}
    </if>
  </where>
</select>
1
2
3
4
5
6
7
8
9
10
<update id="updateAuthorIfNecessary">
  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>

foreach

1
2
3
4
5
6
7
8
9
<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>