Flink 输出至 MySql

【1】引入 pom 依赖，我的 Mysql版本为 5.7.19

<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.47</version>
</dependency>

【2】在 MySQL 中创建需要的数据源[flink]和表，表结构如下：

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for temperatures
-- ----------------------------
DROP TABLE IF EXISTS `temperatures`;
CREATE TABLE `temperatures`  (
  `sensor` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `temp` double(60, 0) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

【3】Flink 输出至 MySql Scala 代码，自动导入的 scala包需要修改为 scala._ 否则会出现错误。

package com.zzx.flink

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

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

object MySqlSinkTest {
  def main(args: Array[String]): Unit = {
      // 创建一个流处理执行环境
      val env = StreamExecutionEnvironment.getExecutionEnvironment
      //从文件中读取数据并转换为 类
      val inputStreamFromFile: DataStream[String] = env.readTextFile("E:\\Project\\flink\\src\\main\\resources\\wordcount.txt")
      //转换
      val dataStream: DataStream[SensorReading] = inputStreamFromFile
        .map( data => {
          var dataArray = data.split(",")
          SensorReading(dataArray(0),dataArray(1).toLong,dataArray(2).toDouble)
        })
      dataStream.addSink(new MyJdbcSink())
      env.execute("mysql sink test")
  }
}

class MyJdbcSink() extends RichSinkFunction[SensorReading]{
  //初始化时建立连接，与编译器
  var conn: Connection = _
  var insertStmt: PreparedStatement = _
  var updateSmt: PreparedStatement = _
  //初始化，创建连接和预编译语句
  override def open(parameters: Configuration): Unit = {
    super.open(parameters)
    conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/flink","root","root")
    insertStmt = conn.prepareStatement("INSERT INTO temperatures(sensor,temp) VALUES(?,?) ")
    updateSmt = conn.prepareStatement("UPDATE temperatures SET temp = ? WHERE sensor = ?")
  }

  //调用连接执行 sql
  override def invoke(value: SensorReading, context: SinkFunction.Context[_]): Unit = {
   //执行更新语句，如果没更新，就进行插入
    updateSmt.setDouble(1,value.temperature)
    updateSmt.setString(2,value.id)
    updateSmt.execute()
    //如果update没有查到数据，那么执行插入语句
    if(updateSmt.getUpdateCount == 0){
      insertStmt.setDouble(2,value.temperature)
      insertStmt.setString(1,value.id)
      insertStmt.execute()
    }
  }

  //关闭资源
  override def close(): Unit = {
    insertStmt.close()
    updateSmt.close()
    conn.close()
  }
}
点击并拖拽以移动

【4】输出结果展示
  ​