mybatis3学习笔记(五)之高级查询

1.修改StudentMapper.xml文件,增加动态sql语句映射
<!-- 动态sql -->

 <select id="dymSelectStudent"  resultMap="studentResultMap">
  select * from student
  <where>
   <!-- 模糊查询 -->
   <!-- mysql -->
   <if test="name != null">
    and stu_name like   concat('%',#{name},'%') 
   </if>
   <!-- sqlserver 
   <if test="name != null">
    and stu_name like   '%'+#{name}+'%'
   </if>
   -->
   <!-- oracle
   <if test="name != null">
    and stu_name like   '%'||#{name}||'%'
   </if>
    -->
   <choose>
    <when test="code=='001'">
     and stu_code = '001'
    </when>
    <when test="code=='002'">
     and stu_code = '002'
    </when>
    <otherwise>
     and stu_code = #{code}
    </otherwise>
   </choose>
   
   <if test="list!=null">
   and id in 
   <foreach collection="list" index="index" item="item" open="(" separator="," close=")">
    #{item}
   </foreach>
   </if>
    order by ${orderName}
  </where>
 </select>
2.增加测试用例
@Test
 public void dymSelectStudentTest(){
  SqlSession session = sessionFactory.openSession();
  try{
   HashMap<String,Object> map = new HashMap<String, Object>();
   List<Long> ids = new ArrayList<Long>();
   ids.add(9L);
   ids.add(10L);
   map.put("code", "005");
   map.put("name", "hel");
   map.put("list", ids);
   map.put("orderName", "id");
   List<Student> studentList =  (List<Student>)session.selectList("com.mybatis.mapper.StudentMapper.dymSelectStudent", map);
   for(Student s : studentList)
    System.out.println("name:"+s.getName());
  }catch(Exception e){
   e.printStackTrace();
  }finally{
   session.close();
  }
 }