第四章 动态sql

动态sql: sql的内容是变化的，可以根据条件获取到不同的sql语句。主要是where部分发生变化。

动态sql的实现，使用的是mybatis提供的标签 if , where, foreach

1)是判断条件的，

   语法<if test="判断java对象的属性值">
		部分sql语句
	</if>
List<Student> selectStudentIf(Student student);

<select id="selectStudentIf" resultType="com.mybatis.study.domain.Student">
        
        select id, name, email, age from Student
        where 
        <if test="name != null and name != '' ">
            name = #{name}
        </if>
        
        <if test="age > 0">
            and age > #{age}
        </if>
        
    </select>


public void testSelectStudentIf(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        StudentDao dao = sqlSession.getMapper(StudentDao.class);
        Student student = new Student();
        student.setName("张飞");
        student.setAge(20);
        List<Student> students= dao.selectStudentIf(student);
        for (Student stu:students
             ) {
            System.out.println(stu);
        }
        
    }

2) 用来包含 多个的， 当多个if有一个成立的， 会自动增加一个where关键字，
并去掉 if中多余的 and ，or等。

// where使用
    List<Student> selectStudentWhere(Student student);


<!--  where: <where> <if> <if> ... </ where>  -->
    <select id="selectStudentWhere" resultType="com.mybatis.study.domain.Student">
        select id, name, email, age from Student
        <where>
            <if test="name != null and name != '' ">
                name = #{name}
            </if>
            <if test="age > 0">
                or age > #{age}
            </if>
        </where>
        
    </select>

  public void testSelectStudentWhere(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        StudentDao dao = sqlSession.getMapper(StudentDao.class);
        Student student = new Student();
        student.setName("张飞");
        student.setAge(20);
        List<Student> students= dao.selectStudentWhere(student);
        for (Student stu:students
        ) {
            System.out.println(stu);
        }

    }

3） 循环java中的数组，list集合的。 主要用在sql的in语句中。
学生id是 1001,1002,1003的三个学生

	 select * from student where id in (1001,1002,1003)

	 public List<Student> selectFor(List<Integer> idlist)

	 List<Integer> list = new ...
	 list.add(1001);
	 list.add(1002);
	 list.add(1003);

	 dao.selectFor(list)


	 <foreach collection="" item="" open="" close="" separator="">
             #{xxx}
    </foreach>

    collection:表示接口中的方法参数的类型， 如果是数组使用array , 如果是list集合使用list

	 item:自定义的，表示数组和集合成员的变量

	 open:循环开始是的字符

	 close:循环结束时的字符

    separator:集合成员之间的分隔符

 List<Student> selectForeachOne(List<Integer> myidList);


<select id="selectForeachOne" resultType="com.mybatis.study.domain.Student">
        select * from student where id in 
        <foreach collection="list" item="myid" open="(" close=")" separator=",">
            #{myid}
        </foreach>
         
        
    </select>


 @Test
    public void testFor(){
        List<Integer> list =  new ArrayList<>();
        list.add(1001);
        list.add(1002);
        list.add(1003);
        String sql = "select * from student where id in ";
        StringBuilder stringBuilder =new StringBuilder("");
        stringBuilder.append("(");
        int init = 0;
        int len  = list.size();
        for (Integer i:list
             ) {
            stringBuilder.append(i).append(",");
        }
        stringBuilder.deleteCharAt(stringBuilder.length()-1);
        stringBuilder.append(")");
        sql = sql+stringBuilder;
        System.out.println(sql);
        
        
        
    }

    @Test
    public void testSelectForEach(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        StudentDao dao = sqlSession.getMapper(StudentDao.class);
        
        List<Integer> list = new ArrayList<>();
        list.add(1001);
        list.add(1002);
        list.add(1003);
        List<Student> students= dao.selectForeachOne(list);
        for (Student stu:students
        ) {
            System.out.println(stu);
        }

    }

第二种方式

 // foreach二种用法
    List<Student> selectForeachTow(List<Student> stuList);

<select id="selectForeachTow" resultType="com.mybatis.study.domain.Student">
        select * from student where id in
        <foreach collection="list" item="stu" open="(" close=")" separator=",">
            #{stu.id}
        </foreach>


    </select>

4）sql代码片段， 就是复用一些语法

步骤
	 
1.先定义 <sql id="自定义名称唯一">  sql语句， 表名，字段等 </sql>
    
2.再使用， include refid="id的值"