spring boot批量插入execl的方法

原创

keyboard_sun 2023-03-19 07:36:28 博主文章分类：java8 ©著作权

文章标签 spring java spring boot bc 批量插入 文章分类 HarmonyOS 后端开发

©著作权归作者所有：来自51CTO博客作者keyboard_sun的原创作品，请联系作者获取转载授权，否则将追究法律责任

1、注入必要的jebdTemple

private val jdbcTemplate: NamedParameterJdbcTemplate

2、编写批量插入的方法

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("resource_name", it.resourceName)
        .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,resource_name,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,:resource_name,: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)
          ON conflict(record_id)  DO NOTHING;
          """,
      sources.toTypedArray()
    )
  }

3、调用批量插入的方法

fun saveAll(tencentUsages: List<TencentUsage>, billingCycle: YearMonth) {
    if (tencentUsages.isNotEmpty()) {
      logger.info("tencentUsage save db,size=${tencentUsages.size}")
      batchInsert(tencentUsages)
      
    }
  }