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() }}
以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持我们。
本文标题: scala 操作数据库的方法
以上就上有关scala操作数据库的方法的全部内容，学步园全面介绍编程技术、操作系统、数据库、web前端技术等内容。