MyBatis动态SQL详解与应用

动态 SQL 概述

MyBatis 提供了强大的动态 SQL 功能，使得 SQL 语句可以根据不同的条件进行灵活组合。以下是几种常用的动态 SQL 标签及其应用场景。

1. 条件判断：if 和 where

    SELECT * FROM t_employee
    
        
            id=#{id}
        
        
            AND last_name=#{lastName}
        
        
            AND email=#{email}
        
        
            AND gender=#{gender}
        
    

2. 自定义字符串截取规则：trim

    SELECT * FROM t_employee
    
        
            id=#{id} AND
        
        
            last_name=#{lastName} AND
        
        
            email=#{email} AND
        
        
            gender=#{gender}
        
    

3. 分支选择：choose (when, otherwise)

    SELECT * FROM t_employee
    
        
            
                id=#{id}
            
            
                last_name LIKE #{lastName}
            
            
                email=#{email}
            
            
                1=1
            
        
    

4. 封装查询和修改条件：where 和 set

UPDATE t_employee


last_name=#{lastName},


email=#{email},


gender=#{gender}



id=#{id}

5. 批量处理：foreach

    SELECT * FROM t_employee WHERE id IN
    
        #{item_id}
    

INSERT INTO t_employee(id, last_name, gender, email) VALUES(#{emp.id}, #{emp.lastName}, #{emp.gender}, #{emp.email})

6. 内置参数和 bind

    
    
        SELECT * FROM t_employee
        
            WHERE last_name LIKE #{_lastName}
        
    
    
        SELECT * FROM employee
        
            WHERE last_name=#{lastName}
        
    

7. 可重用 SQL 片段

    
        id, last_name, gender, email
    
    
        --------
    


    
        INSERT INTO t_employee() VALUES(#{emp.id}, #{emp.lastName}, #{emp.gender}, #{emp.email})