flink不同方式加载MySQL维表demo

在flink sql中加载维表比较简单
定时加载：

CREATE TABLE zktest(
  id int NOT NULL,
  name string,
  PRIMARY KEY (id) NOT ENFORCED  --指定主键
) WITH (
  'connector' = 'jdbc',
  'url' = 'jdbc:mysql://xxxx:xxxx/spd?useSSL=false&autoReconnect=true',
  'driver' = 'com.mysql.cj.jdbc.Driver',
  'table-name' = '***',
  'username' = '******',
  'password' = '******',
  'lookup.cache.max-rows' = '3000',
  'lookup.cache.ttl' = '10s',
  'lookup.max-retries' = '3'
);

cdc实时加载，需要修改MySQL日志存储格式，并且给够特定权限，好处是可以实时获取维表变化，需1.11版本以上支持：

CREATE TABLE zktest(
  id int,
  name string
) WITH (
  'connector' = 'mysql-cdc',
  'hostname' = '******',
  'port' = '3307',
  'username' = '******',
  'password' = '******',
  'database-name' = 'cdc',
  'table-name' = '***',
  'server-time-zone' = 'Asia/Shanghai'
);

使用cdc时一定注意时区问题。

api 中可以使用RichMap函数定时加载维表信息：

import com.alibaba.fastjson.JSONObject;
import com.zktest.flink.Utils.DateUtil;
import com.zktest.flink.com.zktest2.flink2MySql.Student;
import org.apache.flink.api.common.functions.RichMapFunction;
import org.apache.flink.configuration.Configuration;
import org.apache.flink.util.ExecutorUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.util.HashMap;
import java.util.Map;
import java.util.concurrent.ScheduledExecutorService;
import java.util.concurrent.ScheduledThreadPoolExecutor;
import java.util.concurrent.TimeUnit;


public class MyRichMapTimer extends RichMapFunction<String,String> {
    private static final Logger LOGGER = LoggerFactory.getLogger(MyRichMapTimer.class);
    private static final long serialVersionUID = 1L;
    private static final String STU_INFO_QUERY = "select * from zktest;";

    private PreparedStatement ps;
    private Connection connection;

    private transient ScheduledExecutorService dbScheduler;
    private Map<Integer, Student> stuCache;

    @Override
    public void open(Configuration parameters) throws Exception {
        super.open(parameters);
        stuCache = new HashMap<>(1024);

        dbScheduler = new ScheduledThreadPoolExecutor(1,r->{
            Thread thread = new Thread(r, "stuInfo");
            thread.setUncaughtExceptionHandler((t,e)->{
                LOGGER.error("Thread"+ t +"got uncaught exception:" +e);
            });
        return thread;
        });
        final Runnable beeper = new Runnable(){

            @Override
            public void run() {
                try {
                    Class.forName("com.mysql.cj.jdbc.Driver");
                    connection = DriverManager.getConnection("jdbc:mysql://xxx:3306/test?characterEncoding=utf-8&serverTimezone=GMT&useSSL=false&autoReconnect=true","xxx","xxx");
                }catch (Exception e) {
                    System.out.println("-----------mysql get connection has exception , msg = "+ e.getMessage());
                }
                try {
                    ps = connection.prepareStatement(STU_INFO_QUERY);
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                try {
                    ResultSet resultSet = ps.executeQuery();
                    while (resultSet.next()){
                        int id = resultSet.getInt(1);
                        String name = resultSet.getString(2);
                        String pw = resultSet.getString(3);
                        int age = resultSet.getInt(4);
                        stuCache.put(id,new Student(id,name,pw,age));
                    }
                    LOGGER.info("Fetched {} site info records, {} records in cache",resultSet.getFetchSize(),stuCache.size());
                } catch (SQLException e) {
                    e.printStackTrace();
                }
//                dbScheduler.shutdown();
                System.out.println("mysql收到了"+stuCache.size()+"条数据"+" 当前时间："+ DateUtil.format(System.currentTimeMillis(),DateUtil.YYYY_MM_DD_HH_MM_SS));
            }
        };
        //scheduleAtFixedRate达到间隔时间就执行任务
        //scheduleWithFixedDelay等待任务执行后开始算间隔时间   两者差一个任务执行时间
        dbScheduler.scheduleWithFixedDelay(beeper,0,1, TimeUnit.MINUTES);
    }

    @Override
    public void close() throws Exception {
        super.close();
        stuCache.clear();
        ExecutorUtils.gracefulShutdown(10,TimeUnit.SECONDS,dbScheduler);
        ps.close();
        connection.close();
    }

    @Override
    public String map(String s) {
        JSONObject jo = new JSONObject();
        jo.put("输入",s);
        String stu = stuCache.get(Integer.parseInt(s))==null?"无此学生":stuCache.get(Integer.parseInt(s)).toString();
        jo.put(s,stu);
        System.out.println(jo);
        return jo.toString();
    }
}