· 8 years ago · Feb 08, 2018, 09:40 PM
1// 1.1. Let's add a new table via Play Evolutions (https://pastebin.com/5qhdsZp2)
2// 4.sql:
3# students schema, stipend table
4
5# --- !Ups
6
7# Attention! if you don't have `students` table, please remove CONSTRAINT clause
8CREATE TABLE `stipend` (
9 `stipend_id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'primary key',
10 `student_id` BIGINT(20) UNSIGNED NOT NULL COMMENT 'reference to a student table',
11 `semester` TINYINT(5) UNSIGNED NOT NULL COMMENT 'semester number',
12 `value` INT(11) UNSIGNED NOT NULL COMMENT 'payment, unique for student/semester pair',
13 PRIMARY KEY (`stipend_id`),
14 UNIQUE INDEX `IDX_stipend_student_semester` (`student_id` ASC, `semester` ASC),
15 CONSTRAINT `FK_stipend_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`student_id`) ON UPDATE CASCADE ON DELETE CASCADE
16) COMMENT 'table to store students stipend' COLLATE 'utf8_general_ci' ENGINE InnoDB;
17
18# --- !Downs
19
20DROP TABLE IF EXISTS `stipend`;
21
22
23'
24// 1.2. Also create DAO
25import javax.inject.Inject
26
27import play.api.db.slick.{DatabaseConfigProvider, HasDatabaseConfigProvider}
28import play.db.NamedDatabase
29import slick.jdbc.JdbcProfile
30import slick.lifted.ProvenShape
31
32class StipendDao @Inject()(
33 @NamedDatabase("students") val dbConfigProvider: DatabaseConfigProvider
34 ) extends HasDatabaseConfigProvider[JdbcProfile] {
35
36 import profile.api._
37
38 case class Stipend(misdemeanorId: Option[Long], studentId: Long, semester: Int, count: Int)
39
40 private class StipendTable(tag: Tag) extends Table[Stipend](tag, "stipend") {
41 def stipendId: Rep[Long] = column[Long]("stipend_id", O.PrimaryKey, O.AutoInc)
42 def studentId: Rep[Long] = column[Long]("student_id")
43 def semester: Rep[Int] = column[Int]("semester")
44 def value: Rep[Int] = column[Int]("value")
45
46 override def * : ProvenShape[Stipend] =
47 (stipendId.?, studentId, semester, value) <> (Stipend.tupled, Stipend.unapply)
48 }
49
50 private lazy val table = TableQuery[StipendTable]
51
52 def insert(studentId: Long, semester: Int, stipendValue: Int) = {
53 val query = table insertOrUpdate Stipend(None, studentId, semester, stipendValue)
54 println(query.statements.mkString)
55 db.run(query)
56 }
57}
58
59
60
61// 1.3. and controller:
62package controllers
63
64import javax.inject.Inject
65
66import scala.concurrent.{ExecutionContext, Future}
67import scala.util.Try
68import scala.xml.NodeSeq
69
70import play.api.libs.json.{JsValue, Json, Reads}
71import play.api.mvc._
72
73import dao.StipendDao
74
75
76class TestController @Inject()(
77 stipendDao: StipendDao, cc: ControllerComponents)(implicit ec: ExecutionContext) extends AbstractController(cc) {
78
79 // add to routes file: PUT /students/:studentId/semester/:semester/stipend controllers.TestController.setStipend(studentId, semester)
80 def setStipend(studentId: Long, semester: Int): Action[NodeSeq] = Action(parse.xml).async { request =>
81 val valueOpt = for {
82 node <- (request.body \\ "value").headOption
83 text <- node.map(_.text).headOption
84 value <- Try(text.toInt).toOption
85 } yield value
86
87 valueOpt match {
88 case Some(value) => stipendDao.insert(studentId, semester, value) map {t => Ok(s"Result: $t")}
89 case None => Future.successful(BadRequest("Cannot parse XML"))
90 }
91 }
92}
93
94
95
96// 2. If you have a student table - insert a row into it
97INSERT INTO `students`.`student` (`name`, `birthday`, `sex`, `admission_year`, `faculty_id`) VALUES ('Tommy', '2000-01-01', 'Male', 2017, 1);
98
99
100
101// 3.1. Let's test it! We add stipend for the first semester. Use curl, Postman or another tool you wish
102http://localhost:9000/students/1/semester/1/stipend
103
104// Output: For request 'PUT /students/1/semester/1/stipend' [Expecting xml body]
105
106
107
108// 3.2. Error! Don't forget to specify application/xml header and XML-body:
109http://localhost:9000/students/1/semester/1/stipend with XML: <value>1250</value>
110
111// Output: Result: 1
112// DB:
113// "stipend_id" "student_id" "semester" "value"
114// "1" "1" "1" "1250"
115
116
117
118// 4. Also add stipend for the second semester:
119http://localhost:9000/students/1/semester/2/stipend with XML: <value>1750</value>
120
121// Output: Result: 1
122// DB:
123// "stipend_id" "student_id" "semester" "value"
124// "1" "1" "1" "1250"
125// "2" "1" "2" "1750"
126
127
128
129// 5. And finally let's cheat! Call endpoints:
130http://localhost:9000/students/1/semester/1/stipend with XML: <value>5000</value>
131http://localhost:9000/students/1/semester/2/stipend with XML: <value>6000</value>
132
133// Output:
134// Result: 1
135// Result: 1
136// DB:
137// "stipend_id" "student_id" "semester" "value"
138// "1" "1" "1" "5000"
139// "2" "1" "2" "6000"