scala 操作数据库的方法

1、定义数据库连接

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、执行查询

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
 
}

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

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持。