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);
        }
    }