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()
}
}
}
}
生命不息,编码不止;人尚在人间,创新不可停,程序员共勉之~