动态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);
insert into d_user(name,age) values('Jhon',16);
insert into d_user(name,age) values('Bart',15);
insert into d_user(name,age) values('Lisa',17);
问题分析
查询名字中带“o”的，并且年龄在13–18岁之间的user
普通sql查询
select * from d_user where name like '
%o%' and age between 13 and 18;
+
| id | name | age |
+
| 2 | Bob | 13 |
| 4 | Jhon | 16 |
+
2 rows in
set
使用mybatis查询
封装查询条件的bean
package com.bart.mybatis.beans;
/**
* 查询条件的封装类
* @author hp
*
*/
public class ConditionUser {
@Override
public String
toString() {
return "ConditionUser [name=" + name +
", minAge=" + minAge
+
", 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;
}
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();
}
}
配置mapper
<?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.bart.mybatis.mapper.conditionUserMapper">
<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>
配置conf.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>
<properties resource="db.properties">
</properties>
<typeAliases>
<typeAlias type="com.bart.mybatis.beans.User" alias="_User"/>
<typeAlias type="com.bart.mybatis.beans.ConditionUser" alias="_ConditionUser"/>
</typeAliases>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC" />
<dataSource type="POOLED">
<property name="driver" value="${driver}" />
<property name="url" value="${url}" />
<property name="username" value="${name}" />
<property name="password" value="${password}" />
</dataSource>
</environment>
</environments>
<mappers>
resource="com/bart/mybatis/mapper/conditionUserMapper.xml"/>
</mappers>
</configuration>
创建测试
@Test
public void Test(){
SqlSessionFactory factory = DBUtil.getSessionFactory();
SqlSession session = factory.openSession();
String statement=
"com.bart.mybatis.mapper.conditionUserMapper.getUser";
String name =
"o";
ConditionUser cu =
new ConditionUser(
"%"+name+
"%",
13,
18);
List<User> list = session.selectList(statement,cu);
System.out.println(list);
session.close();
}
结果
当String name = "null"; 相当于只查询年龄13–18 的user
[
User [id=
2, name=Bob, age=
13],
User [id=
3, name=Jack, age=
18],
User [id=
4, name=Jhon, age=
16],
User [id=
5, name=Bart, age=
15],
User [id=
6, name=Lisa, age=
17],
User [id=
7, name=Lisa, age=
17]
]
当String name = "o";相当于查询名字中带“o”的年龄在13–18 之间的user
[User [id=
2, name=Bob, age=
13],
User [id=
4, name=Jhon, age=
16]]
总结
在mapper中配置select查询的时候用到了类似于JSTL的判断语句，当满足该条件时，包含的条件作为查询条件，否则不作为查询条件，实现了SQL的动态查询
转载请注明原文地址: https://ju.6miu.com/read-600347.html
