MyBatis总结(二十八)--任务39：MyBatis_动态sql_if_判断&OGNL

太过爱你忘了你带给我的痛 2022-05-12 08:24 105阅读 0赞

本文内容来自尚硅谷

Junit

package com.atguigu.mybatis.test;
    
    import java.io.IOException;
    import java.io.InputStream;
    import java.util.ArrayList;
    import java.util.Arrays;
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    
    import org.apache.ibatis.io.Resources;
    import org.apache.ibatis.session.SqlSession;
    import org.apache.ibatis.session.SqlSessionFactory;
    import org.apache.ibatis.session.SqlSessionFactoryBuilder;
    import org.junit.Test;
    
    import com.atguigu.mybatis.bean.Department;
    import com.atguigu.mybatis.bean.Employee;
    import com.atguigu.mybatis.dao.DepartmentMapper;
    import com.atguigu.mybatis.dao.EmployeeMapper;
    import com.atguigu.mybatis.dao.EmployeeMapperAnnotation;
    import com.atguigu.mybatis.dao.EmployeeMapperDynamicSQL;
    import com.atguigu.mybatis.dao.EmployeeMapperPlus;
    
    /**
     * 1、接口式编程
     * 	原生：		Dao		====>  DaoImpl
     * 	mybatis：	Mapper	====>  xxMapper.xml
     * 
     * 2、SqlSession代表和数据库的一次会话；用完必须关闭；
     * 3、SqlSession和connection一样她都是非线程安全。每次使用都应该去获取新的对象。
     * 4、mapper接口没有实现类，但是mybatis会为这个接口生成一个代理对象。
     * 		（将接口和xml进行绑定）
     * 		EmployeeMapper empMapper =	sqlSession.getMapper(EmployeeMapper.class);
     * 5、两个重要的配置文件：
     * 		mybatis的全局配置文件：包含数据库连接池信息，事务管理器信息等...系统运行环境信息
     * 		sql映射文件：保存了每一个sql语句的映射信息：
     * 					将sql抽取出来。	
     * 
     * 
     * @author lfy
     *
     */
    public class MyBatisTest {
    	
    
    	public SqlSessionFactory getSqlSessionFactory() throws IOException {
    		String resource = "mybatis-config.xml";
    		InputStream inputStream = Resources.getResourceAsStream(resource);
    		return new SqlSessionFactoryBuilder().build(inputStream);
    	}
    	
    	@Test
    	public void testDynamicSql() throws IOException{
    		SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
    		SqlSession openSession = sqlSessionFactory.openSession();
    		try{
    			EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
    			Employee employee = new Employee(1, "%e%", null, null);
    			List<Employee> emps = mapper.getEmpsByConditionIf(employee );
    			for (Employee emp : emps) {
    				System.out.println(emp);
    			}
    		}finally{
    			openSession.close();
    		}
    	}
    
    }

dao

package com.atguigu.mybatis.dao;
    
    import java.util.List;
    
    import org.apache.ibatis.annotations.Param;
    
    import com.atguigu.mybatis.bean.Employee;
    
    public interface EmployeeMapperDynamicSQL {
    	
    	public List<Employee> getEmpsTestInnerParameter(Employee employee);
    	
    	//携带了哪个字段查询条件就带上这个字段的值
    	public List<Employee> getEmpsByConditionIf(Employee employee);
    	
    	public List<Employee> getEmpsByConditionTrim(Employee employee);
    	
    	public List<Employee> getEmpsByConditionChoose(Employee employee);
    	
    	public void updateEmp(Employee employee);
    	
    	//查询员工id'在给定集合中的
    	public List<Employee> getEmpsByConditionForeach(@Param("ids")List<Integer> ids);
    	
    	public void addEmps(@Param("emps")List<Employee> emps);
    
    }

xml

遇见特殊符号应该去写转义字符&&

<select id="getEmpsByConditionIf" resultType="com.atguigu.mybatis.bean.Employee">
    	 	select * from tbl_employee
    	 	
    	 	<where>
    		 	
    		 	<if test="id!=null">
    		 		id=#{id}
    		 	</if>
    		 	<if test="lastName!=null && lastName!=""">
    		 		and last_name like #{lastName}
    		 	</if>
    		 	<if test="email!=null and email.trim()!=""">
    		 		and email=#{email}
    		 	</if> 
    		 	
    		 	<if test="gender==0 or gender==1">
    		 	 	and gender=#{gender}
    		 	</if>
    	 	</where>
    	 </select>

javabean

package com.atguigu.mybatis.bean;
    
    import org.apache.ibatis.type.Alias;
    
    @Alias("emp")
    public class Employee {
    	
    	private Integer id;
    	private String lastName;
    	private String email;
    	private String gender;
    	private Department dept;
    	
    	public Employee() {
    		super();
    	}
    	
    	
    	
    	public Employee(Integer id, String lastName, String email, String gender,
    			Department dept) {
    		super();
    		this.id = id;
    		this.lastName = lastName;
    		this.email = email;
    		this.gender = gender;
    		this.dept = dept;
    	}
    
    
    
    	public Employee(Integer id, String lastName, String email, String gender) {
    		super();
    		this.id = id;
    		this.lastName = lastName;
    		this.email = email;
    		this.gender = gender;
    	}
    	
    	
    	
    
    	public Department getDept() {
    		return dept;
    	}
    
    	public void setDept(Department dept) {
    		this.dept = dept;
    	}
    
    	public Integer getId() {
    		return id;
    	}
    	public void setId(Integer id) {
    		this.id = id;
    	}
    	public String getLastName() {
    		return lastName;
    	}
    	public void setLastName(String lastName) {
    		this.lastName = lastName;
    	}
    	public String getEmail() {
    		return email;
    	}
    	public void setEmail(String email) {
    		this.email = email;
    	}
    	public String getGender() {
    		return gender;
    	}
    	public void setGender(String gender) {
    		this.gender = gender;
    	}
    	@Override
    	public String toString() {
    		return "Employee [id=" + id + ", lastName=" + lastName + ", email="
    				+ email + ", gender=" + gender + "]";
    	}
    	
    	
    
    }