/*此為代碼格式，實(shí)際中使用應(yīng)替換相應(yīng)字段中的內(nèi)容*/
val url = "jdbc:mysql://localhost:/database"        
val tableName = "table"
// 設(shè)置連接用戶&密碼
val prop = new java.util.Properties
prop.setProperty("user","username")  //實(shí)際使用中替換username為相應(yīng)的用戶名
prop.setProperty("password","pwd")   //實(shí)際使用中替換pwd為相應(yīng)的密碼

/*此為代碼格式，實(shí)際中使用應(yīng)替換相應(yīng)字段中的內(nèi)容*/
def jdbc(
url: String,
table: String,
columnName: String, // 根據(jù)該字段分區(qū)，需要為整型，比如 id 等
lowerBound: Long, // 分區(qū)的下界
upperBound: Long, // 分區(qū)的上界
numPartitions: Int,  //分區(qū)的個(gè)數(shù)
connectionProperties: Properties): DataFrame

/*此為代碼格式，實(shí)際中使用應(yīng)替換相應(yīng)字段中的內(nèi)容*/
val url = "jdbc:mysql://mysqlHost:3306/database"
val tableName = "table"
val columnName = "colName"
val lowerBound = 1,
val upperBound = 10000000,
val numPartitions = 10,
// 設(shè)置連接用戶&密碼
val prop = new java.util.Properties
prop.setProperty("user","username")
prop.setProperty("password","pwd")

/*此為代碼格式，實(shí)際中使用應(yīng)替換相應(yīng)字段中的內(nèi)容*/
jdbc(
url: String,
table: String,
predicates: Array[String],
connectionProperties: Properties): DataFrame

/*此為代碼格式，實(shí)際中使用應(yīng)替換相應(yīng)字段中的內(nèi)容*/
val url = "jdbc:mysql://mysqlHost:3306/database"
val tableName = "table"
// 設(shè)置連接用戶&密碼
val prop = new java.util.Properties
prop.setProperty("user","username")
prop.setProperty("password","pwd")
/**
* 將 9 月 16-12 月 15 三個(gè)月的數(shù)據(jù)取出，按時(shí)間分為 6 個(gè) partition
* 為了減少事例代碼，這里的時(shí)間都是寫(xiě)死的
* modified_time 為時(shí)間字段
*/
val predicates =
Array(
"2015-09-16" -> "2015-09-30",
"2015-10-01" -> "2015-10-15",
"2015-10-16" -> "2015-10-31",
"2015-11-01" -> "2015-11-14",
"2015-11-15" -> "2015-11-30",
"2015-12-01" -> "2015-12-15"
).map {
case (start, end) =>
s"cast(modified_time as date) >= date '$start' " + s"AND cast(modified_time
as date) <= date '$end'"
}

create database test;    //創(chuàng)建數(shù)據(jù)庫(kù)test
use test;        //進(jìn)入數(shù)據(jù)庫(kù)test
create table people( name varchar(12), age int);         //創(chuàng)建表格people并構(gòu)建結(jié)構(gòu)
insert into people values ("Andy",30),("Justin",19),("Dela",25),("Magi",20),("Pule",21),("Mike",12);        //向people表中插入數(shù)據(jù)
select * from people;         //輸出people表中全部數(shù)據(jù)

//導(dǎo)入依賴環(huán)境
import org.apache.spark.{SparkConf, SparkContext}
import org.apache.spark.sql.{DataFrame, SQLContext}
import java.util.Properties
val url = "jdbc:mysql://localhost/test"       //MySQL地址及數(shù)據(jù)庫(kù)
val username = "root"       //用戶名
val sqlContext = new SQLContext(sc)       
sc.setLogLevel("WARN")
val uri = url + "?user=" + username + "&useUnicode=true&characterEncoding=UTF-8"           //設(shè)置讀取路徑及用戶名
val properties = new Properties()      //創(chuàng)建JDBC連接信息
properties.put("user","root")
properties.put("driver", "com.mysql.jdbc.Driver")
val df_test: DataFrame = spark.sqlContext.read.jdbc(uri, "people", properties)  //讀取數(shù)據(jù)
df_test.select("name","age").collect().foreach(row => {         //輸出數(shù)據(jù)
	println("name " + row(0) + ", age" + row(1))
})
df_test.write.mode("append").jdbc(uri,"people",properties)     //向people表中寫(xiě)入讀出的數(shù)據(jù)，相當(dāng)于people表中有兩份一樣的數(shù)據(jù)