mybatis的Dao层sql示例

忘是亡心i 2024-03-26 18:14 15阅读 0赞

一、循环批量插入及if条件

// 查询字段
    String columnAll = " id,item,position,store,create_time,create_by";

@Select("<script> "
                + "select"+ columnAll +" from tb_name WITH(NOLOCK) "
                + " <where> "
                + " <if test='param.position!=null'> AND position=#{param.position} </if> "
                + " <if test='param.storeId!=null'> AND store_id=#{param.storeId} </if> "
    
                + "<if test='param.itemNbrList != null and param.itemNbrList.size()>0 '>AND item_nbr in "
                + "<foreach collection='param.itemNbrList' index='index' item='item' open='(' separator=',' close=')'>"
                +" #{item}"
                +" </foreach>"
                +" </if>"
    
                + " </where> "
    
                + " ORDER BY position asc,item asc"
                + "</script>")
        List<ProductScope> queryAll(@Param("param") ProductScopeQuery productScope);

2、循环批量更新及foreach条件

@Update("<script> "
                + " update shelves_order_detail "
                + " <trim prefix='set' suffixOverrides=','> "
                + " 	<trim prefix='real_quantity=case' suffix='end,'> "
                + " 		 <foreach collection='list' item='item'> "
                + " 			 <if test='item.realQuantity!=null'> "
                + " 				when id=#{item.id} then #{item.realQuantity} "
                + " 			 </if> "
                + " 		 </foreach> "
                + " 	</trim> "
                + " 	<trim prefix='defect_reasons=case' suffix='end,'> "
                + " 		 <foreach collection='list' item='item'> "
                + " 			 <if test='item.defectReasons!=null'> "
                + " 				when id=#{item.id} then #{item.defectReasons} "
                + " 			 </if> "
                + " 		 </foreach> "
                + " 	</trim> "
                + " 	<trim prefix='update_time=case' suffix='end,'> "
                + " 		 <foreach collection='list' item='item'> "
                + " 			when id=#{item.id} then getdate() "
                + " 		 </foreach> "
                + " 	</trim> "
                + "  </trim> "
                + " <where> "
                + " <foreach item='item' collection='list' open=' and ' separator=' or '>  "
                + " 	(id=#{item.id}) "
                + " </foreach> "
                + " </where> "
                + " </script>")
        int updateBatch(List<ShelvesOrderDetail> shelvesOrderDetailList);