Mybatis批量插入注解形式（Mysql和Oracle）

数据库：PostgreSQL，MYSQL

@Mapper
public interface CheckQualificationInfoMapper {
    // 方法一
    @Insert({"<script>" +
            "insert into check_qualification_info " +
            "(id_mp_check_qualification_info," +
            "name," +
            "id_no,"+
            "mobile,"+
            "apply_no,"+
            "cust_no,"+
            "batch_id," +
            "reason)"+
            "values " +
            "<foreach collection='poList' item='item' index='index' separator =','> " +
            "(#{item.id}," +
            " <choose>" +
            " <when test='item.name != null'>" +
            "  #{item.name}," +
            " </when>" +
            " <otherwise> null, </otherwise>" +
            " </choose>"+
            " <choose>" +
            " <when test='item.idNo != null'>" +
            "  #{item.idNo}," +
            " </when>" +
            " <otherwise> null, </otherwise>" +
            " </choose>"+
            " <choose>" +
            " <when test='item.phone != null'>" +
            "  #{item.phone}," +
            " </when>" +
            " <otherwise> null, </otherwise>" +
            " </choose>"+
            " <choose>" +
            " <when test='item.applyNo != null'>" +
            "  #{item.applyNo}," +
            " </when>" +
            " <otherwise> null, </otherwise>" +
            " </choose>"+
            " <choose>" +
            " <when test='item.custNo != null'>" +
            "  #{item.custNo}," +
            " </when>" +
            " <otherwise> null, </otherwise>" +
            " </choose>"+
            " <choose>" +
            " <when test='item.batchId != null'>" +
            "  #{item.batchId}," +
            " </when>" +
            " <otherwise> null, </otherwise>" +
            " </choose>"+
            "#{item.reason})"+
            "</foreach>" +
            "</script>"})
    void batchSaveInfo(@Param("poList") List<CheckQualificationInfoPO> poList);

    //方法二：
    @InsertProvider(type = BatchSaveCheckDTO.class,method = "insertAll" )
    public void insertAll(@Param("poList") List<CheckQualificationInfoPO> poList);

    public  class BatchSaveCheckDTO {
        public String insertAll(@Param("poList") List<CheckQualificationInfoPO> poList) {          
            StringBuilder sb = new StringBuilder();
            sb.append("INSERT INTO check_qualification_info ");
            sb.append("( name, id_no, mobile,apply_no ,cust_no ,batch_id, reason) ");
            sb.append("VALUES ");
            MessageFormat mf = new MessageFormat("(#'{'poList[{0}].name},#'{'poList[{0}].idNo},#'{'poList[{0}].phone},#'{'poList[{0}].applyNo}" +
                    ",#'{'poList[{0}].custNo},#'{'poList[{0}].batchId},#'{'poList[{0}].reason})");
            for (int i = 0; i < poList.size(); i++) {
                sb.append(mf.format(new Object[]{i}));
                if (i < poList.size() - 1) {
                    sb.append(",");
                }
            }
            return sb.toString();
        }
    }

    //方法三：
@Insert("<script> INSERT INTO check_qualification_info "
        + "(name,id_no,mobile,apply_no,cust_no,batch_id,reason) "
        + "VALUES "
        + "<foreach collection = 'infoList' item='record' separator=',' > "
        + " (#{record.name}, "
        + " #{record.idNo},"
        + " #{record.mobile},"
        + " #{record.applyNo},"
        + " #{record.custNo}, "
        + " #{record.batchId}, "     
        + " #{record.reason}) "
        + "</foreach> "
        + "</script>")
boolean bathInsertStatitisticsInfo(@Param("infoList")  List<CheckQualificationInfoPO> infoList);

}

public class CheckQualificationInfoPO {
    private String id;
    
    private String name;

    private String phone;

    private String idNo;

    private String applyNo;

    private String reason;

    private String custNo;
    
    private String batchId;

    public String getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }

    public String getIdNo() {
        return idNo;
    }

    public void setIdNo(String idNo) {
        this.idNo = idNo;
    }

    public String getApplyNo() {
        return applyNo;
    }

    public void setApplyNo(String applyNo) {
        this.applyNo = applyNo;
    }

    public String getReason() {
        return reason;
    }

    public void setReason(String reason) {
        this.reason = reason;
    }

    public String getCustNo() {
        return custNo;
    }

    public void setCustNo(String custNo) {
        this.custNo = custNo;
    }

    public String getBatchId() {
        return batchId;
    }

    public void setBatchId(String batchId) {
        this.batchId = batchId;
    }

    @Override
    public String toString() {
        return "CheckQualificationInfoPO{" +
                "id='" + id + '\'' +
                ", name='" + name + '\'' +
                ", phone='" + phone + '\'' +
                ", idNo='" + idNo + '\'' +
                ", applyNo='" + applyNo + '\'' +
                ", reason='" + reason + '\'' +
                ", custNo='" + custNo + '\'' +
                ", batchId='" + batchId + '\'' +
                '}';
    }
}

 

数据库：Oracle

import org.apache.ibatis.annotations.*;
import java.text.MessageFormat;
import java.util.List;
import java.util.Map;


@Mapper
public interface ContactsMapper {

    /***
    * @Description: 方法一；loanType和 applyNo为每条数据的公共参数
    * @Date: 2019-10-16
    */
    @Insert("<script>" +
            "insert all  " +
            "<foreach collection='map.contactList' item='item' index='index'  separator =''  > " +
            "into csp_appl_tel_list ( LOAN_TYPE ,APPLY_NO, NAME, TEL_NO,  RELATION,DATE_CREATED )\n" +
            " values (#{map.loanType},#{map.applyNo},#{item.name},#{item.telNo},#{item.relation},#{item.dateCreated}) " +
            "</foreach> " +
            "select 1 from dual" +
            "</script>")
    public void insertTelList(@Param("map") Map<String, Object> map);


    /**
    * @Description:  方法二;loanType和 applyNo为每条数据的公共参数;设置到每个Map里面（比较死板，不如方法三）
    * @Date: 2019-10-16
    */
    @InsertProvider(type = InsertContactsAll.class , method = "insertContactsAll")
    int inserAll(@Param("contactList") List<Map<String,Object>> list );

    public  class  InsertContactsAll{
        public String insertContactsAll( @Param("contactList") List<Map<String,Object>> list ){
            StringBuilder sb = new StringBuilder();
            sb.append(" INSERT ALL ");
            String inVal=" INTO csp_appl_tel_list ( LOAN_TYPE , APPLY_NO,NAME, TEL_NO,  RELATION, DATE_CREATED ) VALUES ";
            sb.append(inVal);
            MessageFormat mf = new MessageFormat("("
                    + " #'{'contactList[{0}].loanType,jdbcType=VARCHAR},  #'{'contactList[{0}].applyNo,jdbcType=VARCHAR}, #'{'contactList[{0}].name,jdbcType=VARCHAR}, #'{'contactList[{0}].telNo,jdbcType=VARCHAR}"
                    + ", #'{'contactList[{0}].relation,jdbcType=VARCHAR}, #'{'contactList[{0}].dateCreated,jdbcType=DATE} )" );
            for (int i = 0; i < list.size(); i++) {
                sb.append(mf.format(new Object[] { i+"" }));
                if (i < list.size() - 1) {
                    sb.append(inVal);
                }
            }
            sb.append(" select 1 from dual");
            return sb.toString();
        }
    }

    /**
    * @Description: 方法三；这里考虑的每条数据具有公共参数，公共参数无需封装在每一条数据里面
    * @Param: [map]  注意！！！此处无需再加注解@Param，其一会导致内部类方法的入参出现相同的两份，其二会导致MessageFormat里面参数的取值层级发生变化
    * @Date: 2019-10-16
    */
    @InsertProvider(type = InsertContactsAllData.class , method = "insertContactsAllData")
    int inserAllData( Map map );

    public  class  InsertContactsAllData{
        public String insertContactsAllData(  Map map  ){
            StringBuilder sb = new StringBuilder();
            List<Map<String,Object>> list = (List<Map<String, Object>>) map.get("contactList");
            sb.append(" INSERT ALL ");
            String inVal=" INTO csp_appl_tel_list ( LOAN_TYPE ,APPLY_NO, NAME , TEL_NO , RELATION , DATE_CREATED) VALUES ";
            sb.append(inVal);
            MessageFormat mf = new MessageFormat("("
                    + " #'{'loanType,jdbcType=VARCHAR},  #'{'applyNo,jdbcType=VARCHAR}, #'{'contactList[{0}].name,jdbcType=VARCHAR}, #'{'contactList[{0}].telNo,jdbcType=VARCHAR},"
                    + "#'{'contactList[{0}].relation,jdbcType=VARCHAR},#'{'contactList[{0}].dateCreated,jdbcType=DATE} )" );
            for (int i = 0; i < list.size(); i++) {
                sb.append(mf.format(new Object[] { i+"" }));
                if (i < list.size() - 1) {
                    sb.append(inVal);
                }
            }
            sb.append(" select 1 from dual");
            return sb.toString();
        }
    }
}


如下粗略的测试：
@Component
@RefreshScope
public class MyTestJob {
    private static Logger logger = LoggerFactory.getLogger(MyTestJob.class);

    @Autowired
    ContactsMapper contactsMapper;

    @Scheduled(cron = "0 */1 * * * ?")
    public void push() {
        try {
            // 方法一
            Map telListMap = new HashMap();
            List<Map<String,Object>> contactList = new ArrayList<Map<String,Object>>();
            int i = 1000;
            for (int n =0; n<i;n++){
                Map contactMap = new HashMap();
                contactMap.put("name","name");
                contactMap.put("telNo","telNo");
                contactMap.put("relation","relation");
                contactMap.put("dateCreated",new Date());
                contactList.add(contactMap);

            }
            telListMap.put("contactList",contactList);
            telListMap.put("applyNo","方法1");
            telListMap.put("loanType","loanType1");
            contactsMapper.insertTelList(telListMap);

            // 方法二
            List<Map<String,Object>> contactList2 = new ArrayList<Map<String,Object>>();
            for (int n =0; n<i;n++){
                Map contactMap = new HashMap();
                contactMap.put("name","name");
                contactMap.put("telNo","telNo");
                contactMap.put("relation","relation");
                contactMap.put("dateCreated",new Date());
                contactMap.put("applyNo","方法2");
                contactMap.put("loanType","loanType2");
                contactList2.add(contactMap);
            }
            contactsMapper.inserAll(contactList2);

            // 方法三
            Map telListMap3 = new HashMap();
            List<Map<String,Object>> contactList3 = new ArrayList<Map<String,Object>>();
            for (int n =0; n<i;n++){
                Map contactMap = new HashMap();
                contactMap.put("name","name");
                contactMap.put("telNo","telNo");
                contactMap.put("relation","relation");
                contactMap.put("dateCreated",new Date());
                contactList3.add(contactMap);
            }
            telListMap3.put("contactList",contactList3);
            telListMap3.put("applyNo","方法3");
            telListMap3.put("loanType","loanType3");
            contactsMapper.inserAllData(telListMap3);

          
        } catch (Exception e) {
            logger.error(" MyTestJob ERROR :", e);
        }
    }
}