flink链接mysql_flink 将mysql作为Source和Sink的代码示例

1.maven导入

mysql

mysql-connector-java

5.1.34

2.SourceFromMySQL工具类java代码

import org.apache.flink.configuration.Configuration;

import org.apache.flink.streaming.api.functions.source.RichSourceFunction;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

/**

* @Description TODO

* @Author zytshijack

* @Date 2019-05-14 08:32

* @Version 1.0

*/

public class SourceFromMySQL extends RichSourceFunction { //Student就是一个自定义类

PreparedStatement ps;

private Connection connection;

/**

* open() 方法中建立连接，这样不用每次 invoke 的时候都要建立连接和释放连接。

*

* @param parameters

* @throws Exception

*/

@Override

public void open(Configuration parameters) throws Exception {

super.open(parameters);

connection = getConnection();

String sql = "select * from Student;";

ps = this.connection.prepareStatement(sql);

}

/**

* 程序执行完毕就可以进行，关闭连接和释放资源的动作了

*

* @throws Exception

*/

@Override

public void close() throws Exception {

super.close();

if (connection != null) { //关闭连接和释放资源

connection.close();

}

if (ps != null) {

ps.close();

}

}

/**

* DataStream 调用一次 run() 方法用来获取数据

*

* @param ctx

* @throws Exception

*/

@Override

public void run(SourceContext ctx) throws Exception {

ResultSet resultSet = ps.executeQuery();

while (resultSet.next()) {

Student student = new Student(

resultSet.getInt("id"),

resultSet.getString("name").trim(),

resultSet.getString("password").trim(),

resultSet.getInt("age"));

ctx.collect(student);

}

}

@Override

public void cancel() {

}

private static Connection getConnection() {

Connection con = null;

try {

Class.forName("com.mysql.jdbc.Driver");

con = DriverManager.getConnection("jdbc:mysql://localhost:3306/flink?useUnicode=true&characterEncoding=UTF-8", "root", "123");

} catch (Exception e) {

System.out.println("-----------mysql get connection has exception , msg = "+ e.getMessage());

}

return con;

}

}

3.SinkToMySQL工具类java代码

import org.apache.flink.configuration.Configuration;

import org.apache.flink.streaming.api.functions.sink.RichSinkFunction;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

/**

* @Description TODO

* @Author zytshijack

* @Date 2019-05-14 09:10

* @Version 1.0

*/

public class SinkToMySQL extends RichSinkFunction {

PreparedStatement ps;

private Connection connection;

/**

* open() 方法中建立连接，这样不用每次 invoke 的时候都要建立连接和释放连接

*

* @param parameters

* @throws Exception

*/

@Override

public void open(Configuration parameters) throws Exception {

super.open(parameters);

connection = getConnection();

String sql = "insert into Student(id, name, password, age) values(?, ?, ?, ?);";

ps = this.connection.prepareStatement(sql);

}

@Override

public void close() throws Exception {

super.close();

//关闭连接和释放资源

if (connection != null) {

connection.close();

}

if (ps != null) {

ps.close();

}

}

/**

* 每条数据的插入都要调用一次 invoke() 方法

*

* @param value

* @param context

* @throws Exception

*/

@Override

public void invoke(Student value, Context context) throws Exception {

//组装数据，执行插入操作

ps.setInt(1, value.getId());

ps.setString(2, value.getName());

ps.setString(3, value.getPassword());

ps.setInt(4, value.getAge());

ps.executeUpdate();

}

private static Connection getConnection() {

Connection con = null;

try {

Class.forName("com.mysql.jdbc.Driver");

con = DriverManager.getConnection("jdbc:mysql://localhost:3306/flink?useUnicode=true&characterEncoding=UTF-8&useSSL=false", "root", "123");

} catch (Exception e) {

System.out.println("-----------mysql get connection has exception , msg = "+ e.getMessage());

}

return con;

}

}

4.主函数使用工具类

env.addSource(new SourceFromMySQL())

...

stream.addSink(new SinkToMySQL())