scala 操作数据库的方法

scala 操作数据库的方法

1、定义数据库连接

package com.web.dataSource

import com.alibaba.druid.pool.DruidDataSourhttp://ce

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"),

)

}

//zJrEhXkoD关闭连接

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 != nullhttp://) {

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

}

}

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。