使用cpoy方法更快的插入数据库

读取大的execl文件怎么快速插入数据库

suspend fun insertDataIntoPostgreSQLAsync(data: List<List<String>>): Unit = withContext(Dispatchers.IO) {
    val conn = dataSource.getConnection()
    val statement = conn.prepareStatement("COPY mytable FROM STDIN WITH CSV DELIMITER ','")
    val buffer = StringWriter()

    data.forEach { rowData ->
        buffer.write(rowData.joinToString(","))
        buffer.write("\n")

        if (buffer.size() > 1024 * 1024 * 10) { // 每 10MB 插入一次
            buffer.flush()
            statement.copyIn(CharArrayReader(buffer.toString().toCharArray()))
            buffer.reset()
        }
    }

    if (buffer.size() > 0) {
        buffer.flush()
        statement.copyIn(CharArrayReader(buffer.toString().toCharArray()))
    }

    statement.close()
    conn.close()
}

在spring中的项目中使用

	fun batchInsertByCopy(tencentUsages: List<TencentUsage>) {
		val connString = "jdbc:postgresql://localhost:5432/billing_audit?useServerPrepStmts=false&rewriteBatchedStatements=true"
		val username = "XXXX"
		val password = "XXXXXX"
		Class.forName("org.postgresql.Driver")
		val conn = DriverManager.getConnection(connString, username, password)
		val sql = "COPY 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) FROM STDIN WITH DELIMITER '\t'"
		val copyManager = CopyManager(conn as BaseConnection)
		copyManager.copyIn(sql,tencentUsagesStringBuffer(tencentUsages))
		conn.close()
	}

	private fun tencentUsagesStringBuffer(tencentUsages: List<TencentUsage>): ByteArrayInputStream {
		val outputStream = ByteArrayOutputStream()
		val outputStreamWriter = OutputStreamWriter(outputStream)
		tencentUsages.forEach { usage ->
			val rowValues = listOf(usage.billingCycle,usage.recordId,usage.resourceId,usage.resourceName,usage.subProductName,usage.componentType,usage.componentName,usage.transactionType,usage.vendorId,usage.subVendorId,usage.startTime,usage.startTime,usage.duration,usage.durationUnit,usage.size,usage.sizeUnit,usage.amount)
				.joinToString(separator = "\t", postfix = "\n")
			outputStreamWriter.write(rowValues)
			outputStreamWriter.flush()
		}
		return ByteArrayInputStream(outputStream.toByteArray())
	}