1.xml模板
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapperPUBLIC "-//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：每次循环的分隔符
- collection：必填，值为要迭代循环的集合类型，情况有多种
-
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 <= #{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>
