Scala之使用ScalikeJDBC操作MySQL

简介

ScalikeJDBC是一款给Scala开发者使用的简介访问类库，它是基于SQL的，使用者只需要关注SQL逻辑的编写，所有的数据库操作都交给ScalikeJDBC。这个类库内置包含了JDBCAPI，并且给用户提供了简单易用并且非常灵活的API。并且，QueryDSl（通用查询查询框架）使你的代码类型安全，半年过去可重复使用。我们可以在生产环境大胆地使用这款DB访问类库。

配置

解决依赖

<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文件夹，文件夹下再创建一个application.conf

db.default.driver="com.mysql.jdbc.Driver"
db.default.url="jdbc:mysql://hadoop001/ruoze_d6?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

操作数据库

建表

create table Employer(
    name varchar(10),
    age varchar(4),
    salary  varchar(10)
);

scala编程实现增删改查操作

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 employers = List(Employer("zhangsan", 20, 18000), Employer("zhangliu", 50, 300000), Employer("lisi", 22, 22000))
    
    //批量插入
    insert(employers)
    
    //查询出结果
    val results = select()
    for (employer <- results) {
      println(employer.name, employer.age, employer.salary)
    }
    
    //修改
    update(1000, "zhangsan")
    
    //根据条件删除
    deleteByname("zhangliu")
    
    //删除所有
    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()  //结果转换成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()
    }
  }
}