mybatis动态SQL

if(如果test中的条件满足，则在sql中拼装if之中的条件)

trim自定义sql拼装规则。包括

加前缀 prefix，

加后缀suffix，
去掉前缀prefixOverrides，

去掉后缀suffixOverrides

<select id="getBloggerByDynamicSQL" resultType="com.yunqing.mybatis.bean.Blogger">
        SELECT * FROM t_blogger
        <!--自定义sql，相当于字符串前缀(没有的话)加一个where,后缀（有的话）去掉一个and-->
        <trim prefix="where" suffixOverrides="and">
            <if test="id!=null">
                WHERE id=#{id} AND
            </if>
            <if test="username!=null and username!=''">
                username=#{username} AND
            </if>
            <if test="password!=null and password!=''">
                password=#{password}
            </if>
        </trim>
    </select>

choose相当于switch case

when满足条件，补上sql跳出

都不满足则补上otherwise中的sql

<select id="getBloggerByChoose" resultType="com.yunqing.mybatis.bean.Blogger">
        SELECT * FROM t_blogger
        <where>
            <choose>
                <when test="id!=null">
                    id=#{id}
                </when>
                <when test="username!=null and username!=''">
                    username=#{username}
                </when>
                <when test="password!=null and password!=''">
                    password=#{password}
                </when>
                <otherwise>
                    profile LIKE #{profile}
                </otherwise>
            </choose>
        </where>
    </select>

set相当于修改信息的set,也可以使用trim自定义

<update id="updateBlogger">
        UPDATE t_blogger
        <set>
            <if test="username!=null and username!=''">
                username=#{username},
            </if>
            <if test="password!=null and password!=''">
                password=#{password}
            </if>
        </set>
        <where>
            id=#{id}
        </where>
    </update>

自定义写法：

<update id="updateBlogger">
        UPDATE t_blogger
<!--相当于给sql字符串前边加上set,后边如果多出，则去掉，-->
        <trim prefix="set" suffixOverrides=",">
            <if test="username!=null and username!=''">
                username=#{username},
            </if>
            <if test="password!=null and password!=''">
                password=#{password}
            </if>
        </trim>
        <where>
            id=#{id}
        </where>
    </update>

 foreach:遍历，多用于批量查询，或者批量添加等批量操作

package com.yunqing.mybatis.dao;

import com.yunqing.mybatis.bean.User;
import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface UserDynamicSQLMapper {

    List<User> getUserByDynamicSQL(User user);

    void updateUser(User user);

    void addUsers(@Param("users") List<User> users);//批量添加

    List<User> getAllByIds(@Param("ids") List<Integer> ids);//根据多id查询
}

<!--批量添加
    collection:集合名
    item:集合中子元素命名
    separator:以xx分割<foreach>中的sql
    open:<foreach>中的sql以open中的内容开始
    close:<foreach>中的sql以open中的内容结束
    -->
    <insert id="addUsers">
        INSERT INTO t_user(name,age,dep_id) VALUES
        <foreach collection="users" item="user" separator=",">
            (#{user.name},#{user.age},#{user.dep.id})
        </foreach>
    </insert>
    
    <select id="getAllByIds" resultType="com.yunqing.mybatis.bean.User">
        SELECT * FROM t_user WHERE id IN
        <foreach collection="ids" item="items" separator="," open="(" close=")">
            #{items}
        </foreach>
    </select>

测试

@Test
    public void addUsers() throws IOException {
        SqlSession sqlSession = getSqlSessionFactory().openSession(true);
        UserDynamicSQLMapper mapper = sqlSession.getMapper(UserDynamicSQLMapper.class);
        List<User> list = new ArrayList<>();
        list.add(new User(null,"drj",20,new Department(2)));
        list.add(new User(null,"LYF",2,new Department(2)));
        mapper.addUsers(list);
    }
@Test
    public void getUserByDynamicSQL() throws IOException {
        SqlSession sqlSession = getSqlSessionFactory().openSession();
        UserDynamicSQLMapper mapper = sqlSession.getMapper(UserDynamicSQLMapper.class);
        /*User user = new User(7,"uzi",20,null);
        List<User> userByDynamicSQL = mapper.getUserByDynamicSQL(user);
        for (User u : userByDynamicSQL) {
            System.out.println(u);
        }*/
        List<User> allByIds = mapper.getAllByIds(Arrays.asList(1, 2, 3, 19));
        for (User u : allByIds) {
            System.out.println(u);
        }
    }

 动态sql内置参数有两个：_parameter和_databaseId

<bind>绑定的运用：

<select id="getBloggerByParameter" resultType="com.yunqing.mybatis.bean.Blogger">
        SELECT * FROM t_blogger
<!--给传入的参数绑定模糊查询的%，以便在测试的时候可以直接写要模糊查询的内容不用补%-->
        <bind name="_profile" value="'%'+profile+'%'"/>
        <where>
            <if test="_parameter!=null"><!--传入的内置参数-->
                profile LIKE #{_profile}
            </if>
        </where>
    </select>

测试：

@Test
    public void getBloggerByParam() throws IOException {
        SqlSession sqlSession = getSqlSessionFactory().openSession();
        BloggerDynamicSQLMapper mapper = sqlSession.getMapper(BloggerDynamicSQLMapper.class);
        Blogger blogger = new Blogger();
        blogger.setProfile("e");//如果不绑定%%则在此处需要写（“%e%”）
        List<Blogger> bloggerByParameter = mapper.getBloggerByParameter(blogger);
        for (Blogger blo :
                bloggerByParameter) {
            System.out.println(blo);
        }
    }

<sql>标签抽取可重用sql片段，在需要引用可重用片段处使用<include>标签引用。