spark 读写 mysql 数据demo(scala实现代码)

一、读操作

package cn.guo.spark
import java.sql.DriverManager
import org.apache.spark.rdd.JdbcRDD
import org.apache.spark.{SparkConf, SparkContext}
object JdbcRDDDemo {
  def main(args: Array[String]) {
    val conf = new SparkConf().setAppName("JdbcRDDDemo").setMaster("local[2]")
    val sc = new SparkContext(conf)
    val connection = () => {
      Class.forName("com.mysql.jdbc.Driver").newInstance()
      DriverManager.getConnection("jdbc:mysql://localhost:3306/bigdata", "root", "123456")
    }
    val jdbcRDD = new JdbcRDD(
      sc,
      connection,
      "SELECT * FROM ta where id >= ? AND id <= ?",
      1, 4, 2,
      r => {
        val id = r.getInt(1)
        val code = r.getString(2)
        (id, code)
      }
    )
    val jrdd = jdbcRDD.collect()
    println(jrdd.toBuffer)
    sc.stop()
  }
}
 

二、写操作

/**
    * 存储到mysql
    */
  val dataToMysql = (partition: Iterator[(String)]) => {
    var conn: Connection = null
    var past: PreparedStatement = null
    try {
      conn = DriverManager.getConnection ("jdbc:mysql://master:3306/spark", "root", "root")
      //CREATE TABLE loc_base(loc VARCHAR(100),base_x DOUBLE,base_y DOUBLE,phone_num VARCHAR(20),time BIGINT)DEFAULT CHARSET=UTF8
      val sql = "INSERT INTO  loc_base(loc,base_x,base_y,phone_num,time) VALUES(?,?,?,?,?)"
      past = conn.prepareStatement (sql)
      partition.foreach (info => {
        val fields = info.split ("\t", -1)
        past.setString (1, fields (0))
        past.setDouble(2, fields (1).toDouble)
        past.setDouble (3, fields (2).toDouble)
        past.setString (4, fields (3))
        past.setInt(5, fields (4).toInt)
        past.execute ()
      })
    } catch {
      case e: Exception => e.printStackTrace ()
    } finally {
      if (past != null) {
        past.close ()
      }
      if (conn != null) {
        conn.close ()
      }
    }
  }