mybatis常用使用

1.xml模板

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.gen.mapper.StudentMapper">
</mapper>

2.普通增删改查

• mapper文件

int insert(Student student);

int delete(Integer id);

int update(Student student);

Student selectById(Integer id);

List<Student> selectAll();

• xml文件

<insert id="insert">
    INSERT INTO `student` (`name`, `sex`, `create_time`)
    VALUES (#{name}, #{sex}, #{createTime})
</insert>

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

<update id="update">
    UPDATE `student` SET `name` = #{name}, `sex` = #{sex}, `create_time` = #{createTime} WHERE `id` = #{id};
</update>

<select id="selectById" resultType="com.gen.entity.Student">
    select * from student where id = #{id}
</select>

<select id="selectAll" resultType="com.gen.entity.Student">
    select * from student
</select>

3.模糊查询

• mapper文件

List<Student> selectLikeName(String name);

• xml文件

<select id="selectLikeName" resultType="com.gen.entity.Student">
    select * from student where name like concat('%', #{name}, '%')
</select>

4.驼峰字段Java对象映射

• mapper配置文件

<settings>
     <setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>

5.获取自增主键

• mapper文件

int insert(Student student);

• xml文件

<insert id="insert" useGeneratedKeys="true" keyColumn="id" keyProperty="id">
    INSERT INTO `student` (`name`, `sex`, `create_time`)
    VALUES (#{name}, #{sex}, #{createTime})
</insert>

6.批量插入

• foreach：用于循环拼接的内置标签，常用于批量新增、in查询等

  • collection：必填，值为要迭代循环的集合类型，情况有多种
    • 入参是List类型的时候，collection属性值为list
    • 入参是Map类型的时候，collection属性值为map的key值
  • item：每一个元素进行迭代时的别名
  • index：索引的属性名，在集合数组情况下值为当前索引值，当迭代对象是map时，这个值是map的key
  • open：整个循环内容的开头字符串
  • close：整个循环内容的结尾字符串
  • separator：每次循环的分隔符

• mapper文件

int insertBatch(List<Student> list);

• xml文件

<insert id="insertBatch">
    INSERT INTO `student` (`name`, `sex`, `create_time`) VALUES
    <foreach collection="list" item="item" separator=",">
        (#{item.name}, #{item.sex}, #{item.createTime})
    </foreach>
</insert>

7.部分更新非空字段

• mapper文件

int update(Student student);

• xml文件 （注意：数值类型不要加上!= ‘’，否则无法更新0值情况）

<update id="update">
    UPDATE `student`
    <trim prefix="set" suffixOverrides=",">
        <if test="name != null and name != ''">
            `name` = #{name},
        </if>
        <if test="sex != null and sex != ''">
            `sex` = #{sex},
        </if>
        <if test="createTime != null">
            `create_time` = #{createTime},
        </if>
        <if test="age != null">
            `age` = #{age},
        </if>
    </trim>
    WHERE `id` = #{id}
</update>

8.转义字符

• mapper文件

int deleteByCreateTime(@Param("startDate") LocalDateTime startDate, @Param("endDate") LocalDateTime endDate);

• xml文件（两种方式）

<delete id="deleteByCreateTime">
    <![CDATA[
        delete from student where create_time >= #{startDate} and create_time <= #{endDate}
        ]]>
</delete>

<delete id="deleteByCreateTime">
    delete from student where create_time >= #{startDate} and create_time &lt;= #{endDate}
</delete>

9.sql片段

• mapper文件

List<Student> selectAll();

• xml文件

<sql id="Base_Column">id, name</sql>
<select id="selectAll" resultType="com.gen.entity.Student">
    select
    <include refid="Base_Column"/>
    from student
</select>

10.一对一映射association

• mapper文件

List<Student> selectAll();

• xml文件

<resultMap id="BaseResultMap" type="com.gen.entity.Student">
    <id column="id" property="id"/>
    <result column="name" property="name"/>
    <result column="sex" property="sex"/>
    <result column="create_time" property="createTime"/>
    <result column="clazz_id" property="clazzId"/>
    <association column="clazz_id" property="clazz" select="com.gen.mapper.ClazzMapper.selectById"/>
</resultMap>
<select id="selectAll" resultMap="BaseResultMap">
    select * from student
</select>

11.一对多映射collection

• mapper文件

Clazz selectById(Integer id);

• xml文件

<resultMap id="BaseResultMap" type="com.gen.entity.Clazz">
    <id column="id" property="id"/>
    <result column="name" property="name"/>
    <collection column="id" property="students" select="com.gen.mapper.StudentMapper.selectByClazzId"/>
</resultMap>
<select id="selectById" resultMap="BaseResultMap">
    select * from clazz where id = #{id}
</select>

12.懒加载

• 什么是懒加载：按需加载，先从单表查询，需要时再从关联表去关联查询，能大大提高数据库性能，并不是所有场景下使用懒加载都能提高效率

• mybatis懒加载：resultMap 里面的association、collection有延迟加载功能

• mapper配置文件

<settings>
    <!--延迟加载总开关-->
    <setting name="lazyLoadingEnabled" value="true"/>
    <!--将aggressiveLazyLoading设置为false表示按需加载，默认为true-->
    <setting name="aggressiveLazyLoading" value="false"/>
</settings>

• mapper文件

List<Student> selectAll();

• xml文件

<resultMap id="BaseResultMap" type="com.gen.entity.Student">
    <id column="id" property="id"/>
    <result column="name" property="name"/>
    <result column="sex" property="sex"/>
    <result column="create_time" property="createTime"/>
    <result column="clazz_id" property="clazzId"/>
    <association column="clazz_id" property="clazz" select="com.gen.mapper.ClazzMapper.selectById"/>
</resultMap>
<select id="selectAll" resultMap="BaseResultMap">
    select * from student
</select>