Mybatis的动态SQL(if、where、choose、bind、set、foreach标签)

Mybatis的动态SQL(if、where、choose、bind、set、foreach标签)

1．if

单分支判断语句

<select id="selectUserByProperty" resultType="com.zd.pojo.Users">
        select * from users where 1=1
        <if test="userid!=0">
            and userid=#{userid}
        </if>
        <if test="username!=null and username!=''">
            and username=#{username}
        </if>
        <if test="usersex!=null and usersex!=''">
            and usersex=#{usersex}
        </if>
    </select>

@Test
    public void selectUserByProperty(){
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        Users users=new Users(0,"张三","男");
        Users users1 = userDao.selectUserByProperty(users);
        System.out.println(users1);
    }

2 ．choose、when、otherwise

从多个条件中选择一个

<!--动态SQL choose-->
    <select id="selectUserChoose" parameterType="com.zd.pojo.Users" resultType="com.zd.pojo.Users">
        select * from users where 1=1
        <choose>
            <when test="username!=null and username!=''">
                and username=#{username}
            </when>
            <when test="usersex!=null and usersex!=''">
                and usersex=#{usersex}
            </when>
            <otherwise>
                and userid=1
            </otherwise>
        </choose>
    </select>

当第一个when满足时就不再拼接后面的条件，都不满足是执行otherwise的条件

    @Test
    public void selectUserChoose(){
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        Users users=new Users(0,"张三","男");
        Users users1 = userDao.selectUserChoose(users);
        System.out.println(users1);
    }

3．where标签

使用where标签，就不需要提供where 1=1 这样的条件了。如果判断判断条件不为空，自动添加where关键字，并且会自动去掉第一个条件前面的and和or

    <!--动态SQL where-->
    <select id="selectWhere" resultType="com.zd.pojo.Users">
        select * from users
        <where>
            <choose>
                <when test="username!=null and username!=''">
                    username=#{username}
                </when>
                <when test="usersex!=null and usersex!=''">
                    usersex=#{usersex}
                </when>
                <otherwise>
                    userid=1
                </otherwise>
            </choose>
        </where>
    </select>

4 ．bind标签

bind标签允许我们在OGNL表达式以外创建一个变量，并可以将其绑定到当前的SQL语句中。一般应用于模糊查询，通过bind绑定通配符和查询值

    <!--模糊查询-->
    <select id="selectUserByName" parameterType="com.zd.pojo.Users">
        <bind name="likeName" value="'%'+_parameter+'%'"/>
        select * from users where username like #{likeName}
    </select>

参数类型为String时value必须写_parameter或者注解@Param绑定参数

5．set标签

set标签用在update语句中。借助if标签，可以只对有具体值得字段进行更新。set标签会自动添加set关键字，自动去掉最后一个if语句的多余的逗号

<!--选择更新-->
    <update id="updateUser" parameterType="com.zd.pojo.Users">
        update users
        <set>
            <if test="username!=null and username!=''">
                username=#{username},
            </if>
            <if test="usersex!=null and usersex!=''">
                usersex=#{usersex},
            </if>
        </set>
        where userid=#{userid}
    </update>

@Test
public void updateUser(){
    SqlSession sqlSession = MybatisUtil.getSqlSession();
    UserDao userDao = sqlSession.getMapper(UserDao.class);
    Users user=new Users(1,"Jack","man");
    userDao.updateUser(user);
    System.out.println(userDao.selectUsersById(1));
}

６．foreach标签

foreach可以迭代List、Set、Map或者数组对象　

迭代集合

类型： collection=“collection”
名称：List selectUserByIdCollection(@Param(“suibian”)List list);
collection=“suibian”

<!--查询用户id为1，2，3的用户-->
<!--select * from users where userid in (1,2,3)-->
    <select id="selectUserByIdCollection" resultType="com.zd.pojo.Users">
        select * from users where userid in
        <foreach collection="collection" item="userid" open="(" separator="," close=")">
            #{userid}
        </foreach>
    </select>

List<Users> selectUserByIdCollection(List<Integer> list);

迭代数组

类型： collection=“array”
名称 List selectUserByIdArray(@param(“suibian”)Integer[] arr);

    <!--查询用户id为1，2，3的用户-->
    <!--select * from users where userid in (1,2,3)  数组-->
    <select id="selectUserByIdArray" resultType="com.zd.pojo.Users">
        select * from users where userid in 
        <foreach collection="array" item="userid" open="(" separator="," close=")">
            #{userid}
        </foreach>
    </select>

迭代map

只能通过名称的方式绑定collection

//动态SQL foreach迭代Map
    int selectUserCountMap(@Param("map") Map<String,String> map);

    <!--根据给定的条件做计数  select count(*) from users where username=? and usersex=?-->
    <select id="selectUserCountMap" resultType="int">
        select count(*) from users where
        <foreach collection="map" separator="and" item="value" index="key">
            ${key}=#{value}
        </foreach>
    </select>

    @Test
    public void selectUserCountMap(){
        SqlSession sqlSession=MybatisUtil.getSqlSession();
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        Map<String,String> map=new HashMap<>();
        map.put("usersex","男");
        int i = userDao.selectUserCountMap(map);
        System.out.println("i = " + i);
    }

批量添加数据

//批量添加
    int insertUserForeach(List<Users> list);

    <!--动态批量添加 insert into users values (default,?,?),(default,?,?)-->
    <insert id="insertUserForeach">
        insert into users values
        <foreach collection="collection" item="users" separator=",">
            (default,#{users.username},#{users.usersex})
        </foreach>
    </insert>

    @Test
    public void insertUserForeach(){
        SqlSession sqlSession=MybatisUtil.getSqlSession();
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        Users users1=new Users("李白","男");
        Users users2=new Users("杨玉环","女");
        Users users3=new Users("武则天","女");
        List<Users> list=new ArrayList<>();
        list.add(users1);
        list.add(users2);
        list.add(users3);
        int i = userDao.insertUserForeach(list);
        System.out.println("共添加"+i+"条数据");
        sqlSession.commit();
    }