scala（一）：通过jdbc连接mysql数据库

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()

  }

}