动态sql和模糊查询
<?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.hous.day6.userMapper">
<!--
根据id查询到一个对象
模糊查询:name like #{name} 或者 name like '%${name}%'
查询条件and处理:使用<where>标签
-->
<select id="getUser" parameterType="com.hous.day6.ConditionUser"
resultType="com.hous.day6.User">
select * from users
<where>
<if test="name != '%null%'">
and name like #{name}
</if>
<if test="minAge != 0 and maxAge != 0">
and age between #{minAge} and #{maxAge}
</if>
</where>
</select>
</mapper>
<?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"/> <dataSource type="POOLED"> <property name="driver" value="com.mysql.jdbc.Driver"/> <property name="url" value="jdbc:mysql://localhost:3306/mybatis"/> <property name="username" value="root" /> <property name="password" value="root" /> </dataSource> </environment> </environments> <mappers> <mapper resource="com/hous/day6/userMapper.xml" /> </mappers> </configuration>
package com.hous.day6;
import static org.junit.Assert.*;
import java.io.InputStream;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Before;
import org.junit.Test;
public class MyTest {
private SqlSessionFactory factory = null;
@Before
public void setUp() throws Exception {
String resource = "day6/config.xml";
InputStream config = MyTest.class.getClassLoader().getResourceAsStream(resource);
factory = new SqlSessionFactoryBuilder().build(config);
}
@Test
public void testGetUsers() {
SqlSession session = factory.openSession();
String statement = "com.hous.day6.userMapper.getUser";
String name = null;
ConditionUser conditionUser = new ConditionUser("%"+name+"%", 1, 100);
List<User> users = session.selectList(statement, conditionUser);
session.commit();
session.close();
System.out.println("查询结果:" + users);
}
}
package com.hous.day6;
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 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;
}
}
package com.hous.day6;
public class User {
private int id;
private String name;
private int 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 + "]";
}
}