MyBatis系列之模糊查询(注解方式+映射文件方式)

 

本博客以一个demo的形式说明模糊查询实现的两种方法：

项目结构如图：

 

ConditionUser.java:

package com.mybatis.entity;

public class ConditionUser {
	
	private String name;
	
	private int minAge;
	
	private int maxAge;
	
	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;
	}

	
}

 

User.java:

package com.mybatis.entity;

public class User {

	/*
	 * ID
	 */
	private int id;
	
	/*
	 * 用户姓名 
	 */
	private String name;
	
	/*
	 * age
	 */
	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 + "]";
	}
	
}

我们先以注解方式实现：

<?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" />  
     
   <!-- 实体类,简称 -设置别名 -->
	<typeAliases>
		<typeAlias alias="User" type="com.mybatis.entity.User" />
			</typeAliases>

	
	<!-- 读取db.properties里的数据，进行数据库配置 -->
	<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="${username}"/>
	            <property name="password" value="${password}"/>
			</dataSource>
		</environment>
	</environments>
	
	<!--  
		注册userMapper.xml文件 
	<mappers>
		<mapper resource="com/mybatis/mapper/userMapper.xml"/>
	</mappers>
	-->
	
	<mappers>
		<mapper class="com.mybatis.mapper.UserMapper"/>
			</mappers>
	
</configuration>

db.properties:

 

driver=com.mysql.jdbc.Driver
url=jdbc\:mysql\://localhost\:3306/db_mysql?useUnicode\=true&characterEncoding\=utf-8
username=root
password=111

 

 

 

 

 

UserMapper.java:

 

package com.mybatis.mapper;

import java.util.List;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import com.mybatis.entity.ConditionUser;
import com.mybatis.entity.User;

public interface UserMapper {
	@Insert("insert into users(name,age) values(#{name},#{age})")
	public void save(User user);
	@Update("update users set name=#{name},age=#{age} where id=#{id}")
	public boolean update(User user);
	@Delete("delete from users where id=#{id}")
	public boolean delete(int id);
	@Select("select * from users where id=#{id}")
	public User findById(int id);
	@Select("select * from users where name like #{name} and age >= #{minAge}")
	public List<User> findAll(ConditionUser u);
}

 

 

 

测试：

String resource = "mybatis-config.xml";
		try{
			//加载Mybatis的配置文件
			Reader reader = Resources.getResourceAsReader(resource);
			//构建sqlSession的工厂
	        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
			//创建可以执行sql的SqlSession
	        SqlSession session = sessionFactory.openSession();
//			List<User> list  = session.selectList("com.mybatis.mapper.userMapper.findById",new ConditionUser("%a%",0, 1));
//	        System.out.println(list);
	        UserMapper mapper = session.getMapper(UserMapper.class);
	        List<User> list = mapper.findAll(new ConditionUser("%a%", 0, 1));
	        System.out.println(list);
	        
	        Classes c = session.selectOne("com.mybatis.mapper.classesMapper.findById4",1);
	        System.out.println(c);
	      
	        
		}catch (Exception e) {
			e.printStackTrace();
		}
	}
	

 

 

然后以映射文件的方式实现：

注意注解方式和映射文件方式是会冲突的，所以要先删了注解的接口类

然后，在新建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，namespace的值习惯上设置成包名+sql映射文件名，这样就能够保证namespace的值是唯一的 
-->
<mapper namespace="com.mybatis.mapper.userMapper">

<!-- 
	在select标签中编写查询的SQL语句， 设置select标签的id属性为findById，id属性值必须是唯一的，不能够重复
    parameterType可以在MyBatis的配置文件里设置，也可以直接用包名+类名
 -->
 
	<insert id="save" parameterType="User">
	insert into users(id,name,age) value(#{name},#{age})
	</insert>
	<update id="update" parameterType="User">
	update users set name=#{name},age=#{age} where id=#{id}
	</update>
	<delete id="delete" parameterType="int">
	delete from users where id=#{id}
	</delete>
	<select id="findById" parameterType="com.mybatis.entity.ConditionUser" resultType="User">
	select * from users where age>=#{minAge}
	<if test='name!="%null%"'>and name like #{name}</if>
	</select>
	<select id="findAll" resultType="User">
	select * from users
	</select>
</mapper>

 

修改mybatis配置文件：

 

<?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" />  
     
   <!-- 实体类,简称 -设置别名 -->
	<typeAliases>
		<typeAlias alias="User" type="com.mybatis.entity.User" />
		
	</typeAliases>

	
	<!-- 读取db.properties里的数据，进行数据库配置 -->
	<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="${username}"/>
	            <property name="password" value="${password}"/>
			</dataSource>
		</environment>
	</environments>
	
	<!--  
		注册userMapper.xml文件 
	<mappers>
		<mapper resource="com/mybatis/mapper/userMapper.xml"/>
	</mappers>
	-->
	
	<mappers>
		<mapper resource="com/mybatis/mapper/userMapper.xml"/>
	</mappers>
	
</configuration>

 

 

 

 

测试：

 

String resource = "mybatis-config.xml";
		try{
			//加载Mybatis的配置文件
			Reader reader = Resources.getResourceAsReader(resource);
			//构建sqlSession的工厂
	        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
			//创建可以执行sql的SqlSession
	        SqlSession session = sessionFactory.openSession();
			List<User> list  = session.selectList("com.mybatis.mapper.userMapper.findById",new ConditionUser("%a%",0, 1));
	        System.out.println(list);
//	        UserMapper mapper = session.getMapper(UserMapper.class);
//	        List<User> list = mapper.findAll(new ConditionUser("%a%", 0, 1));
//	        System.out.println(list);
	        
	        Classes c = session.selectOne("com.mybatis.mapper.classesMapper.findById4",1);
	        System.out.println(c);
	      
	        
		}catch (Exception e) {
			e.printStackTrace();
		}