一、新建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()
}
}
}