1.什么是scalikejdbc?
ScalikeJDBC是一款给Scala开发者使用的简洁DB访问类库,它是基于SQL的,使用者只需要关注SQL逻辑的编写,所有的数据库操作都交给ScalikeJDBC。这个类库内置包含了JDBC API,并且给用户提供了简单易用并且非常灵活的API。并且,QueryDSL(通用查询查询框架)使你的代码类型安全的并且可重复使用。我们可以在生产环境大胆地使用这款DB访问类库。
2.使用scalikejdbc操作MySQL
2.1在pom文件中添加如下依赖
<!--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>
<scala.version>2.11.8</scala.version>
<scalikejdbc.version>3.3.2</scalikejdbc.version>
<mysql.jdbc.version>5.1.38</mysql.jdbc.version>
2.2编译jdbc连接信息文件
如下在src下创建了一个名为config文件夹,里面添加了一个名为application.conf的配置文件
注意,若想使配置文件能够被加载,需将config设置为resource
application.conf文件配置信息如下
// 默认的配置就是db.default.xxx,如果使用其他配置需要setup('name)指明
db.default.driver="com.mysql.jdbc.Driver"
db.default.url="jdbc:mysql://192.168.175.135/test?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"
2.3scala编程实现增删改查操作
package com.wsk.bigdata.scala.scalikejdbc
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 wsktest(name,age,salary) values(?,?,?)")
.bind(employer.name, employer.age, employer.salary)
.update().apply()
}
}
}
def select(): List[Employer] = {
DB.readOnly { implicit session =>
SQL("select * from wsktest")
.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 wsktest set age = ? where name = ?").bind(age, name).update().apply()
}
}
def deleteByname(name: String): Unit = {
DB.autoCommit { implicit session =>
SQL("delete from wsktest where name = ?").bind(name).update().apply()
}
}
def deleteAll(): Unit ={
DB.autoCommit { implicit session =>
SQL("delete from wsktest ").update().apply()
}
}
}