Flink 入门实战之六Source自定义读取mysql数据

自定义Source连接mysql

• 重写SourceRichFunction通过JDBC方式连接Mysql
• 使用预执行语句执行带参数sql语句

package com.day

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

import org.apache.flink.configuration.Configuration
import org.apache.flink.streaming.api.functions.source.{RichSourceFunction, SourceFunction}
import org.apache.flink.streaming.api.scala._
object SourceMysql {
  case class User (uid: Int, name: String, age: Int, sex:String)

  def main(args: Array[String]): Unit = {
    // 1、获取流式环境变量
    val env = StreamExecutionEnvironment.getExecutionEnvironment
    // 2、添加自定义source  连接mysql
    val dataStream = env.addSource(new MysqlSource)
    // 3、打印数据
    dataStream.print()
    // 4、执行任务
    env.execute("mysql Job")
  }

  //自定义source连接mysql
  class MysqlSource extends RichSourceFunction[User] {
    val uri = "jdbc:mysql://jeff200:3306/test_db?characterEncoding=utf-8"
    val user = "root"
    val pwd = "root"
    classOf[com.mysql.jdbc.Driver]
    var conn: Connection= _
    // 预编带参数语句
    var preStatement: PreparedStatement = _
    override def open(parameters: Configuration): Unit = {
      conn = DriverManager.getConnection(uri, user, pwd)
      val sql =
        """
          |SELECT uid, name, age, sex
          |FROM t_user
          |WHERE uid = ?
          |""".stripMargin
      preStatement = conn.prepareStatement(sql)
    }

    override def run(ctx: SourceFunction.SourceContext[User]): Unit = {
      val uid = 1
      preStatement.setInt(1, uid) // 查询uid为1的用户信息
      val result = preStatement.executeQuery()
      while (result.next){
        ctx.collect(
          User(
            uid = result.getInt(1),
            name = result.getString(2),
            age = result.getInt(3),
            sex = result.getString(4)
          )
        )
      }
    }

    override def cancel(): Unit = {
      if (conn != null) conn.close
    }
  }
}

• 核心依赖pom.xml

<!-- mysql 连接器 -->
<dependency>
   <groupId>mysql</groupId>
   <artifactId>mysql-connector-java</artifactId>
   <version>5.1.35</version>
</dependency>

• 结果输出
  
• 注意
  由于使用第三方JDBC连接器，发布到生产环境时候要将mysql-connector-java-5.1.35.jar引入flink/lib下面