MyBatis标签与字符
标签
<where>标签
- 在使用<if>标签的时候,若SQL中没有where条件,优先考虑在<if>标签外使用<where>标签,不使用where 1=1。
<foreach>标签
以学生表为例:
- 若传入的参数为id数组
update student_table
set grade = '3'
where id in
<foreach item="id" collection="ids" open="(" separator="," close=")">
#{id}
</foreach>
- 若传入的参数为学生的对象数组
update student_table
set grade = '3'
where id in
<foreach item="student" collection="students" open="(" separator="," close=")">
#{student.id}
</foreach>
- 若传入的参数为以逗号分隔的id字符串,则将其转换为数组
update student_table
set grade = '3'
where id in
<foreach item="id" collection="ids.split(',')" open="(" separator="," close=")">
#{id}
</foreach>
- 若传入的参数是对象List,需要查出满足这个List的所有数据
设传入的参数名为itemList,那么写法如下:
itemList:
[{grade: '三年级', age: 3}, {grade: '二年级', age: 2}]
写法1:
select *
from student_table s
<where>
<if test="conditionList != null">
and
<foreach item="item" collection="itemList" open="(" separator="or" close=")">
(s.grade = #{item.grade} and s.age = #{item.age})
</foreach>
</if>
</where>
写法2:
select *
from student_table s
<where>
<if test="conditionList != null">
and (s.grade, s.age) in
<foreach item="item" collection="itemList" open="(" separator="," close=")">
(#{item.grade}, #{item.age})
</foreach>
</if>
</where>
<sql>标签
可通过<include>标签进行引用,如:
<sql id="param_list">
id, name, grade
</sql>
<select id="" resultType="">
select
<include refid="param_list"></include>
from student_table
</select>
<set>标签
类似<where>标签,可用于update语句
update student_table
<set>
<if test="grade != null and grade != ''">
grade = #{grade}
</if>
<if test="age != null and age != ''">
age = #{age}
</if>
</set>
where id = #{id}
<choose>标签
配合<when>、<otherwise>标签使用
<select id="" resultType="">
select id, name
from student_table
<choose>
<when test="grade != null and grade != ''">
grade = #{grade}
</when>
<when test="age != null and age != ''">
age = #{age}
</when>
<otherwise>
del = 0
</otherwise>
</choose>
</select>
字符替换
第一类写法
字符 | 替换字符 | 含义 |
---|---|---|
< | < | 小于 |
> | > | 大于 |
<= | <= | 小于等于 |
>= | >= | 大于等于 |
第二类写法
使用<![CDATA[ ]]>,例:
select id, name
from student_table
where grade <![CDATA[ >= ]]> '3'
输出映射
resultType
基本映射,需要查询字段,数据库字段,实体字段均保持对应,才能映射成功从而查询出来。
retultMap
高级映射,可以对数据库字段与实体字段进行映射关系,
<resultMap type="cn.eagle.entity.studentEntity" id="student">
<result property="id" column="id"/>
<result property="name" column="student_name"/>
</resultMap>
<select id="select" resultType="student">
select id, student_name
from student
</select>