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

我会带着你远行 2021-09-10 23:38 581阅读 0赞

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

项目结构如图：

![Center][]

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>
    
    	
    	
    	<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>
    	
    	
    	
    	<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="com.mybatis.mapper.userMapper">
    
    
     
    	<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>
    
    	
    	
    	<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>
    	
    	
    	
    	<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();
    		}

[Center]: /images/20210813/4616d72d08f444a2b6fbfc502a46cb0f.png