2016/11/07

JDBC Database Persistence in Scala play 2.5

在 scala play 2.5 framework 中，要將資料儲存在 DB 中有好幾種方式，都是以整合第三方套件的方式實作，我們測試了原始的 JDBC 以及 slick 兩種，以下是 JDBC 的部分。

準備資料庫

雖然很多範例都是以 H2 memory database 展示，不過我們還是以 Maria DB 進行測試，首先要準備資料庫。

  • create mysql database: playdb
CREATE DATABASE playdb DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
  • create table: user
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`login_id` varchar(45) NOT NULL,
`password` varchar(50) NOT NULL,
`name` varchar(45) DEFAULT NULL,
`dob` bigint(20) DEFAULT NULL,
`is_active` tinyint(1) NOT NULL DEFAULT '1',
PRIMARY KEY (`id`),
UNIQUE KEY `login_id_UNIQUE` (`login_id`),
UNIQUE KEY `id_UNIQUE` (`id`)
)

BEGIN;
INSERT INTO `user` (login_id, password, name) VALUES ('test1', 'test1', 'test1'), ('test2', 'test2', 'test2');
COMMIT;

準備 scala play project

  • 以 activator 產生一個 scala play project
activator new test5 play-scala
  • 修改 build.sbt

增加 jdbc 及 "mysql" % "mysql-connector-java" % "5.1.36",

name := """test5"""

version := "1.0-SNAPSHOT"

lazy val root = (project in file(".")).enablePlugins(PlayScala)

scalaVersion := "2.11.7"

libraryDependencies ++= Seq(
  jdbc,
  cache,
  ws,
  filters,
  "mysql" % "mysql-connector-java" % "5.1.36",
  "org.scalatestplus.play" %% "scalatestplus-play" % "1.5.1" % Test
)

resolvers += "scalaz-bintray" at "http://dl.bintray.com/scalaz/releases"
  • application.conf 增加 default db 的設定

scala play 是以 HikariCP 作為 DB connection pool library，可以在 application.conf 中調整 DB 及 connection pool 設定。

play.db {
  config = "db"
  default = "default"

  prototype {
    # Sets a fixed JDBC connection pool size of 2
    hikaricp.minimumIdle = 2
    hikaricp.maximumPoolSize = 5
  }
}

db {

  default.driver=com.mysql.jdbc.Driver
  default.url="jdbc:mysql://localhost:3306/playdb?useUnicode=true&characterEncoding=utf-8"
  default.username = "root"
  default.password = "password"

  default.logSql=true
}

要確認 connection pool 有沒有作用，可以直接在 MySQL client 中，用以下的指令查看 DB 的資訊。

# 查看資料庫狀態
show status;

# 列出連線的數量

show status where `variable_name` = 'Threads_connected';

# 列出有哪些session
show processlist;

# 列出所有的session
show full processlist;

# 列出目前連線最多的 IP
mysql -uroot -p -e "show processlist"|awk '{print $3}' |awk -F: '{print $1}' |sort |uniq -c |sort -nr
  • 修改 routes

因為是測試而已，簡單將 Application 的 Action 各自對應到獨立的 URI。

GET        /                     controllers.Application.index

GET        /dbUser               controllers.Application.fetchDBUser

GET        /dbUser2              controllers.Application.fetchDBUser2

GET        /addUser/:name        controllers.Application.addUser(name:String)
  • 修改 controllers/Application.scala

注意 Application 的 constructor，因為 Play 2.5 已經使用 Inject 的方式取得資源，我們要使用的 DB 要在 constructor 中引用進來: @NamedDatabase("default") db: Database。

後面使用 db 的部分，就跟一般的 JDBC 差不多，createStament 以後，再執行 query 或是 update。

package controllers

import javax.inject.Inject

import play.api.mvc._
import play.api.db._

class Application @Inject()(@NamedDatabase("default") db: Database) extends Controller {

  def index = Action {
    Ok(views.html.main())
  }

  def fetchDBUser = Action {
    var result = "DB User:\n"
    val conn = db.getConnection()
    try {
      val rs = conn.createStatement().executeQuery("SELECT * from user")
      while (rs.next()) {
        result += rs.getString("login_id") + "\n"
      }
    } finally {
      conn.close()
    }
    Ok(result)
  }

  // without try blocks，db.withConnection 的 connection 會在結束時自動關閉
  def fetchDBUser2 = Action {
    var result = "DB User:" + "\n"
    db.withConnection { conn =>
      val rs = conn.createStatement().executeQuery("SELECT * from user")
      while (rs.next()) {
        result += rs.getString("login_id") + "\n"
      }
    }
    Ok(result)
  }

  def addUser(name: String) = Action {
    db.withTransaction { conn =>
      val rs = conn.createStatement().executeUpdate(s"insert into user (login_id, password, name) values('$name', '$name', '$name')")
    }
    Ok
  }

}

測試

> curl 'http://localhost:9000/dbUser'
DB User:
test1
test2
> curl 'http://localhost:9000/dbUser2'
DB User:
test1
test2
> curl -v 'http://localhost:9000/addUser/test3'
*   Trying ::1...
* Connected to localhost (::1) port 9000 (#0)
> GET /addUser/test4 HTTP/1.1
> Host: localhost:9000
> User-Agent: curl/7.50.1
> Accept: */*
>
< HTTP/1.1 200 OK
< X-ExampleFilter: foo
< Content-Length: 0
< Date: Thu, 11 Aug 2016 07:58:02 GMT
<
* Connection #0 to host localhost left intact

JDBC 的 logback 設定

如果要讓 log 記錄 SQL statement，就修改 logback.xml，增加這三行，但不建議在 production 環境加上這個設定

  <logger name="org.jdbcdslog.ConnectionLogger" level="OFF"  /> <!-- Won' log connections -->
  <logger name="org.jdbcdslog.StatementLogger"  level="INFO" /> <!-- Will log all statements -->
  <logger name="org.jdbcdslog.ResultSetLogger"  level="OFF"  /> <!-- Won' log result sets -->

完整的 logback.xml 內容如下

<!-- https://www.playframework.com/documentation/latest/SettingsLogger -->
<configuration>

  <conversionRule conversionWord="coloredLevel" converterClass="play.api.libs.logback.ColoredLevel" />

<!--
  <appender name="FILE" class="ch.qos.logback.core.FileAppender">
    <file>${application.home:-.}/logs/application.log</file>
    <encoder>
      <pattern>%date [%level] from %logger in %thread\n\t%message%n%xException</pattern>
    </encoder>
  </appender>
-->

<appender name="FILE"
          class="ch.qos.logback.core.rolling.RollingFileAppender">
    <append>true</append>
    <rollingPolicy
            class="ch.qos.logback.core.rolling.TimeBasedRollingPolicy">
        <param name="FileNamePattern"
               value="${application.home:-.}/logs/application.%d{yyyy-MM-dd}.log.zip">
        </param>
    </rollingPolicy>
    <encoder>
        <!-- <pattern>%d %-5p %c %L%n %m%n</pattern> -->
        <!-- <charset class="java.nio.charset.Charset">UTF-8</charset>  -->
        <pattern>%date [%level] from %logger in %thread\n\t%message%n%xException</pattern>
    </encoder>
</appender>

  <appender name="STDOUT" class="ch.qos.logback.core.ConsoleAppender">
    <encoder>
      <pattern>%coloredLevel %logger{15} in %thread\n\t%message%n%xException{10}</pattern>
    </encoder>
  </appender>

  <appender name="ASYNCFILE" class="ch.qos.logback.classic.AsyncAppender">
    <appender-ref ref="FILE" />
  </appender>

  <appender name="ASYNCSTDOUT" class="ch.qos.logback.classic.AsyncAppender">
    <appender-ref ref="STDOUT" />
  </appender>

  <logger name="play" level="INFO" />
  <logger name="application" level="DEBUG" />

  <logger name="org.jdbcdslog.ConnectionLogger" level="OFF"  /> <!-- Won' log connections -->
  <logger name="org.jdbcdslog.StatementLogger"  level="INFO" /> <!-- Will log all statements -->
  <logger name="org.jdbcdslog.ResultSetLogger"  level="OFF"  /> <!-- Won' log result sets -->

  <!-- Off these ones as they are annoying, and anyway we manage configuration ourselves -->
  <logger name="com.avaje.ebean.config.PropertyMapLoader" level="OFF" />
  <logger name="com.avaje.ebeaninternal.server.core.XmlConfigLoader" level="OFF" />
  <logger name="com.avaje.ebeaninternal.server.lib.BackgroundThread" level="OFF" />
  <logger name="com.gargoylesoftware.htmlunit.javascript" level="OFF" />

  <root level="WARN">
    <appender-ref ref="ASYNCFILE" />
    <appender-ref ref="ASYNCSTDOUT" />
  </root>

</configuration>

這樣就可以在 log 中看到 SQL statement，當然這個設定只能用在 Dev 環境，不適合用在 Production。

[info] o.j.StatementLogger in application-akka.actor.default-dispatcher-2
    java.sql.Statement.executeQuery: SELECT * from user;
[info] o.j.StatementLogger in application-akka.actor.default-dispatcher-3
    java.sql.Statement.executeUpdate: insert into user (login_id, password, name) values('test4', 'test4', 'test4');

Database Evolution

scala play 內建了 db 升降版本的功能，如果要使用，必須先準備一個全新的空白的資料庫，但先不要加上 tables。

  • build.sbt 中增加 libraryDependencies += evolutions
name := """test5"""

version := "1.0-SNAPSHOT"

lazy val root = (project in file(".")).enablePlugins(PlayScala)

scalaVersion := "2.11.7"

libraryDependencies ++= Seq(
  jdbc,
  evolutions,
  cache,
  ws,
  filters,
  "mysql" % "mysql-connector-java" % "5.1.36",
  "com.typesafe.play" %% "anorm" % "2.5.0",
  //"com.typesafe.play" %% "play-slick" % "2.0.0",
  //"com.typesafe.play" %% "play-slick-evolutions" % "2.0.0",
  "org.scalatestplus.play" %% "scalatestplus-play" % "1.5.1" % Test
)

resolvers += "scalaz-bintray" at "http://dl.bintray.com/scalaz/releases"

application.conf 增加 ply.evolutions 這個部分的設定

play.evolutions {
  # You can disable evolutions for a specific datasource if necessary
  db.default.enabled = true
  autoApply = true
  autoApplyDowns = true
}
  • 新增兩個 sql file

DB evolution 是以 sql file 的方式，進行 DB 版本升降，要注意 sql file 裡面規定一定要有 Ups 以及 Downs 這兩個部分。

/conf/evolutions/default/1.sql

# Users schema

# --- !Ups

CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`login_id` varchar(45) NOT NULL,
`password` varchar(50) NOT NULL,
`name` varchar(45) DEFAULT NULL,
`dob` bigint(20) DEFAULT NULL,
`is_active` tinyint(1) NOT NULL DEFAULT '1',
PRIMARY KEY (`id`),
UNIQUE KEY `login_id_UNIQUE` (`login_id`),
UNIQUE KEY `id_UNIQUE` (`id`)
);

INSERT INTO `user` (login_id, password, name) VALUES ('test1', 'test1', 'test1'), ('test2', 'test2', 'test2');


# --- !Downs

DROP TABLE user;

/conf/evolutions/default/2.sql

# Users schema

# --- !Ups

CREATE TABLE temp (
    id bigint(20) NOT NULL AUTO_INCREMENT,
    email varchar(255) NOT NULL,
    password varchar(255) NOT NULL,
    fullname varchar(255) NOT NULL,
    isAdmin boolean NOT NULL,
    PRIMARY KEY (id)
);

# --- !Downs

DROP TABLE temp;
  • 測試 evolution

啟動 play，如果有了第一個 DB 連線，就會進行 1.sql, 2.sql 建立兩個 tables，如果想要降版，就把 2.sql 改成 2.sql.bak，server code reload 後，有了第一個 DB 連線，就會自動降版。

mysql DB 裡面會自動產生一個 table: play_evolutions，他會記錄每一次 DB 升降版本執行的 db script。

DROP TABLE IF EXISTS `play_evolutions`;
CREATE TABLE `play_evolutions` (
  `id` int(11) NOT NULL,
  `hash` varchar(255) NOT NULL,
  `applied_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `apply_script` mediumtext,
  `revert_script` mediumtext,
  `state` varchar(255) DEFAULT NULL,
  `last_problem` mediumtext,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Play DB Evolutions

於

沒有留言:

張貼留言