Spark 从MySQL中 读取 和插入 数据

     在MySQL中  创建 rdd 数据库

      创建 user（id, name, age）表

    添加测试数据 （1,''zhangsan",20）,（2,''lisi",30），（3,''zhangsan",40）

 

package com.bigdata.spark.Connect

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

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


// 连接Mysql 从Mysql 中读取数据

object Spark18_Mysql {

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

    // 创建Spark上下文对象
    val sc = new SparkContext(new SparkConf().setMaster("local[*]").setAppName("Mysql"))

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



    // 查询数据
    val sql = "select name ,age from user where id >=? and id <=?"
   // val sql = "select name ,age from user " // 查询数据要带范围，要不会报错

    val jdbcRDD: JdbcRDD[Unit] = new JdbcRDD(
      sc,
      () => {
        Class.forName(driver)
        java.sql.DriverManager.getConnection(url, username, passwd)
      },
      sql,
      1,  //下限
      3, //上限
      2,  //分区数
      (rs) => {
        println(rs.getString(1) + " , " + rs.getString(2)) //数字是列索引
      }

    )

    jdbcRDD.collect




    // todo 向MYSQL中插入数据

    val rdd: RDD[(Int, String, Int)] = sc.makeRDD(List((4,"zhangsan",20),(5,"lisi",30),(6,"wangwu",40)))


      rdd.foreachPartition(datas=>{
        val connection: Connection = java.sql.DriverManager.getConnection(url, username, passwd)
        datas.foreach{
          case (id,name,age) =>{
            val sql = "insert into user (id,name, age) values (?, ?, ?) "
             val preparedStatement: PreparedStatement = connection.prepareStatement(sql)
                  preparedStatement.setInt(1,id)
                  preparedStatement.setString(2,name)
                  preparedStatement.setInt(3,age)

                  preparedStatement.executeUpdate()

            preparedStatement.close()

          }
        }
        connection.close()
      }

      )

    sc.stop()



}}