MyBatis笔记07 - 动态SQL与模糊查询

7. 动态SQL与模糊查询

MyBatis中可用的动态SQL标签

• if
• choose(when，otherwise)
• trim(where，set)
• foreach

7.1 提出需求: 

实现多条件查询用户(姓名模糊匹配, 年龄在指定的最小值到最大值之间)

7.2 准备数据表和数据:

create table d_user(  
	id int primary key auto_increment,  
	name varchar(10),
	age int(3)
); 


insert into d_user(name,age) values('Tom',12);  
insert into d_user(name,age) values('Bob',13);  
insert into d_user(name,age) values('Jack',18);

7.3 ConditionUser(查询条件实体类)

public class ConditionUser {
private String name;
private int minAge;
private int maxAge;
//生成set和get方法
......
}

7.4 User(表实体类)

public class User {
private int id;
private String name;
private int age;
//生成set和get方法
......
}

7.5. 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.atguigu.day03_mybatis.test6.userMapper">
<select id="getUser" parameterType="com.atguigu.day03_mybatis.test6.ConditionUser" resultType="com.atguigu.day03_mybatis.test6.User">
			select * from d_user where age>=#{minAge} and age<=#{maxAge}
		<if test='name!="%null%"'>and name like #{name}</if>
	</select>
</mapper>

7.6. UserTest(测试)

public class UserTest {

	public static void main(String[] args) throws IOException {
		
		Reader reader = Resources.getResourceAsReader("conf.xml");
		
		SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
		
		SqlSession sqlSession = sessionFactory.openSession();
		
		String statement = "com.atguigu.day03_mybatis.test6.userMapper.getUser";
		
		List<User> list = sqlSession.selectList(statement, new ConditionUser("%a%", 1, 12));
		
		System.out.println(list);
	}
}