MyBatis与动态SQL查询

冷不防 2022-11-27 03:26 345阅读 0赞

MyBatis的强大特性之一便是它的动态SQL，现在使用的MyBatis的动态SQL在XML中支持以下几种标签：

**if用法**

*  关于if的用法有如下几种：
 *  在where条件中使用if
 *  在update更新列中使用if
 *  在insert动态插入列中使用if

**在where条件中使用if：**

数据还是之前的数据，假设现在有一个新的需求：实现一个用户管理的高级查询功能，根据输入的条件去检索用户信息。当只输入用户名时，需要根据用户名进行模糊查询；当只输入密码时，根据密码进行匹配，当同时输入这两个条件的时候，用这两个条件去查询匹配的用户。操作方法和之前一样，还是之前的三个步骤。

package Interface;
    
    import pojo.User;
    
    public interface UserMapper {
        //高级查询：当输入用户名时进行模糊查询，当输入密码时进行完全匹配，当输入用户名和密码时，两个条件一起匹配
        List<User> selectByUserNameOrUserPassword(User user);
    
    }

<?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="Interface.UserMapper">
        
        <select id="selectByUserNameOrUserPassword" resultType="pojo.User">
            select id,user_name userName,user_password userPassword,
            user_email userEmail,user_info userInfo,head_Img headImg,create_time createTime
            from t_user
            where 1 = 1
            <if test="userName != null and userName != ''">
                and user_name like concat('%',#{userName},'%')
            </if>
            <if test="userPassword != null and userPassword != ''">
                and user_password = #{userPassword}
            </if>
        </select>
    </mapper>

@Test
        public void testSelectByUserNameOrUserPassword() {
            SqlSession sqlSession = getSqlSession();
            try {
                UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
                User query = new User();
                //只查询用户名时
                query.setUserName("李");
                List<User> userList = userMapper.selectByUserNameOrUserPassword(query);
                System.out.println("当使用用户名查询时：");
                for (User user : userList)
                {
                    System.out.println(user);
                }
                //只查询密码时
                query = new User();
                query.setUserPassword("999999");
                userList = userMapper.selectByUserNameOrUserPassword(query);
                System.out.println("当使用密码查询时：");
                for (User user : userList)
                {
                    System.out.println(user);
                }
                //同时查询用户名和邮箱时
                query = new User();
                query.setUserName("李明");
                query.setUserPassword("999999");
                userList = userMapper.selectByUserNameOrUserPassword(query);
                System.out.println("当同时使用用户名和密码查询时：");
                for (User user : userList)
                {
                    System.out.println(user);
                }
            } finally {
                sqlSession.close();
            }
        }

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppYW9tdWJhaQ_size_16_color_FFFFFF_t_70][]

观察上面的userMapper.xml文件，需要说明的是：if标签有一个必填的属性test，test的属性值是一个符合OGNL（Object-Graph Navigation Language，对象图导航语言）要求的判断表达式，表达式的结果可以是true或false，除此之外所有的非0值都为true，只有0为false。为了方便理解，在表达式中，建议只使用true或false作为结果。现在我们来看看OGNL表达式中的内容解读：

*  判断条件property != null 或 property == null : 适用于任何类型的字段，用于判断属性值是否为空。
 *  判断条件property != '' 或 property == '' : 仅仅适用于String类型的字段，用于判断是否为空字符串。
 *  and和or : 当有多个判断条件时，使用and和or进行连接，嵌套的判断可以使用小括号分组，and相当于Java中的与（&&），or相当于Java中的或（||）。

我们所写的两个条件的属性类型都是String，对字符串的判断和Java中的判断类似，既要判断字段是否为null，又要判断是否为空。除此之外，我们还需要特别注意SQL代码中的where 1 = 1，由于我们设置的两个条件都是动态的，所以如果没有1 = 1这个默认条件，当两个if的判断都不满足时，最后生成的SQL代码会以where来结束，不符合SQL规范，会报错。但是当加上1 = 1这个条件就可以避免SQL语法错误导致的异常。

**在update更新列中使用if**

在日常工作中，我们常常需要对某条数据只做部分的更新，即更新是选择性的。也就是更新的时候不能将原来有值但没有发生变化的字段不能更新为空或null。操作方法也是一样。

package Interface;
    
    import pojo.User;
    
    public interface UserMapper {
        //在update中使用if进行动态查询
        //一般情况下，MyBatis中选择性更新的方法会以Selective作为后缀
        int updateByIdSelective(User user);
    
    }

<?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="Interface.UserMapper">
        
        <update id="updateByIdSelective">
            update t_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 and headImg != ''">
                head_img = #{headImg},
            </if>
            <if test="createTime != null and createTime != ''">
                create_time = #{createTime,jdbcType = TIMESTAMP},
            </if>
            id = #{id}
            where id = #{id}
        </update>
    </mapper>

以上XML文件的意思就是我们可以更新User的某个用户的任何字段的信息，也就是说不用像之前那样更新某条信息的时候需要将所有字段写上，即使它本就不需要更新。需要注意的是：每个if元素里面SQL语句后面都有逗号，除此之外，where的前后都有id = \#\{id\}语句，以下这两点可以帮助我们更好的理解这两个注意点：

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

如果where之前有id = \#\{id\}这个条件，那么当所有的查询条件都不满足时，最终的SQL语句是

<update id="updateByIdSelective">
            update t_user
            set
            id = #{id}
            where id = #{id}
        </update>

如果没有这个条件，最终的SQL语句就明显是错误的。这就是where之前的那个id = \#\{id\}的重要性。

*  查询条件只有一个不是null也不是空

如果where之前有id = \#\{id\}这个条件，那么当所有的查询条件都不满足时，最终的SQL语句是

<update id="updateByIdSelective">
            update t_user
            set
            user_name = #{userName},
            id = #{id}
            where id = #{id}
        </update>

如果没有这个条件，最终的SQL语句是这样的：

<update id="updateByIdSelective">
            update t_user
            set
            user_name = #{userName},
            where id = #{id}
        </update>

很明显这是错误的，因为在关键字where之前出现了逗号。从上面两种情况来看，id = \#\{id\}这个条件可以最大限度保证方法不出错。

@Test
        public void testUpdateByIdSelective() {
            SqlSession sqlSession = getSqlSession();
            try {
                UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
                User user = new User();
                //更新1009用户
                user.setId(1003L);
                //我们只修改用户名和简介，其他保持不变
                user.setUserName("一零零三");
                user.setUserInfo("一零零三");
                int res = userMapper.updateByIdSelective(user);
                if (res > 0) {
                    System.out.println(res);
                    System.out.println("更新成功！");
                }
            } finally {
                sqlSession.commit();
                sqlSession.close();
            }
        }

![20200813181144312.png][]

![20200813181211813.png][]

**在insert动态插入列中使用if**

在数据库表中插入数据的时候，如果某一列的参数值不为空，就是用传入的值，如果传入的参数为空，就使用数据库中的默认值，而不使用传入的空值。首先，我们修改t\_user表中user\_email列的默认值。

ALTER TABLE `t_user` 
    MODIFY COLUMN `user_email` VARCHAR(50) NULL DEFAULT 'study@study' COMMENT '邮箱'
    AFTER `user_password`;

然后我们再写一个新的insert方法insertUser()，之后再在UserMapper.xml文件中写上SQL语句。

package Interface;
    
    import pojo.User;
    
    public interface UserMapper {
        //在insert中使用if
        int insertUser(User user);
    
    }

<?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="Interface.UserMapper">
        
        <insert id="insertUser" useGeneratedKeys="true" keyProperty="id">
            insert into t_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>
    </mapper>

之后编写测试文件，进行该方法的测试：

@Test
        public void testInsertUser() {
            SqlSession sqlSession = getSqlSession();
            try {
                UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
                User user = new User();
                user.setUserName("999");
                user.setUserPassword("789999");
                user.setUserInfo("九九九");
                user.setHeadImg(new byte[]{1,2,3});
                user.setCreateTime(new Date());
                int res = userMapper.insertUser(user);
                if(res > 0) {
                    System.out.println(user.getId());
                    System.out.println("插入成功！");
                }
            } finally {
                sqlSession.commit();
                sqlSession.close();
            }
        }

![20200813183011933.png][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppYW9tdWJhaQ_size_16_color_FFFFFF_t_70 1][]

此时，插入便成功了，但是为什么id是1006呢，因为我们之前进行删除操作时已经将id为1005的用户删除掉了。又因为id号我们设置的自增，自增时它是不会考虑到已删除的元素的，所以此时的id号是1006。

**choose用法**

if标签能够提供基本的条件判断，但是无法满足if/else这种的逻辑，要想实现这样的逻辑就需要用到choose标签。choose元素中包含when和otherwise两个标签，一个choose中至少有一个when，有0个或1个otherwise。现在我们假设一个查询场景：当传入的参数有id时，就使用id查询，当传入的参数中没有id时我们再看有没有用户名，如果有，则按照用户名进行查询；如果用户id和用户名都没有值时，我们就判定查询无果。

package Interface;
    
    import pojo.User;
    
    public interface UserMapper {
        //choose动态查询，若有用户ID，就根据用户ID查询，若没有，就根据用户名查询
        User selectUserByIdOrName(User user);
    
    }

<?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="Interface.UserMapper">
        
        <select id="selectUserByIdOrName" resultType="pojo.User">
            select id,user_name userName,user_password userPassword,
            user_email userEmail,user_info userInfo,head_img headImg,create_time createTime
            from t_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>
    </mapper>

@Test
        public void testSelectUserByIdOrName() {
            SqlSession sqlSession = getSqlSession();
            try {
                UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
                User query = new User();
                //只查询用户名时
                query.setId(1001L);
                query.setUserName("张三");
                User user = userMapper.selectUserByIdOrName(query);
                System.out.println("只查询用户名：");
                System.out.println(user);
                //当没有id时
                query.setId(null);
                user = userMapper.selectUserByIdOrName(query);
                System.out.println("当没有id，按照用户名查询：");
                System.out.println(user);
                //当Id和name都为空时
                query.setUserName(null);
                user = userMapper.selectUserByIdOrName(query);
                System.out.println("当id和用户名都没有时：");
                System.out.println(user);
            } finally {
                sqlSession.close();
            }
        }

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppYW9tdWJhaQ_size_16_color_FFFFFF_t_70 2][]

**where、set、trim用法**

where标签的作用：如果该标签包含的元素中有返回值，就插入一个where；如果where后面的字符串是以and和or开头的，就将它们剔除。以之前说过的selectByUserNameOrUserPassword()方法来举例，我们依然是使用用户名和密码来查询，只不过这次我们使用where，不使用if了。

<?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="Interface.UserMapper">
        <select id="selectByUserNameOrUserPassword2" resultType="pojo.User">
            select id,user_name userName,user_password userPassword,
            user_email userEmail,user_info userInfo,head_Img headImg,create_time createTime
            from t_user
            <where>
                <if test="userName != null and userName != ''">
                    and user_name like concat('%',#{userName},'%')
                </if>
                <if test="userPassword != null and userPassword != ''">
                    and user_password = #{userPassword}
                </if>
            </where>
        </select>
    </mapper>

当if条件都不满足的时候，where元素中没有内容，所以在SQL中不会出现where。如果if条件满足，where元素的内容就是以and开头的条件，where会自动去除掉开头的and，这也能保证where条件正确。同时也不会出现where 1 = 1的情况。

@Test
        public void testSelectByUserNameOrUserPassword2() {
            SqlSession sqlSession = getSqlSession();
            try {
                UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
                User query = new User();
                //只查询用户名时
                query.setUserName("李");
                List<User> userList = userMapper.selectByUserNameOrUserPassword2(query);
                System.out.println("当使用用户名查询时：");
                for (User user : userList)
                {
                    System.out.println(user);
                }
                //只查询密码时
                query = new User();
                query.setUserPassword("999999");
                userList = userMapper.selectByUserNameOrUserPassword2(query);
                System.out.println("当使用密码查询时：");
                for (User user : userList)
                {
                    System.out.println(user);
                }
                //同时查询用户名和邮箱时
                query = new User();
                query.setUserName("李明");
                query.setUserPassword("999999");
                userList = userMapper.selectByUserNameOrUserPassword2(query);
                System.out.println("当同时使用用户名和密码查询时：");
                for (User user : userList)
                {
                    System.out.println(user);
                }
            } finally {
                sqlSession.close();
            }
        }

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppYW9tdWJhaQ_size_16_color_FFFFFF_t_70 3][]

**set用法**

set标签的作用：如果该标签包含的元素中有返回值，就插入一个set；如果set后面的字符串是以逗号结尾的，就将这个逗号剔除。其实，就是我们之前说的在更新列中使用if的另外一种方法。这次我们不重新写方法了，直接使用之前的那个updateByIdSelective()方法，这次我们不使用if，使用set了。

<?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="Interface.UserMapper">
        <update id="updateByIdSelective">
            update t_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 and headImg != ''">
                    head_img = #{headImg},
                </if>
                <if test="createTime != null and createTime != ''">
                    create_time = #{createTime,jdbcType = TIMESTAMP},
                </if>
                id = #{id},
            </set>
            where id = #{id}
        </update>
    </mapper>

其实在update的时候使用set标签并不比在select的时候使用where标签效果好，为什么呢？因为在set标签的用法中，虽然解决了SQL语句后面的逗号的问题，但是如果set元素中没有内容，依然会出现SQL错误，所以为了避免错误发生，类似id = \#\{id\}这样必然存在的赋值仍然有保留的必要。所以使用时仍然需要注意。

**trim用法**

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

where标签对应trim的实现为：

<?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="Interface.UserMapper">
        <select id="selectByUserNameOrUserPassword2" resultType="pojo.User">
            select id,user_name userName,user_password userPassword,
            user_email userEmail,user_info userInfo,head_Img headImg,create_time createTime
            from t_user
            <trim prefix="WHERE" prefixOverrides="AND |OR ">
                <if test="userName != null and userName != ''">
                    and user_name like concat('%',#{userName},'%')
                </if>
                <if test="userPassword != null and userPassword != ''">
                    and user_password = #{userPassword}
                </if>
            </trim>
        </select>
    </mapper>

注意AND和OR之后的空格不能省略。

set标签对应的trim的实现为：

<?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="Interface.UserMapper">
        <update id="updateByIdSelective">
            update t_user
            <trim prefix="SET" suffixOverrides=",">
                <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 and headImg != ''">
                    head_img = #{headImg},
                </if>
                <if test="createTime != null and createTime != ''">
                    create_time = #{createTime,jdbcType = TIMESTAMP},
                </if>
                id = #{id},
            </trim>
            where id = #{id}
        </update>
    </mapper>

trim标签有以下属性：

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

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppYW9tdWJhaQ_size_16_color_FFFFFF_t_70]: /images/20221124/e50d6300142541dc9eb052aa9571da6f.png
[20200813181144312.png]: /images/20221124/8d70e945b5af44cd99fa9f978faca86b.png
[20200813181211813.png]: /images/20221124/9501d69412f14b08af5e5ce2c24e9954.png
[20200813183011933.png]: /images/20221124/231050292d53470dbd0bf06070d54924.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppYW9tdWJhaQ_size_16_color_FFFFFF_t_70 1]: /images/20221124/000d360aecd64a6c9cdf3dd5cfa437bb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppYW9tdWJhaQ_size_16_color_FFFFFF_t_70 2]: /images/20221124/6cca1c36f8914b008f8a1a14870dcbd4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppYW9tdWJhaQ_size_16_color_FFFFFF_t_70 3]: /images/20221124/8a13cbecd7cb49319c1d1888b18b4236.png