1.主题描述
scala针对MySQL数据库进行增删改查的基本操作。
2.代码
package SparkSQLproject.Log
import java.sql.{Connection, DriverManager}
/**
* scala通过jdbc连接mysql
*/
object self_scalaMysql {
def main(args: Array[String]): Unit = {
//1.数据库配置
// 访问本地MySQL服务器,通过3306端口访问mysql数据库
val url ="jdbc:mysql://bigdata.ibeifeng.com:3306/imooc_spark"
//驱动名称
val driver="com.mysql.jdbc.Driver"
//用户名
val username = "root"
//密码
val password = "123456"
//2.初始化数据连接
var connection: Connection = null
try{
//注册Driver
Class.forName(driver)
//得到连接
connection = DriverManager.getConnection(url, username, password)
val statement = connection.createStatement
//3.数据库操作
//(1)执行查询语句,并返回结果
val sql="SELECT * FROM day_video_access_topn_stat"
val rs = statement.executeQuery(sql)
//打印返回结果
while (rs.next) {
val day = rs.getString("day")
val cms_id = rs.getLong("cms_id")
val times = rs.getLong("times")
println("day ="+day+",cms_id ="+cms_id+",times="+times)
}
println("查询数据完成!")
/**
* 查询结果:
day =20170511,cms_id =14540,times=111027
day =20170511,cms_id =4000,times=55734
day =20170511,cms_id =14704,times=55701
day =20170511,cms_id =14390,times=55683
day =20170511,cms_id =14623,times=55621
day =20170511,cms_id =4600,times=55501
day =20170511,cms_id =4500,times=55366
day =20170511,cms_id =14322,times=55102
*/
//(2)插入操作【成功!】
val sql2="INSERT INTO `day_video_access_topn_stat` (`day`, `cms_id`,`times`) VALUES ('20170511', '1543','4500')"
val rs2 = statement.executeUpdate(sql2)
println("插入数据完成")
//(3)更新操作【成功!】
val sql3="UPDATE day_video_access_topn_stat set times=4501 WHERE `cms_id`=1543"
val rs3 = statement.executeUpdate(sql3)
println("更新数据完成!")
//(4)删除操作【成功!】
val sql4="delete from day_video_access_topn_stat WHERE `cms_id`=1543"
val rs4 = statement.executeUpdate(sql4)
println("删除数据完成!")
}catch {
case e:Exception=>e.printStackTrace()
}
connection.close()
}
}