MyBatis提供的动态SQL-if标签

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

如果注释掉 user.setSex(“1”)
效果如下：

测试结果很显然不合理。
要解决这个问题，需要编写多个sql，查询条件越多，需要编写的sql就更多了，显然这样是不靠谱的。

解决方案，使用动态sql的if标签

UserMapper.xml:

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.sea.crm.mapper.UserMapper">
	<select id="queryBySexAndName" parameterType="User" resultType="User">
		SELECT id, username, birthday, sex, address FROM `user` WHERE 1=1
		<if test="sex!=0">
			And sex=#{sex}
		</if>
		<if test="username!=null and username!=''">
			And username LIKE '%${username}%'
		</if>
		</select>
</mapper>

UserMapper接口添加方法：

public List<User> queryBySexAndName(User user);

单元测试

public class MyBatisTest {
	SqlSessionFactory factory = null;
	private UserMapper userMapper = null;

	@Before
	public void testInit() {

		SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
		InputStream in = MyBatisTest.class.getResourceAsStream("/SqlMapConfig.xml");
		factory = builder.build(in);

	}
    @Test
	public void testQueryBySexAndName() {
		SqlSession sqlSession = factory.openSession();
		UserMapper mapper = sqlSession.getMapper(UserMapper.class);
		User user=new User();
		//user.setSex(1);
		user.setUsername("张");
		List<User> users = mapper.queryBySexAndName(user);
		for (User user2 : users) {
			System.out.println(user2);
			
		}
		sqlSession.close();
	}
	    }

效果：