动态SQL与模糊查询

一：

1.需求

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

2.结构目录

3.准备数据与建表

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

4.新建实体类User.java

 1 package com.cao.bean;
 2 
 3 public class User {
 4     private int id;
 5     private String name;
 6     private int age;
 7     public User() {}
 8     
 9     public User(int id, String name, int age) {
10         super();
11         this.id = id;
12         this.name = name;
13         this.age = age;
14     }
15 
16     public int getId() {
17         return id;
18     }
19     public void setId(int id) {
20         this.id = id;
21     }
22     public String getName() {
23         return name;
24     }
25     public void setName(String name) {
26         this.name = name;
27     }
28     public int getAge() {
29         return age;
30     }
31     public void setAge(int age) {
32         this.age = age;
33     }
34     @Override
35     public String toString() {
36         return "User [id=" + id + ", name=" + name + ", age=" + age + "]";
37     }
38     
39 }

5.新建实体类ConditionUser

 1 package com.cao.bean;
 2 
 3 public class ConditionUser {
 4     private String name;
 5     private int minAge;
 6     private int maxAge;
 7     public ConditionUser() {}
 8     public ConditionUser(String name,int minAge,int maxAge) {
 9         this.name=name;
10         this.minAge=minAge;
11         this.maxAge=maxAge;
12     }
13     public String getName() {
14         return name;
15     }
16     public void setName(String name) {
17         this.name = name;
18     }
19     public int getMinAge() {
20         return minAge;
21     }
22     public void setMinAge(int minAge) {
23         this.minAge = minAge;
24     }
25     public int getMaxAge() {
26         return maxAge;
27     }
28     public void setMaxAge(int maxAge) {
29         this.maxAge = maxAge;
30     }
31     @Override
32     public String toString() {
33         return "ConditionUser [name=" + name + ", minAge=" + minAge + ", maxAge=" + maxAge + "]";
34     }
35     
36 }

6.映射文件

 1 <?xml version="1.0" encoding="UTF-8"?>
 2 <!DOCTYPE mapper
 3     PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
 4     "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
 5 
 6 <mapper namespace="getUserLike">
 7     <select id="getUser" parameterType="com.cao.bean.ConditionUser" resultType="com.cao.bean.User">
 8         select * from d_user where
 9         <if test='name != "%null%"'>
10             name like #{name} and
11         </if>
12         age between #{minAge} and #{maxAge}
13     </select>
14 </mapper>

7.配置文件Configuration.xml

 1 <?xml version="1.0" encoding="UTF-8" ?>
 2 <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
 3 
 4 <configuration>
 5   <environments default="development">
 6     <environment id="development">
 7       <transactionManager type="JDBC">
 8         <property name="" value=""/>
 9       </transactionManager>
10       <dataSource type="UNPOOLED">
11         <property name="driver" value="com.mysql.jdbc.Driver"/>
12         <property name="url" value="jdbc:mysql://127.0.0.1:3308/mybatis"/>
13         <property name="username" value="root"/>
14         <property name="password" value="123456"/>
15       </dataSource>
16     </environment>
17   </environments>
18 
19    <mappers>
20       <mapper resource="com/cao/sql/config/users.xml"/>
21    </mappers>
22 
23 </configuration>

8.测试类

 1 package com.cao.test;
 2 
 3 import java.io.IOException;
 4 import java.io.Reader;
 5 import java.util.List;
 6 
 7 import org.apache.ibatis.io.Resources;
 8 import org.apache.ibatis.session.SqlSession;
 9 import org.apache.ibatis.session.SqlSessionFactory;
10 import org.apache.ibatis.session.SqlSessionFactoryBuilder;
11 import org.junit.Test;
12 
13 import com.cao.bean.ConditionUser;
14 import com.cao.bean.User;
15 
16 
17 public class MainTest {
18     @Test
19     public void test1() throws Exception {
20         Reader reader=Resources.getResourceAsReader("com/cao/config/Configuration.xml");
21         SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(reader);
22         SqlSession sqlSession=sqlSessionFactory.openSession(true);                //true后是自动提交
23         //
24         String statement="getUserLike.getUser";
25         String name="o";
26         ConditionUser conditionUser=new ConditionUser("%"+name+"%",13,18);
27         //
28         List<User> selectList = sqlSession.selectList(statement, conditionUser);    
29         System.out.println(selectList);
30         sqlSession.close();
31     }
32 }

9.效果