7. 动态SQL与模糊查询
7.1. 提出需求:
实现多条件查询用户(姓名模糊匹配, 年龄在指定的最小值到最大值之间)
7.2. 准备数据表和数据:
create table d_user(
idint primary key auto_increment,
namevarchar(10),
ageint(3)
);
insert into d_user(name,age)values('Tom',12);
insert into d_user(name,age)values('Bob',13);
insertinto d_user(name,age) values('Jack',18);7.3. ConditionUser(查询条件实体类)
private String name;
private int minAge;
privateint maxAge;7.4.User(表实体类)
private int id;
private String name;
privateint age;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.mybatis.test7.userMapper">
<!-- 实现多条件查询用户(姓名模糊匹配, 年龄在指定的最小值到最大值之间) -->
<!-- sql语句中用#{}取的属性即parameterType="com.mybatis.test7.ConditionUser"中的属性 -->
<select id="getUser" parameterType="com.mybatis.test7.ConditionUser" resultType="com.mybatis.test7.User">
SELECT * FROM d_user WHERE name like #{name} AND age between #{minAge} AND #{maxAge}
</select>
</mapper>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.mybatis.test7.userMapper">
<!-- 实现多条件查询用户(姓名模糊匹配, 年龄在指定的最小值到最大值之间) -->
<!-- sql语句中用#{}取的属性即parameterType="com.mybatis.test7.ConditionUser"中的属性 -->
<select id="getUser" parameterType="com.mybatis.test7.ConditionUser" resultType="com.mybatis.test7.User">
SELECT * FROM d_user WHERE name like #{name} AND age between #{minAge} AND #{maxAge}
</select>
</mapper>
7.6. Test(测试)
public class Test7 {
@Test
public void getUser() throws IOException {
SqlSessionFactory factory = MybatisUtils.getFactory();
SqlSession session = factory.openSession();
String statement = "com.mybatis.test7.userMapper.getUser";
//姓名模糊匹配,含有o的
ConditionUser parameter = new ConditionUser("%o%",13,18);
//把查询条件实体类ConditionUser的实例作为参数传入,在xml文件中
//用parameterType="com.mybatis.test1.User"和#{}完成参数传递
List<User> list = session.selectList(statement, parameter);
System.out.println(list);
}
}