需求:根据性别和名字查询用户
查询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();
}
}
效果: