scala 操作数据库的方法

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

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]]() //获取结果 while(resultSet.next()){ resultListMap = resultListMap :+ Map(                                "username"->resultSet.getString("username"),                                "password"->resultSet.getString("password"),                                "sex"->resultSet.getInt("sex"),                               ) } //关闭连接 stmt.close() connection .close() //返回结果 resultListMap }

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

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

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