最终的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>