九、使用动态SQL完成多条件查询

作者: lifeline张 | 来源:发表于2018-08-26 15:29 被阅读0次

九、使用动态SQL完成多条件查询
MyBatis多条件查询看这一篇就够了
单表的 CURD 操作（第四讲）
Mybatis框架（复杂动态SQL），一对一，一对多，多对多
Mybatis框架（复杂动态SQL），一对一，一对多，多对多
五、使用MyBatis实现条件查询
7.平凡之路-动态SQL语句
mybatis动态order by 排序问题 2021-09-
hibernateQuery
Chapter 5 Advanced SQL

一、本课目标

掌握使用if+where完成多条件查询
掌握使用if+trim完成多条件查询

二、动态SQL

基于OGNL表达式
使用动态SQL完成多条件查询等逻辑实现
用于实现动态SQL的元素主要有：
if、trim、where、set、choose（when、otherwise）、foreach

三、if

需求说明：改造查询用户信息列表的演示示例，增加查询条件

用户角色（根据角色id查询）
用户名称（模糊查询）

3.1以前的写法

配置文件：

<resultMap type="user" id="userList">
        <result property="userRoleName" column="roleName"/>
    </resultMap>
    
    <!-- 查询用户列表 -->
    <select id="getUserList" resultMap="userList">
        select 
            u.*, r.roleName 
        from 
            smbms_user u, smbms_role r
        where 
            userName like CONCAT('%',#{userName},'%')
        and 
            userRole=#{userRole}
        and 
            u.userRole=r.id
    </select>

接口代码：

    public List<User> getUserList(
            @Param("userName")String userName,
            @Param("userRole")Integer roleId
            );

测试代码：

@Test
    public void testGetUserList() {
        List<User> userList = null;
        SqlSession sqlSession = null;
        try {
            sqlSession = MyBatisUtil.createSqlSession();
        // 4、调用mapper文件来对数据进行操作,操作之前必须将mapper文件引入到mabatis-config.xml中
        //  userList = sqlSession.selectList("mmp.UserMapper.getUserList");
            userList = sqlSession.getMapper(UserMapper.class).getUserList("孙", 3);
            
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            MyBatisUtil.closeSqlSession(sqlSession);
        }
        for (User user:userList) {
            logger.debug("testGetUserList userCode:" + user.getUserCode()
                    + "and userName" + user.getUserName() );
        }
    }

运行结果：

image.png

现在的问题是：当传入用户角色参数为空的时候，查询结果为空吗？

测试代码这样写：

@Test
    public void testGetUserList() {
        List<User> userList = null;
        SqlSession sqlSession = null;
        try {
            sqlSession = MyBatisUtil.createSqlSession();
        // 4、调用mapper文件来对数据进行操作,操作之前必须将mapper文件引入到mabatis-config.xml中
        //  userList = sqlSession.selectList("mmp.UserMapper.getUserList");
            userList = sqlSession.getMapper(UserMapper.class).getUserList("孙", null);
            
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            MyBatisUtil.closeSqlSession(sqlSession);
        }
        for (User user:userList) {
            logger.debug("testGetUserList userCode:" + user.getUserCode()
                    + "and userName" + user.getUserName() );
        }
    }

运行结果：

image.png

这是因为执行的sql语句是这个样子：

image.png

这个时候我是拿不到任何数据的。但是这个时候我往往需要的是把这个空的条件去掉之后的其他掉件下的数据。
比如我之前写的排片页面，用ajax去获取厅号、日期，只有在日期发生改变的时候我才会调用ajax去获取数据。这样做的问题是：1、在厅号发生改变的时候无法查询，2、厅号没填的情况下查询不到数据。如果进行优化的话，则需要获取这两个框的数据进行非空判断，然后分别调用不同的sql语句，挺麻烦的还是。我想要的结果是我直接把页面上这两个框的数据拿到发送给后台，就只有一条sql语句，在sql中判断如果是空的话则这一个where条件就不执行。

3.2动态sql写法

xml文件：

<resultMap type="user" id="userList">
        <result property="userRoleName" column="roleName"/>
    </resultMap>
    
    <!-- 查询用户列表 -->
    <select id="getUserList" resultMap="userList">
        select 
            u.*, r.roleName 
        from 
            smbms_user u, smbms_role r
        where 
            u.userRole=r.id
        <if test="userRole != null">
            and 
                userRole=#{userRole}
        </if>
        <if test="userName != null and userName != ''">
            and 
                userName like CONCAT('%',#{userName},'%')
        </if>
    </select>

测试代码：

@Test
    public void testGetUserList() {
        List<User> userList = null;
        SqlSession sqlSession = null;
        try {
            sqlSession = MyBatisUtil.createSqlSession();
        // 4、调用mapper文件来对数据进行操作,操作之前必须将mapper文件引入到mabatis-config.xml中
        //  userList = sqlSession.selectList("mmp.UserMapper.getUserList");
            userList = sqlSession.getMapper(UserMapper.class).getUserList("", null);
            
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            MyBatisUtil.closeSqlSession(sqlSession);
        }
        for (User user:userList) {
            logger.debug("testGetUserList userCode:" + user.getUserCode()
                    + "and userName" + user.getUserName() );
        }
    }

运行结果：

image.png

可以看到，sql语句变化了。

四、 where

需求同上。
xml配置文件：

<!-- 查询用户列表 -->
    <select id="getUserList" resultType="user">
        select 
            u.*
        from 
            smbms_user u
        where 
        <if test="userName != null and userName != ''">
            and 
                userName like CONCAT('%',#{userName},'%')
        </if>
        <if test="userRole != null">
            and 
                userRole=#{userRole}
        </if>
    </select>

测试代码：

@Test
    public void testGetUserList() {
        List<User> userList = null;
        SqlSession sqlSession = null;
        try {
            sqlSession = MyBatisUtil.createSqlSession();
        // 4、调用mapper文件来对数据进行操作,操作之前必须将mapper文件引入到mabatis-config.xml中
        //  userList = sqlSession.selectList("mmp.UserMapper.getUserList");
            userList = sqlSession.getMapper(UserMapper.class).getUserList("", 3);
            
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            MyBatisUtil.closeSqlSession(sqlSession);
        }
        for (User user:userList) {
            logger.debug("testGetUserList userCode:" + user.getUserCode()
                    + "and userName" + user.getUserName() );
        }
    }

运行结果：

image.png

运行报了sql异常，原因是当第一个条件为空的时候，去拼接sql语句，会多出来一个and。而if那个例子里面没有出现这个问题的原因是在where后面有一个固定的条件，后续条件直接拼接就行了。

解决办法：
修改配置文件如下：

 <!-- 查询用户列表 -->
    <select id="getUserList" resultType="user">
        select 
            u.*
        from 
            smbms_user u
        <where>
            <if test="userName != null and userName != ''">
                userName like CONCAT('%',#{userName},'%')
            </if>
            <if test="userRole != null">
                userRole=#{userRole}
            </if>
        </where>
    </select>

测试结果正常。

分析：where标签会自动识别标签内是否有返回值，如果有的话，则会自动插入where，如果没有的话，则会自动剔除。插入了之后，后面的语句以and或者or开头，它也会进行一个自动剔除，所以说非常灵活。
where主要是简化了sql语句中where的条件判断，可以智能处理and和or
where主要是可以去除多余的and和or，而不能自行添加and和or

五、trim

image.png

trim元素会自动识别在他这个标签内是否含有返回值，如果有返回值，他就会在自己所包含的内容前面加上一些前缀，也可以在后面加上一些后缀，也可以把包含的首部的一些内容覆盖掉，或者是尾部的一些内容进行覆盖。与这四个操作对应的属性就是图中的四个属性。

在上面的基础上修改xml文件：

<!-- 查询用户列表 -->
    <select id="getUserList" resultType="user">
        select 
            u.*
        from 
            smbms_user u
        <trim prefix="where" prefixOverrides="and/or"> 
            <if test="userName != null and userName != ''">
                userName like CONCAT('%',#{userName},'%')
            </if>
            <if test="userRole != null">
                and userRole=#{userRole}
            </if>
        </trim>
    </select>

测试代码：

@Test
    public void testGetUserList() {
        List<User> userList = null;
        SqlSession sqlSession = null;
        try {
            sqlSession = MyBatisUtil.createSqlSession();
        // 4、调用mapper文件来对数据进行操作,操作之前必须将mapper文件引入到mabatis-config.xml中
        //  userList = sqlSession.selectList("mmp.UserMapper.getUserList");
            userList = sqlSession.getMapper(UserMapper.class).getUserList("孙", 3);
            
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            MyBatisUtil.closeSqlSession(sqlSession);
        }
        for (User user:userList) {
            logger.debug("testGetUserList userCode:" + user.getUserCode()
                    + "and userName" + user.getUserName() );
        }
    }

运行结果：

image.png

注：这个地方视频讲的也不是太清楚，可以自己深入摸索一下。
trim跟where一样，也只是只能剔除，不能自动添加and和or。所以在使用这两者的时候，在if的条件判断里面，尽量把and加上，这样就不会出错了。

六、小结

image.png

九、使用动态SQL完成多条件查询
一、本课目标掌握使用if+where完成多条件查询掌握使用if+trim完成多条件查询二、动态SQL 基于O...
MyBatis多条件查询看这一篇就够了
一：使用动态SQL完成多条件查询 a:使用if+where实现多条件查询首先场景需求，有个年级和班级表，第一个...
单表的 CURD 操作（第四讲）
动态 SQL 动态 SQL ,主要用于解决查询条件不确定的情况:在程序运行期间，根据用户提交的查询条件进行查询。提...
Mybatis框架（复杂动态SQL），一对一，一对多，多对多
复合条件查询(动态SQL)MyBatis 的强大特性之一便是它的动态 SQL。如果你有使用 JDBC 或其它类似框...
Mybatis框架（复杂动态SQL），一对一，一对多，多对多
复合条件查询(动态SQL)MyBatis 的强大特性之一便是它的动态 SQL。如果你有使用 JDBC 或其它类似框...
五、使用MyBatis实现条件查询
一、本课目标掌握SQL映射文件的常用元素掌握使用select完成（单/多）条件查询简介：1、mapper，是...
7.平凡之路-动态SQL语句
动态SQL语句是核心之一,这里我们通过几个示例来演示一多条件查询专题 1.通过恒等式完成动态SQL语句涉及到...
mybatis动态order by 排序问题 2021-09-
1、问题 sql查询需要根据条件动态修改排序字段以及升降序；刚开始尝试在sql中直接使用choose when进行...
hibernateQuery
使用Query接口 Hql 条件查询 hql与sql语句对比及查询步骤查询全部条件查询>索引占位符条件查询...
Chapter 5 Advanced SQL
高级SQL(Chapter 5) 使用其他语言调用SQL 动态SQL 运行时以字符串形式构建SQL查询→提交查询→...