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.SparkContextimport org.apache.spark.SparkConfimport java.math.BigDecimalobject 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      }    }  }}