动态sql语句

通过mybatis提供的各种标签方法实现动态拼接sql。

需求：根据性别和名字查询用户
查询sql：
SELECT id, username, birthday, sex, address FROM user WHERE sex = 1 AND username LIKE ‘%张%’
UserMapper.xml配置sql，如下：

<!-- 根据条件查询用户 -->
<select id="queryUserByWhere" parameterType="user" resultType="user">
	SELECT id, username, birthday, sex, address FROM `user`
	WHERE sex = #{sex} AND username LIKE
	'%${username}%'
</select>

编写Mapper接口，如下图：
在UserMapperTest添加测试方法，如下：

@Test
	public void testQueryUserByWhere() throws IOException {
		String resource = "sqlMapConfig.xml";
		InputStream in = Resources.getResourceAsStream(resource);
		//创建SqlSessionFactory
		SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
		//创建SqlSession
		SqlSession sqlSession = sqlSessionFactory.openSession();
		UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

		// 使用userMapper执行根据条件查询用户
		User user = new User();
		user.setSex("1");
		user.setUsername("张");

		List<User> list = userMapper.queryUserByWhere(user);

		for (User u : list) {
			System.out.println(u);
		}


		sqlSession.close();
	}

之后运行结果
但是去掉user.setSex(“1”)，
结果是
显然这边出错，因为查询条件限制越少，查询结果越多才对，这边出现了0，所以引入动态sql语句。
将xml文件中sql语句中进行修改

<select id="queryUserByWhere" parameterType="user" resultType="user">
	    SELECT id, username, birthday, sex, address FROM `user`
		WHERE 1=1
	<if test="sex != null and sex != ''">
		AND sex = #{sex}
	</if>
	<if test="username != null and username != ''">
		AND username LIKE
		'%${username}%'
	</if>

再运行就出现了正确的结果