CREATE TABLE `category` (

`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,

`editdate` char(20) NOT NULL DEFAULT "20170622",

`number`  CHAR(100) NOT NULL DEFAULT "spell",

`category` CHAR(100) NOT NULL DEFAULT "综合",

`words` CHAR(100) NOT NULL DEFAULT "name",

 PRIMARY KEY (`id`)

) DEFAULT CHARSET=utf8;

ALTER TABLE `category` 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

      }

    }

  }}