MyBatis动态SQL语句

if标签

        我们根据实体类的不同取值，使用不同的SQL语句来进行查询。比如在id如果不为空时可以根据 id查询，如果username不同空时还要加入用户名作为条件。这种情况在我们的多条件组合查询中经常会碰到。

mapper接口

public interface UserDao {

    //复杂条件查询
    public List<User> findByUser(User user);
}

mapper映射文件

<?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.by.dao.UserDao">
    <select id="findByUser" resultType="user">
        select * from user where 1=1
        <if test="username!=null and username != ''">
            and username=#{username}
        </if>
        <if test="birthday!=null">
            and birthday=#{birthday}
        </if>
        <if test="sex!=null and sex != ''">
            and sex=#{sex}
        </if>
        <if test="address!=null and address != ''">
            and address=#{address}
        </if>
    </select>
</mapper>

测试

@Test
    public void testFindAll(){
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        User user = new User();
        user.setSex("男");
        user.setAddress("香港");
        List<User> userList = userDao.findByUser(user);
        for(User u : userList){
            System.out.println(u);
        }
    }

where标签

        为了简化上面where 1=1的条件拼装，我们可以使用where标签将if标签代码块包起来，将1=1条件去掉。

若查询条件的开头为 “AND” 或 “OR”，where 标签会将它们去除。

mapper映射文件

<?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.by.dao.UserDao">
    <select id="findByUser" resultType="User">
        select * from user where and username=#{username}
        <!--where标签将if标签代码块包起来,并去掉开头 “AND” 或 “OR”-->
        <where>
            <if test="username!=null and username != ''">
                and username=#{username}
            </if>
            <if test="birthday!=null">
                and birthday=#{birthday}
            </if>
            <if test="sex!=null and sex != ''">
                and sex=#{sex}
            </if>
            <if test="address!=null and address != ''">
                and address=#{address}
            </if>
        </where>
    </select>
</mapper>

set标签

        set标签用于动态包含需要更新的列，并会删掉额外的逗号

mapper

public void updateByUser(User user);

mapper映射文件 

<update id="updateByUser" parameterType="user">
        update user
        <set>
            <if test="username!=null and username != '' ">
                username=#{username},
            </if>
            <if test="birthday!=null">
                birthday=#{birthday},
            </if>
            <if test="sex!=null and sex != '' ">
                sex=#{sex},
            </if>
            <if test="address!=null and address != '' ">
                address=#{address},
            </if>
        </set>
        where id=#{id}
    </update>

测试

@Test
    public void testUpdateByUser(){
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        User user = new User();
        user.setId(50);
        user.setBirthday(new Date());
        user.setAddress("加拿大");
        userDao.updateByUser(user);
    }

trim标签

        trim标签可以代替where标签、set标签

mapper

    //修改
    public void updateByUser2(User user);

mapper映射文件 

<update id="updateByUser2" parameterType="User">
        update user
        <!-- 增加SET前缀，忽略，后缀 -->
        <trim prefix="SET" suffixOverrides=",">
            <if test="birthday!=null">
                birthday=#{birthday},
            </if>
            <if test="sex!=null and username != '' ">
                sex=#{sex},
            </if>
            <if test="address!=null and username != '' ">
                address=#{address},
            </if>
        </trim>
        where id=#{id}
    </update>

foreach标签

        foreach标签的常见使用场景是集合进行遍历  

mapper

    //批量删除
    public void deleteUserByIds(@Param("ids") List<Integer> ids);
    //批量添加
    public void insertUsers(@Param("userList") List<User> userList);

mapper映射文件 

<delete id="deleteUserByIds" parameterType="list">
        delete from user where id in
        <!--
            collection：取值list、array、map、@Param("keyName")、对象的属性名
            item：循环取出的具体对象
            open：起始符
            separator：分隔符
            close：结束符
        -->
        <foreach collection="ids" item="id" open="(" close=")" separator=",">
            #{id}
        </foreach>
    </delete>

	<insert id="insertUsers" parameterType="list">
        INSERT INTO user(username,password,birthday,sex,address)
        VALUES
        <foreach collection ="userList" item="user" separator =",">
            (#{user.username},#{user.password},#{user.birthday},
            							#{user.sex},#{user.address})
        </foreach>
    </insert>

测试

 @Test
    public void testDeleteUserByIds(){
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        List<Integer> ids = new ArrayList();
        ids.add(50);
        ids.add(64);
        ids.add(67);
        userDao.deleteUserByIds(ids);
    }

	@Test
    public void testInsertUsers(){
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        long start = System.currentTimeMillis();
        List<User> userList = new ArrayList<>();
        for(int i = 0 ;i < 10000; i++) {
            User user = new User();
            user.setUsername("刘德华");
            user.setPassword("111");
            user.setBirthday(new Date());
            user.setSex("男");
            user.setAddress("香港");
            //userDao.insertUser(user);
            userList.add(user);
        }
        userDao.insertUsers(userList);
        long end = System.currentTimeMillis();
        System.out.println("一万条数据总耗时：" + (end-start) + "ms" );
        sqlSession.commit();
    }

sql标签

        sql元素标签用来定义可重复使用的SQL代码片段，使用时只需要用include元素标签引用即可

mapper

   //复杂条件查询
    public List<User> findByUser3(User user);

mapper映射文件 

 <!-- 定义SQL片段 -->
    <sql id="query_user_where">
        <if test="username!=null and username != ''">
            and username=#{username}
        </if>
        <if test="birthday!=null">
            and birthday=#{birthday}
        </if>
        <if test="sex!=null and sex != ''">
            and sex=#{sex}
        </if>
        <if test="address!=null and address != ''">
            and address=#{address}
        </if>
    </sql>
    <select id="findByUser3" resultType="User">
        select * from user
        <where>
            <include refid="query_user_where"></include>
        </where>
    </select>

测试

 @Test
    public void testFindAll3(){
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        User user = new User();
        user.setAddress("香港");
        user.setUsername("刘德华");
        List<User> userList = userDao.findByUser3(user);
        for(User u : userList){
            System.out.println(u);
        }
    }