Datenbankintegration mit Slick/Doobie

Warum Datenbanken integrieren?

Slick und Doobie: Datenbankwerkzeuge für Scala

Slick in Play einrichten

import play.sbt.PlayImport._

libraryDependencies ++= Seq(
  jdbc,
  ws,
  "com.typesafe.play" %% "play-slick" % "5.0.0",
  "com.typesafe.play" %% "play-slick-evolutions" % "5.0.0",
  "com.h2database" % "h2" % "2.2.224"
)

Datenbankkonfiguration

slick.dbs.default.profile="slick.jdbc.H2Profile$"

play.db { 
  prototype.slick = {
    db.driver="org.h2.Driver"
    db.url="jdbc:h2:mem:play;DB_CLOSE_DELAY=-1"
    db.username="sa"
    db.password=""
  }

  # Apply the prototype to our default DB
  default = ${play.db.prototype.slick}
}

Ihr Modell definieren (Slick-Tabelle)

import slick.jdbc.H2Profile.api._

// Define a case class for our data model
case class User(id: Option[Long], name: String, email: String)

// Define the table mapping
class Users(tag: Tag) extends Table[User](tag, "users") {
  def id = column[Option[Long]]("id", O.PrimaryKey, O.AutoInc)
  def name = column[String]("name")
  def email = column[String]("email")
  
  // Every table needs a * projection with the default mapping
  def * = (id, name, email) <> (User.tupled, User.unapply)
}

// TableQuery for accessing the Users table
val users = TableQuery[Users]

Code: Tabelle und Schema erstellen

import slick.jdbc.H2Profile.api._
import scala.concurrent.ExecutionContext.Implicits.global
import scala.concurrent.Await
import scala.concurrent.duration._

case class User(id: Option[Long], name: String, email: String)

class Users(tag: Tag) extends Table[User](tag, "users") {
  def id = column[Option[Long]]("id", O.PrimaryKey, O.AutoInc)
  def name = column[String]("name")
  def email = column[String]("email")
  def * = (id, name, email) <> (User.tupled, User.unapply)
}

object SlickDemo {
  val users = TableQuery[Users]

  def main(args: Array[String]): Unit = {
    val db = Database.forURL("jdbc:h2:mem:test1;DB_CLOSE_DELAY=-1", driver = "org.h2.Driver")
    try {
      val setup = DBIO.seq(
        users.schema.create
      )
      val future = db.run(setup)
      Await.result(future, 2.seconds)
      println("Users table created successfully!")
    } finally {
      db.close()
    }
  }
}

DAO-Muster für Operationen

import slick.jdbc.H2Profile.api._
import scala.concurrent.Future

class UserDAO(db: Database) {
  import SlickDemo.users // Assuming users TableQuery is in SlickDemo object

  def insert(user: User): Future[User] = {
    db.run(users returning users.map(_.id) into ((user, id) => user.copy(id = id)) += user)
  }

  def findAll(): Future[Seq[User]] = {
    db.run(users.result)
  }

  def findById(id: Long): Future[Option[User]] = {
    db.run(users.filter(_.id === id).result.headOption)
  }
}

Code: Daten einfügen und abfragen

import slick.jdbc.H2Profile.api._
import scala.concurrent.ExecutionContext.Implicits.global
import scala.concurrent.Await
import scala.concurrent.duration._

case class User(id: Option[Long], name: String, email: String)

class Users(tag: Tag) extends Table[User](tag, "users") {
  def id = column[Option[Long]]("id", O.PrimaryKey, O.AutoInc)
  def name = column[String]("name")
  def email = column[String]("email")
  def * = (id, name, email) <> (User.tupled, User.unapply)
}

class UserDAO(db: Database) {
  val users = TableQuery[Users]
  def insert(user: User): Future[User] = 
    db.run(users returning users.map(_.id) into ((u, id) => u.copy(id = id)) += user)
  def findAll(): Future[Seq[User]] = 
    db.run(users.result)
}

object SlickOperations {
  def main(args: Array[String]): Unit = {
    val db = Database.forURL("jdbc:h2:mem:test2;DB_CLOSE_DELAY=-1", driver = "org.h2.Driver")
    val userDAO = new UserDAO(db)
    try {
      Await.result(db.run(userDAO.users.schema.create), 2.seconds)
      println("Table created.")

      val future = for {
        user1 <- userDAO.insert(User(None, "Alice", "alice@example.com"))
        user2 <- userDAO.insert(User(None, "Bob", "bob@example.com"))
        allUsers <- userDAO.findAll()
      } yield allUsers

      val result = Await.result(future, 5.seconds)
      println(s"All users: $result")

    } finally {
      db.close()
    }
  }
}

DAO in Play integrieren

import play.api.mvc._
import play.api.libs.json._
import scala.concurrent.ExecutionContext
import javax.inject._

// Assuming User and UserDAO are defined as before

@Singleton
class UserController @Inject()(cc: ControllerComponents, userDAO: UserDAO)
                              (implicit ec: ExecutionContext) extends AbstractController(cc) {

  def listUsers = Action.async {
    userDAO.findAll().map { users =>
      Ok(Json.toJson(users))
    }
  }

  def createUser = Action.async(parse.json) {
    request =>
      request.body.validate[User].map { user =>
        userDAO.insert(user).map(u => Created(Json.toJson(u)))
      }.getOrElse(Future.successful(BadRequest("Invalid User Data")))
  }
}

Kurzer Check: Slick-Tabelle

Zusammenfassung: Datenbankintegration