scalikeJdbc使用

● 加载数据库配置

package com.ws.spark
import scalikejdbc.{DB, NamedDB, SQL}
import scalikejdbc.config.DBs
import scala.collection.immutable

object ScalikeJdbcDemo {
  def main(args: Array[String]): Unit = {

    //默认加载application.conf文件的db.default.*配置，底层读取配置使用safeConfig
    //加载数据库配置
    //DBs.setup('ws) //只加载db.ws.*配置
    DBs.setup() //默认只加载db.default.*配置
    //DBs.setupAll() //默认加载全部配置全局
    //insert()
    //val list = selectAll()
   // val result = txInsert()
    val result = insertAndReturnId()
    println(result)
  }
}

case class Emp(id: Int, name: String, age: Int, address: String)

● 查询数据

  def selectSingle(): List[String] = {
    val list: List[String] = DB.readOnly {
      implicit session =>
        SQL("SELECT name from emp").map(rs => rs.string("name")).list().apply()
    }
    list
  }

● 查询数据封装到case class

case class Emp(id: Int, name: String, age: Int, address: String)

  def selectAll(): List[Emp] = {
    val list: List[Emp] = DB.readOnly {
      implicit session =>
        SQL("SELECT * from emp").map(rs => Emp(rs.int(1), rs.string(2), rs.int(3), rs.string(4))).list().apply()
    }
    list
  }

● 插入一条新数据

  def insert(): Unit = {
    DB.autoCommit { implicit session =>
      SQL("insert into emp(name,age,address) values(?,?,?)").bind("思思", 19, "北京").update().apply()
    }
  }

● 插入数据并返回主键

  def insertAndReturnId(): Long = {

    val id: Long = DB.localTx {
      implicit session =>
        SQL("insert into emp (name,age,address) values(?,?,?)").bind("hello", 3, "爱琴海").updateAndReturnGeneratedKey("id").apply()
    }
    id
  }

● 使用事物插入数据

  def txInsert(): Int = {
    val result = DB.localTx {
      implicit session =>
        SQL("insert into emp (name,age,address) values(?,?,?)").bind("Jack", 6, "海外").update().apply()
    }
    result
  }

● 更新数据

  def update(): Int = {
    val id = 5
    val i: Int = DB.localTx {
      implicit session =>
        SQL("update emp set name = ? where id = ?").bind("rose", id).update().apply()
    }
    i
  }

● 自定义数据库连接名 DBs.setup('ws) 加载db.ws.*配置，读取方式如下

  def dfSelectAll(): List[Emp] = {
    val emps: List[Emp] = NamedDB('ws).readOnly {
      implicit session =>
        SQL("select * from emp").map(rs => Emp(rs.int("id"), rs.string("name"), rs.int("age"), rs.string("address"))).list().apply()
    }
    emps
  }