MyBatis 动态SQL

动态 SQL，主要用于 解决 查询条件不确定的情况。
例如：
select id,name,age,score from student where id=？ name=？ age=？ score=？
每一个条件都 可有可无，那where条件有4*3*2*1=24这么多情况，总不能写24个sql。
可使用动态SQL来解决这一的问题。

动态SQL，即通过MyBatis提供的各种标签对条件作出判断以实现动态拼接SQL语句。这里条件判断使用的表达式为OGNL表达式（Object Graphic Navigation Language 对象图导航语言）

1、if

	<select id="selectByIf" resultType="Student">
	 	select id,name,age,score 
	 	from student 
	 	where 1 = 1
	 	<if test="name != null and name != ''">
	 		and name like '%' #{name} '%'
	 	</if>
	 	<if test="age > 0">
	 		and age > #{age}
	 	</if>
	</select>

if标签，where条件要加 1=1.对于数据量大的查询而言，1=1也是判断条件，还是会影响查询效率。

2、where

	<select id="selectByWhere" resultType="Student">
	 	select id,name,age,score 
	 	from student 
	 	<where>
	 		<if test="name != null and name != ''">
	 			and name like '%' #{name} '%'
	 		</if>
	 		<if test="age > 0">
	 			and age > #{age}
	 		</if>
	 	</where>
	</select>

where标签，会自动去掉第一个 and

3、choose

	<select id="selectByChoose" resultType="Student">
	 	select id,name,age,score 
	 	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 = 2
	 			</otherwise>
	 		</choose>
	 	</where>
	</select>

choose标签，类似java里面的 switch，when对应 case，otherwise对应 default。

4、foreach

	<select id="selectByForeach" resultType="Student">
	 	select id,name,age,score 
	 	from student 
	 	<if test="array.length > 0">
	 		where id in
	 		<foreach collection="array" item="myid" open="(" close=")" separator=",">
	 			#{myid}
	 		</foreach>
	 	</if>
	</select>


	<select id="selectByForeach2" resultType="Student">
	 	select id,name,age,score 
	 	from student 
	 	<if test="list.size > 0">
	 		where id in
	 		<foreach collection="list" item="myid" open="(" close=")" separator=",">
	 			#{myid}
	 		</foreach>
	 	</if>
	</select>


	<select id="selectByForeach3" resultType="Student">
	 	select id,name,age,score 
	 	from student 
	 	<if test="list.size > 0">
	 		where id in
	 		<foreach collection="list" item="myid" open="(" close=")" separator=",">
	 			#{myid.id}
	 		</foreach>
	 	</if>
	</select>

foreach针对数组、List等集合形式的数据。

5、include

	<select id="selectBySqlFragment" resultType="Student">
	 	<include refid="selectFront" />
	 	from student 
	 	<if test="list.size > 0">
	 		where id in
	 		<foreach collection="list" item="myid" open="(" close=")" separator=",">
	 			#{myid.id}
	 		</foreach>
	 	</if>
	</select>
	
	<!-- sql片段可以替换Sql任何部分 -->
	<sql id="selectFront">
		select id,name,age,score 
	</sql>

sql片段可以 提高代码的 复用性，但是 降低了代码的 可读性。

6、mapper对应的Dao接口

@Test
    public void test01() {
        //        Student stu = new Student("张", 23, 89);
        //        Student stu = new Student("", 23, 89);
        Student stu = new Student("", 0, 89);

        List<Student> students = dao.selectByIf(stu);
        for (Student student : students) {
            System.out.println(student);
        }
    }

    @Test
    public void test02() {
        Student stu = new Student("张", 23, 89);
        //        Student stu = new Student("", 23, 89);
        //        Student stu = new Student("", 0, 89);

        List<Student> students = dao.selectByWhere(stu);
        for (Student student : students) {
            System.out.println(student);
        }
    }

    @Test
    public void test03() {
        //        Student stu = new Student("张", 23, 89);
        //        Student stu = new Student("", 23, 89);
        Student stu = new Student("", 0, 89);

        List<Student> students = dao.selectByChoose(stu);
        for (Student student : students) {
            System.out.println(student);
        }
    }

    @Test
    public void test04() {
        int[] ids = { 1, 2, 3, 7 };

        List<Student> students = dao.selectByForeach(ids);
        for (Student student : students) {
            System.out.println(student);
        }
    }

    @Test
    public void test05() {
        List<Integer> ids = new ArrayList<Integer>();
        ids.add(1);
        ids.add(2);
        ids.add(3);
        ids.add(7);

        List<Student> students = dao.selectByForeach2(ids);
        for (Student student : students) {
            System.out.println(student);
        }
    }

    @Test
    public void test06() {
        List<Student> stus = new ArrayList<>();
        Student stu1 = new Student();
        stu1.setId(1);
        Student stu2 = new Student();
        stu2.setId(2);
        Student stu3 = new Student();
        stu3.setId(7);

        stus.add(stu1);
        stus.add(stu2);
        stus.add(stu3);

        List<Student> students = dao.selectByForeach3(stus);
        for (Student student : students) {
            System.out.println(student);
        }
    }

    @Test
    public void test07() {
        List<Student> stus = new ArrayList<>();
        Student stu1 = new Student();
        stu1.setId(1);
        Student stu2 = new Student();
        stu2.setId(2);
        Student stu3 = new Student();
        stu3.setId(7);

        stus.add(stu1);
        stus.add(stu2);
        stus.add(stu3);

        List<Student> students = dao.selectBySqlFragment(stus);
        for (Student student : students) {
            System.out.println(student);
        }
    }