Flink(kafka---＞mysql)

maven pom

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <flink.version>1.11.2</flink.version>
        <scala.binary.version>2.11</scala.binary.version>
        <scala.version>2.11.12</scala.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.0.18</version>
        </dependency>

        <dependency>
            <groupId>org.apache.flink</groupId>
            <artifactId>flink-walkthrough-common_${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.apache.flink</groupId>
            <artifactId>flink-clients_${scala.binary.version}</artifactId>
            <version>${flink.version}</version>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.47</version>
        </dependency>

        <dependency>
            <groupId>org.apache.flink</groupId>
            <artifactId>flink-connector-kafka_2.11</artifactId>
            <version>1.11.2</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.17</version>
        </dependency>
    </dependencies>

表对应实体类

import java.math.BigDecimal;
import java.util.Date;

/**
 * 数据库表对应实体类
 */
public class Table {
    private String precinct_id;
    private String precinct_name;
    private String device_id;
    private String mete_code;
    private String mete_id;
    private BigDecimal report_value;
    private Date report_time;


    public String getPrecinct_id() {
        return precinct_id;
    }

    public void setPrecinct_id(String precinct_id) {
        this.precinct_id = precinct_id;
    }

    public String getPrecinct_name() {
        return precinct_name;
    }

    public void setPrecinct_name(String precinct_name) {
        this.precinct_name = precinct_name;
    }

    public String getDevice_id() {
        return device_id;
    }

    public void setDevice_id(String device_id) {
        this.device_id = device_id;
    }

    public String getMete_code() {
        return mete_code;
    }

    public void setMete_code(String mete_code) {
        this.mete_code = mete_code;
    }

    public String getMete_id() {
        return mete_id;
    }

    public void setMete_id(String mete_id) {
        this.mete_id = mete_id;
    }

    public BigDecimal getReport_value() {
        return report_value;
    }

    public void setReport_value(BigDecimal report_value) {
        this.report_value = report_value;
    }

    public Date getReport_time() {
        return report_time;
    }

    public void setReport_time(Date report_time) {
        this.report_time = report_time;
    }
}

Druid数据库连接池

import com.alibaba.druid.pool.DruidDataSource;
import java.sql.Connection;

/**
 * 数据库连接工具 Druid
 */
public class DbUtils {
    private static DruidDataSource dataSource;

    public static Connection getConnection() throws Exception {
        dataSource = new DruidDataSource();
        dataSource.setDriverClassName("com.mysql.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql://IP地址:端口号/库名?useUnicode=true&characterEncoding=utf8&characterSetResults=utf8");
        dataSource.setUsername("root");
        dataSource.setPassword("*****");
        //设置初始化连接数，最大连接数，最小闲置数
        dataSource.setInitialSize(10);
        dataSource.setMaxActive(50);
        dataSource.setMinIdle(5);
        //返回连接
        return  dataSource.getConnection();
    }
}

Mysql sink端

import java.sql.Timestamp;
import java.util.List;
import org.apache.flink.configuration.Configuration;
import org.apache.flink.streaming.api.functions.sink.RichSinkFunction;
import java.sql.Connection;
import java.sql.PreparedStatement;


public class MysqlJdbcSink extends RichSinkFunction<List<Table>> {
    private PreparedStatement insertPS;
    private Connection connection;

    /**
     * 生命周期开始
     *
     * @param parameters
     * @throws Exception
     */
    @Override
    public void open(Configuration parameters) throws Exception {
        super.open(parameters);
        //获取数据库连接，准备写入数据库
        connection = DbUtils.getConnection();
        String insertSql = "replace into dc_current_voltage_test(precinct_id,precinct_name,device_id,mete_code,mete_id,report_value,report_time) values (?, ?, ?, ?, ?, ?, ?); ";
        insertPS = connection.prepareStatement(insertSql);
    }

    /**
     * 关闭连接
     *
     * @throws Exception
     */
    @Override
    public void close() throws Exception {
        super.close();
        //关闭并释放资源
        if (connection != null) {
            connection.close();
        }

        if (insertPS != null) {
            insertPS.close();
        }
    }

    /**
     * 执行sql
     *
     * @param tables
     * @param context
     * @throws Exception
     */
    @Override
    public void invoke(List<Table> tables, Context context) throws Exception {
        for (Table table : tables) {
            insertPS.setString(1, table.getPrecinct_id());
            insertPS.setString(2, table.getPrecinct_name());
            insertPS.setString(3, table.getDevice_id());
            insertPS.setString(4, table.getMete_code());
            insertPS.setString(5, table.getMete_id());
            insertPS.setBigDecimal(6, table.getReport_value());
            insertPS.setTimestamp(7, new Timestamp(table.getReport_time().getTime()));
            insertPS.addBatch();
        }

        //一次性写入
        int[] count = insertPS.executeBatch();
        System.out.println("成功写入Mysql数量：" + count.length);

    }
}

Kafka source端

import com.alibaba.fastjson.JSONObject;
import org.apache.flink.api.common.functions.MapFunction;
import org.apache.flink.api.common.serialization.SimpleStringSchema;
import org.apache.flink.shaded.guava18.com.google.common.collect.Lists;
import org.apache.flink.streaming.api.datastream.DataStream;
import org.apache.flink.streaming.api.datastream.DataStreamSource;
import org.apache.flink.streaming.api.environment.StreamExecutionEnvironment;
import org.apache.flink.streaming.api.functions.windowing.AllWindowFunction;
import org.apache.flink.streaming.api.windowing.time.Time;
import org.apache.flink.streaming.api.windowing.windows.TimeWindow;
import org.apache.flink.streaming.connectors.kafka.FlinkKafkaConsumer;
import org.apache.flink.util.Collector;
import java.util.List;
import java.util.Properties;

public class KafkaSource {
    public static void main(String[] args) throws Exception {
        //构建流执行环境
        StreamExecutionEnvironment env = StreamExecutionEnvironment.getExecutionEnvironment();

        //kafka
        Properties props = new Properties();
        props.put("bootstrap.servers", "IP地址:端口");
        props.put("group.id", "kafka_mysql");
        props.put("key.deserializer", "org.apache.kafka.common.serialization.StringDeserializer");
        props.put("value.deserializer", "org.apache.kafka.common.serialization.StringDeserializer");
        props.put("auto.offset.reset", "latest");

        DataStreamSource<String> dataStreamSource = env.addSource(new FlinkKafkaConsumer<String>(
                "topic01",
                new SimpleStringSchema(),
                props
        )).
                //单线程打印，控制台不乱序，不影响结果
                        setParallelism(1);

        //dataStreamSource.print();

        //从kafka里读取数据，转换成Table对象
        DataStream<Table> dataStream = dataStreamSource.map(new MapFunction<String, Table>() {
            @Override
            public Table map(String value) throws Exception {
                return JSONObject.parseObject(value, Table.class);
            }
        });
        //收集5秒钟的总数
        dataStream.timeWindowAll(Time.seconds(5L)).
                apply(new AllWindowFunction<Table, List<Table>, TimeWindow>() {

                    @Override
                    public void apply(TimeWindow timeWindow, Iterable<Table> iterable, Collector<List<Table>> out) throws Exception {
                        List<Table> Tables = Lists.newArrayList(iterable);

                        if (Tables.size() > 0) {
                            System.out.println("5秒的总共收到的条数：" + Tables.size());
                            out.collect(Tables);
                        }

                    }
                })
                //sink 到数据库
                .addSink(new MysqlJdbcSink());
        //打印到控制台
        //.print();


        env.execute("kafka 消费任务开始");
    }
}