当前位置:Gxlcms > 数据库问题 > 【转】Scala JDBC 查询和更新MySQL

【转】Scala JDBC 查询和更新MySQL

时间:2021-07-01 10:21:17 帮助过:8人阅读

  1. package com.bi
  2. import java.sql.{Connection, DriverManager, Timestamp}
  3. import java.util.Calendar
  4. /**
  5. * Created by xxx on 2017/6/28.
  6. */
  7. object MySqlConn {
  8. // for test env
  9. val mysqlConfTest = collection.mutable.Map(
  10. "driver" -> "com.mysql.jdbc.Driver",
  11. "url" -> "jdbc:mysql://192.168.18.106:3306/rpt",
  12. "username" -> "test",
  13. "password" -> "test"
  14. )
  15. // for prod env
  16. val mysqlConfProd = collection.mutable.Map(
  17. "driver" -> "com.mysql.jdbc.Driver",
  18. "url" -> "jdbc:mysql://hostname:3306/rpt?autoReconnect=true",
  19. "username" -> "xxxx",
  20. "password" -> "xxxxx"
  21. )
  22. /**
  23. * 创建mysql连接
  24. * @return
  25. */
  26. def getMysqlConn(): Connection = {
  27. Class.forName(mysqlConfTest("driver"))
  28. DriverManager.getConnection(mysqlConfTest("url"), mysqlConfTest("username"), mysqlConfTest("password"))
  29. }
  30. /**
  31. * 查询所有漏斗
  32. * @return
  33. */
  34. def getFunnels(dateStr: String): collection.mutable.Map[Int, (String, Int, Int ,String ,String)] = {
  35. // connect to the database named "mysql" on the localhost
  36. val conn = getMysqlConn
  37. val funnels = collection.mutable.Map[Int, (String, Int, Int ,String ,String)]()
  38. try {
  39. // create the statement, and run the select query
  40. val statement = conn.createStatement()
  41. val sql = s"""select id,funnel_name,status,done,start_time,end_time
  42. |from rpt_funnel_manage
  43. |where status = 1
  44. |and (done in (0,3) or (done = 2 and ‘$dateStr‘ <= end_time and ‘$dateStr‘ >= start_time))
  45. |order by id""".stripMargin
  46. println(sql)
  47. val resultSet = statement.executeQuery(sql)
  48. while (resultSet.next()) {
  49. val fid = resultSet.getInt("id")
  50. val funnelName = resultSet.getString("funnel_name")
  51. val status = resultSet.getInt("status")
  52. val done = resultSet.getInt("done")
  53. val startTime = resultSet.getString("start_time")
  54. val endTime = resultSet.getString("end_time")
  55. funnels += (fid -> (funnelName, status, done, startTime, endTime))
  56. }
  57. } catch {
  58. case e:Throwable => {
  59. println("FunnelGO failed! Coursed by getFunnels error")
  60. e.printStackTrace()
  61. System.exit(1)
  62. }
  63. } finally {
  64. conn.close
  65. }
  66. funnels
  67. }
  68. def getFunnelLevels(funnelId: Int): collection.mutable.Map[Int, List[Int]] = {
  69. // connect to the database named "mysql" on the localhost
  70. val conn = getMysqlConn
  71. val funnel = collection.mutable.Map[Int, List[Int]]()
  72. try {
  73. // create the statement, and run the select query
  74. val statement = conn.createStatement()
  75. val sql = s"select * from rpt_funnel_manage_level where fid = $funnelId order by level desc"
  76. val resultSet = statement.executeQuery(sql)
  77. while (resultSet.next()) {
  78. val fid = resultSet.getInt("fid")
  79. val level = resultSet.getInt("level")
  80. val page_id = resultSet.getInt("page_id")
  81. val pageIds = List(page_id)
  82. // 实际调用的是contains(key)
  83. if(funnel.isDefinedAt(level)){
  84. val pages = funnel(level)
  85. // List 多种姿势
  86. funnel += (level -> pages.++(pageIds))
  87. } else {
  88. funnel += (level -> pageIds)
  89. }
  90. println("id, funnel_name, page_id = " + fid + ", " + level+ ", " + page_id)
  91. }
  92. } catch {
  93. case e:Throwable => e.printStackTrace()
  94. } finally {
  95. conn.close
  96. }
  97. funnel
  98. }
  99. /**
  100. * 更新运行状态至rpt_funnel_manage表的done字段
  101. * 0 未执行 1 执行中 2 执行完成 3 sql执行失败 4 dump执行失败
  102. * @param funnelId
  103. * @param runningStatus
  104. */
  105. def updateRunningStatus(funnelId: Int, runningStatus: Int): Unit = {
  106. // create database connection
  107. val conn = getMysqlConn
  108. try {
  109. val ps = conn.prepareStatement("UPDATE rpt_funnel_manage SET done = ? WHERE id = ?")
  110. // set the preparedstatement parameters
  111. ps.setInt(1, runningStatus)
  112. ps.setInt(2, funnelId)
  113. // call executeUpdate to execute our sql update statement
  114. val res = ps.executeUpdate()
  115. ps.close()
  116. if(res > 0) println(s"UPDATE rpt_funnel_manage SET done = $runningStatus WHERE id = $funnelId success!")
  117. else println(s"UPDATE rpt_funnel_manage SET done = $runningStatus WHERE id = $funnelId failed!")
  118. } catch {
  119. case e:Throwable => e.printStackTrace()
  120. } finally {
  121. conn.close
  122. }
  123. }
  124. /**
  125. * 将sql写入mysql表,同时将sql的运行状态done字段更新为1:运行中
  126. * @param funnelId
  127. * @param sqlString
  128. */
  129. def updateSql(funnelId: Int, sqlString: String): Unit = {
  130. // create database connection
  131. val conn = getMysqlConn
  132. try {
  133. val ps = conn.prepareStatement("UPDATE rpt_funnel_manage SET sqlstring = ?,done = 1 WHERE id = ?")
  134. // set the preparedstatement parameters
  135. ps.setString(1, sqlString)
  136. ps.setInt(2, funnelId)
  137. // call executeUpdate to execute our sql update statement
  138. val res = ps.executeUpdate()
  139. ps.close()
  140. if(res > 0) println(s"UPDATE rpt_funnel_manage SET sqlstring WHERE id = $funnelId success!")
  141. else println(s"UPDATE rpt_funnel_manage SET sqlstring WHERE id = $funnelId failed!")
  142. } catch {
  143. case e:Throwable => e.printStackTrace()
  144. } finally {
  145. conn.close
  146. }
  147. }
  148. private def deleteById(conn: Connection, funnelId: Int): Int = {
  149. var status = 0
  150. try {
  151. val sql = s"delete from rpt_funnel_sql where funnelId = $funnelId"
  152. System.out.println("sql=" + sql)
  153. val st = conn.createStatement()
  154. val result = st.executeUpdate(sql)
  155. //处理结果
  156. if (result > 0) println(s"delete from rpt_funnel_sql where funnelId = $funnelId success!")
  157. else println(s"delete from rpt_funnel_sql where funnelId = $funnelId failed!")
  158. status = result
  159. } catch {
  160. case e:Throwable => e.printStackTrace()
  161. } finally {
  162. conn.close
  163. }
  164. status
  165. }
  166. /**
  167. * 根据 funnelId 删除记录
  168. * @param funnelId
  169. */
  170. def deleteById(funnelId: Int): Int = {
  171. val connection = getMysqlConn
  172. val st = connection.createStatement
  173. var status = 0
  174. // do database insert
  175. try {
  176. val sql = s"delete from rpt_funnel_sql where funnelId = $funnelId"
  177. System.out.println("sql=" + sql)
  178. val result = st.executeUpdate(sql)
  179. //处理结果
  180. if (result > 0) println("操作成功") else println("操作失败")
  181. status = result
  182. } catch {
  183. case e:Throwable => e.printStackTrace()
  184. } finally {
  185. connection.close
  186. }
  187. status
  188. }
  189. /**
  190. * 日期
  191. * @return
  192. */
  193. private def getCurrentTimeStamp(): Timestamp = {
  194. val timeInMillis = Calendar.getInstance.getTimeInMillis
  195. new Timestamp(timeInMillis)
  196. }
  197. def main(args: Array[String]): Unit = {
  198. val funnelId = args(0).toInt
  199. MySqlConn.updateRunningStatus(funnelId,0)
  200. }
  201. }

  

【转】Scala JDBC 查询和更新MySQL

标签:val   new t   jdb   case   eve   结果   div   led   trace   

人气教程排行