MyBatis总结（2）- MyBatis实现原理（三）

核心配置

• JavaBeanMapper.xml（sql映射）

作用

JavaBeanMapper.xml实现：

1. 用来干什么？

   • 定义Sql语句映射。相对照JDBC的实现，是将原本的Sql代码提取出来，最终根据映射关系执行Sql操作。

2. 好处？

   • 解耦，mapper只关心定义Sql的映射关系，与java代码分离，更易维护。

3. 如何使用？

   • 先来展示一个基本的mapper xml，这里涉及到主要的几个标签元素：
     • Select
     • Insert
     • Update
     • Delete
     • ResultMap
     • Sql
     • Cache

<?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="org.example.daos.UserMapper">
    <resultMap id="userMap" type="Customer">
        <result column="pwd" property="password"/>
    </resultMap>

    <select id="getUserList" resultMap="userMap">
        SELECT * FROM mybatis.user
    </select>

    <select id="getUserListByRowBounds" resultMap="userMap">
        SELECT * FROM mybatis.user
    </select>
    <!-- 模糊查询1-->
    <!--<select id="getUserListForFuzzyQuery" resultType="org.example.pojo.User">
        SELECT * FROM mybatis.user where name like #{name}
    </select>-->
    <!-- 模糊查询2： "%"-->
    <select id="getUserListForFuzzyQuery" resultType="org.example.pojo.User">
        SELECT * FROM mybatis.user where name like "%"#{name}
    </select>
    <!-- 形参只有一个，且为基本类型时，parameterType可省略（parameterType="int" ）-->
    <select id="getUserById" resultType="org.example.pojo.User">
        SELECT * FROM mybatis.user where id = #{id}
    </select>

    <insert id="addUser" parameterType="org.example.pojo.User">
        INSERT INTO mybatis.user(id, name, pwd) values (#{id},#{name},#{pwd})
    </insert>

    <update id="updateUserByUser" parameterType="org.example.pojo.User">
        UPDATE mybatis.user set name=#{name}, pwd=#{pwd} where id=#{id}
    </update>

    <update id="updateUserByMap" parameterType="map">
        UPDATE mybatis.user set name=#{userName}, pwd=#{userPwd} where id=#{userId}
    </update>

    <delete id="deleteUser" parameterType="int">
        DELETE FROM mybatis.user where id=#{id}
    </delete>
</mapper>

4. 具体的标签元素：
   • Select：
     • 这里的重点是，resultType，resultMap的使用，两者只能二选一
       • ResultType：语句中返回结果的类全限定名或别名。一般是该sql映射方法的返回值类型。特殊的，如果是集合类型，则只需定义集合的泛型类型即可。
       • ResultMap：对外部 resultMap 的命名引用。一般用于处理复杂的映射结果查询，比如：多表查询（一对多，多对一）：

多对一查询：

<?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="org.example.daos.StudentMapper">

    <!-- 多对一查询方式1：Teacher再查一次:子查询
     相当于:

        select id, name, tid from student where tid = (select id from teacher where id = tid)
     -->
    <!--<resultMap id="StudentTeacher1" type="Student">
        <id property="id" column="id"/>
        <result column="name" property="name"/>
        <association property="teacher" column="tid" select="getTeacherById" javaType="Teacher"/>
    </resultMap>

    <select id="getStudentList" resultMap="StudentTeacher1">
        select * from student
    </select>

    <select id="getTeacherById" resultType="Teacher">
        select * from teacher where id=#{tid}
    </select>-->

    <!-- 多对一查询方式2：按照结果嵌套, 联表查询
    相当于：
        select s.id sid, s.name sname, t.id tid, t.name tname from student s, teacher t where s.tid = t.id
    -->
    <select id="getStudentList" resultMap="StudentTeacher2">
        select s.id sid, s.name sname, t.id tid, t.name tname from student s, teacher t where s.tid = t.id
    </select>

    <resultMap id="StudentTeacher2" type="Student">
        <result property="id" column="sid"/>
        <result property="name" column="sname"/>
        <association property="teacher" javaType="Teacher">
            <id column="id" property="tid"/>
            <result property="name" column="tname"/>
        </association>
    </resultMap>

</mapper>

一对多查询：

<?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="org.example.daos.TeacherMapper">
    <!--一对多：方式1：联表查询 -->
    <select id="getTeacherById" resultMap="TeacherStudent">
        select t.id tid, t.name tname, s.id sid, s.name sname from teacher t, student s where t.id = s.tid and t.id=#{tid}
    </select>

    <resultMap id="TeacherStudent" type="Teacher">
        <result property="id" column="tid"/>
        <result property="name" column="tname"/>
        <collection property="studentList" ofType="Student">
            <result property="id" column="sid"/>
            <result property="name" column="sname"/>
            <result property="tid" column="tid"/>
        </collection>
    </resultMap>
    <!--一对多：方式2：子查询 -->
    <select id="getTeacherById2" resultMap="TeacherStudent2">
        select * from teacher where id=#{tid}
    </select>
    <resultMap id="TeacherStudent2" type="Teacher">
        <result column="id" property="id"/>
        <collection property="studentList" column="id" ofType="Student" select="getStudentByTid"/>
    </resultMap>

    <select id="getStudentByTid" resultType="Student">
        select * from student where tid=#{tid}
    </select>
</mapper>

• Insert：涉及到自动生成主键id的设置(keyProperty, useGeneratedKeys)，多行插入(foreach)
• Update：
• Delete：

<!-- 自动生成主键id -->
<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>

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

• Sql：sql语句重用片段。也可动态赋值

<sql id="if_title_author">
    <if test="title!= null">
        AND title = #{title}
    </if>
    <if test="author != null">
        AND author = #{author}
    </if>
</sql>

<select id="queryBlog1" parameterType="map">
    select * from blog where 1=1
    <include refid="if_title_author"/>
</select>


<!-- 动态赋值: ${include_target}, property -->
<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"/>
  </include>
</select>

• 参数的定义：
  • 如果一个列允许使用 null 值，并且会使用值为 null 的参数，就必须要指定 JDBC 类型（jdbcType）

#{average,javaType=double,jdbcType=NUMERIC,typeHandler=MyTypeHandler,numericScale=2}

• 字符串替换: ${}方式不会被预编译转义，可以通过这种方式指定某个字符串column，而非对应的数值。但存在sql注入风险。

@Select("select * from user where ${column} = #{value}")
User findByColumn(@Param("column") String column, @Param("value") String value);

• association & collection ：collection 用于一对多，association用于多对一。

association 联表查询
<association property="author" column="blog_author_id" javaType="Author">
  <id property="id" column="author_id"/>
  <result property="username" column="author_username"/>
</association>

association 子表查询
<resultMap id="blogResult" type="Blog">
  <association property="author" column="author_id" javaType="Author" select="selectAuthor"/>
</resultMap>

collection子表查询
<collection property="posts" column="id" ofType="Post" select="selectPostsForBlog"/>

collection联表查询
<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>

• OfType：

<collection property="posts" javaType="ArrayList" column="id" ofType="Post" select="selectPostsForBlog"/>

可以读作： “posts 是一个存储 Post 的 ArrayList 集合” 。且在一般情况下，MyBatis 可以推断 javaType 属性，因此并不需要填写。

• 缓存Cache：

  • 一级缓存：默认开启。SqlSession级别的缓存，也叫本地缓存
  • 二级缓存：基于namespace级别的缓存，针对mapper <cache>, LRU, FIFO，开启二级缓存，需要在对于mapper上，加入标签元素<Cache>即可
  • 当会话sqlSession提交commit或关闭close时，一级缓存的数据才会提交到二级缓存中！！！
  • 缓存顺序：当查询业务来到DAO层时：
    • 先查看二级缓存；
    • 再查看一级缓存；
    • 最后再查数据库

• 动态Sql：解决在定义Sql映射时，拼接sql语句：where子句条件，SET子句，多条语句foreach的编写。参考链接

  • If, choose, foreach, trim

• 分页：limit

  • Select * from user limit startIndex, pageSize
  • RowBounds（selectList (String statement, Object parameter, RowBounds rowBounds)）
  • Mybatis PageHelper

• 注解开发： 参考链接