Mybatis入门（四）：动态sql

• if

List getEmpListByMoreTJ(Emp emp);

 
    select * from t_emp where 1=1 
     
        and ename = #{ename} 
     
     
        and age = #{age} 
     
     
        and sex = #{sex} 
     

• where

    
        select * from t_emp
        
            
                emp_name = #{empName}
            
            
                and age = #{age}
            
            
                or sex = #{sex}
            
            
                and email = #{email}
            
        
    

• trim

trim用于去掉或添加标签中的内容
常用属性：
prefix：在trim标签中的内容的前面添加某些内容
prefixOverrides：在trim标签中的内容的前面去掉某些内容
suffix：在trim标签中的内容的后面添加某些内容
suffixOverrides：在trim标签中的内容的后面去掉某些内容

    
        select  from t_emp
        
            
                emp_name = #{empName} and
            
            
                age = #{age} or
            
            
                sex = #{sex} and
            
            
                email = #{email}
            
        
    

• choose、when、otherwise

    
        select * from t_emp
        
            
                      // 当when标签中有一个满足条件时，直接使用这一个条件即可
                    emp_name = #{empName}
                
                
                    age = #{age}
                
                
                    sex = #{sex}
                
                
                    email = #{email}
                
                                                          // 当所有when标签都不满足时，使用otherwise标签中的条件
                    did = 1
                
            
        
    

• foreach

# 批量删除
# 方式1，写法1
int deleteMoreByArray(@Param("eids") Integer[] eids);

    
        delete from t_emp where eid in
        (
        
            #{eid}
        
        )
    

# 方式1，写法2
    
        delete from t_emp where eid in
        
            #{eid}
        
    

# 方式2
    
        delete from t_emp where
        
            eid = #{eid}
        
    

# 测试
    @Test
    public void testDeleteMoreByArray(){
        SqlSession sqlSession = SqlSessionUtils.getSqlSession();
        DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);
        int result = mapper.deleteMoreByArray(new Integer[]{6, 7, 8});
        System.out.println(result);
    }

# 批量添加
int insertMoreByList(@Param("emps") List emps);
    
        insert into t_emp values
        
            (null,#{emp.empName},#{emp.age},#{emp.sex},#{emp.email},null)
        
    

# 测试
    @Test
    public void testInsertMoreByList(){
        SqlSession sqlSession = SqlSessionUtils.getSqlSession();
        DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);
        Emp emp1 = new Emp(null,"a1",23,"男","123@qq.com");
        Emp emp2 = new Emp(null,"a2",23,"男","123@qq.com");
        Emp emp3 = new Emp(null,"a3",23,"男","123@qq.com");
        List emps = Arrays.asList(emp1, emp2, emp3);
        System.out.println(mapper.insertMoreByList(emps));
    }

属性：
collection：设置要循环的数组或集合
item：表示集合或数组中的每一个数据
separator：设置循环体之间的分隔符
open：设置foreach标签中的内容的开始符
close：设置foreach标签中的内容的结束符

• sql片段

 eid,ename,age,sex,did  

select  from t_emp