Spark Scala读写Mysql

Spark Scala读写Mysql

一、写操作

在MySQL中 创建 bigdata_test数据库

创建 user（id, name, age）表

scala代码：

package com.jiang.hello

import java.sql.{Connection, PreparedStatement}

import org.apache.spark.rdd.RDD
import org.apache.spark.{SparkConf, SparkContext}

object RDD_DataSource {

  def main(args: Array[String]): Unit = {

    val conf:SparkConf = new SparkConf().setAppName("wc").setMaster("local[*]")
    val sc:SparkContext = new SparkContext(conf)
    sc.setLogLevel("WARN")

    // 创建JDBCRDD,方法数据库
    val driver = "com.mysql.jdbc.Driver"
    val url = "jdbc:mysql://localhost:3306/bigdata_test"
    var username = "root"
    val passwd = "123456"


    //TODO 1.source 加载数据
    // RDD[(姓名，年龄)]
    val dataSource:RDD[(Int,String,Int)] = sc.makeRDD(List((1,"jiang",18),(2,"zhang",21),(3,"sun",19)))

    //TODO 2.transformation

    //TODO 3.sink 输出
    dataSource.foreachPartition(datas=>{
      // 开启连接
      val connection: Connection = java.sql.DriverManager.getConnection(url, username, passwd)
      val sql = "insert into user (id,name, age) values (?, ?, ?) "
      val ps: PreparedStatement = connection.prepareStatement(sql)
      datas.foreach{
        case (id,name,age) =>{   // 每条数据
          ps.setInt(1,id)
          ps.setString(2,name)
          ps.setInt(3,age)
//          ps.executeUpdate()
//          ps.close()
          ps.addBatch()
        }
          ps.executeBatch()
      }
      connection.close()
      ps.close()
    })
  }

}

二、读操作

scala代码：

package com.jiang.hello

import java.sql.{Connection, DriverManager, PreparedStatement, ResultSet}

import org.apache.spark.rdd.{JdbcRDD, RDD}
import org.apache.spark.{SparkConf, SparkContext}

object RDD_DataSource {

  def main(args: Array[String]): Unit = {

    val conf:SparkConf = new SparkConf().setAppName("wc").setMaster("local[*]")
    val sc:SparkContext = new SparkContext(conf)
    sc.setLogLevel("WARN")

    // 创建JDBCRDD,方法数据库
    val driver = "com.mysql.jdbc.Driver"
    val url = "jdbc:mysql://localhost:3306/bigdata_test"
    var username = "root"
    val passwd = "123456"


    //TODO 1.source 加载数据

  	// 从mysql读数据
    // 查询数据
    val sql = "select id, name ,age from user where id >=? and id <=?"
    val getconnection = () => DriverManager.getConnection(url, username, passwd)
    val mapRow = (r:ResultSet)=>{
      val id:Int = r.getInt("id")
      val name:String = r.getString("name")
      val age:Int = r.getInt("age")
      (id,name,age)
    }

    val jdbcRDD: JdbcRDD[(Int,String,Int)] = new JdbcRDD(
      sc,
      getconnection,
      sql,
      1,  //下限
      3, //上限
      1,  //分区数
      mapRow
    )
    jdbcRDD.foreach(println)
//    jdbcRDD.collect
  }
}