slick与mysql

最新推荐文章于 2022-07-12 15:53:09 发布

杨过悔

最新推荐文章于 2022-07-12 15:53:09 发布

阅读量2k

点赞数

CC 4.0 BY-SA版权

分类专栏： slick知识文章标签： app scala 数据 scheme jdbc

本文链接：https://blog.youkuaiyun.com/u013571243/article/details/46581199

slick知识专栏收录该内容

8 篇文章

订阅专栏

这篇博客主要介绍了如何在Scala应用中使用Slick库来操作MySQL数据库，包括配置application.conf，定义数据表scheme，并特别提醒在MySQL中不能使用text作为主键，这个问题在从H2数据库迁移时需要注意。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

application.conf

mysql = {
  url = "jdbc:mysql://localhost:3306/scala"
  driver = com.mysql.jdbc.Driver
  user = "root"
  password = "111111"
  connectionPool = disabled
  keepAliveConnection = true
}

scheme定义

import slick.driver.MySQLDriver.api._

class Suppliers(tag: Tag) extends Table[(Int, String, String, String, String, String)](tag, "SUPPLIERS") {
  def id = column[Int]("SUP_ID", O.PrimaryKey)

  def name = column[String]("SUP_NAME")

  def street = column[String]("STREET")

  def city = column[String]("CITY")

  def state = column[String]("STATE")

  def zip = column[String]("ZIP")

  def * = (id, name, street, city, state, zip)
}

class Coffees(tag: Tag) extends Table[(Int,String,Int, Double, Int, Int)](tag, "COFFEES") {

  def id = column[Int]("COF_ID", O.PrimaryKey)

  def name = column[String]("COF_NAME")

  def supID = column[Int]("SUP_ID")

  def price = column[Double]("PRICE")

  def sales = column[Int]("SALES")

  def total = column[Int]("TOTAL")

  def * = (id,name, supID, price, sales, total)

  def supplier = foreignKey("SUP_FK", supID, TableQuery[Suppliers])(_.id)
}

注意在mysql中不支持text为主键,我的例子是根据doc的h2修改过来的,当时就遇到过这个问题

import slick.driver.MySQLDriver.api._
import slick.lifted
import scala.concurrent.ExecutionContext.Implicits.global
import scala.concurrent.duration.Duration
import scala.concurrent.{Await, Future}


object HelloSlick extends App {
  val db = Database.forConfig("mysql")
  try {

    val suppliers: TableQuery[Suppliers] = TableQuery[Suppliers]
    val coffees: TableQuery[Coffees] = TableQuery[Coffees]

    val setup = DBIO.seq(
      (suppliers.schema ++ coffees.schema).create,
      suppliers +=(101, "Acme, Inc.", "99 Market Street", "Groundsville", "CA", "95199"),
      suppliers +=(49, "Superior Coffee", "1 Party Place", "Mendocino", "CA", "95460"),
      suppliers +=(150, "The High Ground", "100 Coffee Lane", "Meadows", "CA", "93966")
    )
    val setupFuture: Future[Unit] = db.run(setup)

    val f = setupFuture.flatMap { _ =>
      val insertAction: DBIO[Option[Int]] = coffees ++= Seq(
        (12, "Colombian", 101, 7.99, 0, 0),
        (22, "French_Roast", 49, 8.99, 0, 0),
        (11, "Espresso", 150, 9.99, 0, 0),
        (1, "Colombian_Decaf", 101, 8.99, 0, 0),
        (32, "French_Roast_Decaf", 49, 9.99, 0, 0)
      )
      val insertAndPrintAction: DBIO[Unit] = insertAction.map { coffeesInsertResult =>
        // Print the number of rows inserted
        coffeesInsertResult foreach { numRows =>
          println(s"Inserted $numRows rows into the Coffees table")
        }
      }

      val allSuppliersAction: DBIO[Seq[(Int, String, String, String, String, String)]] = suppliers.result

      val combinedAction: DBIO[Seq[(Int, String, String, String, String, String)]] = insertAndPrintAction >> allSuppliersAction

      val combinedFuture: Future[Seq[(Int, String, String, String, String, String)]] = db.run(combinedAction)

      combinedFuture.map { allSuppliers =>
        allSuppliers.foreach(println)
      }
    }.flatMap{  _ =>
      //打印数据
      db.run(coffees.result).map{ all =>
        all.foreach{
          case (id, name, supID, price, sales, total) => println(" " +id + "\t" + name + "\t" + supID + "\t" + price + "\t" + sales + "\t" + total)
          case x => println(x)
        }
      }
    }.flatMap{ _ =>
      println("===========================")

      val q1 = for{c <- coffees} yield LiteralColumn("  ") ++ c.name ++ "\t" ++ c.supID.asColumnOf[String] ++
        "\t" ++ c.price.asColumnOf[String] ++ "\t" ++ c.sales.asColumnOf[String] ++ "\t" ++ c.total.asColumnOf[String]
      db.stream(q1.result).foreach{println}

    }
    Await.result(f, Duration.Inf)
  } finally db.close

}