flink使用DataStream Api消费kafka数据保存到mysql

flink使用DataStream Api消费kafka数据保存到mysql

pom.xml文件

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>com.imooc</groupId>
    <artifactId>flinkMooc</artifactId>
    <version>1.0-SNAPSHOT</version>
    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <flink.version>1.7.0</flink.version>
        <java.version>1.8</java.version>
        <scala.binary.version>2.11</scala.binary.version>
        <scala.version>2.11.12</scala.version>
        <maven.complier.source>${java.version}</maven.complier.source>
        <maven.complier.target>${java.version}</maven.complier.target>
    </properties>
    <repositories>
        <repository>
            <id>apache.snapshots</id>
            <name>Apache Development Snapshot Repository</name>
            <url>https://repository.apache.org/content/repositories/snapshots/</url>
            <releases>
                <enabled>false</enabled>
            </releases>
            <snapshots>
                <enabled>true</enabled>
            </snapshots>
        </repository>
    </repositories>
    <dependencies>
        <dependency>
            <groupId>org.apache.flink</groupId>
            <artifactId>flink-java</artifactId>
            <version>${flink.version}</version>
        </dependency>
        <dependency>
            <groupId>org.apache.flink</groupId>
            <artifactId>flink-streaming-java_${scala.binary.version}</artifactId>
            <version>${flink.version}</version>
        </dependency>
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-log4j12</artifactId>
            <version>1.7.21</version>
        </dependency>
        <dependency>
            <groupId>org.apache.flink</groupId>
            <artifactId>flink-scala_${scala.binary.version}</artifactId>
            <version>${flink.version}</version>
        </dependency>
        <dependency>
            <groupId>org.apache.flink</groupId>
            <artifactId>flink-streaming-scala_${scala.binary.version}</artifactId>
            <version>${flink.version}</version>
        </dependency>
        <dependency>
            <groupId>org.scala-lang</groupId>
            <artifactId>scala-library</artifactId>
            <version>2.11.12</version>
        </dependency>
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.apache.flink/flink-table -->
        <dependency>
            <groupId>org.apache.flink</groupId>
            <artifactId>flink-table_2.11</artifactId>
            <version>1.7.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.flink</groupId>
            <artifactId>flink-connector-kafka_2.11</artifactId>
            <version>1.7.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.kafka</groupId>
            <artifactId>kafka-clients</artifactId>
            <version>1.1.1</version>
        </dependency>
    </dependencies>
</project>

FlinkStreamKafkaApi

case class Person(name:String,age:Integer,sex:String)
object FlinkStreamKafkaApi {
  def main(args: Array[String]): Unit = {

  val env=StreamExecutionEnvironment.getExecutionEnvironment
//  val tEnv=TableEnvironment.getTableEnvironment(env)
 val topic="flink_lhq"
  val properties=new Properties()
  properties.setProperty("bootstrap.servers","192.168.*.*:9092")
  properties.setProperty("group.id","test")

    val dataStream: DataStream[String] = env.addSource(new FlinkKafkaConsumer[String](topic,new SimpleStringSchema(),properties))
    val resultStream: DataStream[Person] = dataStream.map(_.split(",")).map(x=>new Person(x(0),x(1).toInt,x(2)))
    resultStream.addSink(new MyJdbcSink1)
     env.execute("TableSQLStreamApi")
}

}

MyJdbcSink1

class MyJdbcSink1() extends RichSinkFunction[Person] {
  //定义sql连接、预编译器
  var conn: Connection = _
  var insertStmt: PreparedStatement = _
  var updateStmt: PreparedStatement = _

  //初始化 创建连接 和 预编译语句
  override def open(parameters: Configuration): Unit = {
    super.open(parameters)
    conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8", "root", "111111")
    insertStmt = conn.prepareStatement("insert into person2 (name,age, sex) values (?,?,?)")
    updateStmt = conn.prepareStatement("update person2 set sex=?, age  = ? where name = ?")
  }

  // 调用连接 执行sql
  override def invoke(value: Person, context: SinkFunction.Context[_]): Unit = {
    // 执行更新语句
    updateStmt.setString(3, value.name)
    updateStmt.setInt(2,value.age)
    updateStmt.setString(1, value.sex)
    updateStmt.execute()
    //如果update没有更新 即 没有查询到数据 即 没有该id 那么执行插入
    if (updateStmt.getUpdateCount == 0) {
      insertStmt.setString(1, value.name)
      insertStmt.setInt(2,value.age)
      insertStmt.setString(3, value.sex)
      insertStmt.execute()
    }
  }

  //关闭时做清理工作
  override def close(): Unit = {
    insertStmt.close()
    updateStmt.close()
    conn.close()
  }
}