Mybatis-02动态SQL语句标签使用

续上节

一、if

1.修改UserMapper接口

	List<User> getUserByDomain(User user);

2.修改UserMapper.xml文件

<select id="getUserByDomain" parameterType="user" resultType="com.mybatis.domain.User">
		SELECT
		  `id`,
		  `username`,
		  `birthday`,
		  `sex`,
		  `address`
		FROM `user`
		<!-- WHERE 1=1
		<if test="username!=null and username!=''">
			and username LIKE '%${username}%'
		</if>
		<if test="sex != null and sex != ''">
			and sex = #{sex}
		</if> -->
		
		<!-- where自动补全where关键字同时处理多余的and，不能手动加where关键字 -->
		<where>
		<if test="username!=null and username!=''">
			and username LIKE '%${username}%'
		</if>
		<if test="sex != null and sex != ''">
			and sex = #{sex}
		</if>
		</where>
	</select>

3.单元测试

@Test
	public void testgetUserByDomain() {
		SqlSession sql=SqlSessionFactoryUtils.getSql().openSession();
		//接口代理实现类
		UserMapper userMapper=sql.getMapper(UserMapper.class);
		User user = new User();
		user.setUsername("百");
		user.setSex("1");
		List<User> list=userMapper.getUserByDomain(user);
		for(User user1:list)
			System.out.println(user1);
		sql.close();
	}

二、foreach的使用

1.修改UserMapper接口

	List<User> getUserByIds(QuervVo quervVo);

2.修改UserMapper.xml文件

<select id="getUserByIds" parameterType="QuervVo" resultType="user">
		SELECT
		  <include refid="user_id"></include>
		FROM `user`
		<where>
			<!-- 遍历集合 item循环变量，separator分隔符 -->
			<foreach collection="ids" open="id IN(" item="uId" separator="," close=")">
				#{uId}
			</foreach>
		</where>
	</select>

3.包装类QuervVo添加方法

private List<Integer> ids;

	public List<Integer> getIds() {
		return ids;
	}

	public void setIds(List<Integer> ids) {
		this.ids = ids;
	}

4.单元测试

@Test
	public void testgetUserByIds() {
		SqlSession sql=SqlSessionFactoryUtils.getSql().openSession();
		//接口代理实现类
		UserMapper userMapper=sql.getMapper(UserMapper.class);
		QuervVo vo =new QuervVo();
		vo.setIds(Arrays.asList(21,27,29,30));
		List<User> list=userMapper.getUserByIds(vo);
		for(User user1:list)
			System.out.println(user1);
		sql.close();
	}