mybatis的动态SQL和模糊查询实例详解

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

public class User {
 private Integer id;
 private String name;
 private Integer age;

　　//getters and setters
　　
 @Override
 public String toString() {
  return "User [id=" + id + ", name=" + name + ", age=" + age + "]";
 }

 public User(Integer id, String name, Integer age) {
  super();
  this.id = id;
  this.name = name;
  this.age = age;
 }

 public User() {
  super();
 }
}

public class ConditionUser {
 private String name;
 private int minAge;
 private int maxAge;

　　//getters and setters
　　
 public ConditionUser(String name, int minAge, int maxAge) {
  super();
  this.name = name;
  this.minAge = minAge;
  this.maxAge = maxAge;
 }

 public ConditionUser() {
  super();
 }
}

<?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">
 <select id="getUser" parameterType="ConditionUser" resultType="User">
  SELECT * FROM d_user WHERE age &gt;= #{minAge} AND age &lt;= #{maxAge}
  <if test="name!=null">
   AND name LIKE CONCAT(CONCAT('%',#{name}),'%')</if>
 </select>
</mapper>

public class Test {

 private SqlSessionFactory sessionFactory;
 private SqlSession session;

 @Before
 public void init(){
  //读取配置文件
  String resource = "conf.xml";
  InputStream is = this.getClass().getClassLoader().getResourceAsStream(resource);

  //创建SqlSessionFactory和SqlSession
  sessionFactory = new SqlSessionFactoryBuilder().build(is);
  session = sessionFactory.openSession();
 }

 @After
 public void free(){
  session.commit();
  session.close();
 }


 @org.junit.Test
 public void getUser() {
  String statement = "com.mybatis.test7.userMapper"+".getUser";
  ConditionUser conditionUser = new ConditionUser("o", 13, 18);
  List<User> list = session.selectList(statement, conditionUser);
  System.out.println(list);
 }
}

<?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">
 <select id="getUser" parameterType="ConditionUser" resultType="User">
  SELECT * FROM d_user WHERE age &gt;= #{minAge} AND age &lt;= #{maxAge}
  <if test="name!=null">
   AND name LIKE '%${name}%'</if>
 </select>
</mapper>