动态sql

需求1：在综合查询时，查询条件由用户输入，用户名可为空等
需求2：综合查询，根据用户ID集合进行查询

if标签：可以对输入参数进行判断

where标签：默认去掉后面第一个and，如果没有参数，则把自己干掉

sql片段

foreach标签：可以循环的传入参数值

1映射文件

<sql id="whereclause">
	<if test="user != null">
		<if test="user.username != null and user.username !=  '' ">
		 and username like '%${user.username}%'
		</if>
		<if test="user.sex != null and user.sex != '' ">
		and sex = #{user.sex}
		</if>
	</if>
	<!-- foreach标签，可以循环的传入参数值 -->
	<if test="idList != null">
		<foreach collection="idList"  item="id" open="AND id IN (" close=")" separator=",">
			#{id}
		</foreach>
	</if>

</sql>
<select id="findUserList" parameterType="cn.itcast.po.UserCustom" resultType="cn.itcast.po.User">
	select * from user 
	<where>
		<include refid="whereclause"></include>
	</where>	
</select>

2测试

	@Test
	public void testFindUserList() {
		//创建UserMapper对象
		SqlSession sqlSession = sqlSessionFactory.openSession(true) ;
		UserMapper userMapper = sqlSession.getMapper(UserMapper.class) ;
		
		UserCustom userCustom = new UserCustom();
//		User user = new User() ;
//		//user.setSex("1");
//		user.setUsername("小明");
//		userCustom.setUser(user);		
		
		
		List<Integer> idList = new ArrayList<Integer>();
		idList.add(1);
		idList.add(10);
		idList.add(16);
		userCustom.setIdList(idList);
		List<User> list = userMapper.findUserList(userCustom);	
		System.out.println(list);
		sqlSession.close();	
	}