一、if标签

<select id="queryEmpByCondition" resultType="User">select * from t_user where 1=1<if test="username != null and username != ''">and username = #{username}</if></select>

if：根据标签中的test属性内容条件决定是否拼接到sql中

@Testpublic void test3(){SqlSession sqlSession = SqlSessionUtils.sqlSession();DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);User admin = new User(null, "admin", null, null, null, null);List<User> users = mapper.queryEmpByCondition(admin);System.out.println(users);}

 二、where标签

条件内容前的and或or

select id="queryEmpByCondition" resultType="User">select * from t_user<where><if test="username != null and username != ''">and username = #{username}</if></where></select>

 条件内容后的and或or

<select id="queryEmpByCondition" resultType="User">select * from t_user<where><if test="username != null and username != ''">username = #{username} and</if></where></select>

where：当where标签中有内容会生成where关键字，没有内容将不会产生任何效果，并且会将条件内容前的and或or去掉，但条件内容后的依然会生效 

三、trim标签

<select id="queryEmpByCondition" resultType="User">select * from t_user<trim prefix="where"  suffixOverrides="and|or" ><if test="username != null and username != ''">username = #{username} and</if></trim></select>

trim：

标签中有内容

prefix="" | suffix="" ：将trim标签中的内容前面或后面添加指定内容

prefixOverrides="" | suffixOverrides="" ：将trim标签中内容前面或后面去掉指定内容

标签中没有内容则trim标签不生效 

四、choose、when、otherwise标签 

<select id="queryEmpByCondition" resultType="User">select * from t_user<trim prefix="where"  suffixOverrides="and|or" ><choose><when test="username != null and username != ''">username = #{username}</when><when test="password != null and password != ''">password = #{password}</when><otherwise>id = 1</otherwise></choose></trim></select>

 相当于if...else if...else

五、foreach标签 

批量删除：

形式一

<mapper namespace="com.cjc.ssm.mapper.DynamicSQLMapper"><delete id="removeByUserArray">delete from t_user where id in<foreach collection="arr" item="id" separator="," open="(" close=")">#{id}</foreach></delete>

 形式二

<delete id="removeByUserArray">delete from t_user where<foreach collection="arr" item="id" separator="or">id = #{id}</foreach></delete>

批量添加

 <insert id="addUserList">insert into t_user values<foreach collection="list" item="user" separator=",">(null,#{user.username},#{user.password},#{user.age},#{user.sex},#{user.email})</foreach></insert>

 foreach:

collection:设置需要循环的数组或集合

item：数组或集合中的每一个数据

separator:循环体之间的分割符

open：所有循环内容的开始符

close:所有循环内容的结束符

六、sql标签