yNiBWTpf

1// 1. Let's create a new table with Play Evolutions (https://pastebin.com/5qhdsZp2)
2// 5.sql:
3# students schema, building table
4
5# --- !Ups
6CREATE TABLE `building` (
7	`building_id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'primary key',
8	`number` TINYINT(3) UNSIGNED NULL COMMENT 'building number, might be NULL for upcoming buildings',
9	`name` VARCHAR(128) NOT NULL COMMENT 'building name',
10	PRIMARY KEY (`building_id`)
11) COMMENT 'table to store University Campus buildings' COLLATE 'utf8_general_ci' ENGINE InnoDB;
12
13INSERT INTO `building` (`number`, `name`) VALUES
14  (1, 'Main campus'),
15  (2, 'Math campus'),
16  (3, 'Shujaa campus'),
17  (NULL, 'Scotland Yard');
18
19# --- !Downs
20DROP TABLE IF EXISTS `building`;
21
22
23
24'// BuildingDao.scala:
25package dao
26
27import javax.inject.Inject
28import scala.concurrent.Future
29
30import play.api.db.slick.{DatabaseConfigProvider, HasDatabaseConfigProvider}
31import play.db.NamedDatabase
32import slick.jdbc.JdbcProfile
33import slick.lifted.ProvenShape
34
35class BuildingDao @Inject()(
36    @NamedDatabase("students") val dbConfigProvider: DatabaseConfigProvider
37  ) extends HasDatabaseConfigProvider[JdbcProfile] {
38
39  import profile.api._
40
41  case class Building(buildingId: Option[Long], number: Option[Int], name: String)
42
43  private class BuildingTable(tag: Tag) extends Table[Building](tag, "building") {
44    def buildingId: Rep[Long] = column[Long]("building_id", O.PrimaryKey, O.AutoInc)
45    def number: Rep[Option[Int]] = column[Option[Int]]("number")
46    def name: Rep[String] = column[String]("name")
47
48    override def * : ProvenShape[Building] = (buildingId.?, number, name) <> (Building.tupled, Building.unapply)
49  }
50
51  private lazy val table = TableQuery[BuildingTable]
52
53  def findByNumber(number: Int): Future[Option[Building]] = {
54    val query = table.filter(_.number === Option(number)).result
55    println(query.statements.mkString)
56    db.run(query.headOption)
57  }
58}
59
60
61
62// TestController.scala:
63package controllers
64
65import javax.inject.Inject
66
67import scala.concurrent.ExecutionContext
68
69import dao.BuildingDao
70import play.api.mvc._
71
72class TestController @Inject()(
73    buildingDao: BuildingDao,
74    cc: ControllerComponents
75  )(implicit ec: ExecutionContext) extends AbstractController(cc) {
76
77  // add to routes: GET    /building/:buildingNumber    controllers.TestController.getBuilding(buildingNumber: Long)
78  def getBuilding(number: Int): Action[AnyContent] = Action.async {
79    buildingDao.findByNumber(number) map {
80      case Some(building) => Ok(s"Result: $building")
81      case None => NotFound(s"Building $number not found")
82    }
83  }
84}
85
86
87
88// Let's test via browser:
89// http://localhost:9000/building/1
90// select `building_id`, `number`, `name` from `building` where `number` = 1
91// Result: Building(Some(1),Some(1),Main campus)
92
93// http://localhost:9000/building/2
94// select `building_id`, `number`, `name` from `building` where `number` = 2
95// Result: Building(Some(2),Some(2),Math campus)
96
97// http://localhost:9000/building/3
98// select `building_id`, `number`, `name` from `building` where `number` = 3
99// Result: Building(Some(3),Some(3),Shujaa campus)
100
101// http://localhost:9000/building/4
102// select `building_id`, `number`, `name` from `building` where `number` = 4
103// Building 4 not found
104
105
106
107// 2. Now let's check Some instead of Option:
108val query = table.filter(_.number === Some(number)).result
109
110// Output: 
111// type mismatch;
112//  found   : Some[Int]
113//  required: slick.lifted.Rep[?]
114 
115 
116 
117// 3. And now let's remove it at all!
118val query = table.filter(_.number === number).result
119
120// Output:
121// http://localhost:9000/building/1
122// select `building_id`, `number`, `name` from `building` where `number` = 1
123// Result: Building(Some(1),Some(1),Main campus)
124
125// http://localhost:9000/building/2
126// select `building_id`, `number`, `name` from `building` where `number` = 2
127// Result: Building(Some(2),Some(2),Math campus)
128
129// http://localhost:9000/building/3
130// select `building_id`, `number`, `name` from `building` where `number` = 3
131// Result: Building(Some(3),Some(3),Shujaa campus)
132
133// http://localhost:9000/building/4
134// select `building_id`, `number`, `name` from `building` where `number` = 4
135// Building 4 not found
136
137
138
139// As you can see, you can omit Option(number)