Scala操作关系数据库增删改查

最新推荐文章于 2023-03-25 17:02:31 发布

wtzhm

最新推荐文章于 2023-03-25 17:02:31 发布

阅读量2.8k

点赞数

CC 4.0 BY-SA版权

分类专栏：大数据文章标签： Scala操作关系数据库

本文链接：https://blog.youkuaiyun.com/wtzhm/article/details/80874649

大数据专栏收录该内容

9 篇文章

订阅专栏

本文介绍了使用Scala通过JDBC连接MySQL数据库的方法，并提供了查询、插入、更新和删除数据的具体示例代码。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Scala操作关系数据库增删改查

1. 引入依赖

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

2. 查询数据

 object IpJdbc01 {
  def main(args: Array[String]): Unit = {
    val conf = new SparkConf().setAppName("IpJdbc").setMaster("local[2]")
    val sc = new SparkContext(conf)
    var conn: Connection = null
    var ps: PreparedStatement = null
    val sql = "select * from location_info"
    try{
      conn = DriverManager.getConnection("jdbc:mysql://192.168.126.31:3306/sparkdatabase?useUnicode=true&characterEncoding=utf-8", "root", "Zhm@818919")
      ps = conn.prepareStatement(sql)
      val rs = ps.executeQuery()
      //将数据保存到ArrayBuffer中
     /* val rsmd = rs.getMetaData
      val size = rsmd.getColumnCount
      val buffer = new ArrayBuffer[scala.collection.mutable.HashMap[String, Any]]()
      while (rs.next()) {
        val map = mutable.HashMap[String, Any]()
        for (i <- 1 to size) {
          map += (rsmd.getColumnLabel(i) -> rs.getString(i))
        }
        buffer += map
      }
      println(buffer.toBuffer)*/
      //直接获取
      while(rs.next()){
        println(rs.getString("location")+"-------"+rs.getInt("counts"));
      }
    }catch {
      case e: Exception => println("myException")
    }finally {
      if (conn != null)
        conn.close()
      if (ps != null) {
        ps.close()
      }
    }
    sc.stop()
  }

}

3. 插入数据

object UpdateLocation {

  def main(args: Array[String]): Unit = {
    val conf = new SparkConf().setAppName("UpdateLocation").setMaster("local[2]")
    val sc = new SparkContext(conf)
    var conn: Connection = null
    var ps: PreparedStatement = null
    try {
      val sql = "INSERT INTO location_info(location,accesse_date,counts) VALUES (?,?,?)"
      conn = DriverManager.getConnection("jdbc:mysql://192.168.126.31:3306/sparkdatabase?useUnicode=true&characterEncoding=utf-8", "root", "Zhm@818919")
      ps = conn.prepareStatement(sql)
      ps.setString(1, "深圳")
      ps.setString(2, "2018-7-2")
      ps.setInt(3, 122)
      ps.execute()
    } catch {
      case e: Exception => println("myException")
    } finally {
      if (conn != null) {
        conn.close()
      }
      if (ps != null) {
        ps.close()
      }
    }
    sc.stop()
  }
}

4. 删除操作

object DeleteLocation {

  def main(args: Array[String]): Unit = {
    val conf = new SparkConf().setAppName("UpdateLocation").setMaster("local[2]")
    val sc = new SparkContext(conf)
    var conn: Connection = null
    var ps: PreparedStatement = null
    try {
      val sql = "delete from location_info where location = ?"
      conn = DriverManager.getConnection("jdbc:mysql://192.168.126.31:3306/sparkdatabase?useUnicode=true&characterEncoding=utf-8", "root", "Zhm@818919")
      ps = conn.prepareStatement(sql)
      ps.setString(1, "深圳")
      ps.execute()
    } catch {
      case e: Exception => println("myException")
    } finally {
      if (conn != null) {
        conn.close()
      }
      if (ps != null) {
        ps.close()
      }
    }
    sc.stop()
  }
}

5. 更新操作

object InsertLocation {

  def main(args: Array[String]): Unit = {
    val conf = new SparkConf().setAppName("UpdateLocation").setMaster("local[2]")
    val sc = new SparkContext(conf)
    var conn: Connection = null
    var ps: PreparedStatement = null
    try {
      val sql = "update location_info set location=?  where id = ?";
      conn = DriverManager.getConnection("jdbc:mysql://192.168.126.31:3306/sparkdatabase?useUnicode=true&characterEncoding=utf-8", "root", "Zhm@818919")
      ps = conn.prepareStatement(sql)
      ps.setString(1, "深圳")
      ps.setInt(2,26)
      ps.execute()
    } catch {
      case e: Exception => println("myException")
    } finally {
      if (conn != null) {
        conn.close()
      }
      if (ps != null) {
        ps.close()
      }
    }
    sc.stop()
  }
}