[一点笔记]Mybatis配置及sql操作（二）-CSDN博客

本文链接：https://blog.csdn.net/u012393192/article/details/84956006

继续更新练习项目，包含：
动态sql环境搭建
if、choose、trim、foreach标签
set、bind标签
sql标签

新建配置文件mapperDynamicSQL.xml、接口UserMapperDynamicSQL、mybaits-config中添加相应mapper

mapperDynamicSQL

<?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="com.model.UserMapperDynamicSQL">
    <!--
        if 判断
        choose(when,otherwise) 分支选择
        trim(where,set) 字符串截取
        foreach
        自定义字符串截取规则-->
    <select id="getUserConditionIf" resultType="usr">
        SELECT
          <!--引用外部定义的sql-->
          <include refid="insertColumn"/>
        FROM user
        <where>
            <!--test:判断表达式（OGNL）
            用法可参照官方文档
            遇见特殊符号（）“”等，应该写转义字符-->
            <if test="ID!=null">
                ID=#{ID}
            </if>
            <if test="username!=null and username!=&quot;&quot;">
                <!--<if test="username!=null &amp;&amp; username!=&quot;&quot;"/>-->
                AND username LIKE #{username}
            </if>
            <if test="password!=null">
                AND password LIKE #{password}
            </if>
            <!--查询时某些条件没有拼装会导致sql语句出错
                1、给where加1=1，后面所有条件都用and XXX
                2、mybatis使用where标签将所有查询条件包括在内-->
        </where>
    </select>

    <select id="getUserConditionTrim" resultType="usr">
        SELECT * FROM user
        <!--prefix="" 前缀：trim标签体中是整个字符串拼接以后的结果，perfix给拼装后的结果加前缀
            prefixOverrides="" 前缀覆盖：去掉字符串前面多余字符
            suffix="" 后缀：给拼装后的结果加后缀
            suffixOverrides="" 后缀覆盖：去掉字符串后面多余字符-->
        <trim prefix="WHERE" suffixOverrides="AND" prefixOverrides="AND">
            <!--test:判断表达式（OGNL）
            用法可参照官方文档
            遇见特殊符号（）“”等，应该写转义字符-->
            <if test="ID!=null">
                ID=#{ID} AND
            </if>
            <if test="username!=null and username!=&quot;&quot;">
                <!--<if test="username!=null &amp;&amp; username!=&quot;&quot;"/>-->
                AND username LIKE #{username}
            </if>
            <if test="password!=null">
                AND password LIKE #{_password}
            </if>
            <!--查询时某些条件没有拼装会导致sql语句出错
                1、给where加1=1，后面所有条件都用and XXX
                2、mybatis使用where标签将所有查询条件包括在内-->
        </trim>
    </select>

    <select id="getUserConditionChoose" resultType="usr">
        <!--bind可以将OGNL表达式的值绑定到变量中，方便后面引用-->
        <bind name="_username" value="'%'+username+'%'"/>
        SELECT * FROM user
        <where>
            <choose>
                <when test="ID!=null">
                    ID=#{ID}
                </when>
                <when test="username!=null">
                    username LIKE #{_username}
                </when>
                <when test="password!=null">
                    password = #{password}
                </when>
                <otherwise>
                    <!--查所有-->
                    1=1
                </otherwise>
            </choose>
        </where>
    </select>

    <select id="updateByIf" resultType="usr">
        UPDATE user
        <set>
            <if test="username!=null">
                username = #{username}
            </if>
            <if test="password!=null">
                ,password = #{password}
            </if>
            WHERE ID = #{ID}
        </set>
    </select>
    
    <select id="getUserConditionForeach" resultType="usr">
        SELECT * FROM user WHERE ID IN
        <!--collection：指定要遍历的集合
            item：将当前遍历出的元素赋值给指定变量
            separator：每个元素之间的分隔符
            open：遍历出所有结果并拼接一个开始的字符
            close：遍历出所有结果并拼接一个结束的字符
            index：索引，遍历list时是索引
                         遍历map时index表示map的key，item表示map的值-->
        <foreach collection="list" item="item_id" separator="," open="(" close=")">
            #{item_id}
        </foreach>
    </select>

    <insert id="addUsers">
        INSERT INTO user (username,password,j_ID) VALUES 
        <foreach collection="usrs" separator="," item="usr">
            (#{usr.username},#{usr.password},#{usr.j_ID})
        </foreach>
    </insert>
    
    <!--sql：抽取可重用sql片段，方便后面引用
        内部可以添加动态标签，如if
        1、经常将要查询、插入用的列名抽取出来方便引用
        2、include来引用已抽取的sql：
        3、include还可以自定义property，sql标签内部就能使用自定义的属性
            include内定义<property name="" value="">,sql中使用${}引用-->
    <sql id="insertColumn">
        ID,username,password
    </sql>

</mapper>

UserMapperDynamicSQL

package com.model;

import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface UserMapperDynamicSQL {
    List<User> getUserConditionIf(User user);

    List<User> getUserConditionTrim(User user);

    List<User> getUserConditionChoose(User user);

    void updateByIf(User user);

    List<User> getUserConditionForeach(List list);

    void addUsers(@Param("usrs") List<User> lists);
}

测试类

    @Test
//    测试if、where
    public void DynamicTest()throws Exception{
        SqlSession session = getfactory("mybatis-config.xml").openSession();

        try {
            UserMapperDynamicSQL userMapperDynamicSQL = session.getMapper(UserMapperDynamicSQL.class);
            User user = new User(null,"%like%",null);
            List<User> users = userMapperDynamicSQL.getUserConditionIf(user);
            System.out.println(users);
        }finally {
            session.close();
        }
    }

    @Test
    public void DynamicTest2()throws Exception{
        SqlSession session = getfactory("mybatis-config.xml").openSession();

        try {
            UserMapperDynamicSQL userMapperDynamicSQL = session.getMapper(UserMapperDynamicSQL.class);
//            User user = new User(null,"%k%",null);
            User user = new User(null,"%k%",null);
            List<User> users = userMapperDynamicSQL.getUserConditionTrim(user);
            System.out.println(users);
        }finally {
            session.close();
        }
    }

    @Test
    public void DynamicTest3()throws Exception{
        SqlSession session = getfactory("mybatis-config.xml").openSession();

        try {
            UserMapperDynamicSQL userMapperDynamicSQL = session.getMapper(UserMapperDynamicSQL.class);
            User user = new User(null,"lu",123);
            List<User> users = userMapperDynamicSQL.getUserConditionChoose(user);
            System.out.println(users);
        }finally {
            session.close();
        }
    }

    @Test
    public void DynamicTest4()throws Exception{
        SqlSession session = getfactory("mybatis-config.xml").openSession();

        try {
            UserMapperDynamicSQL userMapperDynamicSQL = session.getMapper(UserMapperDynamicSQL.class);
            User user = new User(9,"xiaolu",917);
            userMapperDynamicSQL.updateByIf(user);
            System.out.println();
        }finally {
            session.close();
        }
    }

    @Test
    public void DynamicTest5()throws Exception{
        SqlSession session = getfactory("mybatis-config.xml").openSession();

        try {
            UserMapperDynamicSQL userMapperDynamicSQL = session.getMapper(UserMapperDynamicSQL.class);
            List<User> list = userMapperDynamicSQL.getUserConditionForeach(Arrays.asList(1,2,3,4));
//            System.out.println(Arrays.asList(1,2,3,4));
            for (User user:list){
                System.out.println(user);
            }
        }finally {
            session.close();
        }
    }

    @Test
    public void DynamicTest6()throws Exception{
        SqlSession session = getfactory("mybatis-config.xml").openSession(true);

        try {
            UserMapperDynamicSQL userMapperDynamicSQL = session.getMapper(UserMapperDynamicSQL.class);
            List<User> lists = new ArrayList<User>();
            lists.add(new User("lulu",123123,2));
            lists.add(new User("lulu2",111111,2));
            lists.add(new User("lulu3",000000,1));
            userMapperDynamicSQL.addUsers(lists);
        }finally {
            session.close();
        }
    }