def Save2Mysql(stateDStream: DStream[Row]): Unit = {
stateDStream.foreachRDD { rdd => {
rdd.foreachPartition(partitionRecords => {
/* var conn: Connection = null
var ps: PreparedStatement = null
val sql = "insert IGNORE into realtimehppro_autodiagnostic(systemid,subdevid,mo_subdevid,recordtime,leadRecordTime,errName,errReson) VALUES(?,?,?,?,?,?,?)"
val driverClass="com.mysql.jdbc.Driver"
Class.forName(driverClass)
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8", "root", "")
*/
val pro = new Properties()
val p2 = newDoLogic.getClass().getResourceAsStream("/db.properties")
pro.load(p2)
val url = pro.getProperty("url")
val password = pro.getProperty("password")
val username = pro.getProperty("user")
val driverClass = pro.getProperty("driverClass")
Class.forName(driverClass)
val conn = DriverManager.getConnection(url, username, password)
val sql = "insert IGNORE into realtimehppro_autodiagnostic(systemid,subdevid,mo_subdevid,recordtime,leadRecordTime,errName,errReson) VALUES(?,?,?,?,?,?,?)"
val ps = conn.prepareStatement(sql)
for (partitionRecord <- partitionRecords) {
ps.setInt(1, partitionRecord.getInt(0).toInt)
ps.setInt(2, partitionRecord.getInt(1).toInt)
ps.setInt(3, partitionRecord.getInt(2).toInt)
ps.setTimestamp(4, partitionRecord.getTimestamp(4))
ps.setTimestamp(5, partitionRecord.getTimestamp(5))
ps.setString(6, partitionRecord.getString(6))
ps.setString(7, partitionRecord.getString(7))
ps.addBatch()
}
try {
ps.executeBatch()
} catch {
case e: Exception => println("插入数据失败!")
sys.exit(1)
} finally {
ps.close()
conn.close()
}
}
)
}
}
}
在工程的resource目录下新建db.properties
url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8
user=root
password=
driverClass=com.mysql.jdbc.Driver
pom依赖
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.35</version>
</dependency>
为了达到以上去重的目的:
一。在navicate中 对数据库新建表(字符集选择utf8) 指定栏位之后,在保存表之前,点索引,新增索引
1.指定索引名字
2.在栏位那里选择字段(选择的字段 就是你想要按哪些字段去重的字段,即满足这些字段值相同的不同记录行,只会插入1行到该表中)
3.在索引类型那里 选择Unique (表示只要唯一的一条记录)
4.在索引方法那里选择BTREE
5.最后点保存 指定表名,完成表的创建
二。 在java api 中 的sql 插入语句
记得在 insert 之后into之前 加上 IGNORE 来将当前结果中满足主键或者索引的行 忽略,从而达到为数据库表去重的目的。