java实现数据库批量插入

1、mysql批量插入写法

?

1 2 3 4 5 6 7 8

<!-- 接收 信息 批量  插入数据 改状态--> <insert id="insertAppMonitorResults" parameterType="List" useGeneratedKeys="false">     insert into app_monitor_result (id,monitor_id,app_id,abstracts,keywords,content,exception_type,file_path,media_type,create_time) values     <foreach collection="list" item="MonitorResult" index="index" separator="," >         (nextval("seq_appmonitorresult"),#{MonitorResult.monitorId}, #{MonitorResult.appId}, #{MonitorResult.abstracts}, #{MonitorResult.keywords}, #{MonitorResult.content}, #{MonitorResult.exceptionType},          #{MonitorResult.filePath},#{MonitorResult.mediaType},#{MonitorResult.createTime})     </foreach> </insert><br><br><br>

 

ids太多的情况用此方法，因为in只支持1000以内的
<select id="selectBatchByIds" resultMap="TfMediaMaterialMap">
    select
        <include refid="materialColSql"/>
    from tf_media_material a
    where a.status = 'E' and ( a.id in
    <foreach collection="ids" index="index" item="item" open="(" separator="," close=")">
        <if test="(index % 999) == 998"> NULL ) OR a.id IN (</if>#{item}
    </foreach>
    )
</select>

 

 

 

2、ora批量插入数据

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

<!-- 插入数据 -->     <insert id="insertViresLibrary" parameterType="java.util.List">         INSERT INTO VIRES_LIBRARY         (ID,BIG_TYPE,SMALL_TYPE,SHOW,DESCRIPTION,TYPE,DELETED,CREATE_TIME,CHARACTERISTIC_VALUE,USER_ID,REMARKS)         SELECT SEQ_VIRES_LIBRARY.NEXTVAL ID, A.*         FROM(         <foreach collection="list" item="item" index="index" separator="UNION ALL">             SELECT             #{item.bigType,jdbcType=INTEGER} BIG_TYPE ,             #{item.smallType,jdbcType=VARCHAR} SMALL_TYPE,             #{item.show,jdbcType=VARCHAR} SHOW,             #{item.description,jdbcType=VARCHAR} DESCRIPTION,             #{item.type,jdbcType=INTEGER} TYPE,             #{item.deleted,jdbcType=VARCHAR} DELETED,             #{item.createTime} CREATE_TIME,             #{item.characteristicValue,jdbcType=VARCHAR} CHARACTERISTIC_VALUE ,             #{item.userId,jdbcType=INTEGER} USER_ID ,             #{item.remarks,jdbcType=INTEGER} REMARKS             FROM dual         </foreach>         )A     </insert>