SSM（七）使用动态SQL完成多条件查询

一、目标

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

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

二、动态SQL

基于OGNL表达式

使用动态SQL完成多条件查询等逻辑实现

用于实现动态SQL的元素主要有

• if
• trim
• where
• set
• choose(when、otherwise)
• foreach

三、if

需求说明：

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

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

错误代码：

UserMapper.java

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

UserMapper.xml

	<resultMap type="user" id="userMapDSQL">
		<id property="id" column="id"/>
		<result property="userCode" column="userCode"/>
		<result property="userName" column="userName"/>
		<result property="userRole" column="userRole"/>
		<result property="userRoleName" column="roleName"/>
	</resultMap>
	<select id="getUserListDSQL" resultMap="userMapDSQL">
		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>

UserMapperTest.java

	@Test
	public void testGetUserListDSQL() {
		List<User> userList = new ArrayList<User>();
		SqlSession sqlSession = null;
		String userName = "a";
		Integer userRole = 1;
		try {
			sqlSession = MyBatisUtil.createSqlSession();
			userList = sqlSession.getMapper(UserMapper.class).getUserListDSQL(userName, userRole);
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			MyBatisUtil.closeSqlSession(sqlSession);
		}
		
		for(User _user : userList) {
			logger.debug("testGetUserListDSQL userCode: " + _user.getUserCode()
					+" and userName: " + _user.getUserName()
					+ " and userRole: " + _user.getUserRole()
					+ " and userRoleName: " + _user.getUserRoleName());
		}
	}

问题：

当传入用户角色参数为空的时候，查询结果为空。

分析：

正确结果：所有用户角色下的用户数据。

原因：select * from smbms_user u, smbms_role r where u.userRole=r.id and u.userName like CONCAT('%','a','%') and u.userRole=null;

如何处理：if（判断参数）：实现简单的条件判断

UserMapper.java

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

UserMapper.xml

<resultMap type="user" id="userMapDSQL2">
		<id property="id" column="id"/>
		<result property="userCode" column="userCode"/>
		<result property="userName" column="userName"/>
		<result property="userRole" column="userRole"/>
		<result property="userRoleName" column="roleName"/>
	</resultMap>
	<select id="getUserListDSQL2" resultMap="userMapDSQL2">
		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>

UserMapperTest.java

@Test
	public void testGetUserListDSQL2() {
		List<User> userList = new ArrayList<User>();
		SqlSession sqlSession = null;
		String userName = "a";
		Integer userRole = null;
		try {
			sqlSession = MyBatisUtil.createSqlSession();
			userList = sqlSession.getMapper(UserMapper.class).getUserListDSQL2(userName, userRole);
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			MyBatisUtil.closeSqlSession(sqlSession);
		}
		
		for(User _user : userList) {
			logger.debug("testGetUserListDSQL userCode: " + _user.getUserCode()
					+" and userName: " + _user.getUserName()
					+ " and userRole: " + _user.getUserRole()
					+ " and userRoleName: " + _user.getUserRoleName());
		}
	}

四、where

错误代码：

UserMapper.java

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

UserMapper.xml

	<select id="getUserListDSQLW" 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>
	</select>

UserMapperTest.java

@Test
	public void testGetUserListDSQLW() {
		List<User> userList = new ArrayList<User>();
		SqlSession sqlSession = null;
		String userName = null;
		Integer userRole = 3;
		try {
			sqlSession = MyBatisUtil.createSqlSession();
			userList = sqlSession.getMapper(UserMapper.class).getUserListDSQLW(userName, userRole);
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			MyBatisUtil.closeSqlSession(sqlSession);
		}
		
		for(User _user : userList) {
			logger.debug("testGetUserListDSQL userCode: " + _user.getUserCode()
					+" and userName: " + _user.getUserName()
					+ " and userRole: " + _user.getUserRole()
					+ " and userRoleName: " + _user.getUserRoleName());
		}
	}

结果报错，打印SQL：

select * from smbms_user where             and userRole = ?

修改UserMapper.xml

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

where:

简化SQL语句中where条件判断

只能处理and和or

五、trim

属性：

• prefix
• suffix
• prefixOverrides
• suffixOverrides

更灵活的去处多余的关键字

替代where

UserMapper.java

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

UserMapper.xml

<select id="getUserListDSQLT" resultType="User">
		select * from smbms_user
		<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>

UserMapperTest.java

@Test
	public void testGetUserListDSQLT() {
		List<User> userList = new ArrayList<User>();
		SqlSession sqlSession = null;
		String userName = null;
		Integer userRole = 1;
		try {
			sqlSession = MyBatisUtil.createSqlSession();
			userList = sqlSession.getMapper(UserMapper.class).getUserListDSQLT(userName, userRole);
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			MyBatisUtil.closeSqlSession(sqlSession);
		}
		logger.debug(userList.size());
		for(User _user : userList) {
			logger.debug("testGetUserListDSQLW userCode: " + _user.getUserCode()
			+" and userName: " + _user.getUserName()
			+ " and userRole: " + _user.getUserRole());
		}
	}