scala数据批量写入数据库

package com.dfssi.dataplatform

import java.sql.{Connection, DriverManager, PreparedStatement, Statement}
import java.util

import com.alibaba.fastjson.{JSON, JSONObject}
import com.dfssi.dataplatform.analysis.jdbc.SQL
import org.apache.spark.broadcast.Broadcast
import org.apache.spark.internal.Logging
import org.apache.spark.rdd.RDD
import org.apache.spark.{SparkConf, SparkContext}

import scala.collection.mutable

/**
  * description:
  *
  * @author WangHD
  * @date 2020/5/13 0013  10:37
  * @version 1.0
  */
case class RowData(id: String, district: String, enterprise: String, originalMsg: String, vid: String, vin: String, commandSign: String) extends Serializable

object DataToMysql extends Serializable with Logging {
  def main(args: Array[String]): Unit = {
    val conf: SparkConf = new SparkConf().setMaster("local[*]").setAppName(s"DataToMysql").set("spark.serializer", "org.apache.spark.serializer.KryoSerializer")
    val sc = new SparkContext(conf)
    val dataRdd: RDD[String] = sc.textFile("E:\\raw201905.txt", 2)

    val resMap: Array[Map[String, Any]] = dataRdd.map(line => {
      val jObject: JSONObject = JSON.parseObject(line)

      val cotime: Long = jObject.get("collectTime").toString.toLong
      val district: String = jObject.getOrDefault("district", "").toString
      val enterprise: String = jObject.getOrDefault("enterprise", "").toString
      val originalMsg: String = jObject.getOrDefault("originalMsg", "").toString
      val vid: String = jObject.getOrDefault("vid", "").toString
      val vin: String = jObject.getOrDefault("vin", "").toString
      val commandSign: String = jObject.getOrDefault("commandSign", "").toString
      Map(
        "collectTime" -> cotime,
        "district" -> district,
        "enterprise" -> enterprise,
        "originalMsg" -> originalMsg,
        "vid" -> vid,
        "vin" -> vin,
        "commandSign" -> commandSign
      )
    }).filter(_ != null).collect()


    insertBatch(resMap, "hz_raw_data201905")


  }


  private def insertBatch(array: Array[Map[String, Any]],
                          table: String
                         ): Unit = {
    // 访问本地MySQL服务器，通过3306端口访问mysql数据库
    val url = "jdbc:mysql://xxx.xx.xx.xx:3306/hz_data?useUnicode=true&characterEncoding=utf-8&useSSL=false"
    //驱动名称
    val driver = "com.mysql.jdbc.Driver"

    //用户名
    val username = "root"
    //密码
    val password = "xxxx"
    //初始化数据连接
    var connection: Connection = null
    Class.forName(driver)

    if (array.nonEmpty) {

      val keys = array.head.keySet.toList
      val length = keys.length
      val sql = new SQL() {
        {
          INSERT_INTO(table)
          keys.foreach(key => {
            VALUES(key, "?")
          })
        }
      }.toString

      var connection: Connection = null
      var prepareStatement: PreparedStatement = null
      try {
        connection = DriverManager.getConnection(url, username, password)
        connection.setAutoCommit(false)
        prepareStatement = connection.prepareStatement(sql)
        var rowCount = 0
        array.foreach(record => {
          (1 to length).map(i => {
            prepareStatement.setObject(i, record.getOrElse(keys(i - 1), null))
          })
          prepareStatement.addBatch()
          rowCount += 1
          if (rowCount % 1000 == 0) {
            prepareStatement.executeBatch()
            rowCount = 0
            logInfo("插入1000条数据...")
          }
        })

        if (rowCount != 0) {
          prepareStatement.executeBatch()
        }
        connection.commit()
      } catch {
        case e: Throwable =>
          logError(s"写入表${table}失败!", e)
      } finally {
        if (prepareStatement != null) {
          try {
            prepareStatement.close()
          } catch {
            case e: Throwable => ""
          }
        }
       connection.close()
      }
    }


  }
}