Java --- Mybatis的动态sql标签

一、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中

@Test
    public 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>