Mybitis常用语句

最新推荐文章于 2023-03-07 08:57:33 发布

景天

最新推荐文章于 2023-03-07 08:57:33 发布

阅读量485

点赞数 1

分类专栏： ORM框架文章标签： Mybatis

本文链接：https://blog.csdn.net/lixinyao5281/article/details/70225146

版权

ORM框架专栏收录该内容

5 篇文章 0 订阅

订阅专栏

MyBitis是企业常用的数据持久化工具之一，以下介绍几种常用的数据操作，Mybits的XML文件写法

一、单条insert语句

<insertid="insertUser" parameterType="java.util.Map"useGeneratedKeys="true">
    <selectKeyresultType="java.lang.Long" keyProperty="dto.id"order="BEFORE">
                     selectseq_USER.nextval+100 as id from dual
    </selectKey>
                insertinto user
                (            
                           id                             ,
                           user_no                        ,
                           org_id                         ,
                           user_state                     ,
                           user_name                      ,
                           card_id                        ,
                           bel_company                    ,
                           user_type                      ,
                           rank                           ,
                           area_manager                   ,
                           branch_manager                 ,
                           city_manager                   ,
                           org_manager                    ,
                           bteam_manager                  ,
                           team_manager                   ,
                           entry_date                          ,
                           mop                                          ,
                           base_perf_amt                     ,
                           owner_id                            ,
                           create_by                      ,
                           create_time                               ,
                           validate_state                      ,
                           area_id                               ,
                           state
                          
                )
                values(  #{dto.id}
                           ,#{dto.id,jdbcType=VARCHAR}
                           ,#{dto.orgId,jdbcType=VARCHAR}
                           ,#{dto.userState,jdbcType=VARCHAR}
                           ,#{dto.userName,jdbcType=VARCHAR}
                           ,#{dto.cardId,jdbcType=VARCHAR}
                           ,#{dto.belCompany,jdbcType=VARCHAR}
                          ,#{dto.userType,jdbcType=VARCHAR}
                           ,#{dto.rank,jdbcType=VARCHAR}
                           ,#{dto.areaManager,jdbcType=VARCHAR}
                           ,#{dto.branchManager,jdbcType=VARCHAR}
                           ,#{dto.cityManager,jdbcType=VARCHAR}
                           ,#{dto.orgManager,jdbcType=VARCHAR}
                           ,#{dto.bteamManager,jdbcType=VARCHAR}
                           ,#{dto.teamManager,jdbcType=VARCHAR}
                           ,sysdate
                           ,0
                           ,#{dto.basePerfAmt,jdbcType=DECIMAL}
                           ,#{dto.ownerId,jdbcType=VARCHAR}
                           ,#{dto.createBy,jdbcType=VARCHAR}
                           ,systimestamp
                           ,'1'
                           ,f_get_areaid(#{dto.orgId,jdbcType=VARCHAR})
                           ,'3'
                )
</insert>

二、批量insert语句

<!-- 批量新增对象 PRODUCT -->
<insertid="insertBatchProduct"parameterType="java.util.Map">
<foreachcollection="list" item="dto" index="index"open="begin" close="end;">       
                insertinto product
                (            
                           id                             ,
                           product_name                   ,
                           product_code                   ,
                           product_periods                ,
                           product_type                   ,
                           validate_state                 ,
                           product_state                  ,
                           create_time                    ,
                           create_by                      ,
                           modify_time                    ,
                           modify_by                      ,
                           memo                           
                )
                values(  seq_PRODUCT.nextval
                           ,#{dto.productName,jdbcType=VARCHAR}
                           ,#{dto.productCode,jdbcType=VARCHAR}
                           ,#{dto.productPeriods,jdbcType=VARCHAR}
                           ,#{dto.productType,jdbcType=VARCHAR}
                           ,#{dto.validateState,jdbcType=VARCHAR}
                           ,#{dto.productState,jdbcType=DECIMAL}
                           ,#{dto.createTime,jdbcType=TIMESTAMP}
                           ,#{dto.createBy,jdbcType=VARCHAR}
                           ,#{dto.modifyTime,jdbcType=TIMESTAMP}
                           ,#{dto.modifyBy,jdbcType=VARCHAR}
                           ,#{dto.memo,jdbcType=VARCHAR}
                );
     </foreach>
</insert>

三、查询并且批量insert语句查询

<!-- 新增对象 USER -->
<insert id="bakUserDatatoHis" parameterType="java.util.Map" >
insert into USERHIS
(
 id,
 user_no,
 card_id,
 user_name,
 user_type,
 rank,
 org_id,
 bel_company,
 area_manager,
 branch_manager,
 city_manager,
 org_manager,
 bteam_manager,
 team_manager,
 entry_date,
 user_state,
 mop,
 cancel_date,
 base_perf_amt,
 owner_id,
 validate_state,
 create_time,
 create_by,
 modify_time,
 modify_by,
 area_id,
 state,
 PAY_MONTH             
)
select
 seq_userhis.nextval,
 user_no,
 card_id,
 user_name,
 user_type,
 rank,
 org_id,
 bel_company,
 area_manager,
 branch_manager,
 city_manager,
 org_manager,
 bteam_manager,
 team_manager,
 entry_date,
 user_state,
 mop,
 cancel_date,
 base_perf_amt,
 owner_id,
 validate_state,
 #{dto.createTime,jdbcType=TIMESTAMP},
 create_by,
 #{dto.createTime,jdbcType=TIMESTAMP},
 modify_by,
 area_id,
 state,
 #{dto.payMonth,jdbcType=VARCHAR}
from user
</insert>

四、单条数据更新

<!-- 更新对象 USER -->
<updateid="updateUser" parameterType="java.util.Map">
         update user t1
               set
                           t1.card_id                        =#{dto.cardId,jdbcType=VARCHAR},
                           t1.user_type                     =#{dto.userType,jdbcType=VARCHAR},
                           t1.rank                          =#{dto.rank,jdbcType=VARCHAR},
                           t1.org_id                         =#{dto.orgId,jdbcType=VARCHAR},
                           t1.bel_company                   =#{dto.belCompany,jdbcType=VARCHAR},
                           t1.area_manager                  =#{dto.areaManager,jdbcType=VARCHAR},
                           t1.branch_manager                 =#{dto.branchManager,jdbcType=VARCHAR},
                           t1.city_manager                  =#{dto.cityManager,jdbcType=VARCHAR},
                           t1.org_manager                   =#{dto.orgManager,jdbcType=VARCHAR},
                           t1.bteam_manager                  =#{dto.bteamManager,jdbcType=VARCHAR},
                           t1.team_manager                  =#{dto.teamManager,jdbcType=VARCHAR},
                           t1.entry_date                    =#{dto.entryDate,jdbcType=DATE},
                           t1.user_state                     =#{dto.userState,jdbcType=VARCHAR},
                           t1.mop                           =#{dto.mop,jdbcType=DECIMAL},
                           t1.cancel_date                   =#{dto.cancelDate,jdbcType=DATE},
                           t1.base_perf_amt                 =#{dto.basePerfAmt,jdbcType=DECIMAL},                      
                           t1.validate_state                 ='1',                       
                           t1.modify_time                    =systimestamp,
                           t1.modify_by                     =#{dto.modifyBy,jdbcType=VARCHAR},
                           t1.state                                  =#{dto.state,jdbcType=VARCHAR}
         where t1.id = #{dto.id}
</update>

五、批量删除语句

<!-- 主键删除 USER -->
<deleteid="deleteUserByID" parameterType="java.util.Map">
     delete from user t1 wheret1.ID in (${ids} )
</delete>
<!-- 主键假删除 USER -->
<updateid="deleteUserByPrimaryKey"parameterType="java.util.Map">
         update sm_t_user t1
               set  t1.validate_state='0'    
         where t1.id in( ${ids})
</update>