MyBatis动态SQL

where标签

where用于解决SQL语句中where关键字以及条件前面的and或者or的问题

trim标签

trim 可以在条件判断完的SQL语句前后添加或者去掉指定的字符
prefix: 添加前缀
prefixOverrides: 去掉前缀
suffix: 添加后缀
suffixOverrides: 去掉后缀

<select id="getEmployeeByConditionIf" resultType="employee">
        select id,last_name,email,salary,dept_id
        from employees
        <trim prefix="where" suffixOverrides="and">
            <if test="id != null">
            id = #{id} and</if>
            <if test="lastName != null">
             last_name = #{lastName} and</if>
            <if test="email != null ">
             email = #{email} and </if>
            <if test="salary != null">
            salary = #{salary} and</if>
            <if test="deptId != null">
             dept_id = #{deptId}</if>
        </trim>
    </select>

choose标签

choose 主要是用于分支判断，类似于java中的switch case,只会满足所有分支中的一个

<select id="getEmployeeByConditionChoose" resultType="employee">
    select id,last_name,email,salary,dept_id
        from employees
    <where>
        <choose>
            <when test="id != null">
                id = #{id}
            </when>
            <when test="lastName != null">
                last_name = #{lastName}
            </when>
            <when test="email != null">
                email = #{email}
            </when>
            <when test="salary != null">
                salary = #{salary}
            </when>
            <otherwise>
                dept_id = #{deptId}
            </otherwise>
        </choose>
    </where>
</select>

  @Test
    public void testEmployeeByConditionChoose() throws IOException {
        //获取SqlSessionFactory
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
        //获取sqlSession
        SqlSession sqlSession = sqlSessionFactory.openSession();
        try {
            EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
            Employee employee = new Employee(null,"张山风","2525@11",2500.0,2);
            Employee employeeByConditionChoose = mapper.getEmployeeByConditionChoose(employee);
            System.out.println(employeeByConditionChoose);
        }finally {
            sqlSession.close();
        }
        }


//sql    select id,last_name,email,salary,dept_id from employees WHERE last_name = ? 

set标签

主要是用于解决修改操作中SQL语句中可能多出逗号的问题

<update id="UpdateEmployeeByConditionSet">
        update employees
        <set>
            <if test="lastName != null">
                last_name = #{lastName} ,</if>
            <if test="email != null ">
                email = #{email} ,</if>
            <if test="salary != null">
                salary = #{salary} ,</if>
            <if test="deptId != null">
                dept_id = #{deptId},</if>
        </set>
        where id = #{id}
    </update>

set标签

foreach 主要用于循环迭代
collection: 设置遍历的集合
item: 设置填充占位符的key
open: 开始字符
close:结束字符
separator: 元素与元素之间的分隔符
index:
迭代的是List集合: index表示的当前元素的下标
迭代的Map集合: index表示的当前元素的key

 <select id="getEmployeesByConditionForeach" resultType="employee">
        select id,last_name,email,salary,dept_id
    from employees
    where id in
    <foreach collection="ids" open="(" close=")" separator="," item="id">
        #{id}
    </foreach>
    </select>

sql标签

sql 标签是用于抽取可重用的sql片段，将相同的，使用频繁的SQL片段抽取出来，单独定义，方便多次引用.

1)抽取SQL:

<sql id="selectSql">
    select id,last_name,email,salary,dept_id
    from employees
</sql>

2)引用SQL:

<include refid="selectSql"></include>