mybatis的批量操作

系列文章目录

一、模糊查询

1.concat

普通模糊查询

<if test="queryDTO.regid != null and queryDTO.regid !=''">
  AND  lca.RegId like concat("%",#{queryDTO.regid},"%")
</if>

对字段进行截取

 select CONCAT(LEFT( s.DistrictId,2),'0000') AS areaParentId,

 SELECT  s.DistrictId,d1.DistrictName,d.DistrictName AS areaname,s.SYDW_NAME,
            v.VehicleId AS originid,v.cz_date as cztime
            FROM vehiclegasczrecord  v
            LEFT JOIN sydw s ON s.ID = v.`AddByWho`
            INNER JOIN district d1  ON d1.`DistrictId` =  CONCAT(LEFT( s.`DistrictId`,4),'00')
            INNER JOIN district d  ON d.`DistrictId` = s.`DistrictId`
         WHERE s.is_delete = 0 <include refid="BigQuAnXianVehicle"/> ORDER BY cz_date DESC  LIMIT 0,5

二、插入

1.INSTR

<if test="queryDTO.tagid != null and queryDTO.tagid !=''">
        AND  INSTR(lca.TagId,#{queryDTO.tagid})
</if>

2.批量操作 .插入

 //批量插入
    void insertList(@Param("list")List<LpggasMovesAddDTO> list);
 <insert id="insertList">
        insert into lpggas_moves (lid,state,InnerID,TagID,operator, SydwId,OriginID,uid,longitude,latitude)
        values
        <foreach collection="list"  separator="," item="item">
        (#{item.lid},#{item.state},#{item.innerid },#{item.tagid},#{item.operator},#{item.sydwid},#{item.originid},
        #{item.uid}, #{item.longitude}, #{item.latitude}
        )
        </foreach>
    </insert>

批量插入，并且 转换日期格式

 //excel插入
    Integer insertByExcel(@Param("results")List<LpggasDaoRuExcelVO> results, @Param("sydwid") String sydwid);

 <insert id="insertByExcel" useGeneratedKeys="true">
        insert into lpggas (
            RegId,OriginID,InnerID,MakeCom,MakeDate,CheckDate,CheckCircle,Life,NextCheckDate,RealVolume,
            PublicPressure,ThickNess,BFDate,Jiezhi,GasType,SydwId)
        values
        <foreach collection="results"  separator="," item="item">
            (#{item.regid},#{item.originid},#{ item.innerid},#{item.makecom}, DATE_FORMAT(#{item.makedate},'%Y-%m-%d'),DATE_FORMAT(#{item.checkdate},'%Y-%m-%d'),
            #{item.checkcircle},#{item.life},  DATE_FORMAT(#{item.nextcheckdate},'%Y-%m-%d'),#{item.realvolume},#{item.publicpressure},
            #{item.thickness},DATE_FORMAT(#{item.bfdate},'%Y-%m-%d'),#{item.jiezhi},#{item.gastype},#{sydwid})
        </foreach>
    </insert>

3.插入操作，判断非空

  <insert id="add" useGeneratedKeys="true" keyProperty="ctid">
        insert into customers
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="ctname != null and ctname != ''">CTName,</if>
            <if test="ctmanager != null and ctmanager != ''">CTManager,</if>
            <if test="ctcardid != null and ctcardid != ''">CTCardID,</if>
            <if test="cttel != null and cttel != ''">CTTel,</if>
            <if test="ctadr != null and ctadr != ''">CTAdr,</if>
            <if test="sydwid != null">SydwId,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="ctname != null and ctname != ''">#{ctname},</if>
            <if test="ctmanager != null and ctmanager != ''">#{ctmanager},</if>
            <if test="ctcardid != null and ctcardid != ''">#{ctcardid},</if>
            <if test="cttel != null and cttel != ''">#{cttel},</if>
            <if test="ctadr != null and ctadr != ''">#{ctadr},</if>
            <if test="sydwid != null">#{sydwid},</if>
        </trim>
    </insert>

三、批量操作 .删除

 //批量删除
    void deleteByIdList(@Param("idList") List<Long> idList);
 <delete id="deleteByIdList">
        delete from lpggas_moves where id in
        <foreach collection="idList" open="(" close=")" separator="," item="item">
            #{item}
        </foreach>
    </delete>

四、批量操作 .改

1.判断字段非空

  void updatePerson(@Param("updateVO")LpggasczrecordUpdateVO updateVO);

  <update id="updatePerson">
        update lpggasczrecord
        <trim prefix="set" suffixOverrides=",">
            <if test="updateVO.beforeName!=null and updateVO.beforeName!= ''">before_name=#{updateVO.beforeName},</if>
            <if test="updateVO.personname!=null and updateVO.personname!= '' ">PersonName=#{updateVO.personname},</if>
            <if test="updateVO.afterName!=null and updateVO.afterName!= ''">after_name=#{updateVO.afterName},</if>
        </trim>
        where Id in
        <foreach collection="updateVO.idList" open="(" close=")" separator="," item="item">
            #{item}
        </foreach>
    </update>

2.全局批量修改
解决mybatis批量更新(update foreach)失败的问题

 <update id="updateInfoList">
        <foreach collection="listNew" item="item" index="index"  separator=";">
            update lpggas set  RegId = #{item.regid},CheckDate = #{item.checkdate} ,
            NextCheckDate = #{item.nextcheckdate},MakeDate = #{item.makedate} ,
            MakeCom = #{item.makecom}
            where Id = #{item.id}
        </foreach>
    </update>

3.先查再改

UPDATE  lpggasczrecord SET  InnerId = (SELECT InnerID FROM  lpggas WHERE id = lpggasId) ,
  Jiezhi = (SELECT Jiezhi FROM  lpggas WHERE id = lpggasId) ,
  GasType = (SELECT GasType FROM  lpggas WHERE id = lpggasId) 

4.多表修改
inner join连接修改

UPDATE tableA pb 
INNER JOIN (SELECT bc.img_url,bc.book_author,bc.book_name,bc.book_id  FROM tableB bc) book
ON pb.book_id = book.book_id
SET pb.img_url = book.img_url, pb.book_author = book.book_author,pb.new_book_name = book.book_name;

join连接修改

UPDATE customers s ,
(SELECT cid, COUNT(lid) AS num FROM customers_lpggas WHERE cid IN 
(SELECT CTID FROM customers)
GROUP BY cid ) cl  
SET 
s.lgNumber = cl.num 
WHERE s.`CTID` = cl.cid 

---

©著作权归作者所有：来自51CTO博客作者JavaAlpha的原创作品，请联系作者获取转载授权，否则将追究法律责任
MySQL 批量修改字段内容，值来之另外一张表，INNER JOIN 使用
https://blog.51cto.com/javaalpha/5892581

6.批量操作 .查

  /**
         * 查询批量导出数据
         * @param idList
         * @return
         */
    List<LpggasMovesExcelVO> queryBatchExportData(@Param("idList")List<Long> idList);
  <select id="queryBatchExportData" resultMap="LpggasMovesExcelVO">
        select
         id, lid, state,SydwId,InnerID, TagID,OriginID,
         operator,uid, create_time,update_time
        from lpggas_moves
        where id in
        <foreach collection="idList" open="(" close=")" separator="," item="item">
            #{item}
        </foreach>
    </select>

mybatis中判断传入的数组与集合是否为空+mybatis中Foreach的使用详解

mybatis-plus – mapper中foreach循环操作（新增，或修改）