06.动态SQL和foreach

动态sql:

映射文件代码:

 <!-- 动态sql,根据名字和年龄查询,where标签会处理第一个and，其他位置的and不会自动处理 -->
  <select id="queryStudentByNameAndAge" parameterType="student" resultMap="student1">
    select * from student 
    <!-- 1.使用where 1=1 ，避免where后直接跟and -->
     <!-- 2.使用where关键字，避免where后直接跟and -->
    <where>
        <if test="sname != null and sname != '' ">
            and sname = #{sname}
        </if>
        <if test="age != null and age != '' ">
            and age = #{age}
        </if>
    </where>
  </select>

foreach循环属性集合:

映射文件:

  <!-- foreach循环属性集合-->
  <select id="queryStudentsByHomeAddress" parameterType="Home" resultMap="student1">
    select * from student 
    <where>
    <if test="homeAddress != null and homeAddress != '' ">
    <!-- colleaction是属性中的集合，item是遍历出来的每个元素，open是循环结果前边的东西，close是循环结果后边要加的东西，separator是循环结果用什么符号分隔 -->
        <foreach collection="homeAddress" item="homeAr" open=" and homeaddress in (" close=" )" separator=",">
            #{homeAr}            
        </foreach>
    </if>    
    </where>
  </select>

测试类:

 Home home = new Home();
 List<String> homeAddress = new ArrayList<String>();
 homeAddress.add("北京");
 homeAddress.add("南京");
 home.setHomeAddress(homeAddress);
 List<Student> stu = studentMapper.queryStudentsByHomeAddress(home);

foreach循环集合:

映射文件:（需要注意的是循环要循环list）

 <!-- foreach循环集合-->
  <select id="queryStudentsWithList" parameterType="list" resultMap="student1">
    select * from student 
    <where>
    <!-- 必须写list -->
    <if test="list != null and list != '' ">
        <foreach collection="list" item="homeAr" open=" and homeaddress in (" close=" )" separator=",">
            #{homeAr}            
        </foreach>
    </if>    
    </where>
  </select>

测试类:

List<String> list = new ArrayList<String>();
list.add("北京");
list.add("南京");
List<Student> stu = studentMapper.queryStudentsWithList(list);

foreach循环数组:

映射文件:(注意循环必须都写array，输入参数不是简单类型时都写Object[])

 <!-- foreach循环数组-->
  <select id="queryStudentsWithArray" parameterType="Object[]" resultMap="student1">
    select * from student 
    <where>
    <!-- 必须写array -->
    <if test="array != null and array != '' ">
        <foreach collection="array" item="homeAr" open=" and homeaddress in (" close=" )" separator=",">
            #{homeAr}            
        </foreach>
    </if>    
    </where>
  </select>

测试类:

String[] str = {"北京","南京"};
List<Student> stu = studentMapper.queryStudentsWithArray(str);

foreach循环对象数组:

映射文件:(需要注意的是这时的item要写循环出来的对象了，不能瞎写名字了,然后通过对象点的方式获取值)

<!-- foreach循环对象数组-->
  <select id="queryStudentsWithObjectArray" parameterType="Object[]" resultMap="student1">
    select * from student 
    <where>
    <!-- 必须写array -->
    <if test="array != null and array != '' ">
     <!-- item里边写的是循环出来的对象 -->
        <foreach collection="array" item="address" open=" and homeaddress in (" close=" )" separator=",">
            <!-- 对象点属性获取值 -->
            #{address.homeAddress}            
        </foreach>
    </if>    
    </where>
  </select>

测试类:

Address addr1 = new Address();
addr1.setHomeAddress("北京");
Address addr2 = new Address();
addr2.setHomeAddress("南京");
Address[] address = {addr1,addr2};
List<Student> s = studentMapper.queryStudentsWithObjectArray(address);