Mybatis编写sql案例

[toc]

列举开发中常用的sql编写案例

返回受影响行数

@Update({
   "update VirtualWarehouse set deleted=1 where id=#{virtualWarehouse.id}"})
@Options(useGeneratedKeys=true )
Long  deleteVirtualWarehouse(@Param("virtualWarehouse") VirtualWarehouse virtualWarehouse);

for循环

@Select({
""})
List findWarehouseListByIds(List ids);

### 遍历Set

```java
@Select({"<script>",        
"select 3  as status  ,'可用' as statusName, 
case COUNT(*) when null  then 0 else COUNT(*) end as 
statusCount from Warehouse",       
" where organizationId in ( ",        
" <foreach collection='collection' item='item' open='(' close=')' separator=','>",       
"       #{item}",        
"</foreach>",       
")",       
"</script>"})
WarehouseStatusCount 
findAvailableStatusWarehouseCount(Set<Long> organizationIds);

If语句

@Update({
          
"<script>",     
    "update VirtualWarehouse set ",   
    "name=#{virtualWarehouse.name} ",    
    " , updateTime=getdate()",     
    "<if test=\" virtualWarehouse.description !=null  and           virtualWarehouse.description !='' \" > ",   
     "   description=#{virtualWarehouse.description} ",  
    "</if>",   
    " where id=#{virtualWarehouse.id}",    
"</script>"
})
@Options(useGeneratedKeys = true)Long updateVirtualWarehouse(@Param("virtualWarehouse") VirtualWarehouse virtualWarehouse);

If-Else语句

@Select({
   
    "<script>",
    " select * from user ",
    "<where>",
    "<choose>",
    "   <when  test=\" user.name !=null and user.name !='' \" >",
    "       and name like CONCAT('%' , #{user.name} , '%')",
    "   </when>",
    "   <otherwise>",
    "       and name =#{currentUserName} ",
    "   </otherwise>",
    "</choose>",
    "</where>",
    "</script>"
})
List<User> findUser(@Param("user") User user, @Param("currentUserName") String currentUserName);

like 模糊查询

@Select({
   
"<script>",      
"   select count(id)  from Warehouse warehouse  ",     
"<where>",      
" organizationId in (",    
" <foreach collection='organizationIds' item='item' open='(' close=')' separator=','>",     
"       #{item}",    
"</foreach>",       
")",   
"   <if test=\" warehouse.id !=null and warehouse.id !=0 
\" >" ,          
" and  Warehouse.id=#{warehouse.id}" ,    
"</if>",      
"   <if test=\" warehouse.name !=null and warehouse.name 
!='' \" >" ,           
" and  Warehouse.name like CONCAT('%' , 
#{
   warehouse.name} , '%') " ,       
"</if>",     
"   <if test=\" warehouse.type !=null and 
warehouse.type!=0 \" >" ,        
" and  Warehouse.type=#{warehouse.type}" ,     
"   </if>",     
"</where>",    
"</script>",})
Integer countFindWarehouseList(@Param("warehouse") Warehouse warehouse ,   @Param("organizationIds") Set<Long> organizationIds);

或者

 sku.name like '%'+ #{
   keywords} + '%'  ",

if判断中带for循环

/** 
* 查询可用仓库，无分页 
*
* @return 
*/

@Select({
   "<script>",       
"select  * ",       
"from Warehouse   ",      
"<where>",       
"   <if test=\" searchProvisoDTO.name !=null and 
searchProvisoDTO.name !='' \" > ",       
"       and  name like CONCAT('%' , 
#{
   searchProvisoDTO.name} , '%') ",       
"   </if>",       
"   <if test=\" searchProvisoDTO.ids !=null  \" > ",       
"       <foreach collection='searchProvisoDTO.ids' 
item='id' open='and id in (' close=')'  separator=','>",       
"           #{id}",       
"       </foreach>",       
"   </if>",       
"</where>",       
"</script>",})List<Warehouse> 
findWarehouseListWithNoPage(@Param("searchProvisoDTO") 
SearchProvisoDTO searchProvisoDTO );

insert，返回主键

/** 
* 创建 * 
* @return 
*/
@Insert({
           
"<script>",        
" insert into DeliveryRouteStreet( deliveryRouteId , streetId , deleted , createTime) ",        
" VALUES ",        
"(#{
   deliveryRouteStreet.deliveryRouteId}, 
#{
   deliveryRouteStreet.streetId} ",        
" , 0 , now())",        
"</script>"})
@Options(useGeneratedKeys = true)
Long insertDeliveryRouteStreet(@Param("deliveryRouteStreet") 
DeliveryRouteStreet deliveryRouteStreet);

复杂分页查询

    @Select({
            "<script>",
            " SELECT u.id ,  u.account , u.username , u.mobile , u.dept_logic_id as deptId , u.org_logic_id as orgId ,  s.times , s.login_time  ",
            "<if test=\" condition.loginState !=null and condition.accounts !=null and  condition.accounts.size > 0 \" > ",
            "<choose>",
            "   <when  test=\"condition.accounts !=null and  condition.accounts.size > 0 and  condition.loginState !=null  and condition.loginState ==1 \" >",
            "       , '在线' as state ",
            "   </when>",
            "   <when  test=\"condition.accounts !=null and  condition.accounts.size > 0 and  condition.loginState !=null  and condition.loginState == 2 \" >",
            "       , '注销' as state ",
            "   </when>",
            "   <otherwise>",
            "     ,  '' as state ",
            "   </otherwise>",
            "</choose>",
            "</if>",
            " from bams_user u  LEFT JOIN login_statistics s on u.account=s.account ",
            "<where>",
            "   u.has_del=0 ",
            "   <if test=\"condition.account != null and condition.account !='' \"> and u.account like CONCAT('%' , #{condition.account} , '%') </if>",
            "   <if test=\"condition.username != null and condition.username !='' \"> and u.username like CONCAT('%' , #{condition.username} , '%') </if>",
            "   <if  test=\" condition.areaDeptId !=null  and condition.areaDeptId !='' \" >",
            "          and u.dept_logic_id like  CONCAT( #{condition.areaDeptId} , '%') ",
            "   </if>",
            "   <if test=\"condition.ids !=null and  condition.ids.size > 0 \" > ",
            "       <foreach collection='condition.ids' item='id' open='and u.id in (' close=')'  separator=','>",
            "           #{id}",
            "       </foreach>",
            "   </if>",
            "   <if test=\"condition.accounts !=null and  condition.accounts.size > 0  and condition.loginState !=null  \" > ",
            "       <choose>",
            "       <when  test=\" condition.loginState !=null   and condition.loginState == 1 \" >",
            "           <foreach collection='condition.accounts' item='id' open='and u.account in (' close=')'  separator=','>",
            "               #{id}",
            "           </foreach>",
            "       </when>",
            "       <otherwise>",
            "           <foreach collection='condition.accounts' item='id' open='and u.account not  in (' close=')'  separator=','>",
            "               #{id}",
            "           </foreach>",
            "       </otherwise>",
            "       </choose>",
            "   </if>",
            "   <if  test=\" condition.loginRecord !=null \" >",
            "       <choose>",
            "       <when  test=\" condition.loginRecord == 1 \" >",
            "           and  ( s.times &gt; 0) ",
            "       </when>",
            "       <when  test=\" condition.loginRecord == 2 \" >",
            "            and  (ISNULL(s.times) or s.times=0 ) ",
            "       </when>",
            "       </choose>",
            "   </if>",
            "   <if test=\"condition.startTime != null \"> and  s.login_time &gt;= #{condition.startTime} </if>",
            "   <if test=\"condition.endTime != null  \"> and  s.login_time &lt;= #{condition.endTime} </if>",
            "</where>",
            " order by u.id   ",
            "<if test=\"condition.needPage == true \"> ",
            "   limit #{condition.pageNum},#{condition.pageSize}  ",
            "</if>",
            "</script>"
    })
    List<LoginStatisticsDto> findPage(@Param("condition") LoginStatisticsCondition condition);

更多例子

https://www.jianshu.com/p/93ff4d89250c