Mybatis动态sql（if、where、sql、foreach、choose）

动态sql就是根据不同的条件生成不同的sql语句（在sql中添加了逻辑运算），运用动态sql可以避免拼接字符串的痛苦

1、if 标签

where后的条件都不为空：

public interface StudentMapper {
     List<Student> selectStudentBySexAndSname(Student student);
}

配置文件：

    <select id="selectStudentBySexAndSname" parameterType="Student" resultType="pers.zhb.pojo.Student">
        select *  from student
        where
        <if test="sex!=null and sex !=''">
        sex=#{sex}
        </if>
        <if test="sname!=null and sname !=''">
        and sname=#{sname}
        </if>
    </select>

test里面是判断表达式（条件）， 也就是说只有条件满足的时候才会拼接上后面的语句

测试：

 public void selectStudentBySexAndSname() throws Exception {
        String resource = "sqlMapConfig.xml";
        InputStream in = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
        Student student=new Student();
        student.setSex("男");
        student.setSname("zhai");
        List<Student> students = studentMapper.selectStudentBySexAndSname(student);
        for(Student stu:students){
            System.out.println(stu);
        }
    }

DEBUG [main] - Find JAR URL: file:/D:/IdeaProjects/MybatisDemo/web/WEB-INF/classes/pers/zhb/mapper/StudentMapper.xml
DEBUG [main] - Not a JAR: file:/D:/IdeaProjects/MybatisDemo/web/WEB-INF/classes/pers/zhb/mapper/StudentMapper.xml
DEBUG [main] - Reader entry: <?xml version="1.0" encoding="UTF-8" ?>
DEBUG [main] - Checking to see if class pers.zhb.mapper.StudentMapper matches criteria [is assignable to Object]
DEBUG [main] - Opening JDBC Connection
DEBUG [main] - Created connection 916419490.
DEBUG [main] - Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@369f73a2]
DEBUG [main] - ==>  Preparing: select * from student where sex=? and sname=? 
DEBUG [main] - ==> Parameters: 男(String), zhai(String)
DEBUG [main] - <==      Total: 1
Student [snumber=201811, sname=zhai, sex=男, birthday=1998-11-11, classno=tx171, point=890, phone=1234567890, email=null]

因为两个条件都不为空，因此，都要作为条件拼接sql语句，执行的时候是执行的拼接后的sql语句。

SELECT *  
FROM student
WHERE sex='男'
AND sname='zhai'  

当只有一个条件不为空：分为两种情况：

当第一个条件不为空：

    @Test
    public void test(){
        SqlSession sqlSession= MybatisUtils.getSqlSession();
        StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
        Student student=new Student();
        student.setSname("zhai");
        List<Student> students = studentMapper.selectStudentBySexAndSname(student);
        for(Student stu:students){
            System.out.println(stu);
        }
    }

测试的时候只给sname赋值，也就是说sex为空，sex后的语句不会拼接，相当于执行下面的语句，这是不符合sql的语法规则的，测试的时候会报错：

SELECT *  
FROM student
WHERE 
AND sname='zhai'  

解决方案：在where后main添加语句：1=1，相当于执行下面的sql语句：

SELECT *  
FROM student
WHERE 1=1
AND sname='zhai'

配置文件：

 <select id="selectStudentBySexAndSname" parameterType="pers.zhb.pojo.Student" resultType="pers.zhb.pojo.Student">
        select *  from student
        where 1=1
        <if test="sex!=null and sex !=''">
            sex=#{sex}
        </if>
        <if test="sname!=null and sname !=''">
            and sname=#{sname}
        </if>
 </select>

但是，当第二个条件为空的时候字符串拼接后依旧符合sql的语法规则，即使不添加1=1的条件依旧可以正常执行sql语句：

    @Test
    public void test(){
        SqlSession sqlSession= MybatisUtils.getSqlSession();
        StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
        Student student=new Student();
        student.setSex("男");
        List<Student> students = studentMapper.selectStudentBySexAndSname(student);
        for(Student stu:students){
            System.out.println(stu);
        }
    }

此时，执行的sql语句为：

SELECT *  
FROM student
WHERE sex='男'

2、where 标签

（1）分别注释掉setsex和setsname：

配置文件：

    <select id="selectStudentBySexAndSname" parameterType="Student" resultType="pers.zhb.pojo.Student">
        select *  from student
        where
        <if test="sex!=null and sex !=''">
        sex=#{sex}
        </if>
        <if test="sname!=null and sname !=''">
        and sname=#{sname}
        </if>
    </select>

测试：

public void selectStudentBySexAndSname() throws Exception {
        String resource = "sqlMapConfig.xml";
        InputStream in = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
        Student student=new Student();
        student.setSex("男");
        student.setSname("zhai");
        List<Student> students = studentMapper.selectStudentBySexAndSname(student);
        for(Student stu:students){
            System.out.println(stu);
        }
    }

当注释掉 student.setSex("男");的时候报错，而注释掉student.setSname("zhai");的时候不会报错，这是因为注释掉后sql语句的字符串拼接出现错误。

注释掉student.setSex("男");后相当于：

    <select id="selectStudentBySexAndSname" parameterType="Student" resultType="pers.zhb.pojo.Student">
        select *  from student
        where

        <if test="sname!=null and sname !=''">
        and sname=#{sname}
        </if>
    </select>

即：where和and直接相连，这是不符合sql语句规范的，因此，需要添加1=1，来拼接sql语句使其能够表达完整含义：

<select id="selectStudentBySexAndSname" parameterType="Student" resultType="pers.zhb.pojo.Student">
        select *  from student
        where 1=1
        <if test="sex!=null and sex !=''">
        sex=#{sex}
        </if>
        <if test="sname!=null and sname !=''">
        and sname=#{sname}
        </if>
    </select>

（2）为了解决上述问题，可以添加where标签，添加之后不用再写1=1了。

    <select id="selectStudentBySexAndSname" parameterType="Student" resultType="pers.zhb.pojo.Student">
        select *  from student
        <where>
        <if test="sex!=null and sex !=''">
        sex=#{sex}
        </if>
        <if test="sname!=null and sname !=''">
        and sname=#{sname}
        </if>
        </where>
    </select>

（3）当什么都不传的时候（条件都为空）：

DEBUG [main] - Logging initialized using 'class org.apache.ibatis.logging.log4j.Log4jImpl' adapter.
DEBUG [main] - Logging initialized using 'class org.apache.ibatis.logging.log4j.Log4jImpl' adapter.
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - Opening JDBC Connection
DEBUG [main] - Created connection 282828951.
DEBUG [main] - ==>  Preparing: select * from student 
DEBUG [main] - ==> Parameters: 
DEBUG [main] - <==      Total: 11

从日志文件可以看出，是查询所有的学生信息

3、sql片段：提取sql语句的公共部分

 <sql id="selector">
        <include refid="selector"></include>
    </sql>
    <select id="selectStudentBySexAndSname" parameterType="Student" resultType="pers.zhb.pojo.Student">
        select *  from student
        <where>
        <if test="sex!=null and sex !=''">
        sex=#{sex}
        </if>
        <if test="sname!=null and sname !=''">
        and sname=#{sname}
        </if>
        </where>
    </select>

4、foreach

（1）创建接口：

public interface StudentMapper {
     Student findStudentById(Integer studentno);
     List<Student> findStudentByQueryVo(QueryVo vo);
     Integer countStudent();
     List<Student> selectAllStudent();
     List<Student> selectStudentBySexAndSname(Student student);
     List<Student> selectStudentByIds(Integer ids[]);
     List<Student> selectStudentByIds(List<Integer> ids);
     List<Student> selectStudentByIds(QueryVo vo);
}

（2）vo实现：

创建vo：

public class QueryVo implements Serializable {
    private static final long serialVersionUID = 1L;
    private Student student;
    List <Integer> idsList;
    Integer[] ids;
    public List<Integer> getIdsList() {
        return idsList;
    }

    public void setIdsList(List<Integer> idsList) {
        this.idsList = idsList;
    }

    public Integer[] getIds() {
        return ids;
    }

    public void setIds(Integer[] ids) {
        this.ids = ids;
    }
    public Student getStudent() {
        return student;
    }

    public void setStudent(Student student) {
        this.student = student;
    }
}

配置文件：

<select id="selectStudentByIds" parameterType="QueryVo" resultType="pers.zhb.pojo.Student">
        select *  from student
        <where>
            <foreach collection="idsList" item="studentno" separator="," open="studentno in(" close=")">
                #{studentno}
            </foreach>
        </where>
    </select>

测试：

public void selectStudentByIds() throws Exception {
        String resource = "sqlMapConfig.xml";
        InputStream in = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
        List<Integer>ids=new ArrayList<>();
        ids.add(201811);
        ids.add(201812);
        ids.add(201813);
        QueryVo vo=new QueryVo();
        vo.setIdsList(ids);
        List<Student> students=studentMapper.selectStudentByIds(vo);
        for(Student student:students){
            System.out.println(student);
        }
    }

（3）数组：

配置文件：

<select id="selectStudentByIds" parameterType="QueryVo" resultType="pers.zhb.pojo.Student">
        select *  from student
        <where>
            <foreach collection="array" item="studentno" separator="," open="studentno in(" close=")">
                #{studentno}
            </foreach>
        </where>
</select>

item是遍历的每一项，分隔符为逗号，array为要遍历的集合，open是以什么开始，close是以什么结束

测试：

public void selectStudentByIds() throws Exception {
        String resource = "sqlMapConfig.xml";
        InputStream in = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
        Integer ids[]=new Integer[3];
        ids[0]=201811;
        ids[1]=201812;
        ids[2]=201813;
        List<Student> students=studentMapper.selectStudentByIds(ids);
        for(Student student:students){
            System.out.println(student);
        }
    }

DEBUG [main] - Logging initialized using 'class org.apache.ibatis.logging.log4j.Log4jImpl' adapter.
DEBUG [main] - Logging initialized using 'class org.apache.ibatis.logging.log4j.Log4jImpl' adapter.
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - Opening JDBC Connection
DEBUG [main] - Created connection 633075331.
DEBUG [main] - ==>  Preparing: select * from student WHERE studentno in( ? , ? , ? ) 
DEBUG [main] - ==> Parameters: 201811(Integer), 201812(Integer), 201813(Integer)
DEBUG [main] - <==      Total: 3
Student(studentno=201811, sname=zhai, sex=男, birthday=1998-11-11, classno=80501, point=890, phone=1234567890, email=null)
Student(studentno=201812, sname=zhai2, sex=男, birthday=1998-11-11, classno=80601, point=893, phone=19837372533, email=null)
Student(studentno=201813, sname=zhai3, sex=男, birthday=1998-11-11, classno=80501, point=892, phone=19837372534, email=null)

（4）集合：

配置文件：

<select id="selectStudentByIds" parameterType="QueryVo" resultType="pers.zhb.pojo.Student">
        select *  from student
        <where>
            <foreach collection="list" item="studentno" separator="," open="studentno in(" close=")">
                #{studentno}
            </foreach>
        </where>
    </select>

测试：

 public void selectStudentByIds() throws Exception {
        String resource = "sqlMapConfig.xml";
        InputStream in = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
        List<Integer> ids=new ArrayList<>();
        ids.add(201811);
        ids.add(201812);
        ids.add(201813);
        List<Student> students=studentMapper.selectStudentByIds(ids);
        for(Student student:students){
            System.out.println(student);
        }
    }

5、choose

有的时候我们不想应用到所有的条件语句，而且想从中选择一项，nyatis的choose元素有点像java中的switch语句

（1）配置：

<select id="selectStudentBySexAndSname" parameterType="pers.zhb.pojo.Student" resultType="pers.zhb.pojo.Student">
        select *  from student
        <where>
            <choose>
                <when test="sname!=null">
                    sname=#{sname}
                </when>
                <when test="sex!=null">
                    and sex=#{sex}
                </when>
                <otherwise>
                    and classno=#{classno}
                </otherwise>
            </choose>
        </where>
</select>

（2）测试：

所有条件都为空：

DEBUG [main] - Logging initialized using 'class org.apache.ibatis.logging.log4j.Log4jImpl' adapter.
DEBUG [main] - Logging initialized using 'class org.apache.ibatis.logging.log4j.Log4jImpl' adapter.
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - Opening JDBC Connection
DEBUG [main] - Created connection 282828951.
DEBUG [main] - ==>  Preparing: select * from student WHERE classno=? 
DEBUG [main] - ==> Parameters: null
DEBUG [main] - <==      Total: 0

最后一个条件不为空（otherwise不为空）：

DEBUG [main] - Logging initialized using 'class org.apache.ibatis.logging.log4j.Log4jImpl' adapter.
DEBUG [main] - Logging initialized using 'class org.apache.ibatis.logging.log4j.Log4jImpl' adapter.
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - Opening JDBC Connection
DEBUG [main] - Created connection 282828951.
DEBUG [main] - ==>  Preparing: select * from student WHERE classno=? 
DEBUG [main] - ==> Parameters: 80501(String)
DEBUG [main] - <==      Total: 5
Student(studentno=201811, sname=zhai, sex=男, birthday=1998-11-11, classno=80501, point=890, phone=1234567890, email=null)
Student(studentno=201813, sname=zhai3, sex=男, birthday=1998-11-11, classno=80501, point=892, phone=19837372534, email=null)
Student(studentno=201814, sname=zhai3, sex=男, birthday=1998-11-11, classno=80501, point=892, phone=19837372534, email=null)
Student(studentno=201815, sname=qwerr, sex=男, birthday=1998-11-11, classno=80501, point=892, phone=19837372534, email=null)
Student(studentno=201816, sname=jiayou, sex=男, birthday=1998-11-11, classno=80501, point=892, phone=19837372534, email=null)

最后一个条件不为空，前面的条件有一个不为空：

DEBUG [main] - Logging initialized using 'class org.apache.ibatis.logging.log4j.Log4jImpl' adapter.
DEBUG [main] - Logging initialized using 'class org.apache.ibatis.logging.log4j.Log4jImpl' adapter.
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - Opening JDBC Connection
DEBUG [main] - Created connection 22069592.
DEBUG [main] - ==>  Preparing: select * from student WHERE sname=? 
DEBUG [main] - ==> Parameters: zhai(String)
DEBUG [main] - <==      Total: 1
Student(studentno=201811, sname=zhai, sex=男, birthday=1998-11-11, classno=80501, point=890, phone=1234567890, email=null)

可以见到只有在所有的条件都为空的情况下，才会执行otherwise里面的条件，前面的只要有一个不为空就会执行此条件而舍弃otherwise里面的条件。

都不为空：

DEBUG [main] - Logging initialized using 'class org.apache.ibatis.logging.log4j.Log4jImpl' adapter.
DEBUG [main] - Logging initialized using 'class org.apache.ibatis.logging.log4j.Log4jImpl' adapter.
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - Opening JDBC Connection
DEBUG [main] - Created connection 22069592.
DEBUG [main] - ==>  Preparing: select * from student WHERE sname=? 
DEBUG [main] - ==> Parameters: zhai(String)
DEBUG [main] - <==      Total: 1
Student(studentno=201811, sname=zhai, sex=男, birthday=1998-11-11, classno=80501, point=890, phone=1234567890, email=null)

优先执行第一个条件

6、模块的提取

使用标签将sql片段提取出来（公共部分），然后在另一段配置文件中引入即可，可以实现代码的复用

    <sql id="iftest">
        <choose>
            <when test="sname!=null">
                sname=#{sname}
            </when>
            <when test="sex!=null">
                and sex=#{sex}
            </when>
            <otherwise>
                and classno=#{classno}
            </otherwise>
        </choose>
    </sql>

    <select id="selectStudentBySexAndSname" parameterType="pers.zhb.pojo.Student" resultType="pers.zhb.pojo.Student">
        select *  from student
        <where>
        <include refid="iftest"></include>
        </where>
    </select>

测试：

DEBUG [main] - Logging initialized using 'class org.apache.ibatis.logging.log4j.Log4jImpl' adapter.
DEBUG [main] - Logging initialized using 'class org.apache.ibatis.logging.log4j.Log4jImpl' adapter.
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - Opening JDBC Connection
DEBUG [main] - Created connection 22069592.
DEBUG [main] - ==>  Preparing: select * from student WHERE sname=? 
DEBUG [main] - ==> Parameters: zhai(String)
DEBUG [main] - <==      Total: 1
Student(studentno=201811, sname=zhai, sex=男, birthday=1998-11-11, classno=80501, point=890, phone=1234567890, email=null)

Process finished with exit code 0