mybatis06–动态sql详解编程语言

1.if标签

public interface StudentDao { 
    /** 
     *动态sql的查询   参数是Student对象  不确定  用户输入几个属性值 
     */ 
    List<Student> selectStudentsByIf(Student student); 
}

xml文件中的内容

<?xml version="1.0" encoding="UTF-8"?> 
<!DOCTYPE mapper 
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" 
"http://mybatis.org/dtd/mybatis-3-Mapper.dtd"> 
<mapper namespace="cn.bdqn.dao.StudentDao"> 
 
<!--  需要注意的事项： 
01. 在xml文件中  特殊字符的使用 
 &&必须换成    and或者 &amp;  
 <  &lt; 
 >  &gt;  
 <=  &lt;= 
 >=  &gt;= 
 '   &apos;  
 "   &quot;  
  
 02.因为不确定用户输入的到底是哪个参数 
     所以 where 之后必须加上 1=1   而且 每个条件之前加上  and 
      
  --> 
    <select id="selectStudentsByIf" resultType="Student"> 
     select id,name,age from student  
     where 1=1 
     <if test="name!=null &amp;  name!=''"> 
      and name like '%' #{name} '%' 
      </if> 
     <if test="age>0"> 
      and  age > #{age} 
      </if> 
    </select>  
</mapper>

测试类

public class StudentTest { 
    StudentDao dao; 
    SqlSession session; 
 
    @Before 
    public void before() { 
        // 因为需要关闭session 需要把session提取出去 
        session = SessionUtil.getSession(); 
        dao = session.getMapper(StudentDao.class); 
    } 
 
    @After 
    public void after() { 
        if (session != null) { 
            session.close(); 
        } 
    } 
 
    // 01.动态查询 
    @Test 
    public void test1() { 
         
        Student stu=new Student(); 
        //01.属性都不赋值  会查询所有 
        //02.只给年龄赋值stu.setAge(10); 
        //03.只给姓名赋值stu.setName("小"); 
        //04.同时给两个属性都赋值 
        stu.setAge(10); 
        stu.setName("小"); 
        List<Student> list = dao.selectStudentsByIf(stu); 
        for (Student student : list) { 
            System.out.println(student); 
        } 
    } 
}

2.where标签

上面的代码有点问题，就是在xml文件中的sql语句有where 1=1，如果查询条件多的话，性能是很低的，因为每次查询都需要判断一次！这时候我们就需要使用 where 标签来代替！

public interface StudentDao { 
 
    List<Student> selectStudentsByWhere(Student student); 
}

xml文件的配置省略了 where 1=1

<?xml version="1.0" encoding="UTF-8"?> 
<!DOCTYPE mapper 
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" 
"http://mybatis.org/dtd/mybatis-3-Mapper.dtd"> 
<mapper namespace="cn.bdqn.dao.StudentDao"> 
     
    <select id="selectStudentsByWhere" resultType="Student"> 
     select id,name,age from student  
        <where> 
        <!-- and 必须要加上mybatis只会减 不会加 --> 
             <if test="name!=null &amp;  name!=''"> 
              and name like '%' #{name} '%' 
              </if> 
             <if test="age>0"> 
              and  age > #{age} 
              </if> 
          </where> 
    </select>  
</mapper>

测试类中新增

    // 02.动态查询 where 
    @Test 
    public void test2() { 
        Student stu=new Student(); 
        //01.属性都不赋值  会查询所有 
        //02.只给年龄赋值stu.setAge(10); 
        //03.只给姓名赋值stu.setName("小"); 
        //04.同时给两个属性都赋值 
        stu.setAge(10); 
        stu.setName("小"); 
        List<Student> list = dao.selectStudentsByWhere(stu); 
        for (Student student : list) { 
            System.out.println(student); 
        } 
    }

运行即可得到相同的结果！

2.choose标签

比如说当姓名不为空的时候，按照姓名来查询，年龄不为空的时候按照年龄来查询！如果都为空则返回空！

public interface StudentDao { 
    /** 
     *动态sql的查询   参数是Student对象 
     */ 
     
    List<Student> selectStudentsByChoose(Student student); 
}

xml文件中配置

<?xml version="1.0" encoding="UTF-8"?> 
<!DOCTYPE mapper 
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" 
"http://mybatis.org/dtd/mybatis-3-Mapper.dtd"> 
<mapper namespace="cn.bdqn.dao.StudentDao"> 
    <!--  姓名不空 按照姓名查询    年龄不为空 按照年龄查询  
       只要满足一个when  则其他的when则不会执行！ 
   如果都不满足，则会执行otherwise   也就是没有查询结果 
     --> 
    <select id="selectStudentsByChoose" resultType="Student"> 
     select id,name,age from student  
        <where> 
            <choose> 
               <when test="name!=null  and name!=''"> 
                    and name like '%' #{name} '%' 
               </when> 
               <when test="age>0"> 
                     and  age > #{age} 
               </when> 
               <otherwise> 
                     1!=1 
               </otherwise> 
            </choose> 
          </where> 
    </select>  
</mapper>

测试类代码

// 03.动态查询 choose 
    @Test 
    public void test3() { 
        Student stu=new Student(); 
        stu.setName("小");  //name 不会空  则会按照name来查询  其他的条件无效 
        stu.setAge(10);   
        //如果都没有赋值 则没有返回结果 
        List<Student> list = dao.selectStudentsByChoose(stu); 
        for (Student student : list) { 
            System.out.println(student); 
        } 
    }

4.choose标签遍历数组

public interface StudentDao { 
     
    List<Student> selectStudentsByForeach(int [] ids); 
}

xml文件中的配置

<?xml version="1.0" encoding="UTF-8"?> 
<!DOCTYPE mapper 
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" 
"http://mybatis.org/dtd/mybatis-3-Mapper.dtd"> 
<mapper namespace="cn.bdqn.dao.StudentDao"> 
     
    <select id="selectStudentsByForeach" resultType="Student"> 
     <!--   这就不是动态查询了   而是把参数写成固定的了 
     select id,name,age from student   where id  in(1,13,15) 
      --> 
       select id,name,age from student  
       <if test="array.length>0"><!-- 看传递来的数组长度是否大于0,如果数组长度为0 则是查询所有信息--> 
          where  id  in 
           <foreach collection="array" item="myId" open="(" separator="," close=")"> 
             #{myId} 
           </foreach> 
       </if> 
    </select>  
</mapper>

测试代码

    // 04.动态查询 foreach 遍历数组 
    @Test 
    public void test4() { 
        int [] ids={1,13,15}; 
        List<Student> list = dao.selectStudentsByForeach(ids); 
        for (Student student : list) { 
            System.out.println(student); 
        } 
    }

4.choose标签遍历list集合

public interface StudentDao { 
     
    List<Student> selectStudentsByForeachArray(List<Integer>  ids); 
}

xml文件中的配置

<?xml version="1.0" encoding="UTF-8"?> 
<!DOCTYPE mapper 
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" 
"http://mybatis.org/dtd/mybatis-3-Mapper.dtd"> 
<mapper namespace="cn.bdqn.dao.StudentDao"> 
 
    <select id="selectStudentsByForeachArray" resultType="Student"> 
       select id,name,age from student  
       <if test="list.size>0"><!-- 看传递来的数组长度是否大于0,如果数组长度为0 则是查询所有信息--> 
          where  id  in 
           <foreach collection="list" item="myId" open="(" separator="," close=")"> 
             #{myId} 
           </foreach> 
       </if> 
    </select>  
</mapper>

测试代码

// 05.动态查询 foreach 遍历list集合 
    @Test 
    public void test5() { 
        List<Integer> ids=new ArrayList<Integer>(); 
        ids.add(1); 
        ids.add(13); 
        ids.add(14); 
        List<Student> list = dao.selectStudentsByForeachArray(ids); 
        for (Student student : list) { 
            System.out.println(student); 
        } 
    }

4.choose标签遍历自定义类型集合

public interface StudentDao { 
 
    List<Student> selectStudentsByForeachStudent(List<Student>  stus); 
}

xml文件中的配置

<?xml version="1.0" encoding="UTF-8"?> 
<!DOCTYPE mapper 
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" 
"http://mybatis.org/dtd/mybatis-3-Mapper.dtd"> 
<mapper namespace="cn.bdqn.dao.StudentDao"> 
  
    <!-- 遍历自定义类型的集合 --> 
    <select id="selectStudentsByForeachStudent" resultType="Student"> 
       select id,name,age from student  
       <if test="list.size>0"><!-- 看传递来的数组长度是否大于0,如果数组长度为0 则是查询所有信息--> 
          where  id  in 
           <foreach collection="list" item="stu" open="(" separator="," close=")"> 
             #{stu.id} 
           </foreach> 
       </if> 
    </select>  
</mapper>

测试代码

// 06.动态查询 foreach 遍历自定义集合 
    @Test 
    public void test6() { 
        Student stu1 = new Student(); 
        stu1.setId(1); 
        Student stu2 = new Student(); 
        stu2.setId(13); 
        Student stu3 = new Student(); 
        stu3.setId(15); 
        List<Student> stus=new ArrayList<Student>(); 
        stus.add(stu1); 
        stus.add(stu2); 
        stus.add(stu3); 
        List<Student> list = dao.selectStudentsByForeachStudent(stus); 
        for (Student student : list) { 
            System.out.println(student); 
        } 
    }

5.sql片段

如果一个xml文件中的sql语句有很多相同的地方，则可以使用sql片段来替换！如：

public interface StudentDao { 
 
    List<Student> selectStudentsBySql(List<Student>  stus); 
}

xml文件中的配置

<?xml version="1.0" encoding="UTF-8"?> 
<!DOCTYPE mapper 
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" 
"http://mybatis.org/dtd/mybatis-3-Mapper.dtd"> 
<mapper namespace="cn.bdqn.dao.StudentDao"> 
  
<!-- sql片段的使用  --> 
    <select id="selectStudentsBySql" resultType="Student"> 
       <include refid="selectStudent"/><!-- 引入sql片段  --> 
       <if test="list.size>0"> 
          where  id  in 
           <foreach collection="list" item="stu" open="(" separator="," close=")"> 
             #{stu.id} 
           </foreach> 
       </if> 
    </select>  
     
    <!-- 如果有需求不查询age了，之前需要在所有的查询中删除age字段，现在只需要在sql片段中删除即可！  --> 
    <sql id="selectStudent"> 
       select id,name,age from student  
    </sql> 
</mapper>

测试代码

// 07.sql片段 
    @Test 
    public void test7() { 
        Student stu1 = new Student(); 
        stu1.setId(1); 
        Student stu2 = new Student(); 
        stu2.setId(13); 
        Student stu3 = new Student(); 
        stu3.setId(15); 
        List<Student> stus=new ArrayList<Student>(); 
        stus.add(stu1); 
        stus.add(stu2); 
        stus.add(stu3); 
        List<Student> list = dao.selectStudentsBySql(stus); 
        for (Student student : list) { 
            System.out.println(student); 
        } 
    }

原创文章，作者：Maggie-Hunter，如若转载，请注明出处：https://blog.ytso.com/12147.html

mybatis06–动态sql详解编程语言

相关推荐

发表回复