Mybatis实现动态SQL与模糊查询

Mybatis实现动态SQL与模糊查询

7.1. 提出 需求:

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

7.2. 准备数据表和数据

[](javascript:void(0)😉

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);

[](javascript:void(0)😉

7.3. ConditionUser( 查询条件实体类)

private String name;
private int minAge;
private int maxAge;

7.4. User（表实体类）

private int id;
private String name;
private int age;

7.5. userMapper.xml(映射文件)

[](javascript:void(0)😉

<?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&lt;=#{maxAge}
　　<if test='name!="%null%"'>and name like #{name}</if>
　　</select>
</mapper>

[](javascript:void(0)😉

7.6. UserTest( 测试)

[](javascript:void(0)😉

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);
　　}
}

[](javascript:void(0)😉

MyBatis 中可用的动态 SQL

• if
• choose(when, otherwise)
• trim(where, set)
  )

MyBatis 中可用的动态 SQL

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