Day27——MyBatis动态SQL if where

Dear 丶 2023-07-04 15:31 1阅读 0赞

# 一. 例子 #

EmployeeMapperDynamicSQL.java

package com.atguigu.mybatis.dao;
    
    import java.util.List;
    
    import com.atguigu.mybatis.beans.Employee;
    
    public interface EmployeeMapperDynamicSQL { 
    
    	
    	public List<Employee> selectEmployeeByConditionIf(Employee Condition);
    	
    }

EmployeeMapperDynamicSQL.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.atguigu.mybatis.dao.EmployeeMapperDynamicSQL">
    	
    	
    	<select id="selectEmployeeByConditionIf" resultType="com.atguigu.mybatis.beans.Employee">
    	     select id, last_name, email, gender from tbl_employee
    	     
    	     <where>
    		     
    		     <if test="id!=null">
    		        and id = #{id}
    		     </if>
    		     <if test="lastName!=null && lastName!=""">
    	  	        and last_name = #{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>
    	
    </mapper>

TestMyBatisDynamicSQL.java

package com.atguigu.mybatis.test;
    
    import java.io.InputStream;
    import java.sql.Connection;
    import java.sql.PreparedStatement;
    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.beans.Employee;
    import com.atguigu.mybatis.dao.EmployeeMapperDynamicSQL;
    
    public class TestMyBatisDynamicSQL { 
    
    
    	@Test
    	public void testIf() throws Exception{ 
    		SqlSessionFactory ssf = getSqlSessionFactory();
    		SqlSession session = ssf.openSession();
    		try { 
    			EmployeeMapperDynamicSQL mapper = session.getMapper(EmployeeMapperDynamicSQL.class);
    			Employee condition = new Employee();
    // condition.setId(1001);
    			condition.setLastName("刚哥");
    // condition.setEmail("gg@sina.com");
    // condition.setGender("1");
    			
    			List<Employee> emps = mapper.selectEmployeeByConditionIf(condition);
    			System.out.println(emps);
    			
    			session.commit();
    		} finally { 
    			// TODO: handle finally clause
    			session.close();
    		}
    		
    	}
    	
    	
    	public SqlSessionFactory getSqlSessionFactory() throws Exception { 
    		String resource = "mybatis-config.xml"; 
    		InputStream inputStream = 
    				Resources.getResourceAsStream(resource); 
    		SqlSessionFactory sqlSessionFactory = 
    				new SqlSessionFactoryBuilder().build(inputStream);
    		return sqlSessionFactory;
    	}
    }