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

动态SQl是MyBatis的一个强大的特性,在使用JDBC操作数据时,如果查询条件特别多,将条件串联成SQL字符串是一件痛苦的事情,通常的解决方法是写很多的if-else条件语句对字符串进行拼接, 并确保不能忘了空格在字段的最后省略都好,MyBatis使用一种很强大的动态SQL语言来改善这种情形.动态SQL基于OGNL的表达式,可使我们方便地在SQL语句中实现某些逻辑.用于实现动态SQL的元素如下.

• if:利用if实现简单的条件选择.
• choose(when,otherwise):相当于Java中的switch语句,通常与when和otherwise搭配.
• when:简化SQL语句中的where条件判断
• set:解决动态更新语句
• 可以灵活地去除多余的关键字
• foreach:迭代一个集合,通常用于in条件;

1使用if+where实现多条件查询

(1)if

在该实例中,采用的是封装User对象入参,进行查询.

/**
	 * 查询用户列表
	 * @param userName
	 * @param userRoId
	 * @return
	 */
	public List<User> getUsersList(@Param("userName")String userName,@Param("userRole")Integer userRoId);
}

在上述代码中使用了@Param注解,并将参数roleId重名名为userRole,故UserMapper.xml的代码如下:

<!-- 查询用户列表 -->
  <select id="getUserList" resultMap="userList">
  		select u.*,r.roleName as userRoleName from smbms_user u,smbms_role r where u.userRole = r.id
  		and u.userName like CONCAT('%',#{userName},'%')
			and u.userRole =#{userRole}
  </select>
  <!-- userList映射 -->
  <resultMap type="cn.smbms.pojo.User" id="userList">
		<result property="id" column="id"/>
		<result property="userCode" column="userCode"/>
		<result property="userName" column="userName"/>
		<result property="phone" column="phone"/>
		<result property="birthday" column="birthday"/>
		<result property="gender" column="gender"/>
		<result property="userRole" column="userRole"/>
		<result property="userRoleName" column="userRoleName"/>
	</resultMap>

完成修改后运行调试类,首先测试2个条件给均给出的情况测试方法,部分代码如下

		int roleId=3;
		String userName="孙";
		List<User> users=sqlSession.getMapper(UserMapper.class).getUserList(userName,roleId);
		for (User user : users) {
			System.out.println("userName: "+user.getUserName()+" userRole: "+user.getUserRoleName());
		}

运行效果如下:

然后模拟用户没有输入所有条件的情况下,如传入的用户角色roleId参数为空,即只按用户名称进行模糊查询,测试方法如下

@Test
	public  void getUserList() {
		// TODO Auto-generated method stub
		SqlSession sqlSession=MyBatisUtil.createSqlsession();
		int roleId=3;
		String userName=null;
		List<User> users=sqlSession.getMapper(UserMapper.class).getUserList(userName,roleId);
		for (User user : users) {
			System.out.println("userName: "+user.getUserName()+" userRole: "+user.getUserRoleName());
		}
	}

运行效果如下:

通过该运行效果可以发现,查询列表为空,根据业务需求,用户并不会完整地输入所有的查询条件,因此用于这只用情况使用动态SQL的if元素来实现,具体实现代码如下:

 <!-- 查询用户列表 -->
<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 u.userRole=#{userRole}
		</if>
		<if test="userName!=null and userName!=''">
			and u.userName like CONCAT('%',#{userName},'%')
		</if>
	</select>
  <!-- userList映射 -->
  <resultMap type="cn.smbms.pojo.User" id="userList">
		<result property="id" column="id"/>
		<result property="userCode" column="userCode"/>
		<result property="userName" column="userName"/>
		<result property="phone" column="phone"/>
		<result property="birthday" column="birthday"/>
		<result property="gender" column="gender"/>
		<result property="userRole" column="userRole"/>
		<result property="userRoleName" column="roleName"/>
	</resultMap>

运行效果如下:

(2)where

改造上面的示例,需求为:根据用户名称(模糊查询)和角色id查询用户列表,但是用户列表不需要显示角色名称.修改示例代码,只需要将UserMapper.xml中的getUserList的select的返回值类型改为resultType,并修改SQL,代码如下:

// TODO Auto-generated method stub
			SqlSession sqlSession=MyBatisUtil.createSqlsession();
			int roleId=3;
			String userName=null;
			List<User> users=sqlSession.getMapper(UserMapper.class).getUserList(userName,roleId);
			System.out.println("userSize: "+users.size());
			for (User user : users) {
				System.out.println("userName: "+user.getUserName()+" userRole: "+user.getUserRoleName());
			}
		}

然后进行测试,测试方法代码片段如下:

			int roleId=3;
			String userName=null;
			List<User> users=sqlSession.getMapper(UserMapper.class).getUserList(userName,roleId);
			System.out.println("userSize: "+users.size());
			for (User user : users) {
				System.out.println("userName: "+user.getUserName()+" userRole: "+user.getUserRoleName());
			}
		}

运行效果如下:

通过运行结果,发现后台报错,具体错误为SQL语句错误即while后面多了一个and;
where元素主要是用来简化SQL语句中的where条件判断,并能智能的处理and和or,不必担心多余关键字导致的语法错误.下面通过一个示例演示,修改UserMapper.xml代码如下:

<select id="getUserList" resultType="User">
		SELECT * FROM smbms_user
		<where>
			<if test="userName!=null and userName!=''">
				userName like CONCAT('%',#{userName},'%')
			</if>
			<if test="userRole!=null">
				and userRole=#{userRole}
			</if>
		</where>
	</select>

通过上述代码,where元素标签会自动识别其标签内是否有返回值,若有,就插入一个where,此外,若该便签的内容是以and或者or开头的,会自动剔除.下面根据以上两种出错情况分别进行运行调试;
> 第一种情况,参数userName传入空字符串(或者null),参数roleId给定值,测试方法代码片段如下:

// TODO Auto-generated method stub
			SqlSession sqlSession=MyBatisUtil.createSqlsession();
			int roleId=3;
			String userName=null;
			List<User> users=sqlSession.getMapper(UserMapper.class).getUserList(userName,roleId);
			System.out.println("userSize: "+users.size());
			for (User user : users) {
				System.out.println("userName: "+user.getUserName()+" userRole: "+user.getUserRoleName());
			}
		}

运行效果如下:

通过上图可知控制台输出的SQL语句根据传递的参数进行了正确拼接,where子句里自动剔除了"and"
>第二种情况:两个参数传入的值均为空,测试方法代码片段如下:

			Integer roleId=null;
			String userName="";
			List<User> users=sqlSession.getMapper(UserMapper.class).getUserList(userName,roleId);
			System.out.println("userSize: "+users.size());
			for (User user : users) {
				System.out.println("userName: "+user.getUserName());
			}

运行效果如下:

通过上图可知运行正确如下,控制台输出的日志SQL语句同样传递的参数进行了正确拼接,由于此种情况下没有参数所以自动去除where;
其实这些方法都很麻烦,下面介绍一个简单的方法
>重点就是在userMapper.xml文件里面修改里面的代码

在sql语句中加入where 1 =1 ,后面直接用if标签判断值是否为空,这样就能避免where标签不能去除,已经多余的and无法剔除;
同样的测试下代码:

			//userName为空
			Integer roleId=3;
			String userName="";
			List<User> users=sqlSession.getMapper(UserMapper.class).getUserList(userName,roleId);
			System.out.println("userSize: "+users.size());
			for (User user : users) {
				System.out.println("userName: "+user.getUserName());
			}

测试结果如下:

测试结果可知去除了roleId的那段代码;
接下来测试当userName为空的时候,测试代码如下:


由上面的日志文件可知,自动剔除了userName的那段代码;
看到这个时候不要生气,不要时候为啥不直接说这个,简单的方法,学习从来没有简单方法,要一步一步走过来,也只有坚持到最后的人,才能看到这个方法