动态SQL标签

一、if标签、where标签

if标签可以动态判断属性值，进而动态拼接查询条件

where标签在缺少查询条件的时候，会自动的去除前面的AND连接

<mapper namespace="dao.TeacherDao">
    <select id="getTeacherById" resultMap="teacherMap">
        SELECT  * FROM teacher WHERE id = #{id}
    </select>

    <resultMap id="teacherMap" type="bean.Teacher">
        <id property="id" column="id"></id>
        <result property="name" column="teacherName"></result>
        <result property="subject" column="subject_name"></result>
        <result property="address" column="address"></result>
        <result property="birth" column="birth_date"></result>
    </resultMap>

    <!--if标签进行判断-->
    <select id="getTeachersByCondition" resultMap="teacherMap">
        SELECT  * FROM teacher
        <where>
            <!--test编写判断条件-->
            <if test="id != null">
                id > #{id} AND
            </if>
            <if test="name != null and name != ''">
                teacherName LIKE #{name} AND
            </if>
            <if test="birth != null">
                birth_date > #{birth}
            </if>
        </where>
    </select>

</mapper>

    @Test
    public void test10() throws IOException {
        //1、根据全局配置文件创建出一个SQLSessionFactory
        //SqlSessionFactory是SqlSession工厂，负责创建SqlSession对象
        //SqlSession是sql会话，代表和数据库的一次会话
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        Teacher teacher = null;
        SqlSession sqlSession = null;

        try {
            //2.获取和数据库的一次会话，类似于getConnection()方法
            sqlSession = sqlSessionFactory.openSession();

            //3、使用SqlSession操作数据库，获取到dao接口的实现
            TeacherDao teacherDao = sqlSession.getMapper(TeacherDao.class);

            //4、调用接口中的方法
            teacher = new Teacher();
            teacher.setId(1);
            teacher.setName("%老%");
            teacher.setBirth(new Date());
            List<Teacher> teachersByCondition = teacherDao.getTeachersByCondition(teacher);
            System.out.println(teachersByCondition);

        } catch (Exception e) {
            e.printStackTrace();
        } finally {

            //默认自动提交为false，所以需要手动提交
            sqlSession.commit();

            //关闭会话
            sqlSession.close();
        }

    }

输出结果：

二、trim标签

    <!--if标签进行判断-->
    <select id="getTeachersByCondition" resultMap="teacherMap">
        SELECT  * FROM teacher
        <!--trim截取字符串
            prefix: 为slq整体添加一个前缀
            prefixOverrides：去除整体字符串前面多余的字符
            suffix：为整体sql字符串添加一个后缀
            suffixOverrides：去除整体字符串后面多余的字符
        -->
        <trim prefix="where" suffixOverrides="and">
            <!--test编写判断条件-->
            <if test="id != null">
                id > #{id} AND
            </if>
            <if test="name != null and name != ''">
                teacherName LIKE #{name} AND
            </if>
            <if test="birth != null">
                birth_date > #{birth} AND
            </if>
        </trim>
    </select>

三、foreach标签

    <select id="getTeachersByIds" resultMap="teacherMap">
        SELECT * FROM teacher WHERE id IN
        <!--foreach遍历集合
            Collection指定遍历的集合的key
             close="": 以什么字符结束
             index="" ：如果遍历的是list，index是指定的变量保存了当前的索引;  如果遍历的是一个map，index是指定的变量保存了当前遍历的元素的key，item就是保存了当前遍历的元素的值
             item="" ：每次遍历的变量名
             open="" ：以什么字符开始
             separator="":指定元素之间的分隔符
        -->
        <foreach collection="ids" item="id_item" separator="," open="(" close=")"><!--需要在接口方法中指明(@Param("ids") List<Integer> ids)-->
            #{id_item}
        </foreach>
    </select>

    @Test
    public void test11() throws IOException {
        //1、根据全局配置文件创建出一个SQLSessionFactory
        //SqlSessionFactory是SqlSession工厂，负责创建SqlSession对象
        //SqlSession是sql会话，代表和数据库的一次会话
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        Teacher teacher = null;
        SqlSession sqlSession = null;

        try {
            //2.获取和数据库的一次会话，类似于getConnection()方法
            sqlSession = sqlSessionFactory.openSession();

            //3、使用SqlSession操作数据库，获取到dao接口的实现
            TeacherDao teacherDao = sqlSession.getMapper(TeacherDao.class);

            //4、调用接口中的方法
            List<Teacher> teachers = teacherDao.getTeachersByIds(Arrays.asList(1, 2, 3));
            System.out.println(teachers);

        } catch (Exception e) {
            e.printStackTrace();
        } finally {

            //默认自动提交为false，所以需要手动提交
            sqlSession.commit();

            //关闭会话
            sqlSession.close();
        }

    }

输出结果：

四、choose标签

    <select id="getTeachersByConditionChoose" resultMap="teacherMap">
        SELECT * FROM teacher
        <where>
            <choose>
                <when test="id != null">
                    id = #{id}
                </when>
                <when test="name != null and !name.equals(&quot;&quot;)">
                    teacherName = #{name}
                </when>
                <when test="birth_date != null">
                    birth_date = #{birth}
                </when>
                <otherwise>
                    1=1
                </otherwise>
            </choose>
        </where>
    </select>

    @Test
    public void test12() throws IOException {
        //1、根据全局配置文件创建出一个SQLSessionFactory
        //SqlSessionFactory是SqlSession工厂，负责创建SqlSession对象
        //SqlSession是sql会话，代表和数据库的一次会话
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        Teacher teacher = null;
        SqlSession sqlSession = null;

        try {
            //2.获取和数据库的一次会话，类似于getConnection()方法
            sqlSession = sqlSessionFactory.openSession();

            //3、使用SqlSession操作数据库，获取到dao接口的实现
            TeacherDao teacherDao = sqlSession.getMapper(TeacherDao.class);

            //4、调用接口中的方法
            teacher = new Teacher();
            teacher.setId(1);
            teacher.setName("%老师%");
            //使用choose标签只会从id，name，birth选择其中一个作为查询条件
            List<Teacher> teachers = teacherDao.getTeachersByConditionChoose(teacher);

            System.out.println(teachers);

        } catch (Exception e) {
            e.printStackTrace();
        } finally {

            //默认自动提交为false，所以需要手动提交
            sqlSession.commit();

            //关闭会话
            sqlSession.close();
        }

    }

输出结果：

五、使用if结合set完成MyBatis动态更新

    <update id="updateTeacher">
        UPDATE teacher
        <set>
            <if test="name != null and !name.equals(&quot;&quot;)">
                teacherName = #{name},
            </if>
            <if test="subject != null and !name.equals(&quot;&quot;)">
                subject_name = #{subject},
            </if>
            <if test="address != null and !name.equals(&quot;&quot;)">
                address = #{address},
            </if>
            <if test="birth != null">
                birth_data = #{birth},
            </if>

        </set>

        <where>
            id = #{id}
        </where>

    </update>

            //4、调用接口中的方法
            teacher = new Teacher();
            teacher.setId(1);
            teacher.setName("update动态更新");
            int i = teacherDao.updateTeacher(teacher);

六、sql标签

抽取可重用的SQL语句，使用include包含进来

    <sql id="selectSql">SELECT  * FROM teacher</sql>

	<select id="getTeacherById" resultMap="teacherMap">
        <include refid="selectSql"></include>
        WHERE id = #{id}
    </select>

七、bind标签

bind标签可以把一个表达式绑定在一个变量上

            <bind name="_name" value="'%'+name+'%'"></bind>
            <if test="name != null and name != ''">
                teacherName LIKE #{_name} AND
            </if>