scala連結mysql

標籤：

方法一：連結並操作mysql

//連結Mysql
val driver = "com.mysql.jdbc.Driver"
val url = "jdbc:mysql://10.1.2.190:8066/mq_sale_disc"
val username = "kr.user"
val password = "[email protected]"
var connectionMqcrm: Connection = null
Class.forName(driver)
connectionMqcrm = DriverManager.getConnection(url, username, password)
val statement = connectionMqcrm.createStatement()

//查詢、插入
var count = 0
val sqlname = "insert into tmp_enterprise( ENTERPRISE_NAME ) values (\‘" + coname + "\‘) "
val sqlcount = "select count(*) from  tmp_enterprise  where  ENTERPRISE_NAME  =\‘" + coname + "\‘ "
var ecusqlcount = statement.executeQuery(sqlcount)
while (ecusqlcount.next()){
    count = ecusqlcount.getInt(1)
}
if (count >0) {
    val ecusqlname = statement.executeUpdate(sqlname)
}
//插入徵信分數
var countnum =0
var companyID = 0
val sqlID = "select  ENTERPRISE_ID  from  tmp_enterprise  where  ENTERPRISE_NAME= \‘" + coname +"\‘ "
val ecusqlID = statement.executeQuery(sqlID)
while (ecusqlID.next()){
    companyID = ecusqlID.getInt("ENTERPRISE_ID")
}
val timenew = System.currentTimeMillis()
var dateFormat: SimpleDateFormat = new SimpleDateFormat("yyyy-MM-dd")
var nowtime: Date = new Date(timenew)
val currentTimenew = dateFormat.format(nowtime)
val sqlscore = "insert  into tm_enterprise_credit(CREATE_TIME ,UPDATE_TIME , ENTERPRISE_ID, CREDIT_SCORE ) values( \‘" + currentTimenew + "\‘ , \‘" + currentTimenew + "\‘,\‘" + companyID + "\‘,\‘" + score + "\‘)"
val sqlcountN = "select count(*) from  tm_enterprise_credit  where  ENTERPRISE_ID  = \‘" + companyID + "\‘"
val ecusqlcountN = statement.executeQuery(sqlcountN)
while (ecusqlcountN.next()){
    countnum = ecusqlcountN.getInt(1)
}
if (countnum > 0 ) {
    val sqlnew = "update tm_enterprise_credit set CREDIT_SCORE= \‘" + score + " \‘ , UPDATE_TIME= \‘" + currentTimenew + "\‘" + "where ENTERPRISE_ID= \‘" + companyID + "\‘"
    val ecusqlnew = statement.executeUpdate(sqlnew)
} else {
    val ecusqlscore = statement.executeUpdate(sqlscore)
}

方法二：查詢

val jdbcDF = sqlContext.read.format("jdbc").options(Map("url" -> "jdbc:mysql://10.1.2.190:8066/mq_crm?user=kr.user&[email protected]",
"dbtable" -> "crm_order")).load()

val tableDF = sqlContext.jdbc("jdbc:mysql://mysql_hostname:mysql_port/testDF?user=your_username&password=your_password", "user")

//查詢mySql資料庫
val tableDF = sqlContext.jdbc("jdbc:mysql://10.1.2.190:8066/mq_sale_disc?user=kr.user&[email protected]", "tmp_enterprise")

注意：execute、executeUpdate、executeQuery三者區別

查詢用executeQuery

插入、更新、刪除用executeUpdate

 

scala連結mysql