大数据量csv数据的批量导入

1、使用 JdbcTemplate 的batchUpdate 方法实现批量插入

@Transactional
	fun saveAll(us: List<FastoneUsage>) {
		measureTimeMillis {
			batchInsert(us)
		}.also {
			logger.info("saved fastone usages: ${us.size} took $it ms.")
		}
	}


	private fun batchInsert(usages: List<FastoneUsage>) {
		val sql =
			"insert into fastone_usage (vendor_id,record_id,billing_cycle,resource_id,billing_item,start_time,end_time,duration,size,vendor_type,resource_type,lifecycle,vendor,fastone_region,platform) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
		jdbcTemplate.batchUpdate(sql, usages, usages.size) { ps,
		                                                     (vendorId,
			                                                     recordId,
			                                                     billingCycle,
			                                                     resourceId,
			                                                     billingItem,
			                                                     startTime,
			                                                     endTime,
			                                                     duration,
			                                                     size,
			                                                     vendorType,
			                                                     resourceType,
			                                                     lifecycle,
			                                                     vendor,
			                                                     fastoneRegion,
			                                                     platform
		                                                     ) ->
			ps.setString(1, vendorId)
			ps.setString(2, recordId)
			ps.setString(3, billingCycle.toString())
			ps.setString(4, resourceId)
			ps.setString(5, billingItem.name)
			ps.setTimestamp(6, Timestamp.valueOf(startTime))
			ps.setTimestamp(7, Timestamp.valueOf(endTime))
			ps.setLong(8, duration)
			ps.setBigDecimal(9, size)
			ps.setString(10, vendorType)
			ps.setString(11, resourceType)
			ps.setString(12, lifecycle.name)
			ps.setString(13, vendor)
			ps.setString(14, fastoneRegion)
			ps.setString(15, platform.name)
		}
	}

注意：可以做到1秒5000条左右，读取csv部分使用easyexecl 。但是这种可读性不是很好

2、 使用 NamedParameterJdbcTemplate 和MapSqlParameterSource 让批量插入的可读性更好一些

@Service
@Transactional(readOnly = true)
class TencentUsageService(
	private val repository: TencentUsageRepository,
	private val jdbcTemplate: NamedParameterJdbcTemplate,
) : Loggable {

	@Transactional
	fun saveAll(us: List<TencentUsage>) {
		batchInsert(us)
	}

	private fun batchInsert(usages: List<TencentUsage>) {

		val sources=usages.map {
			MapSqlParameterSource()
				.addValue("billing_cycle",it.billingCycle.toString())
				.addValue("record_id",it.recordId)
				.addValue("resource_id",it.resourceId)
				.addValue("product_name",it.productName)
				.addValue("sub_product_name",it.subProductName)
				.addValue("component_type",it.componentType)
				.addValue("component_name",it.componentName)
				.addValue("transaction_type",it.transactionType)
				.addValue("vendor_id",it.vendorId)
				.addValue("sub_vendor_id",it.subVendorId)
				.addValue("start_time",it.startTime)
				.addValue("end_time",it.endTime)
				.addValue("duration",it.duration)
				.addValue("duration_unit",it.durationUnit)
				.addValue("size",it.size)
				.addValue("size_unit",it.sizeUnit)
				.addValue("amount",it.amount)
		}

		jdbcTemplate.batchUpdate("insert into tencent_usage (billing_cycle,record_id,resource_id,product_name," +
				"sub_product_name,component_type,component_name,transaction_type,vendor_id,sub_vendor_id,start_time," +
				"end_time,duration,duration_unit,size,size_unit,amount) " +
				"values(:billing_cycle,:record_id,:resource_id,:product_name,:sub_product_name,:component_type,:component_name," +
				":transaction_type,:vendor_id,:sub_vendor_id,:start_time,:end_time,:duration,:duration_unit,:size,:size_unit,:amount)",
			sources.toTypedArray())
	}