flink scala 从Oracle同步数据到MySql

pom

	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<flink.version>1.11.2</flink.version>
		<scala.binary.version>2.11</scala.binary.version>
		<scala.version>2.11.12</scala.version>
		<log4j.version>2.12.1</log4j.version>
	</properties>

	<dependencies>
		<!-- Apache Flink dependencies -->
		<!-- These dependencies are provided, because they should not be packaged into the JAR file. -->
		<dependency>
			<groupId>org.apache.flink</groupId>
			<artifactId>flink-scala_${scala.binary.version}</artifactId>
			<version>${flink.version}</version>
			<scope>compile</scope>
		</dependency>
		<dependency>
			<groupId>org.apache.flink</groupId>
			<artifactId>flink-streaming-scala_${scala.binary.version}</artifactId>
			<version>${flink.version}</version>
			<scope>compile</scope>
		</dependency>
		<dependency>
			<groupId>org.apache.flink</groupId>
			<artifactId>flink-clients_${scala.binary.version}</artifactId>
			<version>${flink.version}</version>
			<scope>compile</scope>
		</dependency>

		<!-- Scala Library, provided by Flink as well. -->
		<dependency>
			<groupId>org.scala-lang</groupId>
			<artifactId>scala-library</artifactId>
			<version>${scala.version}</version>
			<scope>compile</scope>
		</dependency>

		<!-- Add connector dependencies here. They must be in the default scope (compile). -->

		<!-- Example:

		<dependency>
			<groupId>org.apache.flink</groupId>
			<artifactId>flink-connector-kafka_${scala.binary.version}</artifactId>
			<version>${flink.version}</version>
		</dependency>
		-->

		<!-- Add logging framework, to produce console output when running in the IDE. -->
		<!-- These dependencies are excluded from the application JAR by default. -->
		<dependency>
			<groupId>org.apache.logging.log4j</groupId>
			<artifactId>log4j-slf4j-impl</artifactId>
			<version>${log4j.version}</version>
			<scope>runtime</scope>
		</dependency>
		<dependency>
			<groupId>org.apache.logging.log4j</groupId>
			<artifactId>log4j-api</artifactId>
			<version>${log4j.version}</version>
			<scope>runtime</scope>
		</dependency>
		<dependency>
			<groupId>org.apache.logging.log4j</groupId>
			<artifactId>log4j-core</artifactId>
			<version>${log4j.version}</version>
			<scope>runtime</scope>
		</dependency>

		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>fastjson</artifactId>
			<version>1.2.56</version>
		</dependency>

		<dependency>
			<groupId>com.oracle</groupId>
			<artifactId>ojdbc6</artifactId>
			<version>11.1.0.7.0-Production</version>
		</dependency>

		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>8.0.11</version>
		</dependency>

	</dependencies>

Job_Data_Oracle_To_MySql.scala

package org.myorg.quickstart

import java.sql.{Connection, DriverManager, PreparedStatement, ResultSet, Timestamp}
import java.text.SimpleDateFormat
import java.util.Date

import org.apache.flink.configuration.Configuration
import org.apache.flink.streaming.api.functions.sink.{RichSinkFunction, SinkFunction}
import org.apache.flink.streaming.api.functions.source.{RichSourceFunction, SourceFunction}
import org.apache.flink.streaming.api.scala._

object Job_Data_Oracle_To_MySql {

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

    val env = StreamExecutionEnvironment.getExecutionEnvironment

    env.addSource(new OracleSource)
      .name("OracleSource")
      .addSink(new MySqlSink)
      .name("MySqlSink")
      .setParallelism(1)

    env.execute("Data From Oracle To MySql")
  }

  def NowDate(): String = {
    val now: Date = new Date()
    val dateFormat: SimpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS")
    val date = dateFormat.format(now)
    return date
  }
}

case class TB_TEST01(ID: Int, NAME: String, CTIME: Date)


class OracleSource extends RichSourceFunction[TB_TEST01] {

  var conn: Connection = _
  var selectStmt: PreparedStatement = _
  var isRunning: Boolean = true

  override def open(parameters: Configuration): Unit = {
    // 加载驱动
    Class.forName("oracle.jdbc.driver.OracleDriver")
    // 数据库连接
    conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521/orcl", "lxw", "123456")
    selectStmt = conn.prepareStatement("select * from TB_TEST01")
  }

  override def run(sourceContext: SourceFunction.SourceContext[TB_TEST01]): Unit = {

    while (isRunning) {
      var resultSet: ResultSet = selectStmt.executeQuery()
      while (resultSet.next()) {
        var ID: Int = resultSet.getInt("ID")
        var NAME: String = resultSet.getString("NAME")
        var CTIME: Date = resultSet.getTimestamp("CTIME")
        sourceContext.collect(TB_TEST01(ID, NAME, CTIME))
      }
      println(Job_Data_Oracle_To_MySql.NowDate() + "暂停5秒")
      Thread.sleep(5000)
    }
  }

  override def close(): Unit = {
    selectStmt.close()
    conn.close()
  }

  override def cancel(): Unit = {
    isRunning = false
  }
}


class MySqlSink extends RichSinkFunction[TB_TEST01] {

  var conn: Connection = _
  var insertStmt: PreparedStatement = _
  var updateStmt: PreparedStatement = _

  override def close(): Unit = {
    insertStmt.close()
    updateStmt.close()
    conn.close()
  }

  override def open(parameters: Configuration): Unit = {

    Class.forName("com.mysql.cj.jdbc.Driver")
    conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=false&autoReconnect=true&serverTimezone=UTC"
      , "root"
      , "123456")
    insertStmt = conn.prepareStatement("INSERT INTO `test`.`tb_test01`(`id`, `name`, `ctime`) VALUES (?, ?, ?)")
    updateStmt = conn.prepareStatement("UPDATE `test`.`tb_test01` SET `name` = ?, `ctime` =? WHERE `id` = ?")
  }

  override def invoke(value: TB_TEST01, context: SinkFunction.Context[_]): Unit = {

    // 执行更新语句
    updateStmt.setString(1, value.NAME)
    updateStmt.setTimestamp(2, new Timestamp(value.CTIME.getTime))
    updateStmt.setInt(3, value.ID)
    updateStmt.execute()
    //如果update没有更新 即 没有查询到数据 即 没有该id 那么执行插入
    if (updateStmt.getUpdateCount == 0) {
      println(Job_Data_Oracle_To_MySql.NowDate() + "------------------插入数据>>" + value)
      insertStmt.setInt(1, value.ID)
      insertStmt.setString(2, value.NAME)
      insertStmt.setTimestamp(3, new Timestamp(value.CTIME.getTime))
      insertStmt.execute()
    } else {
      println(Job_Data_Oracle_To_MySql.NowDate() + "------------------更新数据>>" + value)
    }

  }

}

控制台输出 

Oracle中 

MySql中