如何使用 Scala 对关系数据库链接表进行建模? [英] How do I model a relational database link-table with Scala?

问题描述

我需要在 Scala 中对对象进行多对多映射并将其保存到关系数据库中.

I need to do quite a few many to many mapping of objects in Scala and save it to a relational database.

这是一个简化问题的假例子:

Here is a fake example to simplify the problem:

假设我们要为教室和学生建模.一个教室可能有很多学生，但这些学生也可以去不同的房间.从关系数据库模型的角度来看，您将创建 3 个表，一张用于 ROOM，一张用于 STUDENT，一张用于链接学生和房间 ROOM_STUDENT.

Let's say we want to model lecture rooms and students. A lecture room may have many students, but those students can also go to different rooms. From a relational database model point of view, you would create 3 tables, one for ROOM, one for STUDENT and one to link students and rooms, ROOM_STUDENT.

假设表格如下所示:

ROOM
-----
id
subject

STUDENT
-------
id
name

ROOM_STUDENT
-------------
room_id
student_id

如果我想使用 Scala 来访问(并可能创建)这样的数据库，我将如何使用 ScalaQuery 或 SLICK 来实现，或者是否有更好的 Scala 解决方案?我应该以某种方式使用案例类，还是以某种方式使用简单的旧 SQL?

If I want to use Scala to access (and maybe create) such a database, how will one do this with, say, ScalaQuery or SLICK, or is there a better Scala solution? Should I use case classes with this in some way, or just plain old SQL in some way or another?

我基本上希望在这里有两个提示，1. 在 Scala 中展示 STUDENT 和 ROOM 的好方法，2. 针对此问题的关系数据库持久性的一些想法.

I'm basically hoping for two hints here, 1. a good way to present the STUDENT and ROOM in Scala, 2. some ideas towards relational database persistence for this problem.

推荐答案

我个人更喜欢 Lift 的 Mapper 库用于此目的，并且偶尔会在 Lift Web 应用程序的上下文之外使用它.以下是一个完整的工作示例，您可以从 sbt 运行，例如，将以下内容作为您的 build.sbt:

I personally prefer Lift's Mapper library for this, and have occasionally used it outside of the context of a Lift web application. The following is a complete working example, which you can run from sbt for example with the following as your build.sbt:

libraryDependencies ++= Seq(
  "net.liftweb" %% "lift-mapper" % "2.4" % "compile->default",
  "com.h2database" % "h2" % "1.2.127"
)

首先是模型:

import net.liftweb.common._, net.liftweb.mapper._

object Student extends Student with LongKeyedMetaMapper[Student]
class Student extends LongKeyedMapper[Student] with IdPK with ManyToMany {
  def getSingleton = Student
  object name extends MappedString(this, 40)
  object rooms extends MappedManyToMany(
    StudentRoom, StudentRoom.student, StudentRoom.room, Room
  )
}

object Room extends Room with LongKeyedMetaMapper[Room]
class Room extends LongKeyedMapper[Room] with IdPK with ManyToMany {
  def getSingleton = Room
  object subject extends MappedString(this, 40)
  object students extends MappedManyToMany(
    StudentRoom, StudentRoom.room, StudentRoom.student, Student
  )
}

object StudentRoom extends StudentRoom with LongKeyedMetaMapper[StudentRoom] {
  override def dbIndexes = Index(student, room) :: super.dbIndexes
}

class StudentRoom extends LongKeyedMapper[StudentRoom] with IdPK {
  def getSingleton = StudentRoom
  object student extends MappedLongForeignKey(this, Student)
  object room extends MappedLongForeignKey(this, Room)
}

以及一些数据库设置:

DB.defineConnectionManager(
  DefaultConnectionIdentifier,
  new StandardDBVendor("org.h2.Driver", "jdbc:h2:mem:example", Empty, Empty)
)

Schemifier.schemify(true, Schemifier.infoF _, Student, Room, StudentRoom)

还有一些数据:

val m = Student.create.name("Mary"); m.save
val j = Student.create.name("John"); j.save
val physics = Room.create.subject("Physics"); physics.save
StudentRoom.create.student(m).room(physics).save
StudentRoom.create.student(j).room(physics).save

我们准备好了:

scala> Room.findAll(By(Room.subject, "Physics")).flatMap(_.students)
res7: List[Student] = List(Student={name=Mary,id=2}, Student={name=John,id=3})

这篇关于如何使用 Scala 对关系数据库链接表进行建模?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！