oracle 批量插入几千条数据只需100ms以内

最终的sql

insert into DR_ADJUST_HISTORY ( AGGREGATOR_NAME, CUSTOMER_LEVEL, ADUJST_TYPE, INVITE_STATE, ADJUST_ID, USER_PROFILE_ID, MAX_ADJUST_P, ADJUST_E, RESPONSE_NUMBER, TOTAL_SUBSIDY, REWARD_POINT ) 
select k.* from (
 select  
		NULL as AGGREGATOR_NAME, 
		NULL as CUSTOMER_LEVEL, 
		NULL as ADUJST_TYPE, 
		NULL as INVITE_STATE, 
		NULL as ADJUST_ID, 
		NULL as USER_PROFILE_ID, 
		NULL as MAX_ADJUST_P, 
		NULL as ADJUST_E, 
		NULL as RESPONSE_NUMBER, 
		NULL as TOTAL_SUBSIDY, 
		NULL as REWARD_POINT
	from dual union
 select  '江苏', 2, 1, 0, '77f18207-54a1-43a8-a948-52c0f18c4e45', '66d1c7c1-73ca-4f04-a3d3-290e2e3204e4', 4.6261826, 7.6549335, 0, 0.0, 0 from dual union
 select  '江苏', 2, 1, 0, '77f18207-54a1-43a8-a948-52c0f18c4e45', 'ef753dcf-762f-4a2b-8cbc-38e28d0ca5d6', 4.485621, 7.2969246, 0, 0.0, 0  from dual union
 select  '江苏', 2, 1, 0, '77f18207-54a1-43a8-a948-52c0f18c4e45', '7a7ce2a8-d1fa-47e7-b169-d2432d3d7a88', 4.4442787, 8.377349, 0, 0.0, 0  from dual union
 
 select  '江苏', 2, 1, 0, '77f18207-54a1-43a8-a948-52c0f18c4e45', '5a557a27-9d18-46b3-b8ab-a5e246b41b51', 0.0, 0.0, 0, 0.0, 0  from dual union
 select  '江苏', 2, 1, 0, '77f18207-54a1-43a8-a948-52c0f18c4e45', '069a8d65-3305-4df5-9bb6-0565d5e1c100', 0.0, 0.0, 0, 0.0, 0 from dual 

) k where k.AGGREGATOR_NAME is not NULL;

使用java代码拼接

private String getBatchSql(List<DrAdjustHistory> histories) {
        StringBuilder builder = new StringBuilder();
        builder.append(" select k.* from ( " +
                " select " +
                " NULL as AGGREGATOR_NAME,  " +
                " NULL as CUSTOMER_LEVEL, " +
                " NULL as ADUJST_TYPE,  " +
                " NULL as INVITE_STATE,  " +
                " NULL as ADJUST_ID,  " +
                " NULL as USER_PROFILE_ID,  " +
                " NULL as MAX_ADJUST_P, " +
                " NULL as ADJUST_E, " +
                " NULL as RESPONSE_NUMBER, " +
                " NULL as TOTAL_SUBSIDY, " +
                " NULL as REWARD_POINT " +
                " from dual union " );
        histories.stream().forEach( item ->{
            builder.append(" SELECT ");
            builder.append(" '" + item.getAGGREGATOR_NAME()+"' " ).append(" , ");
            builder.append(" '" + item.getCUSTOMER_LEVEL()+"' " ).append(" , ");
            builder.append(" '" + item.getADUJST_TYPE()+"' " ).append(" , ");
            builder.append(" '" + item.getINVITE_STATE()+"' " ).append(" , ");
            builder.append(" '" + item.getADJUST_ID()+"' " ).append(" , ");
            builder.append(" '" + item.getUSER_PROFILE_ID()+"' " ).append(" , ");
            builder.append(" '" + item.getMAX_ADJUST_P()+"' " ).append(" , ");
            builder.append(" '" + item.getADJUST_E()+"' " ).append(" , ");
            builder.append(" '" + item.getRESPONSE_NUMBER()+"' " ).append(" , ");
            builder.append(" '" + item.getTOTAL_SUBSIDY()+"' " ).append(" , ");
            builder.append(" '" + item.getREWARD_POINT()+"' " ).append(" from dual union ");

        });
        // 去除最后一个 union
        String sql = builder.toString();
        sql = sql.substring(0,sql.lastIndexOf("union"));
        sql += " ) k where k.AGGREGATOR_NAME is not NULL ";
        return sql;
    }

最终的mapper

  <insert id="batchInsertDataBySql" parameterType="java.lang.String">

        insert into DR_ADJUST_HISTORY
        ( AGGREGATOR_NAME, CUSTOMER_LEVEL, ADUJST_TYPE, INVITE_STATE, ADJUST_ID, USER_PROFILE_ID, MAX_ADJUST_P, ADJUST_E, RESPONSE_NUMBER, TOTAL_SUBSIDY, REWARD_POINT )
        ${batchSql}
    </insert>

基于代码形式的案例

 

插入两千条数据只需 200ms

<insert id="insertBatch" parameterType="java.util.List" useGeneratedKeys="false">
        INSERT INTO
        T_MONITOR_ELEC_POPULATION
        <trim prefix="(" suffix=")" suffixOverrides=",">
            COURT_ID,
            RECORD_DATE,
            SEGMENTID,
            EEGRPID,
            POPULATION,
            INSERT_TIME
        </trim>
        SELECT a.* FROM(
        <foreach collection="list" item="item" separator="union all">
            select
                #{item.courtId},
                #{item.recordDate},
                #{item.segmentid},
                #{item.eegrpid},
                #{item.population},
                #{item.insertTime}
            from
            dual
        </foreach>
        )a
    </insert>