Mybatis—xml映射文件分解

1.select/insert/update/delete

<!--返回主键 -->
<insert id="addReturnKey" parameterType="book" useGeneratedKeys="true" keyProperty="book_id">
    insert into book(name, price) values(#{name}, #{price})
</insert>

<!--生成主键再插入 -->
<insert id="insertUseKey" parameterType="book">
    <selectKey keyProperty="book_id" resultType="int" order="BEFORE">
        select ceil(rand() * 100)
    </selectKey>
        insert into book (book_id, name, price) values (#{book_id}, #{name}, #{price})
</insert>

2.sql：复用SQL片段

<sql id="selectAll" > select * from </sql>

<select id="queryAll" resultType="book">
    <include refid="selectAll" /> book
</select>

<!--可以给SQL传递变量 -->
<sql id="selectAllParms"> select * from ${table_name} </sql>

<select id="queryAll" resultType="book">
    <include refid="selectAllParms">
        <property name="table_name" value="book"/>
    </include>
</select>

3.cache/cache-ref: 二级缓存

• Mybatis分为一级缓存和二级缓存，一级缓存是SqlSession上的缓存(默认开启一级缓存，不需要序列化)
• 二级缓存是SqlSessionFactory上的缓存，需要在命名空间配置，默认缓存所有的select语句

<!-- 开启二级缓存: 二级缓存在SQLSessionFactory中,对象需要序列化,查询结束需要commit()才会缓存 -->
<cache readOnly="true"/>
<!-- 不需要二级缓存的使用useCache关闭 -->
<select id="findBookById" resultType="book" useCache="false">
    select book_id, name, price from book where book_id = #{id}
</select>

4.结果映射

• 默认会将pojo类和MySQL实体自动映射，因此类属性名和表的列名必须相同，对于列名和类属性名不同由以下2种处理方式

-------------------SQL别名 ------------------------
<select id="findBookById" resultType="book">
    select book_id as id, name, price from book where book_id = #{id}
</select>
-------------------显式使用resultMap映射----------------
<select id="findBookById" resultMap="mapBook">
    select * from book where book_id = #{id}
</select>

<resultMap id="mapBook" type="book">
    <id property="id" column="book_id"/>
    <result property="name" column="name"/>
</resultMap>

5.复杂结果映射

• 准备数据表和对应实体类

table:
    studentIdCard{自增id, code} 学生证表
    student{自增id, name, sex, card表的引用外键card_id, classed表的引用外键class_id} 学生表
    classed(自增id, name) 班级表
class:
    public class StudentIdCard {
        private Integer id;
        private String code;
    }
    public class Student {
        private Integer id;
        private String name;
        private String sex;
        private StudentIdCard studentIdCard;
    }
    public class Classed {
        private int id;
        private String name;
        private List<Student> studentList;
    }

• 一对一关联:association

-----------------------------------------------嵌套查询----------------------------------------
<!-- 根据student表的id查出student表信息-->
<select id="findStudentById" parameterType="Integer" resultMap="StudentIdCardWithStudentResult">
    select * from student where id = #{id}
</select>
<!-- 根据studentidcard表的id查出信息-->
<select id="findStudentIdCardById" resultType="studentIdCard">
    select * from studentIdCard where id = #{id}
</select>

<resultMap id="StudentIdCardWithStudentResult" type="Student">
    <!-- 从student表查出的数值赋值给student对象-->
    <id column="id" property="id"/>
    <result column="name" property="name"/>
    <result column="sex" property="sex"/>
    <!-- 根据student表的card_id去查找studentidcard-->
    <association column="card_id" property="studentIdCard" javaType="studentIdCard"                     select="org.example.dao.book.StudentMapper.findStudentIdCardById"/>
</resultMap>
----------------------------------------------关联查询-------------------------------------------------
<select id="findStudentById" parameterType="Integer" resultMap="StudentIdCardWithStudentResult">
    select * from student stu join studentIdCard card on stu.card_id = card.id where stu.id = #{id}
</select>
-------------------级联属性映射
<resultMap id="StudentIdCardWithStudentResult" type="Student">
    <id column="id" property="id"/>
    <result column="name" property="name"/>
    <result column="sex" property="sex"/>
    <result column="card_id" property="studentIdCard.id"/>
    <result column="code" property="studentIdCard.code"/>
</resultMap>
-------------------association映射
<resultMap id="StudentIdCardWithStudentResult" type="Student">
    <id column="id" property="id"/>
    <result column="name" property="name"/>
    <result column="sex" property="sex"/>
    <!-- student表的StudentIdCard类属性去赋值-->
    <association property="studentIdCard" javaType="studentIdCard">
        <id column="card_id" property="id"/>
        <result column="code" property="code"/>
    </association>
</resultMap>

• 一对多关联: collection

<select id="findStudentById" parameterType="Integer" resultMap="StudentIdCardWithStudentResult">
    select clas.*, stu.id as stu_id, stu.name as stu_name, sex from student stu join classed clas on stu.class_id = clas.id 
        where clas.id = #{id}
</select>

<resultMap id="StudentIdCardWithStudentResult" type="classed">
    <id column="id" property="id"/>
    <result column="name" property="name"/>
    <collection property="studentList" ofType="student">
        <id column="stu_id" property="id"/>
        <result column="stu_name" property="name"/>
        <result column="sex" property="sex"/>
    </collection>
</resultMap>

• 鉴别器: discriminator，类似于java多态

table:
    user{自增id, name, age, sex} 用户表
    malehealth{自增id, hegiht, wegiht, qianliexian, classed表的引用外键user_id} 男性体检表
    malehealth{自增id, hegiht, wegiht, 子宫, classed表的引用外键user_id} 女性体检表
class:
    public class User {
        private Integer id;
        private String name;
        private Integer age;
        private String sex;
        private List<Health> healths;
    }
   public class Health {
        private int id;
        private int height;
        private int weight;
    }
   public class MaleHealth extends Health{
        private String qianliexian;
    }

    public class FemaleHealth extends Health{
        private String zigong;
    }


<resultMap type="user" id="userResultMapWithDiscriminator">
    <id property="id" column="id"/>
    <id property="name" column="name"/>
    <id property="age" column="age"/>
    <id property="sex" column="sex"/>
    <discriminator javaType="string" column="sex">
        <case value="男" resultMap="maleHealthMap"/>
        <case value="女" resultMap="femaleHealthMap"/>
    </discriminator>
</resultMap>

<select id="findUserById" resultMap="userResultMapWithDiscriminator">
    select * from userentity where id =#{id}
</select>

<select id="findMaleHealthByUserId" resultType="maleHealth">
    select * from malehealth where userid = #{userid}
</select>

<select id="findFemaleHealthByUserId" resultType="femaleHealth">
    select * from femalehealth where userid = #{userid}
</select>

<resultMap id="maleHealthMap" extends="userResultMapWithDiscriminator" type="User">
    <collection property="healths" ofType="maleHealth" column="id" select="org.example.dao.book.StudentMapper.findMaleHealthById" />
</resultMap>

<resultMap id="femaleHealthMap" extends="userResultMapWithDiscriminator" type="User">
    <collection property="healths" ofType="femaleHealth" column="id" select="org.example.dao.book.StudentMapper.findFemaleHealthById" />
</resultMap>