scalikejdbc官网:http://scalikejdbc.org/
scalikejdbc的简介:
ScalikeJDBC是一款给Scala开发者使用的简介访问类库,它是基于SQL的,使用者只需要关注SQL逻辑的编写,所有的数据库操作都交给ScalikeJDBC。这个类库内置包含了JDBCAPI,并且给用户提供了简单易用并且非常灵活的API。并且,QueryDSl(通用查询查询框架)使你的代码类型安全,半年过去可重复使用。我们可以在生产环境大胆地使用这款DB访问类库。
笔者用的是MySQL数据库,所以此处以MySQL为例,以下是通过ScalikeJDBC来操作数据库的具体过程
【操作大纲】
-
1.拿到connection
-
2.拿到statement
-
3.拿到rs
-
4.结果获取出来
-
5.关掉rs,stmt和connection释放资源
【pom文件的配置】
<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>
【配置连接数据库的信息文件】
1.在src的main目录下配置一个resources文件夹,文件夹下再创建一个application.conf
【配置使文件能够被读取】
若想使配置文件能够被加载,需将config设置为resources
【application.conf文件配置信息如下】
// 默认的配置就是db.default.xxx,如果使用其他配置需要setup('name)指明
db.default.driver="com.mysql.jdbc.Driver"
db.default.url="jdbc:mysql://hadoop001/ruoze_hive?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
# h2 example
#db.default.driver="org.h2.Driver"
#db.default.url="jdbc:h2:file:./db/default"
# PostgreSQL example
#db.default.driver="org.postgresql.Driver"
#db.default.url="jdbc:postgresql://localhost:5432/scalikejdbc"
【在ruoze_hive创建Employer表格】
create table Employer(
name varchar(10),
age varchar(4),
salary varchar(10)
);
【scala编程实现增删改查操作】
package com.ruozedata.bigdata.scala02
import scalikejdbc._
import scalikejdbc.config._
case class Employer(name: String, age: Int, salary: Long)
object JdbcTest {
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 Employer(name,age,salary) values(?,?,?)")
.bind(employer.name, employer.age, employer.salary)
.update().apply()
}
}
}
def select(): List[Employer] = {
DB.readOnly { implicit session =>
SQL("select * from Employer")
.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 Employer set age = ? where name = ?").bind(age, name).update().apply()
}
}
def deleteByname(name: String): Unit = {
DB.autoCommit { implicit session =>
SQL("delete from Employer where name = ?").bind(name).update().apply()
}
}
def deleteAll(): Unit = {
DB.autoCommit { implicit session =>
SQL("delete from Employer ").update().apply()
}
}
}
需求
- 1) 通过SQL准备一张表user,有id,name,age三个字段
- 2) 通过scalikejdbc往user表里添加10条记录
- 3) 通过scalikejdbc查询user表所有的数据
- 4) 通过scalikejdbc将id=8的数据的age做修改,加10
package com.ruozedata.bigdata.scala03
import scalikejdbc._
import scalikejdbc.config._
object scalikeJDBDone {
//创建实例
case class User(id: Int, name: String, age: Int)
def main(args: Array[String]): Unit = {
//加载所有配置文件
DBs.setupAll()
// DBs.setup() 只加载 application.conf文件
// loaded from "db.default.*"
implicit val session = AutoSession
//创建一张表 名字为user
sql"""
create table USER (
id int not null primary key,
name varchar(255),
age int
)
""".execute.apply()
//测试 调用insertuser方法,向表中插入10条数据
val users = List(User(1, "xiaoqiang", 25), User(2, "xiaogong", 18), User(3, "lizi", 15),
User(4, "mingren", 20), User(5, "zuozhu", 38), User(6, "qiya", 30), User(7, "yihu", 19),
User(8, "xiaoxin", 12), User(9, "xiaobai", 13), User(10, "jingshang", 18))
val user = insertuser(users)
//使用sql查询 id=8的年龄并返回给a,
//给 年龄加10岁
val id = 8
DB localTx { implicit session =>
val a = sql"select age from user where id=${id}".map(rs => rs.get[Int]("age")).single().apply()
sql"update user set age= ${a}+10 where id = ${id}".update.apply()
}
//调用selectuser方法 查询所有数据,查询出来的是List集合对象,并循环打印出来。
val userone = selectuser()
for (user <- userone) {
println("id:" + user.id + "," + "name:" + user.name + "," + "age:" + user.age)
}
}
//使用事务插入表中
def insertuser(users: List[User]): Unit = {
DB.localTx { implicit session =>
for (user <- users) {
sql"insert into user(id,name,age) values(?,?,?)"
.bind(user.id, user.name, user.age).update().apply()
}
}
}
//查询所有数据,将数据封装成对象,并返回一个集合
def selectuser(): List[User] = {
DB.readOnly { implicit session =>
sql"select * from USER ".map(rs => User(rs.int("id"), rs.string("name"), rs.int("age"))).list().apply()
}
}
}