MyBatis学习：动态SQL

最新推荐文章于 2024-04-10 09:24:28 发布

溪午闻璐

最新推荐文章于 2024-04-10 09:24:28 发布

阅读量1.2k

点赞数

分类专栏：数据库文章标签： sql 数据库

本文链接：https://blog.csdn.net/sssxlxwbwz/article/details/122840150

版权

数据库专栏收录该内容

21 篇文章 1 订阅

订阅专栏

MyBatis 的强大特性之一便是它的动态SQL。

一、if用法：

if 标签通常用于WHERE语句中，通过判断参数值来决定是否使用某个查询条件，它也经常用于UPDATE语句中判断是否更新某一个字段，还可以在INSERT 语句中用来判断是否插入某个字段的值。

1.1 WHERE条件中使用IF

假设现在有一个需求：实现一个用户管理高级查询功能，根据输入的条件去检索用户信息。这个功能还需要支持以下三种情况：当只输入用户名时，需要根据用户名进行模糊查询；当只输入邮箱时，根据邮箱进行完全匹配；当同时输入用户名和邮箱时，用这两个条件去查询匹配的用户。

以上问题可以使用if 标签来解决，代码如下：

在UserMapper.xml文件中添加如下代码：

	<select id="selectByUser" resultType="com.wyf.mybaties.model.SysUser">
		SELECT id,
		       user_name userName,
		       user_password userPassword,
		       user_email userEmail,
		       user_info userInfo,
		       head_img headImg,
		       create_time createTime
		FROM sys_user
		WHERE 1=1
		<if test="userName!=null and userName!=''">
			and user_name like concat ('%',#{userName},'%')
		</if>
		<if test="userEmail!=null and userEmail!=''">
			and user_email = #{userEmail}
		</if>
	</select>

判断条件property ! =null 或property == null：适用于任何类型的宇段，用于判断属性值是否为空。
判断条件property ！＝ ”或property ＝＝ ”：仅适用于String 类型的宇段，用于判断是否为空字符串。
and 和or：当有多个判断条件时，使用and 或or 进行连接。

在以上XML方法中，有两点需要注意：

注意SQL 中where 关键字后面的条件

本例中为WHERE 1=1, 由于两个条件都是动态的，所以如果没有1=1这个默认条件，当两个if 判断都不满足时，最后生成的SQL就会以where 结束，这样不符合SQL规范，因此会报错。加上1=1 这个条件就可以避免SQL 语法错误导致的异常。

注意条件中的and （或or)

and user name like concat （’ % ’，＃{userName }， ’ % ’ ）,这里的and （或 or）需要手动添加，当这部分条件拼接到where 1 = 1 后面时仍然是合法的SQL。因为有默认的1=1 这个条件，我们才不需要判断第一个动态条件是否需要加上and（或 or），因为这种情况下and （或or）是必须有的。

在UserMapper.xml对应的接口文件添加对应的方法：

    /**
     * 在Where条件中使用if
     */
    List<SysUser> selectByUser(SysUser sysUser);

测试代码：

   /**
     * if标签测试
     */
    @Test
    public void testSelectByUser(){
        //获取SqlSession
        SqlSession sqlSession = getSqlSession();
        try{
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            /**
             * 只查询用户名
             */
            SysUser sysUser = new SysUser();
            sysUser.setUserName("wyf");
            List<SysUser> userList = userMapper.selectByUser(sysUser);
            System.out.println(userList.get(0).getUserName());
            /**
             * 只查询用户邮箱时
             */
            sysUser = new SysUser();
            sysUser.setUserEmail ("test@mybatis.tk");
            userList = userMapper.selectByUser(sysUser);
            System.out.println(userList.get(0).getUserName());
            /**
             * 同时查询邮箱和用户
             */
            sysUser = new SysUser();
            sysUser.setUserName("wyf");
            sysUser.setUserEmail ("test@mybatis.tk");
            //查询结果为0，因为没有满足userName=wyf 且 userEmail = test@mybatis.tk
            userList = userMapper.selectByUser(sysUser);
            System.out.println(userList.size());
        }finally {
            sqlSession.close();
        }
    }

1.2 在UPDATE 更新列中使用if

若要实现这样一个需求：只更新有变化的字段。需要注意，更新的时候不能将原来有值但没有发生变化的字段更新为空或null。通过if 标签可以实现这种动态列更新。

一般情况下， MyBatis 中选择性更新的方法名会以Selective 作为后缀。

在UserMapper.xml文件中添加如下代码：

	<update id="updateByldSelective">
		update sys_user
		set
			<if test="userName!=null and userName!=''">
				user_name=#{userName},
			</if>
			<if test="userPassword!=null and userPassword!=''">
				user_password=#{userPassword},
			</if>
			<if test="userEmail!=null and userEmail!=''">
				user_email=#{userEmail},
			</if>
		    <if test="userInfo!=null and userInfo!=''">
				user_info=#{userInfo},
			</if>
			<if test="headImg!=null">
				head_img =#{headImg,jdbcType=BLOB},
			</if>
			<if test="createTime!=null">
				create_time =#{createTime,jdbcType=TIMESTAMP},
			</if>
		    id = #{id}
		WHERE id = #{id}
	</update>

这里要结合业务层的逻辑判断，确保最终产生的SQL语句没有语法错误。需要注意的有两
点：第一点是每个if 元素里面SQL语句后面的逗号；第二点就是where 关键字前面的id=#{id｝这个条件。以下两种情况可以帮助大家理解为什么需要关注这两点：

全部的查询条件都是null 或者空。

在全部查询条件为空时，如果有id = #{id｝这个条件，最终的SQL 如下：
update sys_user set id= #{id} where id= #{id}
如果没有这个条件，最终的SQL 如下:
update sys_user set where id = #{id}
这个SQL很明显是错误的， set 关键字后面没有内容，直接是where 关键宇，不符合SQL语句规范。

查询条件只有一个不是null 也不是空（假设是userName)

        如果有id = #{id｝这个条件，最终的SQL 如下:
                update sys_user set user_name= #{userName} , id = #{id} where id= #{id}
        如果没有这个条件，最终的SQL如下。
                update sys_user set user_name= # {userName}, where id= #{id}
where 关键宇前面直接是一个逗号，这个SQL语句也是错的。

从上面两种情况来看， id = #{id｝这个条件可以最大限度保证方法不出错。

在UserMapper.xml对应的接口文件中添加如下方法：

    /**
     * 根据主键更新
     * @param sysUser
     * @return
     */
    int updateByldSelective(SysUser sysUser);

测试代码：

    /**
     * if标签实现选择性更新
     */
    @Test
    public void testUpdateByIdSelective(){
        //获取SqlSession
        SqlSession sqlSession = getSqlSession();
        try{
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            //创建一个新的用户对象
            SysUser sysUser = new SysUser();
            //更新id=1004的用户
            sysUser.setId(1004L);
            sysUser.setUserEmail ("wyf@mybatis.com");
            //更新邮箱
            int res = userMapper.updateByldSelective(sysUser);
            System.out.println("影响行数："+res);
        }finally {
            sqlSession.commit();
            sqlSession.close();
        }
    }

1.3 在INSERT动态插入列中使用if

在数据库表中插入数据的时候，如果某一列的参数值不为空，就使用传入的值，如果传入参数为空，就使用数据库中的默认值（通常是空），而不使用传入的空值。使用if 就可以实现这种动态插入列的功能。

在UserMapper.xml文件中添加如下代码：

	<insert id="insert2" useGeneratedKeys="true" keyProperty="id">
		insert into sys_user(
		                     user_name,
		                     user_password,
		                     <if test="userEmail!=null and userEmail!=''">
		                     	user_email,
							 </if>
							 user_info,
		                     head_img,
		                     create_time)
		VALUES (#{userName},
		        #{userPassword},
		        <if test="userEmail!=null and userEmail!=''">
					#{userEmail},
				</if>
				#{userInfo},
				#{headImg,jdbcType=BLOB},
				#{createTime,jdbcType=TIMESTAMP})
	</insert>

在INSERT 中使用时要注意，若在列的部分增加if 条件，则values 的部分也要增加相同的if 条件，必须保证上下可以互相对应，完全匹配。

在对应的接口文件添加对应函数：

    /**
     * 插入数据
     * @param sysUser
     * @return
     */
    int insert2(SysUser sysUser);

编写测试代码：

    @Test
    public void Insert2Selective(){
        //获取SqlSession
        SqlSession sqlSession = getSqlSession();
        try{
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            SysUser sysUser = new SysUser();
            sysUser.setUserName("wyf-test");
            sysUser.setUserPassword("222222");
            sysUser.setUserInfo("wyf-test info");
            sysUser.setHeadImg(new byte[]{1,2,3}); //实际是一张图片
            sysUser.setCreateTime(new Date());
            //插入数据
            int res = userMapper.insert2(sysUser);
            System.out.println("数据插入执行结果："+res);
        }finally {
            //默认的sqlSessionFactory.openSession()是不自动提交的
            //因此不手动执行commit 也不会提交到数据库
            sqlSession.commit(); //提交数据插入
            sqlSession.close();
        }
    }

结果可以看到，插入的user_email为默认值：

二、choose(when,otherwise)

if 标签提供了基本的条件判断，但是它无法实现if. . . else、 if... else ... 的逻辑，要想实现这样的逻辑，就需要用到choose when otherwise 标签(注意多个when是顺序判断的，只要有一个满足了，后面就不会生效。就是java的if ..else语法)。

choose 元素中包含whe口和otherwise 两个标签，一个choose 中至少有一个when，有0 个或者l 个otherwise。

在己有的sys_user 表中，除了主键id 外，我们认为user_name （用户名）也是唯一的，所有的用户名都不可以重复。现在进行如下查询：当参数 id 有值的时候优先使用 id 查询，当id 没有值时就去判断用户名是否有值，如果有值就用用户名查询，如果用户名也没有值，就使SQL 查询无结果。

在UserMapper.xml 中添加如下SQL:

	<select id="selectByIdOrUserName" resultType="com.wyf.mybaties.model.SysUser">
		select id,
			   user_name userName,
			   user_password userPassword,
			   user_email userEmail,
			   user_info userInfo,
			   head_img headImg,
			   create_time createTime
		FROM sys_user
		WHERE 1=1
		<choose>
			<when test="id!=null">
				and id = #{id}
			</when>
			<when test="userName!=null and userName!=''">
				and user_name = #{userName}
			</when>
			<otherwise>
				AND 1=2
			</otherwise>
		</choose>
	</select>

在对应的接口文件中添加如下对应的方法：

    /**
     * choose
     * @param sysUser
     * @return
     */
    SysUser selectByIdOrUserName(SysUser sysUser);

测试代码：

    /**
     * choose标签测试
     */
    @Test
    public void selectByIdOrUserName(){
        //获取SqlSession
        SqlSession sqlSession = getSqlSession();
        try{
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            /**
             * id和name都存在
             */
            SysUser sysUser = new SysUser();
            sysUser.setId(1001L);
            sysUser.setUserName("test");
            SysUser user = userMapper.selectByIdOrUserName(sysUser);
            System.out.println(user.getUserName());
            /**
             * 当没有id时
             */
            sysUser.setId(null);
            user = userMapper.selectByIdOrUserName(sysUser);
            System.out.println(user.getUserName());
            /**
             * 当id和name都没有时
             */
            sysUser.setUserName(null);
            user = userMapper.selectByIdOrUserName(sysUser);
            System.out.println(user.getUserName());
        }finally {
            sqlSession.close();
        }
    }

三、where、set以及trim

3.1 where

where 标签的作用：如果该标签包含的元素中有返回值，就插入一个where；如果where后面的字符串是以AND 和OR 开头的，就将它们剔除。

前面我们在where中使用if，需要写WHERE 1=1,以确保SQL语句的正确，但是这样太麻烦而且不够优雅。我们修改上面的selectByUser映射语句：

    <select id="selectByUser" resultType="com.wyf.mybaties.model.SysUser">
        select id,
            user_name userName,
            user_password userPassword,
            user_email userEmail,
            user_info userInfo,
            head_img headImg,
            create_time createTime
        from sys_user
        <where>
		    <if test="userName!=null and userName!=''">
		    	and user_name like concat ('%',#{userName},'%')
	    	</if>
	    	<if test="userEmail!=null and userEmail!=''">
	    		and user_email = #{userEmail}
	    	</if>
        </where>
    </select>

当 if 条件都不满足的时候， where 元素中没有内容，所以在SQL 中不会出现where，也
就不存在1.1节中SQL错误的问题。

3.2 set 用法

set 标签的作用：如果该标签包含的元素中有返回值，就插入一个set；如果set字符串是以逗号结尾的，就将这个逗号剔除。

修改1.2节UserMapper. xml 中的 updateByidSelective 方法：

    <update id="updateByIdSelective">
        update sys_user
        <set>
            <if test="userName != null and userName != ''">
                user_name = #{userName},
            </if>
            <if test="userPassword != null and userPassword != ''">
                user_password = #{userPassword},
            </if>
            <if test="userEmail != null and userEmail != ''">
                user_email = #{userEmail},
            </if>
            <if test="userInfo != null and userInfo != ''">
                user_info = #{userInfo},
            </if>
            <if test="headImg != null">
                head_img = #{headImg, jdbcType=BLOB},
            </if>
            <if test="createTime != null">
                create_time = #{createTime, jdbcType=TIMESTAMP},
            </if>
            id = #{id},
        </set>
        where id = #{id}
    </update>

在set 标签的用法中， SQL后面的逗号没有问题了，但是如果set 元素中没有内容，照样会出现SQL 错误，所以为了避免错误产生，类似id = #{id｝这样必然存在的赋值仍然有保留的必要。

3.3 trim用法

trim的功能最强大，where 和 set 标签的功能都可以用 trim 标签来实现，并且在底层就是通过TrimSqlNode 实现的。

trim标签有如下属性

prefix ：当trim 元素内包含内容时，会给内容增加prefix 指定的前缀。
prefixOverrides ：当trim 元素内包含内容时，会把内容中匹配的前缀字符串去掉。
suffix ：当trim 元素内包含内容时，会给内容增加suffix 指定的后缀。
suffixOverrides ：当trim 元素内包含内容时，会把内容中匹配的后缀字符串去掉。

如，用trim实现where

<trim prefix="where" prefixOverrides="and |or ">
	...
</trim>

四、foreach 用法

foreach可以方便地实现in集合。

foreach 可以对数组、 Map 或实现了Iterable 接口（如 List、 Set）的对象进行遍历。数组在处理时会转换为List对象，因此foreach遍历的对象可以分为两大类： Iterable类型和 Map 类型。

4.1 Foreach实现in集合

Foreach 实现in 集合（或数组）是最简单和常用的一种情况。如下所示：

	<select id="selectByidList" resultType="com.wyf.mybaties.model.SysUser">
		SELECT id,
			   user_name userName,
			   user_password userPassword,
			   user_email userEmail,
			   user_info userInfo,
			   head_img headImg,
			   create_time createTime
		FROM sys_user
		WHERE id IN
		<foreach collection="list" open="(" close=")" separator="," item="id" index="i">
			#{id}
		</foreach>
	</select>

在对应接口文件中添加对应的方法：

    /**
     * 根据用户 id 集合查询
     *
     * @param idList
     * @return
     */
    List<SysUser> selectByidList(List<Long> idList);

foreach 包含以下属性:

collection：必填，值为要迭代循环的属性名。这个属性值的情况有很多。
item：变量名，值为从迭代对象中取出的每一个值。
index：索引的属性名，在集合数组情况下值为当前索引值，当选代循环的对象是Map类型时，这个值为Map 的key （键值）。
open：整个循环内容开头的字符串。
close ：整个循环内容结尾的字符串。
separator ：每次循环的分隔符。

Collection属性的设置规则：

只有一个参数时：
- 集合用collection，List可以用list
- 数组用array
- 推荐使用＠Param 来指定参数的名字，这时可以设置成我们自定义地名字
- 可以使用默认值＿parameter
方法有多个参数时：使用＠Param注解来指定名字

测试代码：

    @Test
    public void SelectByIDList(){
        //获取SqlSession
        SqlSession sqlSession = getSqlSession();
        try{
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            List<Long> idList = new ArrayList<>();
            idList.add(1001L);
            idList.add(1004L);
            idList.add(1005L);
            
            List<SysUser> sysUserList = userMapper.selectByidList(idList);
            System.out.println(sysUserList.size());
        }finally {
            sqlSession.close();
        }
    }

4.2 foreach 实现批量插入

如果数据库支持批量插入，就可以通过foreach 来实现。

	<insert id="insertList">
		insert into sys_user(user_name,
		                     user_password,
		                     user_email,
							 user_info,
		                     head_img,
		                     create_time)
		values 
		<foreach collection="list" item="user" separator=",">
			(#{user.userName},
			 #{user.userPassword},
			 #{user.userEmail},
		     #{user.userInfo},
			 #{user.headImg, jdbcType=BLOB},
			 #{user.createTime , jdbcType=TIMESTAMP})
		</foreach>

	</insert>

通过 item 指定了循环变量名后，在引用值的时候使用的是“属性．属性”的方式，如

user .userName。

在对应接口文件添加相应方法：

    /**
     * 批量插入用户信息
     * @param userList
     * @return
     */
    int insertList(List<SysUser> userList);

测试代码：

    /**
     * 批量插入
     */
    @Test
    public void InsertList(){
        //获取SqlSession
        SqlSession sqlSession = getSqlSession();
        try{
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            List<SysUser> userList =new ArrayList<SysUser>();
            for(int i=0;i<10;i++){
                SysUser user = new SysUser();
                user.setUserName("test"+i);
                user.setUserEmail("test"+i+"@mybatis.com");
                user.setUserPassword("12345"+i);
                userList.add(user);
            }
            int res = userMapper.insertList(userList);
            System.out.println(res);
        }finally {
            sqlSession.commit();
            sqlSession.close();
        }
    }

4.3 foreach 实现动态UPDATE

在xml映射文件中添加如下代码：

	<update id="updateByMap">
		update sys_user
        set
		<foreach collection="_parameter" item="val" index="key" separator=",">
			${key} = #{val}
		</foreach>
		where id = #{id}
	</update>

这里的key 作为列名，对应的值作为该列的值，通过 foreach 将需要更新的字段拼接在
SQL 语句中。

在对应的接口文件中添加相对应的方法：

    /**
     *通过map更新列
     *
     * ＠param map
     * ＠return
    */
    int updateByMap(Map<String,Object> map);

测试代码：

    /**
     * 批量更新
     */
    @Test
    public void UpdateByMap(){
        //获取SqlSession
        SqlSession sqlSession = getSqlSession();
        try{
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            Map<String,Object> map = new HashMap<String,Object>();
            //查询条件，同样也是更新字段，必须存在
            map.put("id",1010L);
            //需要更新的其他字段
            map.put("user_email","test@mybatis.com");
            map.put("user_password",12345678);
            //更新数据
            int res = userMapper.updateByMap(map);
            SysUser user = userMapper.selectById(1010L);
            System.out.println(user.getUserEmail());
        }finally {
            sqlSession.commit();
            sqlSession.close();
        }
    }

溪午闻璐

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
MyBatis学习：动态SQL

MyBatis 的强大特性之一便是它的动态SQL。一、if用法：if 标签通常用于WHERE语句中，通过判断参数值来决定是否使用某个查询条件，它也经常用于UPDATE语句中判断是否更新某一个字段，还可以在INSERT 语句中用来判断是否插入某个字段的值。1.1 WHERE条件中使用IF假设现在有一个需求：实现一个用户管理高级查询功能，根据输入的条件去检索用户信息。这个功能还需要支持以下三种情况：当只输入用户名时，需要根据用户名进行模糊查询；当只输入邮箱时...
复制链接

扫一扫