一、if标签的使用
使用if标签进行判断操作事,后面使用test进行参数判断,示例:
<if test="empno != null">
and empno =#{empno}
</if>
如果有多个判断对象时,使用and进行连接,示例:
<if test="job != null and job != ''">
and job =#{job}
</if>
二、where标签的使用
用于处理where关键字和and,示例:
<select id="findEmpByCondition" resultType="emp">
select * from emp
<where>
<if test="empno != null">
and empno= #{empno}
</if>
<if test="ename != null and ename != ''">
and ename= #{ename}
</if>
</where>
</select>
三、choose标签的使用
前面的when条件成立 后面的 when就不再判断了,(一个when的条件成立后,后面的when不会再执行了)示例:
<select id="findEmpByCondition2" resultType="emp">
select * from emp
<where>
<choose>
<when test="empno != null">
and empno= #{empno}
</when>
<when test="ename != null and ename != ''">
and ename= #{ename}
</when>
<when test="job != null and job != ''">
and job= #{job}
</when>
<when test="mgr != null ">
and mgr= #{mgr}
</when>
</choose>
</where>
</select>
最终Sql结果出来是 select * from emp and empno= #{empno}
四、set标签的使用
通过传入的对象中的参数进行动态判断修改数据,使用if进行参数判断,是null则不修改。示例:
<!--int updateEmpByCondtion(Emp emp);-->
<update id="updateEmpByCondtion" >
update emp
<set>
<if test="ename != null and ename != '' ">
, ename =#{ename}
</if>
<if test="job != null and ename != '' ">
, job =#{job}
</if>
<if test="mgr != null ">
, mgr =#{mgr}
</if>
</set>
where empno =#{empno}
</update>
五、trim标签的使用
在trim标签中有以下几种函数:
prefix 要增加什么前缀
prefixOverrides 要去除什么前缀
suffix 要增加什么后缀
suffixOverrides 要去除什么后缀
【prefix=“set”是trim的一种特殊情况】
trim标签处理set,示例:
<update id="updateEmpByCondition2" >
update emp
<trim prefix="set" suffixOverrides="," >
<if test="ename != null and ename != ''">
ename= #{ename},
</if>
<if test="job != null and job != ''">
job= #{job},
</if>
<if test="mgr != null ">
mgr= #{mgr},
</if>
</trim>
where empno = #{empno}
</update>
trim标签处理where,示例:
<select id="findEmpByCondition" resultType="emp">
select * from emp
<trim prefix="where" prefixOverrides="and">
<if test="empno != null">
and empno= #{empno}
</if>
<if test="ename != null and ename != ''">
and ename= #{ename}
</if>
<if test="job != null and job != ''">
and job= #{job}
</if>
<if test="deptno != null ">
and deptno= #{deptno}
</if>
</trim>
</select>
六、bind标签的使用
一般用于处理模糊查询的模板,示例:
<select id="findEmpByEname" resultType="emp" parameterType="emp">
<bind name="linkPattern" value="'%'+ename+'%'"/>
select * from emp where ename like #{linkPattern}
</select>
七、Sql片段的使用
使用标签进行sql语句片段的编辑,使用 进行片段的使用,示例:
<sql id="empColumn">empno,ename,job,mgr,hiredate,sal,comm,deptno </sql>
<sql id="beaseSelect">select <include refid="empColumn"></include> from emp</sql>
<select id="findByCondition" resultType="emp">
<include refid="beaseSelect"></include> where
<if test="empno != null">
and empno =#{empno}
</if>
<if test="ename != null and ename != ''">
and ename like concat('%',#{ename},'%')
</if>
<if test="job != null and job != ''">
and job =#{job}
</if>
<if test="mgr != null">
and mgr =#{mgr}
</if>
<if test="hiredate != null">
and hiredate =#{hiredate}
</if>
<if test="sal != null">
and sal =#{sal}
</if>
<if test="comm != null">
and comm =#{comm}
</if>
<if test="deptno != null">
and deptno =#{deptno}
</if>
</select>
八、foreach标签的使用
使用foreach标签时,参考以下的属性含义
- collection="" 遍历的集合或者是数组
- 参数是数组,collection中名字指定为array
- 参数是List集合,collection中名字指定为list
- separator="" 多个元素取出的时候 用什么文字分隔
- open="" 以什么开头
- close="" 以什么结尾
- item="" 中间变量名
示例:
EmpMapper2.xml
<select id="findByEmpnos1" resultType="emp">
select * from emp where empno in
<foreach collection="array" separator="," open="(" close=")" item="deptno">
#{deptno}
</foreach>
</select>
<!-- List<Emp> findByEmpnos2(List<Integer> empnos);-->
<select id="findByEmpnos2" resultType="emp">
select * from emp where empno in
<foreach collection="list" separator="," open="(" close=")" item="deptno">
#{deptno}
</foreach>
</select>
测试代码
@Test
public void testSelectEmpno(){
EmpMapper2 mapper = sqlSession.getMapper(EmpMapper2.class);
/*List<Emp> a = mapper.findByEmpnos1(new int[]{7521,7839,7499});*/
List<Integer> list = new ArrayList<>();
Collections.addAll(list,7521,7839,7499);
List<Emp> byEmpnos2 = mapper.findByEmpnos2(list);
byEmpnos2.forEach(System.out::println);
}
结果:
DEBUG - Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@5340477f]
DEBUG - ==> Preparing: select * from emp where empno in ( ? , ? , ? )
DEBUG - ==> Parameters: 7521(Integer), 7839(Integer), 7499(Integer)
DEBUG - <== Total: 3
Emp(empno=7499, ename=ALLEN, job=SALESMAN, mgr=7698, hiredate=Fri Feb 20 00:00:00 CST 1981, sal=1600.0, comm=300.0, deptno=30)
Emp(empno=7521, ename=WARD, job=SALESMAN, mgr=7698, hiredate=Sun Feb 22 00:00:00 CST 1981, sal=1250.0, comm=500.0, deptno=30)
Emp(empno=7839, ename=KING, job=PRESIDENT, mgr=null, hiredate=Tue Nov 17 00:00:00 CST 1981, sal=5000.0, comm=null, deptno=10)
DEBUG - Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@5340477f]