MyBatis-15MyBatis动态SQL之【bind】

举报
小工匠 发表于 2021/09/11 00:34:40 2021/09/11
【摘要】 概述用法示例 1.增加接口方法2.配置动态SQL3.单元测试 概述 bind标签可以使用OGNL表达式创建一个变量并将其绑定到上下文中。 用法 我们用之...

概述

bind标签可以使用OGNL表达式创建一个变量并将其绑定到上下文中。

用法

我们用之前的一个例子作为讲解

<select id="selectSysUsersAdvancedWithWhere" resultType="com.artisan.mybatis.xml.domain.SysUser">
        SELECT
            a.id,
            a.user_name userName,
            a.user_password userPassword,
            a.user_email userEmail,
            a.user_info userInfo,
            a.head_img headImg,
            a.create_time createTime
        FROM
            sys_user a
        <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>
  
 
    
  
  • 1
    • 
  
  • 2
    • 
  
  • 3
    • 
  
  • 4
    • 
  
  • 5
    • 
  
  • 6
    • 
  
  • 7
    • 
  
  • 8
    • 
  
  • 9
    • 
  
  • 10
    • 
  
  • 11
    • 
  
  • 12
    • 
  
  • 13
    • 
  
  • 14
    • 
  
  • 15
    • 
  
  • 16
    • 
  
  • 17
    • 
  
  • 18
    • 
  
  • 19
    • 
  
  • 20
    •

使用concat函数连接字符串，在MySQL中，这个函数支持多个参数，但是在Oracle中只支持两个参数。 由于不同数据库之间的语法差异，如果更换了数据库，有些SQL语句可能就需要重写。 针对这种情况，可以使用bind标签来避免由于更换数据库带来的一些麻烦。 我们将上面的语句改为bind方式，如下

<select id="selectSysUserByAdvancedCondition" resultType="com.artisan.mybatis.xml.domain.SysUser">
        SELECT
            a.id,
            a.user_name userName,
            a.user_password userPassword,
            a.user_email userEmail,
            a.user_info userInfo,
            a.head_img headImg,
            a.create_time createTime
        FROM
            sys_user a
        <where>
            <if test="userName != null and userName != '' ">
                <!-- and user_name like concat('%',#{userName},'%') -->
                <bind name="userNameLike" value=" '%' + userName + '%' "/>
                    and user_name like #{userNameLike}
            </if>
            <if test="userEmail != null and userEmail != '' ">
                and user_email = #{userEmail}
            </if>
        </where>
    </select>   
  
 
    
  
  • 1
    • 
  
  • 2
    • 
  
  • 3
    • 
  
  • 4
    • 
  
  • 5
    • 
  
  • 6
    • 
  
  • 7
    • 
  
  • 8
    • 
  
  • 9
    • 
  
  • 10
    • 
  
  • 11
    • 
  
  • 12
    • 
  
  • 13
    • 
  
  • 14
    • 
  
  • 15
    • 
  
  • 16
    • 
  
  • 17
    • 
  
  • 18
    • 
  
  • 19
    • 
  
  • 20
    • 
  
  • 21
    • 
  
  • 22
    •

bind标签的两个属性都是不选项，name为绑定到上下文的变量名，value为OGNL表达式，创建一个bind标签后，就可以在下面直接使用了。 使用bind拼接字符串不仅可以避免因更换数据库而修改SQL，也能预防SQL注入。

示例

1.增加接口方法

/**
     * 
     * 
     * @Title: selectSysUserByAdvancedCondition
     * 
     * @Description: 演示bind用法
     * 
     * @param sysUser
     * @return
     * 
     * @return: List<SysUser>
     */
    List<SysUser> selectSysUserByAdvancedCondition(SysUser sysUser);
  
 
    
  
  • 1
    • 
  
  • 2
    • 
  
  • 3
    • 
  
  • 4
    • 
  
  • 5
    • 
  
  • 6
    • 
  
  • 7
    • 
  
  • 8
    • 
  
  • 9
    • 
  
  • 10
    • 
  
  • 11
    • 
  
  • 12
    • 
  
  • 13
    •

2.配置动态SQL

<select id="selectSysUserByAdvancedCondition" resultType="com.artisan.mybatis.xml.domain.SysUser">
        SELECT
            a.id,
            a.user_name userName,
            a.user_password userPassword,
            a.user_email userEmail,
            a.user_info userInfo,
            a.head_img headImg,
            a.create_time createTime
        FROM
            sys_user a
        <where>
            <if test="userName != null and userName != '' ">
                <!-- and user_name like concat('%',#{userName},'%') -->
                <bind name="userNameLike" value=" '%' + userName + '%' "/>
                    and user_name like #{userNameLike}
            </if>
            <if test="userEmail != null and userEmail != '' ">
                and user_email = #{userEmail}
            </if>
        </where>
    </select>   
  
 
    
  
  • 1
    • 
  
  • 2
    • 
  
  • 3
    • 
  
  • 4
    • 
  
  • 5
    • 
  
  • 6
    • 
  
  • 7
    • 
  
  • 8
    • 
  
  • 9
    • 
  
  • 10
    • 
  
  • 11
    • 
  
  • 12
    • 
  
  • 13
    • 
  
  • 14
    • 
  
  • 15
    • 
  
  • 16
    • 
  
  • 17
    • 
  
  • 18
    • 
  
  • 19
    • 
  
  • 20
    • 
  
  • 21
    • 
  
  • 22
    •

3.单元测试

@Test
    public void selectSysUserByAdvancedConditionTest() {
        logger.info("selectSysUserByAdvancedConditionTest");
        // 获取SqlSession
        SqlSession sqlSession = getSqlSession();

        List<SysUser> userList = null;
        try {
            // 获取UserMapper接口
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

            logger.info("===========1.当用户只输入用户名时，需要根据用户名模糊查询===========");
            // 模拟前台传参 1.当用户只输入用户名时，需要根据用户名模糊查询
            SysUser sysUser = new SysUser();
            sysUser.setUserName("ad");
            // 调用selectSysUserByAdvancedCondition,根据查询条件查询用户
            userList = userMapper.selectSysUserByAdvancedCondition(sysUser);
            // 根据数据库sys_user表中的记录,可以匹配到admin, 期望userList不为空
            Assert.assertNotNull(userList);
            // 根据查询条件，期望只有1条数据
            Assert.assertTrue(userList.size() == 1);
            logger.info("userList:" + userList);

            // 为了测试 匹配多条记录的情况，我们将id=1001这条数据的userName 由test 改为artisan
            sysUser.setUserName("i");
            // 调用selectSysUserByAdvancedCondition,根据查询条件查询用户
            userList = userMapper.selectSysUserByAdvancedCondition(sysUser);
            // 根据数据库sys_user表中的记录,可以匹配到admin和artisan, 期望userList不为空
            Assert.assertNotNull(userList);
            // 根据查询条件，期望只有2条数据
            Assert.assertTrue(userList.size() == 2);

            logger.info("userList:" + userList);

            logger.info("===========2.当用户只输入邮箱使，根据邮箱进行完全匹配===========");
            // 模拟前台传参 2.当用户只输入邮箱使，根据邮箱进行完全匹配
            sysUser.setUserEmail("admin@artisan.com");
            userList = userMapper.selectSysUsersAdvanced(sysUser);
            Assert.assertNotNull(userList);
            Assert.assertTrue(userList.size() == 1);
            logger.info(userList);

            sysUser.setUserEmail("1admin@artisan.com");
            userList = userMapper.selectSysUserByAdvancedCondition(sysUser);
            Assert.assertTrue(userList.size() == 0);

            logger.info("===========3.当用户同时输入用户名和密码时，用这两个条件查询匹配的用户===========");

            // 模拟组合查询条件,存在记录的情况
            sysUser.setUserName("i");
            sysUser.setUserEmail("admin@artisan.com");
            userList = userMapper.selectSysUserByAdvancedCondition(sysUser);
            Assert.assertNotNull(userList);
            Assert.assertEquals("admin@artisan.com", sysUser.getUserEmail());
            Assert.assertTrue(userList.size() == 1);
            logger.info(userList);

            logger.info("===========4.当用户同时输入无法匹配的用户名和密码===========");
            // 模拟组合查询条件,不存在记录的情况
            sysUser.setUserName("x");
            sysUser.setUserEmail("admin@artisan.com");
            userList = userMapper.selectSysUserByAdvancedCondition(sysUser);
            Assert.assertTrue(userList.size() == 0);
            logger.info(userList);

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            sqlSession.close();
            logger.info("sqlSession close successfully ");
        }
    }
  
 
    
  
  • 1
    • 
  
  • 2
    • 
  
  • 3
    • 
  
  • 4
    • 
  
  • 5
    • 
  
  • 6
    • 
  
  • 7
    • 
  
  • 8
    • 
  
  • 9
    • 
  
  • 10
    • 
  
  • 11
    • 
  
  • 12
    • 
  
  • 13
    • 
  
  • 14
    • 
  
  • 15
    • 
  
  • 16
    • 
  
  • 17
    • 
  
  • 18
    • 
  
  • 19
    • 
  
  • 20
    • 
  
  • 21
    • 
  
  • 22
    • 
  
  • 23
    • 
  
  • 24
    • 
  
  • 25
    • 
  
  • 26
    • 
  
  • 27
    • 
  
  • 28
    • 
  
  • 29
    • 
  
  • 30
    • 
  
  • 31
    • 
  
  • 32
    • 
  
  • 33
    • 
  
  • 34
    • 
  
  • 35
    • 
  
  • 36
    • 
  
  • 37
    • 
  
  • 38
    • 
  
  • 39
    • 
  
  • 40
    • 
  
  • 41
    • 
  
  • 42
    • 
  
  • 43
    • 
  
  • 44
    • 
  
  • 45
    • 
  
  • 46
    • 
  
  • 47
    • 
  
  • 48
    • 
  
  • 49
    • 
  
  • 50
    • 
  
  • 51
    • 
  
  • 52
    • 
  
  • 53
    • 
  
  • 54
    • 
  
  • 55
    • 
  
  • 56
    • 
  
  • 57
    • 
  
  • 58
    • 
  
  • 59
    • 
  
  • 60
    • 
  
  • 61
    • 
  
  • 62
    • 
  
  • 63
    • 
  
  • 64
    • 
  
  • 65
    • 
  
  • 66
    • 
  
  • 67
    • 
  
  • 68
    • 
  
  • 69
    • 
  
  • 70
    • 
  
  • 71
    • 
  
  • 72
    •

日志

2018-04-24 20:57:44,130  INFO [main] (BaseMapperTest.java:26) - sessionFactory bulit successfully
2018-04-24 20:57:44,133  INFO [main] (BaseMapperTest.java:29) - reader close successfully
2018-04-24 20:57:44,137  INFO [main] (UserMapperTest.java:861) - selectSysUserByAdvancedConditionTest
2018-04-24 20:57:44,155  INFO [main] (UserMapperTest.java:870) - ===========1.当用户只输入用户名时，需要根据用户名模糊查询===========
2018-04-24 20:57:44,840 DEBUG [main] (BaseJdbcLogger.java:145) - ==>  Preparing: SELECT a.id, a.user_name userName, a.user_password userPassword, a.user_email userEmail, a.user_info userInfo, a.head_img headImg, a.create_time createTime FROM sys_user a WHERE user_name like ? 
2018-04-24 20:57:44,931 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Parameters: %ad%(String)
2018-04-24 20:57:44,983 TRACE [main] (BaseJdbcLogger.java:151) - <==    Columns: id, userName, userPassword, userEmail, userInfo, headImg, createTime
2018-04-24 20:57:44,985 TRACE [main] (BaseJdbcLogger.java:151) - <==        Row: 1, admin, 123456, admin@artisan.com, <<BLOB>>, <<BLOB>>, 2018-04-13 21:12:47.0
2018-04-24 20:57:44,995 DEBUG [main] (BaseJdbcLogger.java:145) - <==      Total: 1
2018-04-24 20:57:45,001  INFO [main] (UserMapperTest.java:880) - userList:[SysUser [id=1, userName=admin, userPassword=123456, userEmail=admin@artisan.com, userInfo=管理员用户, headImg=[18, 49, 35, 18, 48], createTime=Fri Apr 13 21:12:47 BOT 2018]]
2018-04-24 20:57:45,002 DEBUG [main] (BaseJdbcLogger.java:145) - ==>  Preparing: SELECT a.id, a.user_name userName, a.user_password userPassword, a.user_email userEmail, a.user_info userInfo, a.head_img headImg, a.create_time createTime FROM sys_user a WHERE user_name like ? 
2018-04-24 20:57:45,004 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Parameters: %i%(String)
2018-04-24 20:57:45,006 TRACE [main] (BaseJdbcLogger.java:151) - <==    Columns: id, userName, userPassword, userEmail, userInfo, headImg, createTime
2018-04-24 20:57:45,006 TRACE [main] (BaseJdbcLogger.java:151) - <==        Row: 1, admin, 123456, admin@artisan.com, <<BLOB>>, <<BLOB>>, 2018-04-13 21:12:47.0
2018-04-24 20:57:45,008 TRACE [main] (BaseJdbcLogger.java:151) - <==        Row: 1001, artisan, 123456, test@artisan.com, <<BLOB>>, <<BLOB>>, 2018-04-13 21:12:47.0
2018-04-24 20:57:45,010 DEBUG [main] (BaseJdbcLogger.java:145) - <==      Total: 2
2018-04-24 20:57:45,012  INFO [main] (UserMapperTest.java:891) - userList:[SysUser [id=1, userName=admin, userPassword=123456, userEmail=admin@artisan.com, userInfo=管理员用户, headImg=[18, 49, 35, 18, 48], createTime=Fri Apr 13 21:12:47 BOT 2018], SysUser [id=1001, userName=artisan, userPassword=123456, userEmail=test@artisan.com, userInfo=测试用户, headImg=[18, 49, 35, 18, 48], createTime=Fri Apr 13 21:12:47 BOT 2018]]
2018-04-24 20:57:45,012  INFO [main] (UserMapperTest.java:893) - ===========2.当用户只输入邮箱使，根据邮箱进行完全匹配===========
2018-04-24 20:57:45,013 DEBUG [main] (BaseJdbcLogger.java:145) - ==>  Preparing: SELECT a.id, a.user_name userName, a.user_password userPassword, a.user_email userEmail, a.user_info userInfo, a.head_img headImg, a.create_time createTime FROM sys_user a WHERE 1=1 and user_name like concat('%',?,'%') and user_email = ? 
2018-04-24 20:57:45,014 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Parameters: i(String), admin@artisan.com(String)
2018-04-24 20:57:45,016 TRACE [main] (BaseJdbcLogger.java:151) - <==    Columns: id, userName, userPassword, userEmail, userInfo, headImg, createTime
2018-04-24 20:57:45,016 TRACE [main] (BaseJdbcLogger.java:151) - <==        Row: 1, admin, 123456, admin@artisan.com, <<BLOB>>, <<BLOB>>, 2018-04-13 21:12:47.0
2018-04-24 20:57:45,017 DEBUG [main] (BaseJdbcLogger.java:145) - <==      Total: 1
2018-04-24 20:57:45,018  INFO [main] (UserMapperTest.java:899) - [SysUser [id=1, userName=admin, userPassword=123456, userEmail=admin@artisan.com, userInfo=管理员用户, headImg=[18, 49, 35, 18, 48], createTime=Fri Apr 13 21:12:47 BOT 2018]]
2018-04-24 20:57:45,018 DEBUG [main] (BaseJdbcLogger.java:145) - ==>  Preparing: SELECT a.id, a.user_name userName, a.user_password userPassword, a.user_email userEmail, a.user_info userInfo, a.head_img headImg, a.create_time createTime FROM sys_user a WHERE user_name like ? and user_email = ? 
2018-04-24 20:57:45,019 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Parameters: %i%(String), 1admin@artisan.com(String)
2018-04-24 20:57:45,020 DEBUG [main] (BaseJdbcLogger.java:145) - <==      Total: 0
2018-04-24 20:57:45,021  INFO [main] (UserMapperTest.java:905) - ===========3.当用户同时输入用户名和密码时，用这两个条件查询匹配的用户===========
2018-04-24 20:57:45,022 DEBUG [main] (BaseJdbcLogger.java:145) - ==>  Preparing: SELECT a.id, a.user_name userName, a.user_password userPassword, a.user_email userEmail, a.user_info userInfo, a.head_img headImg, a.create_time createTime FROM sys_user a WHERE user_name like ? and user_email = ? 
2018-04-24 20:57:45,023 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Parameters: %i%(String), admin@artisan.com(String)
2018-04-24 20:57:45,024 TRACE [main] (BaseJdbcLogger.java:151) - <==    Columns: id, userName, userPassword, userEmail, userInfo, headImg, createTime
2018-04-24 20:57:45,024 TRACE [main] (BaseJdbcLogger.java:151) - <==        Row: 1, admin, 123456, admin@artisan.com, <<BLOB>>, <<BLOB>>, 2018-04-13 21:12:47.0
2018-04-24 20:57:45,025 DEBUG [main] (BaseJdbcLogger.java:145) - <==      Total: 1
2018-04-24 20:57:45,026  INFO [main] (UserMapperTest.java:914) - [SysUser [id=1, userName=admin, userPassword=123456, userEmail=admin@artisan.com, userInfo=管理员用户, headImg=[18, 49, 35, 18, 48], createTime=Fri Apr 13 21:12:47 BOT 2018]]
2018-04-24 20:57:45,026  INFO [main] (UserMapperTest.java:916) - ===========4.当用户同时输入无法匹配的用户名和密码===========
2018-04-24 20:57:45,027 DEBUG [main] (BaseJdbcLogger.java:145) - ==>  Preparing: SELECT a.id, a.user_name userName, a.user_password userPassword, a.user_email userEmail, a.user_info userInfo, a.head_img headImg, a.create_time createTime FROM sys_user a WHERE user_name like ? and user_email = ? 
2018-04-24 20:57:45,028 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Parameters: %x%(String), admin@artisan.com(String)
2018-04-24 20:57:45,029 DEBUG [main] (BaseJdbcLogger.java:145) - <==      Total: 0
2018-04-24 20:57:45,029  INFO [main] (UserMapperTest.java:922) - []
2018-04-24 20:57:45,031  INFO [main] (UserMapperTest.java:928) - sqlSession close successfully 

  
 
    
  
  • 1
    • 
  
  • 2
    • 
  
  • 3
    • 
  
  • 4
    • 
  
  • 5
    • 
  
  • 6
    • 
  
  • 7
    • 
  
  • 8
    • 
  
  • 9
    • 
  
  • 10
    • 
  
  • 11
    • 
  
  • 12
    • 
  
  • 13
    • 
  
  • 14
    • 
  
  • 15
    • 
  
  • 16
    • 
  
  • 17
    • 
  
  • 18
    • 
  
  • 19
    • 
  
  • 20
    • 
  
  • 21
    • 
  
  • 22
    • 
  
  • 23
    • 
  
  • 24
    • 
  
  • 25
    • 
  
  • 26
    • 
  
  • 27
    • 
  
  • 28
    • 
  
  • 29
    • 
  
  • 30
    • 
  
  • 31
    • 
  
  • 32
    • 
  
  • 33
    • 
  
  • 34
    • 
  
  • 35
    • 
  
  • 36
    • 
  
  • 37
    • 
  
  • 38
    • 
  
  • 39
    • 
  
  • 40
    • 
  
  • 41
    •

文章来源: artisan.blog.csdn.net，作者：小小工匠，版权归原作者所有，如需转载，请联系作者。

原文链接：artisan.blog.csdn.net/article/details/80073978

【版权声明】本文为华为云社区用户转载文章，如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱： cloudbbs@huaweicloud.com
MyBatis SQL
  • 点赞
  • 收藏
  • 关注作者

评论（0）

0/1000
抱歉，系统识别当前为高风险访问，暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称，即可参与社区互动！

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

确认 取消

加入云驻计划，成为创作者

  • 华为云周边好礼
  • 免费体验产品
  • 特殊身份标识
  • 线下官方门票
  • 内部专家零距离
  • 与10000+优质创作者共同成长
立即加入