Spark作业四 - 使用Scalikejdbc操作mysql数据库

一、新建Maven的Scala工程

二、在pom.xml中添加依赖

三、配置连接数据库的信息文件

四、基础测试jdbc连接测试

五、根据给定需求开发函数

一、新建Maven的Scala工程

前置环境：IDEA+Maven3.5.3+mysql5.6.23+scala2.11.8

1、File --> new --> project --> 选中maven -->选中create from archetype -->选中org.scala-tools.archetypes:scala-archetype-simple

2、输入Groupid、Artifactedid 、version

3、再选择自己的maven home directory，settings file，local repository

4、一切就绪以后，import changes

二、在pom.xml中添加依赖

<properties>
    <scala.version>2.11.8</scala.version>
      <scalikejdbc.version>3.3.2</scalikejdbc.version>		//自行添加
      <mysql.jdbc.version>5.1.38</mysql.jdbc.version>			//自行添加
  </properties>

<!--Scala相关依赖-->
<dependency>
    <groupId>org.scala-lang</groupId>
    <artifactId>scala-library</artifactId>
    <version>${scala.version}</version>

</dependency>

<!--scalikejdbc相关依赖-->
<dependency>
    <groupId>org.scalikejdbc</groupId>
    <artifactId>scalikejdbc_2.11</artifactId>
    <version>${scalikejdbc.version}</version>
</dependency>

<dependency>
    <groupId>org.scalikejdbc</groupId>
    <artifactId>scalikejdbc-config_2.11</artifactId>
    <version>${scalikejdbc.version}</version>
</dependency>

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>${mysql.jdbc.version}</version>
</dependency>

三、配置连接数据库的信息文件

• 在src的main目录下新建一个resource的文件夹，在resource下新建一个一个application.conf的配置文件。
  为了能够使这个配置文件能够被加载，需要将其设置为resources，进行如下设置。
  

application.conf中的配置信息如下

db.default.driver="com.mysql.jdbc.Driver"
db.default.url="jdbc:mysql://hadoop004/ruoze_g6?characterEncoding=utf-8"
db.default.user="root"
db.default.password="123456"

# Connection Pool settings
db.default.poolInitialSize=10
db.default.poolMaxSize=20
db.default.connectionTimeoutMillis=1000

在MySQL中使用ruoze_g6数据库，并在其下创建emp表：

use ruoze_g6;
Database changed

创建emp表
create table emp(
name varchar(40),
age int(10),
salary varchar(40));

四、基础版测试连接

最基本的查询：

查询ruoze_g6数据库下dbs表中的三个字段：DB_ID,DB_LOCATION_URI,NAME

package com.ruozedata.bigdata

import java.sql.DriverManager



object jdbc {
  def main(args: Array[String]): Unit = {

    //指定数据库的url,username,password
    val url = "jdbc:mysql://10.0.0.135:3306/ruoze_g6"
    val user = "root"
    val password = "960210"
    val sql = "select DB_ID,DB_LOCATION_URI,NAME from dbs"


    //加载驱动
    Class.forName("com.mysql.jdbc.Driver")

    val connection = DriverManager.getConnection(url,user,password)
    val stmt = connection.createStatement()
    val rs = stmt.executeQuery(sql)

    while(rs.next()){
      val dbid = rs.getString(1)
      val location = rs.getString(2)
      val name = rs.getString(3)
      println(dbid +"  "+ location + "  "+ name)

    }


    rs.close()
    stmt.close()
    connection.close()

  }

}

输出如下：
1  hdfs://localhost:9000/user/hive/warehouse  default
6  hdfs://hadoop004:9000/user/hive/warehouse/ruozeg6.db  ruozeg6
11  hdfs://hadoop004:9000/user/hive/warehouse/test.db  test

连接数据库中出现的问题：

Access denied for user ‘root’@’%’ to database ‘mytest’

换台机器：

于是就要赋予权限：

• grant all privileges on . To ‘root’@‘10.0.0.1’ IDENTIFIED BY ‘960210’ with grant option;

翻译：
允许root用户从IP为10.0.0.1的主机连接到MySQL服务器，并使用960210作为密码。

• flush privileges;
• 刷新权限

五、根据给定需求开发函数

package scala02

import scalikejdbc._
import scalikejdbc.config._

case class Employer(name: String, age: Int, salary: Long)

object ScalikeJdbcApp {
  def main(args: Array[String]): Unit = {
    DBs.setupAll()

    val config = DBs.config

    val employers = List(Employer("zhangsan", 20, 18000),Employer("zhangliu", 50, 300000), Employer("lisi", 22, 22000))
    //批量插入
    insert(employers)
    println("----------------insert执行完毕---------------")

    //查询出结果
    val results = select()
    for (employer <- results) {
      println(employer.name, employer.age, employer.salary)
    }
    println("----------------select执行完毕---------------")
    //修改
    update(1000, "zhangsan")
    println("----------------update执行完毕---------------")
    //根据姓名删除
    deleteByname("zhangliu")
    println("----------------deleteByname执行完毕---------------")
    //删除所有记录
    deleteAll()
    println("----------------deleteAll执行完毕---------------")

    DBs.closeAll()
  }


  def insert(employers: List[Employer]): Unit = {
    DB.localTx { implicit session =>
      for (employer <- employers) {
        SQL("insert into emp(name,age,salary) values(?,?,?)")
          .bind(employer.name, employer.age, employer.salary)
          .update().apply()
      }

    }
  }

  def select(): List[Employer] = {
    DB.readOnly { implicit session =>
      SQL("select * from emp")
        .map(rs => Employer(rs.string("name"), rs.int("age"), rs.long("salary")))
        .list().apply()
    }
  }

  def update(age: Int, name: String) {
    DB.autoCommit { implicit session =>
      SQL("update emp set age = ? where name = ?").bind(age, name).update().apply()
    }
  }


  def deleteByname(name: String): Unit = {
    DB.autoCommit { implicit session =>
      SQL("delete from emp where name = ?").bind(name).update().apply()
    }
  }

  def deleteAll(): Unit ={
    DB.autoCommit { implicit session =>
      SQL("delete from emp ").update().apply()
    }
  }
}