Spark使用Java读取Mysql

JDK版本：8

Mysql版本：8.3.0，安装：Linux Centos8 Mysql8.3.0安装-CSDN博客

Spark版本：3.5.0，安装：Linux Centos Spark安装配置-CSDN博客

1 Mysql

创建表user表和插入数据

create table user (
    id int(10) primary key comment "id",
    name varchar(64) not null comment "username",
    age int(10) not null comment "age"
) engine=InnoDB default charset=utf8;

insert into user values(1, 'zhang san', 26);
insert into user values(2, 'wang si', 27);
insert into user values(3, 'tom', 30);

2 Idea,maven工程

pom文件内容:

<dependency>
    <groupId>org.apache.spark</groupId>
    <artifactId>spark-sql_2.12</artifactId>
    <version>3.1.3</version>
</dependency>

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

spark代码，读取mysql:

SparkSession spark = SparkSession
    .builder()
    .appName("mysql read")
    .getOrCreate();

String url = "jdbc:mysql://localhost:3306/test";
String table = "user";

Properties connectionProperties = new Properties();
connectionProperties.put("user","root");
connectionProperties.put("password","123456");
connectionProperties.put("driver","com.mysql.cj.jdbc.Driver");

Dataset<Row> jdbcDF = spark.read().jdbc(url, table, connectionProperties);
Dataset<Row> result = jdbcDF.select("*");

result.show();

spark.stop();

spark代码，写入mysql：

SparkSession spark = SparkSession
    .builder()
    .appName("mysql write")
    .getOrCreate();

String url = "jdbc:mysql://localhost:3306/test";
String table = "user";

Properties connectionProperties = new Properties();
connectionProperties.put("user", "root");
connectionProperties.put("password", "123456");
connectionProperties.put("driver", "com.mysql.cj.jdbc.Driver");

List<Row> data = Arrays.asList(
    RowFactory.create(4, "jack", 35),
    RowFactory.create(5, "micheal", 40)
);

StructType schema = new StructType(new StructField[]{
    new StructField("id", DataTypes.IntegerType, false, Metadata.empty()),
    new StructField("name", DataTypes.StringType, false, Metadata.empty()),
    new StructField("age", DataTypes.IntegerType, false, Metadata.empty())
});

Dataset<Row> dataSet = spark.createDataFrame(data, schema);
dataSet.write().mode("append").jdbc(url, table, connectionProperties);

spark.stop();

com.mysql.jdbc.Driver和com.mysql.cj.jdbc.Driver的区别，参考：java中连接数据库com.mysql.jdbc.Driver和com.mysql.cj.jdbc.Driver的区别？_com.mysql.jdbc.driver com.mysql.cj.jdbc.driver-CSDN博客

spark读取mysql参考：Spark使用Java读取mysql数据和保存数据到mysql_spark连接数据库查看数据并存入mysql数据库-CSDN博客