Mybatis（二）-动态SQL

将复杂的SQL语句拼接简单化，常用的动态SQL元素：if、choose(when、otherwise)、where、set、foreach、bind

---

xml:

if:

<select id="findUserById" resultType="user">
           select * from user where 
           <if test="id != null">
               id=#{id}
           </if>
            and deleteFlag=0;
</select>

where:

where元素知道只有一个以上的if条件有值的情况下才去插入WHERE子句，若最后的内容是“and”“or”开头，where可自己将他们去除；

<select id="findUserById" resultType="user">
           select * from user 
           <where>
               <if test="id != null">
                   id=#{id}
               </if>
               and deleteFlag=0;
           </where>
 </select>

choose:

类比switch;

<select id="selectEmployeeChoose" 
  	parameterType="hashmap" 
  	resultType="org.fkit.domain.Employee">
  	SELECT * FROM tb_employee WHERE state = 'ACTIVE'
  	<!-- 如果传入了id，就根据id查询，没有传入id就根据loginname和password查询，否则查询sex等于男的数据 -->
  	<choose>
  		<when test="id != null">
  			and id = #{id}
  		</when>
  		<when test="loginname != null and password != null">
  			and loginname = #{loginname} and password = #{password}
  		</when>
  		<otherwise>
  			and sex = '男'
  		</otherwise>
  	</choose>
  </select>

set:

消除逗号；

<update id="updateEmployeeIfNecessary" 
  	parameterType="org.fkit.domain.Employee">
	  update tb_employee
	    <set>
	      <if test="loginname != null">loginname=#{loginname},</if>
	      <if test="password != null">password=#{password},</if>
	      <if test="name != null">name=#{name},</if>
	      <if test="sex != null">sex=#{sex},</if>
	      <if test="age != null">age=#{age},</if>
	      <if test="phone != null">phone=#{phone},</if>
	      <if test="sal != null">sal=#{sal},</if>
	      <if test="state != null">state=#{state}</if>
	    </set>
	  where id=#{id}
	</update>

foreach:

对一个集合进行遍历；

<select id="selectEmployeeIn" resultType="org.fkit.domain.Employee">
	  SELECT *
	  FROM tb_employee
	  WHERE ID in
	  <foreach item="item" index="index" collection="list"
	      open="(" separator="," close=")">
	        #{item}
	  </foreach>
  </select>

bind:

从OGNL表达式中创建一个变量并将其绑定到上下文；

<!-- 是一个like的模糊查询-->
<select id="selectEmployeeLikeName"  resultType="org.fkit.domain.Employee">
	  <bind name="pattern" value="'%' + _parameter.getName() + '%'" />
	  	SELECT * FROM tb_employee
	  	WHERE loginname LIKE #{pattern}
	</select>

---

注解：

MyBatis提供了：@SelectProvider @InsertProvider @UpdateProvider @DeleteProvider来帮助构建动态SQL语句；

@SelectProvider实例：

UserMapper接口：

public interface UserMapper {
    
    @Results({
            @Result(id = true,property = "id",column = "id"),
            @Result(property ="username",column = "username"),
            @Result(property ="loginname",column = "loginname"),
            @Result(property ="pword",column = "pword"),
            @Result(property ="phone",column = "phone"),
            @Result(property ="address",column = "address"),
    })
    @SelectProvider(type = UserDy.class,method = "queryUser")
    User selectUserByIdDy(int id);
}

动态SQL提供类：

public class UserDy {
    public String queryUser(Map<String,Integer> map){
        return  new SQL(){
            {
                SELECT("*");
                FROM("tb_user");
                if(map.get("id")!=null){
                    WHERE("id=#{id}");
                }
            }
        }.toString();
    }
}

测试：

@Test
    public void query1(){//动态sql
        SqlSession sqlSession=sqlSessionFactory.openSession();
        try{
            Map<String,Object> map=new HashMap<>();
            map.put("id",1);
            User user=sqlSession.selectOne("selectUserByIdDy",map);
            System.out.println(user.getUsername());
            sqlSession.commit();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            sqlSession.close();
        }
    }