· 8 years ago · Feb 06, 2018, 08:54 PM
1// 1. Let's create a new test table via Play Evolutions (https://pastebin.com/5qhdsZp2)
2// 2.sql
3# students schema, course table
4
5# --- !Ups
6
7CREATE TABLE `course` (
8 `course_id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'primary key',
9 `name` VARCHAR(64) NOT NULL COMMENT 'course name',
10 `duration` TINYINT(2) NOT NULL COMMENT 'duration, in semesters',
11 `group` ENUM('A','B','C','D') NOT NULL COMMENT 'course group',
12 `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'create time',
13 `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'update time',
14 PRIMARY KEY (`course_id`),
15 INDEX `IDX_course_name` (`name` ASC)
16) COMMENT 'course table' COLLATE 'utf8_general_ci' ENGINE InnoDB;
17
18# --- !Downs
19
20DROP TABLE IF EXISTS `course`;
21
22
23
24// CourseDao.scala
25package dao
26
27import java.sql.Timestamp
28import java.time.LocalDateTime
29import javax.inject.Inject
30
31import play.api.db.slick.{DatabaseConfigProvider, HasDatabaseConfigProvider}
32import play.db.NamedDatabase
33import slick.jdbc.JdbcProfile
34import slick.lifted.ProvenShape
35
36import scala.concurrent.Future
37
38case class Course(courseId: Option[Long] = None, name: String, duration: Int, group: Int, createdAt: Timestamp, updatedAt: Timestamp)
39
40/**
41 * Course DAO
42 * @param dbConfigProvider DB Config Provider
43 */
44class CourseDao @Inject() (
45 @NamedDatabase("students") val dbConfigProvider: DatabaseConfigProvider
46 ) extends HasDatabaseConfigProvider[JdbcProfile] {
47
48 import profile.api._
49
50 private class CourseTable(tag: Tag) extends Table[Course](tag, "course") {
51 def courseId: Rep[Option[Long]] = column[Option[Long]]("course_id", O.PrimaryKey, O.AutoInc)
52 def name: Rep[String] = column[String]("name")
53 def duration: Rep[Int] = column[Int]("duration")
54 def group: Rep[Int] = column[Int]("group")
55 def createdAt: Rep[Timestamp] = column[Timestamp]("created_at")
56 def updatedAt: Rep[Timestamp] = column[Timestamp]("updated_at")
57
58 override def * : ProvenShape[Course] =
59 (courseId, name, duration, group, createdAt, updatedAt) <> (Course.tupled, Course.unapply)
60 }
61
62 private lazy val table = TableQuery[CourseTable]
63
64 def addCourse(name: String, duration: Int, group: Int) = {
65 val now = Timestamp.valueOf(LocalDateTime.now)
66 val query = table += Course(None, name, duration, group, now, now)
67 println(query.statements.mkString)
68 db.run(query)
69 }
70}
71
72
73
74// TestController.scala:
75package controllers
76
77import javax.inject.Inject
78import scala.concurrent.{ExecutionContext, Future}
79import play.api.mvc._
80import play.api.libs.json.{JsValue, Json, Reads}
81
82import dao.{CourseDao}
83
84class TestController @Inject()(
85 courseDao: CourseDao, cc: ControllerComponents)(implicit ec: ExecutionContext) extends AbstractController(cc) {
86
87 case class CourseRequest(duration: Int, group: Int)
88 implicit val courseReads: Reads[CourseRequest] = Json.reads[CourseRequest]
89
90 // add to routes file: POST /courses/add/:name controllers.TestController.addNewCourse(name)
91 def addNewCourse(name: String): Action[JsValue] = Action(parse.json).async { request =>
92 request.body.asOpt[CourseRequest] match {
93 case Some(cReq) => courseDao.addCourse(name, cReq.duration, cReq.group) map {res => Ok(s"Result: $res")}
94 case None => Future.successful(BadRequest("Cannot parse json"))
95 }
96 }
97}
98
99
100
101// 2. Check it out! Use curl, Postman or another tool you wish
102http://localhost:9000/courses/add/math
103Cannot parse json
104
105
106
107// 3. Oh, man! We need to specify json data:
108http://localhost:9000/courses/add/math with JSON: {"duration": 3, "group": 1}
109insert into `course` (`name`,`duration`,`group`,`created_at`,`updated_at`) values (?,?,?,?,?)
110Result: 1 // 1 is how many rows affected
111DB:
112"1" "math" "3" "A" "2018-02-06 23:07:25" "2018-02-06 23:07:25"
113
114
115
116// 4. Let's add "returning" feature, so that we can return new ID generated by MySQL
117def addCourse(name: String, duration: Int, group: Int): Future[Option[Long]] = {
118 val now = Timestamp.valueOf(LocalDateTime.now)
119 val query = (table returning table.map(_.courseId)) += Course(None, name, duration, group, now, now)
120 println(query.statements.mkString)
121 db.run(query)
122}
123
124http://localhost:9000/courses/add/history with JSON: {"duration": 3, "group": 1}
125insert into `course` (`name`,`duration`,`group`,`created_at`,`updated_at`) values (?,?,?,?,?)
126Result: Some(2)
127DB:
128"1" "math" "3" "A" "2018-02-06 23:07:25" "2018-02-06 23:07:25"
129"2" "history" "3" "A" "2018-02-06 23:08:40" "2018-02-06 23:08:40"
130
131
132
133// 5. Next feature: we can generate timestamps on the DB side; just specify "O.AutoInc" to
134// skip an argument in INSERT code; otherwise use "forceInsert" instead of "+="
135case class Course(
136 courseId: Option[Long] = None,
137 name: String,
138 duration: Int,
139 group: Int,
140 createdAt: Option[Timestamp] = None,
141 updatedAt: Option[Timestamp] = None
142)
143
144def createdAt: Rep[Option[Timestamp]] = column[Option[Timestamp]]("created_at", O.AutoInc)
145def updatedAt: Rep[Option[Timestamp]] = column[Option[Timestamp]]("updated_at", O.AutoInc)
146
147def addCourse(name: String, duration: Int, group: Int): Future[Option[Long]] = {
148 val query = (table returning table.map(_.courseId)) += Course(None, name, duration, group)
149 println(query.statements.mkString)
150 db.run(query)
151}
152
153http://localhost:9000/courses/add/geography with JSON {"duration": 1, "group": 2}
154insert into `course` (`name`,`duration`,`group`) values (?,?,?)
155Result: Some(3)
156DB:
157"1" "math" "3" "A" "2018-02-06 23:07:25" "2018-02-06 23:07:25"
158"2" "history" "3" "A" "2018-02-06 23:08:40" "2018-02-06 23:08:40"
159"3" "geography" "1" "B" "2018-02-06 23:24:57" "2018-02-06 23:24:57"
160
161
162
163// 6. Next feature: we can use String for Enums instead of Int:
164case class Course(
165 courseId: Option[Long] = None,
166 name: String,
167 duration: Int,
168 group: String,
169 createdAt: Option[Timestamp] = None,
170 updatedAt: Option[Timestamp] = None
171)
172
173def group: Rep[String] = column[String]("group")
174
175def addCourse(name: String, duration: Int, group: String): Future[Option[Long]] = {
176
177// in controller:
178case class CourseRequest(duration: Int, group: String)
179
180http://localhost:9000/courses/add/biology with JSON: {"duration": 1, "group": 2}
181Cannot parse json
182
183
184
185// 6.1. Yeah! Specify "C" group instead of 2:
186http://localhost:9000/courses/add/biology with JSON: {"duration": 1, "group": "C"}
187insert into `course` (`name`,`duration`,`group`) values (?,?,?)
188Result: Some(4)
189DB:
190"1" "math" "3" "A" "2018-02-06 23:07:25" "2018-02-06 23:07:25"
191"2" "history" "3" "A" "2018-02-06 23:08:40" "2018-02-06 23:08:40"
192"3" "geography" "1" "B" "2018-02-06 23:24:57" "2018-02-06 23:24:57"
193"4" "biology" "1" "C" "2018-02-06 23:31:26" "2018-02-06 23:31:26"
194
195
196
197
198// 7. Next feature: you can use "id.?" to get rid of Option[Long] for your id.
199// it allows to return Future[Long] instead of Future[Option[Long]]
200def courseId: Rep[Long] = column[Long]("course_id", O.PrimaryKey, O.AutoInc)
201
202override def * : ProvenShape[Course] =
203 (courseId.?, name, duration, group, createdAt, updatedAt) <> (Course.tupled, Course.unapply)
204
205def addCourse(name: String, duration: Int, group: String): Future[Long]
206
207http://localhost:9000/courses/add/russian with JSON: {"duration": 2, "group": "C"}
208insert into `course` (`name`,`duration`,`group`) values (?,?,?)
209Result: 5
210DB:
211"1" "math" "3" "A" "2018-02-06 23:07:25" "2018-02-06 23:07:25"
212"2" "history" "3" "A" "2018-02-06 23:08:40" "2018-02-06 23:08:40"
213"3" "geography" "1" "B" "2018-02-06 23:24:57" "2018-02-06 23:24:57"
214"4" "biology" "1" "C" "2018-02-06 23:31:26" "2018-02-06 23:31:26"
215"5" "russian" "2" "C" "2018-02-06 23:34:10" "2018-02-06 23:34:10"
216
217
218
219// 8. Let's create incorrect group (e.g. "E"):
220http://localhost:9000/courses/add/physics with JSON: {"duration": 1, "group": "E"}
221insert into `course` (`name`,`duration`,`group`) values (?,?,?)
222[SQLException: Data truncated for column 'group' at row 1]
223DB:
224"1" "math" "3" "A" "2018-02-06 23:07:25" "2018-02-06 23:07:25"
225"2" "history" "3" "A" "2018-02-06 23:08:40" "2018-02-06 23:08:40"
226"3" "geography" "1" "B" "2018-02-06 23:24:57" "2018-02-06 23:24:57"
227"4" "biology" "1" "C" "2018-02-06 23:31:26" "2018-02-06 23:31:26"
228"5" "russian" "2" "C" "2018-02-06 23:34:10" "2018-02-06 23:34:10"
229
230
231
232// 8.1. So we can check it directly in App and not pass it to DB:
233case class Course(
234 courseId: Option[Long] = None,
235 name: String,
236 duration: Int,
237 group: String,
238 createdAt: Option[Timestamp] = None,
239 updatedAt: Option[Timestamp] = None
240) {require(Set("A", "B", "C", "D") contains group)}
241
242http://localhost:9000/courses/add/physics with JSON: {"duration": 1, "group": "E"}
243[IllegalArgumentException: requirement failed]
244In D:\Workspace\play-scala-starter-example\app\dao\CourseDao.scala:20
245DB:
246"1" "math" "3" "A" "2018-02-06 23:07:25" "2018-02-06 23:07:25"
247"2" "history" "3" "A" "2018-02-06 23:08:40" "2018-02-06 23:08:40"
248"3" "geography" "1" "B" "2018-02-06 23:24:57" "2018-02-06 23:24:57"
249"4" "biology" "1" "C" "2018-02-06 23:31:26" "2018-02-06 23:31:26"
250"5" "russian" "2" "C" "2018-02-06 23:34:10" "2018-02-06 23:34:10"