scala 操作数据库的方法
1、定义数据库连接
packagecom.web.dataSource
importcom.alibaba.druid.pool.DruidDataSource
objectMySqlDataSource{
valdriver="com.mysql.jdbc.Driver"
valurl="jdbc:mysql://127.0.0.1:3306"
valusername="root"
valpassword="root"
valconnectionPool=newDruidDataSource()
connectionPool.setUsername(username)
connectionPool.setPassword(password)
connectionPool.setDriverClassName(driver)
connectionPool.setUrl(url)
connectionPool.setValidationQuery("select1")
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、执行查询
defgetOptions(uid:Int)={
valconnection=MySqlDataSource.connectionPool.getConnection
varsql=
s"""selectusername,password,sex
|fromuser
|whereuid=?
""".stripMargin
varstmt=connection.prepareStatement(sql)
stmt.setInt(1,uid)
varresultSet=stmt.executeQuery()
varresultListMap=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、插入数据
objectUpdateLocation{
defmain(args:Array[String]):Unit={
valconf=newSparkConf().setAppName("UpdateLocation").setMaster("local[2]")
valsc=newSparkContext(conf)
varconn:Connection=null
varps:PreparedStatement=null
try{
valsql="INSERTINTOlocation_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{
casee:Exception=>println("myException")
}finally{
if(conn!=null){
conn.close()
}
if(ps!=null){
ps.close()
}
}
sc.stop()
}
}
4、删除操作
objectDeleteLocation{
defmain(args:Array[String]):Unit={
valconf=newSparkConf().setAppName("UpdateLocation").setMaster("local[2]")
valsc=newSparkContext(conf)
varconn:Connection=null
varps:PreparedStatement=null
try{
valsql="deletefromlocation_infowherelocation=?"
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{
casee:Exception=>println("myException")
}finally{
if(conn!=null){
conn.close()
}
if(ps!=null){
ps.close()
}
}
sc.stop()
}
}
5、更新操作
objectInsertLocation{
defmain(args:Array[String]):Unit={
valconf=newSparkConf().setAppName("UpdateLocation").setMaster("local[2]")
valsc=newSparkContext(conf)
varconn:Connection=null
varps:PreparedStatement=null
try{
valsql="updatelocation_infosetlocation=?whereid=?";
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{
casee:Exception=>println("myException")
}finally{
if(conn!=null){
conn.close()
}
if(ps!=null){
ps.close()
}
}
sc.stop()
}
}
以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持毛票票。