mybatis 动态SQL与模糊查询

来源：互联网发布：windows 10 rs1 iso 编辑：程序博客网时间：2024/04/27 22:43

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

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;}@Overridepublic 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;}@Overridepublic 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 {@Testpublic 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文件中注册

0 0