Scala’s method of operating database

Time:2019-9-7

1. Define database connections


package com.web.dataSource
 
import com.alibaba.druid.pool.DruidDataSource
 
object MySqlDataSource {
 
 val driver = "com.mysql.jdbc.Driver"
 val url = "jdbc:mysql://127.0.0.1:3306"
 val username = "root"
 val password = "root"
 
 val connectionPool = new DruidDataSource()
 connectionPool.setUsername(username)
 connectionPool.setPassword(password)
 connectionPool.setDriverClassName(driver)
 connectionPool.setUrl(url)
 connectionPool.setValidationQuery("select 1")
 connectionPool.setInitialSize(15)
 connectionPool.setMinIdle(10)
 connectionPool.setMaxActive(100)
 connectionPool.setRemoveAbandoned(true)
 connectionPool.setRemoveAbandonedTimeoutMillis(180000)
 connectionPool.setMaxWait(5000)
 connectionPool.setTestOnBorrow(false)
 connectionPool.setTestOnReturn(false)
 
}

2. Executing queries

def getOptions(uid:Int) ={
 
 val connection = MySqlDataSource.connectionPool.getConnection
 var sql = 
      s""" select username,password,sex
      |from user 
 
      |where uid = ?
 
      """.stripMargin
 
var stmt = connection.prepareStatement(sql)
 
stmt.setInt(1, uid)
 
var resultSet = stmt.executeQuery()
 
var resultListMap = List[Map[String,String]]()
 
// Get results
 
while(resultSet.next()){
 
resultListMap = resultListMap :+ Map(
 
                               "username"->resultSet.getString("username"),
 
                               "password"->resultSet.getString("password"),
 
                               "sex"->resultSet.getInt("sex"),
 
                              )
 
}
 
// Close the connection
 
stmt.close()
 
connection .close()
 
// Return results
 
resultListMap
 
}

3. Insert data

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", "[email protected]")
   ps = conn.prepareStatement(sql)
   PS. setString (1, "Shenzhen")
   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. Delete operation

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", "[email protected]")
   ps = conn.prepareStatement(sql)
   PS. setString (1, "Shenzhen")
   ps.execute()
  } catch {
   case e: Exception => println("myException")
  } finally {
   if (conn != null) {
    conn.close()
   }
   if (ps != null) {
    ps.close()
   }
  }
  sc.stop()
 }
}

5. Update operation

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", "[email protected]")
   ps = conn.prepareStatement(sql)
   PS. setString (1, "Shenzhen")
   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()
 }
}

The above is the whole content of this article. I hope it will be helpful to everyone’s study, and I hope you will support developpaer more.

Recommended Today

Write a package for parsing abnormal JSON strings – fbbk-json

F [beep] [beep] K JSON Parse a package of abnormal JSON strings. install $ npm install fbbk-json Use This package has only one function: var fJSON = require(“fbbk-json”); fJSON.parse(A_JSON_STRING); however This package supports the following JSON strings (that is, what we usually declare in javascript, without quotation marks). Raise a chestnut. ‘{foo”:”bar”,”baz”: true} < – […]