加载数据到mysql
package utils
import java.io.ByteArrayInputStream
import java.lang.Class._
import java.sql.{Connection, DriverManager, ResultSet}
import com.mysql.jdbc.PreparedStatement
import scala.concurrent.Future
class MysqlClient(driver: String,
url: String,
username: String,
password: String) extends LogSupport with Serializable {
// mysql connection
def getConnection: Connection = {
forName(driver)
DriverManager.getConnection(url, username, password)
}
/**
* load file to mysql.
*
* @param sb the data
* @param tableName the table name
* @param structure the table feild
*/
def loadFile(sb: String,
tableName: String,
structure: String): Unit = {
val con = getConnection
con.setAutoCommit(false)
val sql = s"LOAD DATA LOCAL INFILE '' into TABLE $tableName " +
s"FIELDS TERMINATED BY '\t' ($structure)"
val pstmt = con.prepareStatement(sql)
val is = new ByteArrayInputStream(sb.getBytes())
pstmt.unwrap(classOf[PreparedStatement]).setLocalInfileInputStream(is)
pstmt.execute
con.commit()
pstmt.close()
con.close()
}
/**
* load file to mysql.
*
* @param sb the data
* @param tableName the table name
*/
def loadFile(sb: String,
tableName: String): Unit = {
val con = getConnection
con.setAutoCommit(false)
val pstmt = con.prepareStatement(
s"LOAD DATA LOCAL INFILE '' into TABLE $tableName " +
s"FIELDS TERMINATED BY '\t'")
val is = new ByteArrayInputStream(sb.getBytes())
pstmt.unwrap(classOf[PreparedStatement]).setLocalInfileInputStream(is)
pstmt.execute
con.commit()
pstmt.close()
con.close()
}
import scala.concurrent.ExecutionContext.Implicits._
/**
* 异步插入mysql,需注意表名
*
* @param data the data
* @param table the table name
* @param structure the table feild
*/
def syncLoad(data: String,
table: String,
structure: String): Unit = {
// 异步写入mysql
val future: Future[Unit] = Future {
log.info(s"======> load data to $table")
if (structure == "") loadFile(data, table)
else loadFile(data, table, structure)
}
future onSuccess {
case _ => log.info("Mysql operation succuess.")
}
future onFailure {
case f => log.error(s"Mysql operation error. Exception ${f.toString}")
}
}
/**
* 异步批量根性mysql
*
* @param sql the sql string
*/
def syncUpdate(sql: String): Unit = {
// 异步update mysql
val future: Future[Unit] = Future {
val con = getConnection
con.prepareStatement(sql).execute()
con.close()
}
future onSuccess {
case _ => log.info("Mysql update operation succuess.")
}
future onFailure {
case f => log.error(s"Mysql update operation error. Exception ${f.toString}")
}
}
/**
* 批量根性mysql
*
* @param sql the sql string
*/
def update(sql: String): Unit = {
// update mysql
try {
val con = getConnection
con.prepareStatement(sql).execute()
con.close()
} catch {
case e: Exception =>
log.error(s"更新失败: sql:$sql", e)
}
}
/**
* while return.
*
* @tparam T
* @return
*/
private def results[T](resultSet: ResultSet)(f: ResultSet => T)
= {
new Iterator[T] {
def hasNext: Boolean = resultSet.next()
def next() = f(resultSet)
}
}
/**
* 批量查询mysql
*
* @param sql the sql string
*/
def select[T](sql: String, f: ResultSet => (String, T)): Map[String, T] = {
// select mysql
try {
val con = getConnection
val rs = con.prepareStatement(sql).executeQuery()
val result = results(rs)(f).toMap
rs.close()
con.close()
result
} catch {
case e: Exception =>
log.error(s"更新失败:sql:$sql", e)
Map[String, T]()
}
}
}