mybatis批量插入和批量修改

1.背景 

   批量插入和批量修改在项目使用的场景越来越多，能实现批量操作的插件也越来越多。

 个人了解到的大致有三种：

插件名称	批量插入	批量修改
mybatis	√	√
通用mapper	√	×
mybatis  plus	√	√

 

 

 

 

 

2. mybatils批量插入和修改

2.1  pom.xml

		<dependency>
	  	   	 <groupId>org.mybatis</groupId>
	  	     <artifactId>mybatis</artifactId>
	  		 <version>3.4.4</version>
	  	</dependency>
	  	<dependency>
	  		<groupId>org.mybatis</groupId>
	  	  	<artifactId>mybatis-spring</artifactId>
	  		<version>1.3.1</version>
	  	</dependency>

关于配置就不过阐述，可查看历史博客查看mybatils如何配置。 

2.2 dao类

package com.cloudtech.web.dao;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import org.apache.ibatis.annotations.Param;

import com.cloudtech.web.entity.StationTiming;
import com.cloudtech.web.vo.StationTimingVo;

import tk.mybatis.mapper.common.Mapper;

public interface StationTimingMapper extends Mapper<StationTiming> {

	/**
	 * 批量插入
	 * 
	 * @param insertLsit
	 */
	void batchInsert(@Param("list") ArrayList<StationTiming> list);

	/**
	 * 批量修改
	 * 
	 * @param updateList
	 */
	void batchUpdate(@Param("list") List<StationTiming> list);
}

extends Mapper<StationTiming> 的原因是，使用了通用mapper，而通用mapper没有批量修改操作，所以，使用的mybatis批量插入和修改的方式。

2.2 mapper类

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.cloudtech.web.dao.StationTimingMapper">
		<!-- 批量插入站点校时信息 -->
	<insert id="batchInsert" parameterType="java.util.List">
    			insert into station_timing
    			(station_id,send_time,status,create_time)
    			values
    			<foreach collection="list" item="item" index="index" separator=",">
    				(	
    					#{item.stationId},
    					#{item.sendTime},
    					#{item.status},
    					now()
    				)
    		     </foreach>		
    </insert> 
	

	 <update id="batchUpdate" parameterType="java.util.List">
            update station_timing
            <trim prefix="set" suffixOverrides=",">
              <trim prefix="send_time=case" suffix="end,">
                 <foreach collection="list" item="item" index="index">
                         <if test="item.sendTime!=null">
                          when id=#{item.id} then #{item.sendTime}
                         </if>
                 </foreach>
              </trim>
              <trim prefix="status =case" suffix="end,">
                 <foreach collection="list" item="item" index="index">
                         <if test="item.status!=null">
                          when id=#{item.id} then #{item.status}
                         </if>
                 </foreach>
              </trim>
             </trim>
            where
            <foreach collection="list" separator="or" item="item" index="index">
              id=#{item.id}
          </foreach>
	</update>
</mapper>

 重点注意:

send_time=case    send_time为数据库的字段名，而不是实体类的字段名

foreach标签描述

属性	描述
collection	表示迭代集合的名称，可以使用@Param注解指定，记住dao类的传参一定要增加@Param 必填
item	需要输出的元素，如为List，则为对象，必填
index	当天迭代修改索引位置，选填
separator	迭代需要追加的内容，选填
open	迭代sql以什么开头
close	迭代sql以什么结尾

2.3  实体类

package com.cloudtech.web.entity;

import java.io.Serializable;
import java.util.Date;

import javax.persistence.Column;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Transient;

public class StationTiming implements Serializable {

	private static final long serialVersionUID = 1L;
	@Id
	@Column
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	private Integer id;

	private Integer stationId;
	@Transient
	private String number;

	private Date responseTime;

	private String replyContent;

	private Integer status;

	private Date createTime;

	private Date sendTime;

	public Date getSendTime() {
		return sendTime;
	}

	public void setSendTime(Date sendTime) {
		this.sendTime = sendTime;
	}

	public Integer getId() {
		return id;
	}

	public void setId(Integer id) {
		this.id = id;
	}

	public Integer getStationId() {
		return stationId;
	}

	public void setStationId(Integer stationId) {
		this.stationId = stationId;
	}

	public String getNumber() {
		return number;
	}

	public void setNumber(String number) {
		this.number = number;
	}

	public Date getResponseTime() {
		return responseTime;
	}

	public void setResponseTime(Date responseTime) {
		this.responseTime = responseTime;
	}

	public String getReplyContent() {
		return replyContent;
	}

	public void setReplyContent(String replyContent) {
		this.replyContent = replyContent;
	}

	public Integer getStatus() {
		return status;
	}

	public void setStatus(Integer status) {
		this.status = status;
	}

	public Date getCreateTime() {
		return createTime;
	}

	public void setCreateTime(Date createTime) {
		this.createTime = createTime;
	}

}

如果你热衷技术，喜欢交流，欢迎加入我们！