动态SQL与模糊查询

一：

1.需求

　　实现多条件查询用户（姓名模糊查询，年龄在指定的最小值与最大值之间）

2.结构目录

　　

3.准备数据与建表

CREATE TABLE d_user(
    id int PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20),
    age INT(3)
);
INSERT INTO d_user(name,age) VALUES('Tom',12);
INSERT INTO d_user(name,age) VALUES('Bob',14);
INSERT INTO d_user(name,age) VALUES('Jack',18);

4.新建实体类User.java

package com.cao.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 + "]";
    }
    
}

5.新建实体类ConditionUser

package com.cao.bean;

public class ConditionUser {
    private String name;
    private int minAge;
    private int maxAge;
    public ConditionUser() {}
    public ConditionUser(String name,int minAge,int maxAge) {
        this.name=name;
        this.minAge=minAge;
        this.maxAge=maxAge;
    }
    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 + "]";
    }
    
}

6.映射文件

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

7.配置文件Configuration.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">

<configuration>
  <environments default="development">
    <environment id="development">
      <transactionManager type="JDBC">
        <property name="" value=""/>
      </transactionManager>
      <dataSource type="UNPOOLED">
        <property name="driver" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://127.0.0.1:3308/mybatis"/>
        <property name="username" value="root"/>
        <property name="password" value="123456"/>
      </dataSource>
    </environment>
  </environments>

   <mappers>
      <mapper resource="com/cao/sql/config/users.xml"/>
   </mappers>

</configuration>

8.测试类

package com.cao.test;

import java.io.IOException;
import java.io.Reader;
import java.util.List;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;

import com.cao.bean.ConditionUser;
import com.cao.bean.User;


public class MainTest {
    @Test
    public void test1() throws Exception {
        Reader reader=Resources.getResourceAsReader("com/cao/config/Configuration.xml");
        SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(reader);
        SqlSession sqlSession=sqlSessionFactory.openSession(true);                //true后是自动提交
        //
        String statement="getUserLike.getUser";
        String name="o";
        ConditionUser conditionUser=new ConditionUser("%"+name+"%",13,18);
        //
        List<User> selectList = sqlSession.selectList(statement, conditionUser);    
        System.out.println(selectList);
        sqlSession.close();
    }
}

9.效果