MyBatis笔记

Java类的封装

利用lombok库函数，通过注解的方式简化类的形式

@Data  //自动生成getter, setter, toString等方法
@NoArgsConstructor  //自动生成无参构造函数
@AllArgsConstructor  //自动生成全参构造函数
public class User {
    private Integer id;
    private String name;
    private Short age;
    private Short gender;
    private String phone;
}

接口方法

查询

添加注解：@Select(String sql)

// 定义接口类：mapper/UserMapper.java
@Mapper  //添加@Mapper注解
public interface UserMapper {
    @Select("select * from user")
    public List<User> list();
}

@Mapper
public interface UserMapper {
    @Select("select * from emp where name like concat('%', #{name}, '%') and " +
            "gender = #{gender} and entrydate between #{begin} and #{end} order by update_time desc")
    public List<Emp> list(@Param("name")String name,
                          @Param("gender")Short gender,
                          @Param("begin")LocalDate begin,
                          @Param("end")LocalDate end);

}

删除

添加注解：@Delete(String sql)

@Mapper
public interface EmpMapper {
    @Delete("delete from emp where id = #{id}")
    public void delete(Integer id);
}

插入

添加注解：@Insert(String sql)

@Mapper
public interface EmpMapper {
    @Insert("insert into emp(username, name, gender, image, job, entrydate, dept_id, create_time, update_time)\n" +
            "values (#{username}, #{name}, #{gender}, #{image}, #{job}, #{entrydate}, #{deptId}, #{createTime}, #{updateTime})")
    public void insert(Emp emp);
}

@Mapper
public interface EmpMapper {
    //添加@Options注解，指定插入数据的主键输出为类中的指定属性
    @Options(useGeneratedKeys = true, keyProperty = "id")
    @Insert("insert into emp(username, name, gender, image, job, entrydate, dept_id, create_time, update_time)\n" +
            "values (#{username}, #{name}, #{gender}, #{image}, #{job}, #{entrydate}, #{deptId}, #{createTime}, #{updateTime})")
    public void insert(Emp emp);
}

XML映射文件

使用xml配置文件来编写SQL语句

1. 需要在resources文件夹中创建跟原始XxxMapper文件相同的目录结构，如：resources/com/maximeshe/mapper/EmpMapper.xml

2. 代码框架如下：

   <?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为完整的包名 -->
   <mapper namespace="com.maximeshe.mapper.EmpMapper">
       
   </mapper>
   

动态SQL

where, if

<select id="list" resultType="com.maximeshe.pojo.Emp">
    select *
    from emp
    <!-- where标签，自动管理是否添加where关键字 -->
    <where>
        <!-- if标签 -->
        <if test="name != null">
            name like concat('%', #{name}, '%')
        </if>
        <if test="gender != null">
            and gender = #{gender}
        </if>
        <if test="begin != null and end != null">
            and entrydate between #{begin} and #{end}
        </if>
    </where>

    order by update_time desc
</select>

update, set

<update id="update2">
    update emp
    <set>
        <if test="username != null">
            username = #{username},
        </if>
        <if test="name != null">
            name = #{name},
        </if>
        <if test="gender != null">
            gender = #{gender},
        </if>
        <if test="image != null">
            image = #{image},
        </if>
        <if test="job != null">
            job = #{job},
        </if>
        <if test="entrydate != null">
            entrydate = #{entrydate},
        </if>
        <if test="deptId != null">
            dept_id = #{deptId},
        </if>
        <if test="updateTime != null">
            update_time = #{updateTime}
        </if>
    </set>
    where id = #{id}
</update>

foreach

<delete id="deleteByIds">
    delete from emp where id in
        /*foreach标签*/
    <foreach collection="ids" item="id" separator="," open="(" close=")">
        #{id}
    </foreach>
</delete>