MyBatis 3.Mapper XML 文件

SQL映射有几个顶级元素(按照定义需要的顺序排列):

• cache – 给定命名空间的缓存配置。
• cache-ref – 其他命名空间缓存配置的引用。
• resultMap – 是最复杂也是最强大的元素，用来描述如何从数据库结果集中来加载对象。
• sql – 可被其他语句引用的可重用语句块。
• insert – 映射插入语句
• update – 映射更新语句
• delete – 映射删除语句
• select – 映射查询语句

select

简单的查询语句,此处#{id} , 是用来标记需要替换的参数:

<select id="selectPerson" parameterType="int" resultType="hashmap">
  SELECT * FROM PERSON WHERE ID = #{id}
</select>

以下是等效语句:

// Similar JDBC code, NOT MyBatis…
String selectPerson = "SELECT * FROM PERSON WHERE ID=?";
PreparedStatement ps = conn.prepareStatement(selectPerson);
ps.setInt(1,id);

下面是select属性详解:

<select
  id="selectPerson"    //名称空间中的唯一标示符,可用于引用这个语句
  parameterType="int"  //
  resultType="hashmap"
  resultMap="personResultMap"
  flushCache="false"
  useCache="true"
  timeout="10000"
  fetchSize="256"
  statementType="PREPARED"
  resultSetType="FORWARD_ONLY">

insert / update / delete 很相似

<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>

insert / update / delete 示例:

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

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

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

主键如果设置了自动生成策略 , 那么只需要设置userGeneratedKeys=”true”, 把keyProperty设置到指定的自增目标属性上即可:

<insert id="insertAuthor" useGeneratedKeys="true"
    keyProperty="id">
  insert into Author (username,password,email,bio)
  values (#{username},#{password},#{email},#{bio})
</insert>

如果数据库支持多行插入,可以传入一个对象数组,并返回生成的主键

<insert id="insertAuthor" useGeneratedKeys="true"
    keyProperty="id">
  insert into Author (username, password, email, bio) values
  <foreach item="item" collection="list" separator=",">
    (#{item.username}, #{item.password}, #{item.email}, #{item.bio})
  </foreach>
</insert>

如果不支持自动生成主键的话,也有相应的mybatis与之对应:

<insert id="insertAuthor">
  <selectKey keyProperty="id" resultType="int" order="BEFORE">
    select CAST(RANDOM()*1000000 as INTEGER) a from SYSIBM.SYSDUMMY1
  </selectKey>
  insert into Author
    (id, username, password, email,bio, favourite_section)
  values
    (#{id}, #{username}, #{password}, #{email}, #{bio}, #{favouriteSection,jdbcType=VARCHAR})
</insert>

SQL

可以用来定义可重用的代码段,可以使用include标签包含在其它的代码段中.

<sql id="userColumns"> ${alias}.id,${alias}.username,${alias}.password </sql>

以上语句能够被其它的语句引用(refid)

<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>

property的值可以是动态传入的(此处先定义好两个可以动态传值的sql , 然后include时候 , 使用property属性赋值. )

<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>

参数 Parameters

传入简单参数id

<select id="selectUsers" resultType="User">
  select id, username, password
  from users
  where id = #{id}
</select>

传入复杂参数User(会自动在user中查找id,username,password)

<insert id="insertUser" parameterType="User">
  insert into users (id, username, password)
  values (#{id}, #{username}, #{password})
</insert>

也可以手动指定参数的映射(注意如果null作为值传递 ,jdbcType是必须的)

#{property,javaType=int,jdbcType=NUMERIC}

使用#{}语法的语句,其中的值会被MyBatis安全的预处理 . 但是使用${}语法来替换值的时候,MyBatis不会修改或转义字符串.

Result maps 结果集映射

以下两种设置都是可以的, 前一种会使用hashmap,后一种

<select id="selectUsers" resultType="com.someapp.model.User">
  select id, username, hashedPassword
  from some_table
  where id = #{id}
</select>

<!-- In mybatis-config.xml file -->
<typeAlias type="com.someapp.model.User" alias="User"/>

<!-- In SQL Mapping XML file -->
<select id="selectUsers" resultType="User">
  select id, username, hashedPassword
  from some_table
  where id = #{id}
</select>

如果列名不匹配的时候, 也可使用这种映射方式:

<resultMap id="userResultMap" type="User">
  <id property="id" column="user_id" />
  <result property="username" column="user_name"/>
  <result property="password" column="hashed_password"/>
</resultMap>

注意此处使用的是userResultType

<select id="selectUsers" resultMap="userResultMap">
  select user_id, user_name, hashed_password
  from some_table
  where id = #{id}
</select>

如果是多表关联的映射 , 我们不能简单的通过单表映射来完成, 看以下示例:

<!-- Very Complex Statement -->
<select id="selectBlogDetails" resultMap="detailedBlogResultMap">
  select
       B.id as blog_id,
       B.title as blog_title,
       B.author_id as blog_author_id,
       A.id as author_id,
       A.username as author_username,
       A.password as author_password,
       A.email as author_email,
       A.bio as author_bio,
       A.favourite_section as author_favourite_section,
       P.id as post_id,
       P.blog_id as post_blog_id,
       P.author_id as post_author_id,
       P.created_on as post_created_on,
       P.section as post_section,
       P.subject as post_subject,
       P.draft as draft,
       P.body as post_body,
       C.id as comment_id,
       C.post_id as comment_post_id,
       C.name as comment_name,
       C.comment as comment_text,
       T.id as tag_id,
       T.name as tag_name
  from Blog B
       left outer join Author A on B.author_id = A.id
       left outer join Post P on B.id = P.blog_id
       left outer join Comment C on P.id = C.post_id
       left outer join Post_Tag PT on PT.post_id = P.id
       left outer join Tag T on PT.tag_id = T.id
  where B.id = #{id}
</select>