layer

CREATE table  layer(id INT(12) UNSIGNED NOT NULL  AUTO_INCREMENT,
words VARCHAR(500),
value  decimal(10,7),
PRIMARY KEY(id)
)  DEFAULT CHARSET=utf8;


ALTER TABLE `layer` ADD INDEX(`words`)

/**
  * Created by lkl on 2017/6/27.
  */
import java.sql.{DriverManager, ResultSet}
import org.apache.spark.SparkContext
import org.apache.spark.SparkConf
import java.math.BigDecimal
object layer {
  def main(args: Array[String]) {
    val conf = new SparkConf().setMaster("local").setAppName("test")
    val sc = new SparkContext(conf)
    val sqlContext = new org.apache.spark.sql.SQLContext(sc)
    val role = "jdbc:mysql://192.168.0.37:3306/emotional?user=root&password=123456&useUnicode=true&characterEncoding=utf8&autoReconnect=true&failOverReadOnly=false"

//    val log = sc.textFile("hdfs://192.168.0.211:9000/user/hadoop/emotion/汉语情感词极值表.txt")
//        val g = log.map(line => (line.split(" ").head, line.split(" ").last.trim))
//        import sqlContext.implicits._
//        val df = g.toDF("words", "value").registerTempTable("layer")
//        val value = sqlContext.sql("select words,value from layer")
//        val pp=value.map(p => {
//          val words= p.getString(0)
//          val value = p.getString(1)
//          (words,value)
//        })
//
//        pp.foreach(p => {
//            val v0 = p._1
//            val v1 = p._2.toFloat
//          insert(v0, v1)
//
//        })

    val logCsenticNet = sc.textFile("hdfs://192.168.0.211:9000/user/hadoop/emotion/editCsenticNet.txt")
    val gCsenticNet = logCsenticNet.map(line => (line.split(",")(0), line.split(",")(1), line.split(",")(2), line.split(",")(3), line.split(",")(4)))
    import sqlContext.implicits._
    val dfCsenticNet = gCsenticNet.toDF("words", "attitude", "expression", "extreme", "VALUE").registerTempTable("CsenticNetlayer2")
    val valueCsenticNet = sqlContext.sql("select words,attitude,expression,extreme,VALUE from CsenticNetlayer2")
    val ppCsenticNet = valueCsenticNet.map(p => {
      val v0 = p.getString(0)
      val v1 = p.getString(1)
      val v2 = p.getString(2)
      val v3 = p.getString(3)
      val v4 = p.getString(4)
      (v0, v1, v2, v3, v4)
    })

    ppCsenticNet.foreach(p => {
      val v0 = p._1
      val v1 = p._2
      val v2 = p._3
      val v3 = p._4
      val v4 = p._5
      insert2(v0, v1, v2, v3, v4)
    })



    def insert(value0: String, value1: Float): Unit = {

      println(value0, value1)
      val rl = "jdbc:mysql://192.168.0.37:3306/emotional?user=root&password=123456&useUnicode=true&characterEncoding=utf8&autoReconnect=true&failOverReadOnly=false"

      classOf[com.mysql.jdbc.Driver]
      val conn = DriverManager.getConnection(rl)
      val statement = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE)
      try {
        val prep = conn.prepareStatement("INSERT INTO layer(words,VALUE) VALUES (?,?) ")
        prep.setString(1, value0)
        prep.setFloat(2, value1)

        prep.executeUpdate
      } catch {
        case e: Exception => e.printStackTrace
      }
      finally {
       // conn.close
      }
    }

    def insert2(value0: String, value1: String, value2: String, value3: String, value4: String): Unit = {
      val rl = "jdbc:mysql://192.168.0.37:3306/emotional?user=root&password=123456&useUnicode=true&characterEncoding=utf8&autoReconnect=true&failOverReadOnly=false"

      classOf[com.mysql.jdbc.Driver]
      val conn = DriverManager.getConnection(rl)
      val statement = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE)
      // CREATE TABLE words2(innersessionId VARCHAR(100),words VARCHAR(100), VARCHAR(100),posit VARCHAR(100),va VARCHAR(100))
      try {
        val prep = conn.prepareStatement("INSERT INTO CsenticNetlayer2(words,attitude,expression,extreme,VALUE) VALUES (?,?,?,?,?) ")
        prep.setString(1, value0)
        prep.setString(2, value1)
        prep.setString(3, value2)
        prep.setString(4, value3)
        prep.setString(5, value4)
        prep.executeUpdate
      } catch {
        case e: Exception => e.printStackTrace
      }
      finally {
       // conn.close
      }
    }
  }}