云星数据---Apache Flink实战系列(精品版)】：Flink流处理API详解与编程实战006-DataStream与MySql自定义sink和source(Scala版)001

一、在MySQL中准备测试数据

1.执行命令

1.查询数据库
SHOW DATABASES;

2.创建数据库
CREATE DATABASE flinktest;

3.使用数据库
USE flinktest;

4.创建表格
CREATE TABLE Student
(
    stuid INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
    stuname VARCHAR(10) NOT NULL,
    stuaddr VARCHAR(40) NOT NULL,
    stusex VARCHAR(10) NOT NULL
);

5.插入数据
INSERT INTO Student(stuid,stuname,stuaddr,stusex)VALUES(1,"xiaoming","henan zhengzhou", "male")
INSERT INTO Student(stuid,stuname,stuaddr,stusex)VALUES(2,"xiaoqiang","shandong jinan", "female")
INSERT INTO Student(stuid,stuname,stuaddr,stusex)VALUES(3,"xiaohua","hebei shijiazhuang",   "male")
INSERT INTO Student(stuid,stuname,stuaddr,stusex)VALUES(4,"xiaohong","yunnan kunming",  "female")

6.查询数据
SELECT * FROM Student ;

2.执行效果

二、测试JDBC连接

1.在pom.xml中添加驱动依赖

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.35</version>
</dependency>

2.定义Student实体类，用于封装数据

package code.book.stream.customsinkandsource.jdbc.scala
/**
  * 用于存储数据库中的数据，作为bean使用
  */
case class Student(stuid: Int, stuname: String, stuaddr: String, stusex: String)

2.执行JDBC测试程序

package code.book.stream.customsinkandsource.jdbc.scala

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

/**
  * 本类主要用于检测jdbc连接是否成功
  */
object JdbcTest {
  def main(args: Array[String]) {
    val driver = "com.mysql.jdbc.Driver"
    val url = "jdbc:mysql://qingcheng11:3306/flinktest"
    val username = "root"
    val password = "qingcheng"
    var connection: Connection = null
    var statement: Statement = null
    try {
      //1.加载驱动
      Class.forName(driver)
      //2.创建连接
      connection = DriverManager.getConnection(url, username, password)
      //3.获得执行语句
      statement = connection.createStatement()
      //4.执行查询，获得结果集
      val resultSet = statement.executeQuery("select stuid,stuname,stuaddr,stusex from Student")
      //5.处理结果集
      while (resultSet.next()) {
        val student = Student(resultSet.getInt("stuid"), resultSet.getString("stuname").trim,
         resultSet.getString("stuaddr").trim, resultSet.getString("stusex").trim)
        println(student)
      }
    } catch {
      case e:Throwable => e.printStackTrace
    } finally {
      //6.关闭连接，释放资源
      if (connection != null) {
        connection.close()
      }
      if (statement != null) {
        statement.close()
      }
    }
    connection.close()
  }
}

2.JDBC测试程序成功输出

Student(1,xiaoming,henan zhengzhou,male)
Student(2,xiaoqiang,shandong jinan,female)
Student(3,xiaohua,hebei shijiazhuang,male)
Student(4,xiaohong,yunnan kunming,female)