mybatis 注解批量操作

 

 

有一点要注意，如果运用了，<set></set>语法，来mysql的批量更新是要我们主动去设置的,需要在配置数据库连接地址中加上&allowMultiQueries=true

jdbc:mysql://127.0.0.1:3306/bury_point?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true

jdbcDriver=com.mysql.jdbc.Driver
jdbcUrl=jdbc:mysql:///cake?characterEncoding=utf-8&allowMultiQueries=true
jdbcusername=root
jdbcpassword=1

 

1.批量id查询

@Select({
         "<script>",
         "SELECT ",
         "a.apply_id as 'applyId',a.apply_by_name as 'applyByName',a.project_id as 'projectId',",
         "a.apply_by_id as 'applyById', a.apply_time as 'applyTime',a.apply_type as 'applyType',",
         "a.module as 'module',a.name as 'name',a.identifier as 'identifier',",
         "a.property_classify as 'propertyClassify',a.property_type as 'propertyType',a.property_unit as 'propertyUnit',",
         "a.get_value_way as getValueWay,a.page as page,a.time_version as timeVersion,a.bury_point_channel as buryPointChannel,",
         "a.associate_property_ids as associatePropertyIds,a.business_notes as businessNotes,a.status as status, ",
         "a.last_update_time as lastUpdateTime,a.last_update_by_name as lastUpdateByName,",
         "a.approval_time as approval_time,a.approval_by_name as approvalByName,a.modify_explain as modifyExplain,a.approval_remark approvalRemark ",
         " FROM bury_point_apply a where apply_id in ( ",
         "<foreach collection='applyIds' item='item' index='index' separator=','>",
            "#{item}",
         "</foreach>",
         " )</script>"
        })
    List<ApplyEntity>  queryApplyListByIds(@Param("applyIds") List<String> applyIds);

 

 

  2.批量更新  
    @Update({
         "<script>",

         "<foreach collection='applyList' item='item' index='index' open='' close='' separator=';'>",
            "update bury_point_apply set status=#{item.status}, approval_remark=#{item.approvalRemark} ,approval_by_id=${item.approvalById},approval_by_name=#{item.approvalByName},approval_time=now()  where apply_id = ${item.applyId}",
         "</foreach>",
         "</script>"
        })
    public Integer batchAuditApply(@Param("applyList") List<ApplyEntity> applyList);

3.批量插入

    @Insert({
         "<script>",
         "insert into bury_point_apply(apply_by_id,apply_by_name, project_id,apply_type,module,name,identifier,",
         "page,time_version,bury_point_channel,associate_property_ids,business_notes,status) values ",
         "<foreach collection='applyEntities' item='item' index='index' separator=','>",
         "(#{item.applyById}, #{item.applyByName}, #{item.projectId}, #{item.applyType}, #{item.module}, #{item.name}, #{item.identifier},",
         "#{item.page},#{item.timeVersion},#{item.buryPointChannel},#{item.associatePropertyIds},#{item.businessNotes},#{item.status})",
         "</foreach>",
         "</script>"
        })
    @Options(useGeneratedKeys = true, keyProperty = "applyId")
    int batchAddEventApply(@Param(value="applyEntities") List<ApplyEntity> applyEntities);