Mybatis之动态SQL多条件查询

动态SQL查询

1、if标签

将上一篇getUserList中传入实体类

public List<User> getUserList(User user);

改为传参： 

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

当输入参数为null时无法查询出想要的结果，因此加入if判断非空来拼接：

UserMapper.xml语句

	<!-- 当数据库中的字段信息与对象的属性不一致时需要通过resultMap来映射 -->
	<resultMap type="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>
		<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测试类（roleId传入值为空）及结果：

@Test
	public void testGetUserList() {
		List<User> userList=null;
		SqlSession sqlSession = null;
		/*
		 * User user = new User(); user.setUserName("赵"); user.setUserRole(2);
		 */
		String userName = "孙";
		Integer roleId = null;
				
		try {
			sqlSession = MyBatisUtil.createSqlSession();
			//创建sqlSession
			//调用mapper文件来对数据进行操作,必须将mapper文件引入到mybatis-config.xml文件中
			//userList=sqlSession.selectList("cn.smbms.dao.user.UserMapper.getUserList");
			userList=sqlSession.getMapper(UserMapper.class).getUserList(userName,roleId);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			MyBatisUtil.closeSqlSession(sqlSession);
		}
		for(User _user:userList) {
			logger.debug("testGetUserList---->"+_user.getUserCode()+"  and username:"+_user.getUserName()+"  and userRoleName:");
		}
	}

 

 2、where标签：

将上UserMapper.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>

当输入userName为空时，语句拼接后变为

因此需要引入where标签动态：

<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>
		</where>
	</select>

where标签会自动剔除and和or语句，运行结果如下：

3、trim标签：

trim元素的主要功能是可以在自己包含的内容前加上某些前缀，也可以在其后加上某些后缀，与之对应的属性是prefix和suffix；可以把包含内容的首部某些内容覆盖，即忽略，也可以把尾部的某些内容覆盖，对应的属性是prefixOverrides和suffixOverrides；正因为trim有这样的功能，所以我们也可以非常简单的利用trim来代替where元素的功能。

用trim代替where

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

结果与之前一致