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

最新推荐文章于 2025-05-12 18:57:57 发布

原创最新推荐文章于 2025-05-12 18:57:57 发布 · 118 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#Oracle #MySQL #SQL #XML

IBatis 专栏收录该内容

8 篇文章

订阅专栏

本文介绍如何在MyBatis中使用动态SQL进行模糊查询和条件筛选，并提供了一个具体的测试案例。

1.修改StudentMapper.xml文件,增加动态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();
  }
 }