Mybatis篇-（五）动态SQL

旧城等待， 2023-10-04 12:39 25阅读 0赞

## 五、Mybatis-动态SQL ##

### if（判断）&OGNL ###

EmployeeMapperDynamicSQL.xml

<select id="getEmpsByConditionIf" resultType="com.cvzhanshi.mybatis.entity.Employee">
            select * from 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>
        </select>

测试

@Test
    	public void testDynamicSQL() throws IOException{
        
    		SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
    		SqlSession sqlSession = sqlSessionFactory.openSession();
    
    		try{
        
    			EmployeeMapperDynamicSQL mapper = sqlSession.getMapper(EmployeeMapperDynamicSQL.class);
    			Employee employee = new Employee(3, "%e%", "cvzhanshi@163.com", null);
    			List<Employee> empsByConditionIf = mapper.getEmpsByConditionIf(employee);
    			for (Employee employee1 : empsByConditionIf) {
        
    				System.out.println(employee1);
    			}
    		}finally{
        
    			sqlSession.close();
    		}
    	}

运行效果  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1NDA4Mzkw_size_16_color_FFFFFF_t_70]

> 这样些会出现的问题：当id为null时，拼接sql会出错，多出一个and  
> ![在这里插入图片描述][20210507152258225.png]

解决方法：1、用where标签（能解决前面多了一个and）

2、用trim标签（自定义拼接规则）

**OGNL**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1NDA4Mzkw_size_16_color_FFFFFF_t_70 1]

### where（查询条件） ###

> 当发生上述情况时，用```<where>``</where>```代替where可以解决前面多了一个and错误情况

xml

<select id="getEmpsByConditionIf" resultType="com.cvzhanshi.mybatis.entity.Employee">
            select * from 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>

测试（其中id为null）

@Test
    	public void testDynamicSQL() throws IOException{
        
    		SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
    		SqlSession sqlSession = sqlSessionFactory.openSession();
    
    		try{
        
    			EmployeeMapperDynamicSQL mapper = sqlSession.getMapper(EmployeeMapperDynamicSQL.class);
    			Employee employee = new Employee(null, "%e%", "cvzhanshi@163.com", null);
    			List<Employee> empsByConditionIf = mapper.getEmpsByConditionIf(employee);
    			for (Employee employee1 : empsByConditionIf) {
        
    				System.out.println(employee1);
    			}
    		}finally{
        
    			sqlSession.close();
    		}
    	}

运行效果  
![在这里插入图片描述][20210507152230335.png]

### trim（自定义拼接规则） ###

> 当后面多出的and或者or where标签不能解决

*  trim
    
     *  prefix：prefix给拼串后的整个字符串加一个前缀
     *  prefixOverrides：去掉整个字符串前面多余的字符
     *  suffix：suffix给拼串后的整个字符串加一个后缀
     *  suffixOverrides：去掉整个字符串后面多余的字符

示例

xml

<select id="getEmpsByConditionTrim" resultType="com.cvzhanshi.mybatis.entity.Employee">
            select * from employee
            
            
            <trim prefix="where" suffixOverrides="and">
                <if test="id!=null">
                    id=#{id} and
                </if>
                <if test="lastName!=null && lastName!=""">
                    last_name like #{lastName} and
                </if>
                <if test="email!=null and email.trim()!=""">
                    email=#{email} and
                </if>
                
                <if test="gender==0 or gender==1">
                    gender=#{gender}
                </if>
            </trim>
        </select>

测试

@Test
    	public void testDynamicSQL() throws IOException{
        
    		SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
    		SqlSession sqlSession = sqlSessionFactory.openSession();
    
    		try{
        
    			EmployeeMapperDynamicSQL mapper = sqlSession.getMapper(EmployeeMapperDynamicSQL.class);
    			Employee employee = new Employee(null, "%e%", "cvzhanshi@163.com", null);
    			//测试trim
    			List<Employee> empsByConditionTrim = mapper.getEmpsByConditionTrim(employee);
    			for (Employee employee1 : empsByConditionTrim) {
        
    				System.out.println(employee1);
    			}
    		}finally{
        
    			sqlSession.close();
    		}
    	}

运行效果  
![在这里插入图片描述][20210507152214770.png]

### choose（分支选择） ###

xml

<select id="getEmpsByConditionChoose" resultType="com.cvzhanshi.mybatis.entity.Employee">
            select * from tbl_employee
            <where>
                
                <choose>
                    <when test="id!=null">
                        id=#{id}
                    </when>
                    <when test="lastName!=null">
                        last_name like #{lastName}
                    </when>
                    <when test="email!=null">
                        email = #{email}
                    </when>
                    <otherwise>
                        gender = 0
                    </otherwise>
                </choose>
            </where>
        </select>

测试

@Test
    	public void testDynamicSQL() throws IOException{
        
    		SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
    		SqlSession sqlSession = sqlSessionFactory.openSession();
    
    		try{
        
    			EmployeeMapperDynamicSQL mapper = sqlSession.getMapper(EmployeeMapperDynamicSQL.class);
    			Employee employee = new Employee(null, "%e%", "cvzhanshi@163.com", null);
    			//测试choose
    			List<Employee> empsByConditionChoose = mapper.getEmpsByConditionChoose(employee);
    			for (Employee employee1 : empsByConditionChoose) {
        
    				System.out.println(employee1);
    			}
    		}finally{
        
    			sqlSession.close();
    		}
    	}

运行效果  
![在这里插入图片描述][20210507152154520.png]

### set与if结合动态更新 ###

xml

<update id="updateEmp">
            
            update employee
            <set>
                <if test="lastName!=null">
                    last_name=#{lastName},
                </if>
                <if test="email!=null">
                    email=#{email},
                </if>
                <if test="gender!=null">
                    gender=#{gender}
                </if>
            </set>
            where id=#{id}
            
        </update>

测试

@Test
    	public void testDynamicSQL() throws IOException{
        
    		SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
    		SqlSession sqlSession = sqlSessionFactory.openSession();
    
    		try{
        
    			EmployeeMapperDynamicSQL mapper = sqlSession.getMapper(EmployeeMapperDynamicSQL.class);
    			Employee employee = new Employee(1, "ursula", "cvzhanshi@163.com", null);
    			//测试set
    			mapper.updateEmp(employee);
    			sqlSession.commit();
    		}finally{
        
    			sqlSession.close();
    		}
    	}

运行效果  
![在这里插入图片描述][20210507152142482.png]

### foreach ###

#### 遍历集合 ####

*  foreach
    
     *  collection：指定要遍历的集合（list类型的参数会特殊处理封装在map中，map的key就叫list）
     *  item：将当前遍历出的元素赋值给指定的变量
     *  separator:每个元素之间的分隔符
     *  open：遍历出所有结果拼接一个开始的字符
     *  close:遍历出所有结果拼接一个结束的字符
     *  index:索引：遍历list的时候是index就是索引，item就是当前值  
        遍历map的时候index表示的就是map的key，item就是map的值
     *  \#\{变量名\}就能取出变量的值也就是当前遍历出的元素

示例

xml

<select id="getEmpsByConditionForeach" resultType="com.cvzhanshi.mybatis.entity.Employee">
            select * from employee
           
            <foreach collection="ids" item="item_id" separator=","
                     open="where id in(" close=")">
                #{item_id}
            </foreach>
        </select>

测试

@Test
    	public void testForeach() throws IOException{
        
    		SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
    		SqlSession sqlSession = sqlSessionFactory.openSession();
    		try{
        
    			EmployeeMapperDynamicSQL mapper = sqlSession.getMapper(EmployeeMapperDynamicSQL.class);
    			List<Employee> empsByConditionForeach = mapper.getEmpsByConditionForeach(Arrays.asList(1, 2, 3, 4));
    			for (Employee byConditionForeach : empsByConditionForeach) {
        
    				System.out.println(byConditionForeach);
    			}
    		}finally{
        
    			sqlSession.close();
    		}
    	}

运行效果：  
![在这里插入图片描述][20210507152131770.png]

#### 批量插入（mysql） ####

> 两种方式：详情件xml代码

xml

<insert id="addEmps">
            insert into employee(last_name,email,gender,d_id)
            values
            <foreach collection="emps" item="emp" separator=",">
                (#{emp.lastName},#{emp.email},#{emp.gender},#{emp.dept.id})
            </foreach>
        </insert>

测试

@Test
    	public void testBatchSave() throws IOException{
        
    		SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
    		SqlSession sqlSession = sqlSessionFactory.openSession();
    		try{
        
    			EmployeeMapperDynamicSQL mapper = sqlSession.getMapper(EmployeeMapperDynamicSQL.class);
    			ArrayList<Employee> employees = new ArrayList<>();
    			employees.add(new Employee(null,"fsadf","123@qq.com","1",new Department(1)));
    			employees.add(new Employee(null,"jhgj","123@qq.com","1",new Department(1)));
    			mapper.addEmps(employees);
    			sqlSession.commit();
    		}finally{
        
    			sqlSession.close();
    		}
    	}

运行结果  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1NDA4Mzkw_size_16_color_FFFFFF_t_70 2]

#### 批量插入（Oracle） ####

xml

<insert id="addEmps" databaseId="oracle">
    	 	
    	 	
    	 	
    	 	
    	 	insert into employees(
    	 		
    	 		<include refid="insertColumn">
    	 			<property name="testColomn" value="abc"/>
    	 		</include>
    	 	)
    	 			<foreach collection="emps" item="emp" separator="union"
    	 				open="select employees_seq.nextval,lastName,email from("
    	 				close=")">
    	 				select #{emp.lastName} lastName,#{emp.email} email from dual
    	 			</foreach>
    	 </insert>

测试与mysql一样

### 两个内置参数`_parameter&_databaseId`和bind（绑定） ###

> \_parameter:代表整个参数
> 
>  *  单个参数：\_parameter就是这个参数
>  *  多个参数：参数会被封装为一个map；\_parameter就是代表这个map
> 
> \_databaseId:如果配置了databaseIdProvider标签
> 
>  *  \_databaseId就是代表当前数据库的别名
> 
> bind：可以将OGNL表达式的值绑定到一个变量中，方便后来引用这个变量的值

xml

<select id="getEmpsTestInnerParameter" resultType="com.cvzhanshi.mybatis.entity.Employee">
            
            <bind name="_lastName" value="'%'+lastName+'%'"/>
            <if test="_databaseId=='mysql'">
                select * from tbl_employee
                <if test="_parameter!=null">
                    where last_name like #{lastName}
                </if>
            </if>
            <if test="_databaseId=='oracle'">
                select * from employees
                <if test="_parameter!=null">
                    where last_name like #{_parameter.lastName}
                </if>
            </if>
        </select>

### sql（抽取可重复的sql片段） ###

> 抽取可重用的sql片段。方便后面引用
> 
>  *  sql抽取：经常将要查询的列名，或者插入用的列名抽取出来方便引用
>  *  include来引用已经抽取的sql
>  *  include还可以自定义一些property，sql标签内部就能使用自定义的属性
>     
>      *  include-property：取值的正确方式$\{prop\},\#\{不能使用这种方式\}

示例

<include refid="insertColumn">
        <property name="testColomn" value="abc"/>
    </include>
    
    
    <sql id="insertColumn">
    	  		<if test="_databaseId=='oracle'">
    	  			employee_id,last_name,email
    	  		</if>
    	  		<if test="_databaseId=='mysql'">
    	  			last_name,email,gender,d_id
    	  		</if>
    	  </sql>

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1NDA4Mzkw_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/2021050715230940.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1NDA4Mzkw,size_16,color_FFFFFF,t_70
[20210507152258225.png]: https://img-blog.csdnimg.cn/20210507152258225.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1NDA4Mzkw_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/2021050715224766.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1NDA4Mzkw,size_16,color_FFFFFF,t_70
[20210507152230335.png]: https://img-blog.csdnimg.cn/20210507152230335.png
[20210507152214770.png]: https://img-blog.csdnimg.cn/20210507152214770.png
[20210507152154520.png]: https://img-blog.csdnimg.cn/20210507152154520.png
[20210507152142482.png]: https://img-blog.csdnimg.cn/20210507152142482.png
[20210507152131770.png]: https://img-blog.csdnimg.cn/20210507152131770.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1NDA4Mzkw_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20210507152119137.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1NDA4Mzkw,size_16,color_FFFFFF,t_70