mybatis 第九节 动态sql

mybatis 第九节 动态sql

• if  where
• choose (when, otherwise)
• trim ( set)
• foreach

1、if where 实现

		/* selecttest */
	Employee emp =dao.selecttest(1, "%dgxnew%66");
      System.out.println(emp);

或者 用where 去掉and

 <select id="selecttest" resultMap="zidiyiMap">  
 select * from employee  
<where>
 <if test="id !=null and id !=0 ">
id=#{id} 
 </if>
 <if test="lastName !=null">
last_Name like #{lastName} 
 </if>
</where>  
  </select>

2、choose whens 实现

 <select id="selecttest" resultMap="zidiyiMap">  
    select * from employee where 1=1  
    <choose>
    <when test="id !=null and id !=0">
      and id=#{id} 
    
    </when>
    <when test="lastName !=null">
    and last_Name like #{lastName} 
 </when>        
    </choose>   
   
     </select> 

二、set用法

		/* update set 标签去除后面的, */
//		 Employee emp =new Employee();
//		 emp.setId(6); 
//		 emp.setLastName(null);
//		 emp.setEmail("adddf@126.com");
//		 
//		 dao.updateEmp(emp); 
//		 session.commit();
//		 session.close();

 

  <select id="updateEmp" resultMap="zidiyiMap" >
    UPDATE EMPLOYEE 
    <set>
    <if test="lastName !=null and id !=0"> 
    last_name =#{lastName},      
    </if> 
    <if test ="email !=null">
    email = #{email}
    
    </if>
    </set>
    WHERE ID=#{id}
  </select>

三、foreach用法

	public List<Employee> selectempsById(List<Integer> list);

		List<Integer> list =new ArrayList<Integer>();
		list.add(1);
		list.add(2);
		list.add(6);
		List<Employee> lists =dao.selectempsById(list);		
		System.out.print(lists);
		

  <select id="selectempsById" resultMap="zidiyiMap">
  SELECT * FROM employee WHERE ID IN 
  <foreach item="item" index="index" collection="list"
      open="(" separator="," close=")">
        #{item}
  </foreach>