Scala04-JDBC编程

2019-04-25

思路整理

- MySQL驱动
- Connection  通过连接池的方式
- Statement  SQL执行器
- ResultSet  获取结果集
- Close  释放资源
`

java-JDBC

pom.xml

<!--MySQL驱动-->
<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <version>5.1.28</version>
</dependency>

demo

  val url = "jdbc:mysql://47.94.108.11:3306/mysql"
  val user = "root"
  val password = "root"

  val sql = "SELECT Db,User FROM db"
  val connection = DriverManager.getConnection(url,user,password)
  val statement = connection.createStatement()
  val rs = statement.executeQuery(sql)

  while (rs.next()){
    val Db = rs.getString(1)
    val User = rs.getString(2)
    println(Db+":"+User)
  }

  rs.close()
  statement.close()
  connection.close()

----------
这里在本地可以，在生产环境是没办法执行的
  Class.forName("com.mysql.jdbc.Driver")
  classOf[com.mysql.jdbc.Driver]
需要添加任意行（建议第二种方式）

scalikejdbc

pom.xml

    <dependency>
      <groupId>org.scalikejdbc</groupId>
      <artifactId>scalikejdbc_2.11</artifactId>
      <version>3.3.2</version>
    </dependency>

      <dependency>
        <groupId>org.scalikejdbc</groupId>
        <artifactId>scalikejdbc-config_2.11</artifactId>
        <version>3.3.2</version>
      </dependency>

-----------
这里要注意，一定要跟自己的scala版本一致，不然会报
Exception in thread "main" java.lang.NoSuchMethodError: scala.Product.$init$(Lscala/Product;)V
的错误

application.conf

src/main/resources/application.conf

# JDBC settings
db.default.driver="com.mysql.jdbc.Driver"
db.default.url="jdbc:mysql://47.94.108.11:3307/scalaTest"
db.default.user="root"
db.default.password="root"

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

# Connection Pool settings
db.default.poolInitialSize=5
db.default.poolMaxSize=7
db.default.poolConnectionTimeoutMillis=1000
db.default.poolValidationQuery="select 1 as one"
db.default.poolFactoryName="commons-dbcp2"

demo

package com.dashu.bigdata.scala03

import scalikejdbc._
import scalikejdbc.config._

case class User(id: Int,name: String, age: Int)

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

    val userList:List[User] = List(User(1,"Lynn",20),User(2,"zhangsan",19),User(3,"lisi",17))
    println("新增数据:"+batchSave(userList))

    println("查询数据:")
    val users1 = select()
    for (user <- users1){
      println("id:"+user.id +" name:"+user.name+" age:"+user.age)
    }

    println("更新id：1的年龄:"+update(10,1))
    val users2 = select()
    for (user <- users2){
      println("id:"+user.id +" name:"+user.name+" age:"+user.age)
    }

    println("删除id:1:"+deleteByID(1))
    println("删除id:2:"+deleteByID(2))
    println("删除id:3:"+deleteByID(3))
    DBs.closeAll()

  }

  def deleteByID(id:Int) = {
    DB.autoCommit { implicit session =>
      SQL("delete from user where id = ?").bind(id).update().apply()
    }
  }

  def update(setage:Int,pid:Int) {
    DB.autoCommit { implicit session =>
      SQL("update user set age = ? where id = ?").bind(setage, pid).update().apply()
    }
  }

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

  def batchSave(users:List[User]) :Unit= {
    DB.localTx { implicit session =>
      for (user<- users){
        SQL("insert into user(name,age,id) values(?,?,?)").bind(user.name, user.age, user.id).update().apply()
      }
    }
  }
}