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

User.java

package com.lx.bean;

public class User {

	private int id;
	
	private String name;
	
	private int age;
	public User() {
	}
	public User(int id, String name, int age) {
		super();
		this.id = id;
		this.name = name;
		this.age = age;
	}
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public int getAge() {
		return age;
	}
	public void setAge(int age) {
		this.age = age;
	}
	@Override
	public String toString() {
		return "User [id=" + id + ", name=" + name + ", age=" + age + "]";
	}
	
}

ConditionUser.java

package com.lx.bean;

public class ConditionUser {

	private String name;
	private int minAge;
	private int maxAge;
	public ConditionUser(String name, int minAge, int maxAge) {
		super();
		this.name = name;
		this.minAge = minAge;
		this.maxAge = maxAge;
	}
	public ConditionUser() {
		super();
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public int getMinAge() {
		return minAge;
	}
	public void setMinAge(int minAge) {
		this.minAge = minAge;
	}
	public int getMaxAge() {
		return maxAge;
	}
	public void setMaxAge(int maxAge) {
		this.maxAge = maxAge;
	}
	@Override
	public String toString() {
		return "ConditionUser [name=" + name + ", minAge=" + minAge
				+ ", maxAge=" + maxAge + "]";
	}
	
}

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

测试类：

package com.lx.test6;

import java.util.List;

import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.junit.Test;

import com.lx.bean.Classes;
import com.lx.bean.ConditionUser;
import com.lx.bean.User;
import com.lx.utils.FactoryUtil;

public class Test6 {

	@Test
	public void test1()
	{
		SqlSessionFactory factory = FactoryUtil.getFactory();
		
		SqlSession session = factory.openSession();
	
		String statement = "com.lx.test6.userMapper.getUser";
		
		String name="o";
		name=null;
	    ConditionUser  parameter = new ConditionUser("%"+name+"%", 13, 19);
		List<User> users = session.selectList(statement, parameter );
	    System.out.println(users);
		
		session.close();
	}
	
}

注意：要在conf.xml文件中注册