7. 动态SQL与模糊查询
7.4 User(表实体类)
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);
}
}