// A code block
var foo = 'bar';
package io.github.flink.test
import java.util.Random
import org.apache.flink.streaming.api.functions.sink.{RichSinkFunction, SinkFunction}
import org.apache.flink.streaming.api.functions.source.SourceFunction
import org.apache.flink.streaming.api.scala.StreamExecutionEnvironment
import org.apache.flink.streaming.api.scala._
import java.sql.{Connection, DriverManager, PreparedStatement}
import org.apache.flink.configuration.Configuration
object JdbcSinkTest1 {
//创建一个实体对象
case class CameraSpeedData(id: String,timestamp: Long,speed: Double){};
class MyCameraSource extends SourceFunction[CameraSpeedData] {
var running = true
override def cancel(): Unit = {
running = false
}
override def run(ctx: SourceFunction.SourceContext[CameraSpeedData]): Unit = {
val random = new Random()
//设置可变的速度
var carSpeed = 1.to(100).map(i => ("car_" + i, 66 + random.nextGaussian() * 20))
while (running) {
carSpeed = carSpeed.map(t => (t._1, t._2 + random.nextGaussian()))
//获取时间
val curTime = System.currentTimeMillis()
carSpeed.foreach(t => ctx.collect(CameraSpeedData(t._1, curTime, t._2)))
Thread.sleep(100)
}
}
}
def main(args: Array[String]): Unit = {
val env = StreamExecutionEnvironment.getExecutionEnvironment
env.setParallelism(1)
val inputStream = env.addSource(new MyCameraSource)
val dataStream = inputStream.map(data => {
data})
// sink
dataStream.addSink( new MyMysqlSink() )
env.execute("mysql sink test")
}
class MyMysqlSink extends RichSinkFunction[CameraSpeedData]{
// 定义sql连接、预编译器
var conn: Connection =_
var insertStatm: PreparedStatement =_
var updateStatm: PreparedStatement =_
//重写父类的3个方法
override def open(parameters: Configuration): Unit ={
super.open(parameters)
//创建mysql 链接
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=false", "root", "381079845")
//插入数据carspeed
insertStatm = conn.prepareStatement("INSERT INTO carspeed (carid, speed) VALUES (?,?)")
//更新数据
updateStatm = conn.prepareStatement("UPDATE carspeed SET speed = ? WHERE carid = ?")
}
// 调用连接,执行sql
override def invoke(value: CameraSpeedData, context: SinkFunction.Context[_]): Unit = {
// 执行更新语句
updateStatm.setDouble(1, value.speed)
updateStatm.setString(2, value.id)
updateStatm.execute()
// 如果update没有查到数据,那么执行插入语句
if (updateStatm.getUpdateCount == 0) {
insertStatm.setString(1, value.id)
insertStatm.setDouble(2, value.speed)
insertStatm.execute()
}
}
// // 调用连接,执行sql
// override def invoke(value: CameraSpeedData, context: SinkFunction.Context[_]): Unit = {
// // 执行更新语句
// updateStatm.setDouble(1, value.speed)
// updateStatm.setString(2, value.id)
// updateStatm.execute()
// //如果没有查到更新内容则插入数据
// if(updateStatm.getUpdateCount ==0){
// insertStatm.setString(1,value.id)
// insertStatm.setDouble(2,value.speed)
// updateStatm.execute()
//
// }
//
//
// }
override def close(): Unit = {
updateStatm.close()
insertStatm.close()
conn.close()
}
}
}
结果如下图