动态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})