java.sql

import java.sql.{Connection, DriverManager, Statement}

object SimpleJDBC {

  def main(args: Array[String]): Unit = {
    val url = "jdbc:mysql://localhost:3306/message_weibo"
    val username = "root"
    val password = "root"

    var connection: Connection = null

    //sql_1内容可变
    val sq1_1: String = "INSERT weibo_attitude VALUES(NULL,'2019-09-12 13:24:57',1,0,0);"
    val sql_2: String = "SELECT SUM(wbnum_pos) FROM weibo_attitude;"
    val sql_3: String = "SELECT SUM(wbnum_neg) FROM weibo_attitude;"
    val sql_4: String = "SELECT SUM(wbnum_neu) FROM weibo_attitude;"

    try {
      //make the connection
      classOf[com.mysql.jdbc.Driver]

      connection = DriverManager.getConnection(url, username, password)

      //create the statement, and run the select query
      val statement1 = connection.createStatement()
      val statement2 = connection.createStatement()
      val statement3 = connection.createStatement()
      val statement4 = connection.createStatement()
      val resultSet1 = statement1.executeUpdate(sq1_1)

      val resultSet2 = statement2.executeQuery(sql_2)
      val resultSet3 = statement3.executeQuery(sql_3)
      val resultSet4 = statement4.executeQuery(sql_4)
      while (resultSet2.next()) {
        //wbnum_pos存储当前positive态度的item的总数,得到的wbnum_pos是INT类型
        val wbnum_pos = resultSet2.getString("SUM(wbnum_pos)")
      }
      while (resultSet3.next()) {
        //wbnum_neg存储当前negative态度的item的总数,得到的wbnum_neg是INT类型
        val wbnum_neg = resultSet3.getString("SUM(wbnum_neg)")
      }
      while (resultSet4.next()) {
        //wbnum_neu存储当前neutral态度的item的总数,得到的wbnum_neu是INT类型
        val wbnum_neu = resultSet4.getString("SUM(wbnum_neu)")
      }

    } catch {
      case e: Exception => e.printStackTrace()
    } finally {
      //release the resource
      if (connection == null) {
        connection.close()
      }
    }
  }
}