Ch9 : Play MyBatis & MariaDB

[Play MyBatis & MariaDB]
這個章節我們要學習安裝資料庫MariaDB，以及新增MyBaits資料庫工具到Play上，協助我們跟MariaDB做各種互動。
而我採用是MySQL的另外一個分支MariaDB，大多情況下，SQL語法是跟MySQL相容，而且是免費開源(Free and open source)的資料庫，也是我採用這套資料庫的原因。

那我們就開始吧!!

專案參考 : https://github.com/loveu8/playMyBtaisMariaDB/tree/Ch9

From the beginning , we are nothing. But it make everything is possible to achieve. Just do it.

[MySql]
我們會先在Windows安裝MariaDB資料庫，而我使用的是zip而不是mis安裝檔。要依序手動建立相關服務，也熟悉一下怎樣簡單建立一個資料庫服務。
(PS: 若是使用MacOSX系統，最下方有Bonus For MacOSX的安裝指引，可以參閱安裝)

Step 1 : 我們採用的版本是MariaDB 10.1.16 Stable的版本，我使用的版本是64bit的版本，請到以下網址抓取zip壓縮檔案。
網址:https://downloads.mariadb.org/

Step 2 : 當下載回來後。為了教學方便，我們直接在原本的C:/playFramework下，新增對應的目錄，並把檔案放置進來後，再解壓縮maria-10.1.16-winx64.zip檔案。
EX PATH : C:\playFramework\database

Step 3 : 下載回來之後，我們要複製一份my-medium.ini檔案，並且更名成my.ini，來去新增MySql啟動時，需要的一些設定，設定可以參考下方的設定檔。有特別寫中文註解的部份要特別注意
my.ini

[client]
#設定使用者的預設字元utf8
default-character-set=utf8
port        = 3306
socket        = /tmp/mysql.sock

# The MariaDB server
[mysqld]
port        = 3306
socket        = /tmp/mysql.sock
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
# 設定MariaDB安裝路徑
basedir = C:\\playFramework\\database\\mariadb-10.1.16-winx64

# 設定MariaDB的資料庫檔所存放的位置
datadir = C:\\playFramework\\database\\mariadb-10.1.16-winx64\\data

# 設定伺服器的預設字元utf8
character-set-server = utf8
init-connect='SET NAMES utf8'
collation-server = utf8_unicode_ci

log-bin=mysql-bin

binlog_format=mixed

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id    = 1

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# 設定 MySQL 用戶端的預設字元集，這裡設的是 utf8
default-character-set = utf8

[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

Step 4 : 因為我們採用的是no install方式，所以我們要自己安裝MySql的服務，讓作業系統知道有一個服務是MySql的DataBase。我們會再cmd模式下，下指令切換到MariaDB解壓縮的目錄，執行安裝MySql服務。

#切換目錄到安裝資料夾
cd C:\playFramework\database\mariadb-10.1.16-winx64\bin

#執行安裝指令
C:\playFramework\database\mariadb-10.1.16-winx64\bin\mysqld –install

#系統會吐出這個訊息
Service successfuly install.

Step 5 : 這時候我們已經成功安裝MySql，接下來我們要把資料庫啟動起來。
先開啟一個cmd輸入以下指令，這樣我們的MariaDB就啟動了。

cd C:\playFramework\database\mariadb-10.1.16-winx64\bin
mysqld --console

Step 6 : 設定root的帳號。再開啟一個cmd畫面來設定。我們的帳號是root，密碼是1234。要特別注意就是，因為方便介紹，root設定密碼非常簡潔，請不要在線上運作環境設定這種密碼，以免造成危險。

cd C:\playFramework\database\mariadb-10.1.16-winx64\bin
mysqladmin -u root password "1234"

Step 7 : 接下來我們使用一套免費的資料庫工具Heidisql，若有其他喜愛的工具也可以拿來使用，我們要連線到資料庫，看使用能正常運行。
下載網址 : http://www.heidisql.com/download.php?download=portable
以下是設定畫面，IP是本機127.0.0.1，預設Port 3306。帳號:root，密碼:1234。

登入之後畫面。

Step 8 : 請依下圖順序，我們來創建一個Play資料庫。
這樣我們就可以成功建立一個Play的資料庫了。

Step 9 : 點選到左邊的Play資料庫後，依序執行以下基本SQL指令，看是否能順利執行。若沒有問題，我們的資料庫就算是測試建立成功了。

-- 1.建立表單
CREATE TABLE firstTable( seq int ,Message varchar(255));

-- 2.查詢
SELECT * FROM firstTable;

-- 3.新增
INSERT firstTable VALUES ('1','Hello World');
INSERT firstTable VALUES ('2','Play World');

-- 4.新增後查詢
SELECT * FROM firstTable;

-- 5.刪除
DELETE FROM firstTable WHERE seq = '1';

-- 6.刪除後查詢
SELECT * FROM firstTable;

-- 7.更新
UPDATE firstTable SET seq = '1' WHERE seq = '2';

-- 7.更新後查詢
SELECT * FROM firstTable;

-- 8.丟棄
DROP TABLE firstTable;

執行片段。

Step 10 : 為了之後方便我們啟動MySQL，我們可以寫Windows的bat檔案，寫好之後可以直接點擊啟動或關閉資料庫。
mariadb-run.bat
下面defaults意思是，指定要使用我們寫好的my.ini設定檔來啟動資料庫。

C:\playFramework\database\mariadb-10.1.16-winx64\bin\mysqld --defaults-file=C:\playFramework\database\mariadb-10.1.16-winx64\my.ini --console

mariadb-shutdown.bat
關閉資料庫，這個需要輸入root密碼，才能去關閉。

C:\playFramework\database\mariadb-10.1.16-winx64\bin\mysqladmin -u root -p shutdown

以上我們就成功的在Windows建立MariaDB資料庫，而Windows重新啟動時會自動啟用MySQL服務，當我們執行mariadb-run.bat時會失敗，需要先運行mariadb-shutdown.bat關閉後，再重啟mariadb-run.bat即可。若不喜歡Windows每次重開機自動啟動MySQL服務的話，可以從服務裡，把MySQL調成手動開啟即可。

[MyBatis]
這個部份因為(Play + MyBatis + MariaDB)相互搭配，才能達成我們想要連結資料庫的功能。程式碼主要是參閱innoq/play24-guice-mybatis後，實作出來的。在實作過程中，必須要特別注意，是否有哪個細節錯誤導致Play無法跟MariaDB連線。以下依照順序來實作吧!

Step 0 : 我們在設定Play之前，我們先去DB建立一個User的Table。來給Play+MyBaits呼叫與查詢。

-- 1.建立表單
CREATE TABLE Users( id long , name varchar(50));

-- 2.新增兩筆資料
INSERT Users VALUES ('0001','Tom');
INSERT Users VALUES ('0002','Jerry');

-- 3. 查詢
SELECT * FROM Users;

Step 1 : 設定我們的build.sbt檔案。我們要把MyBatis與MariaDB需要相關的jar與宣告，寫到build.sbt，協助我們Play可以利用MyBaits來跟MariaDB資料庫連線。完成後，輸入指令下載我們需要的jar檔與編譯專案。
指令 : activator clean update compile eclipse

name := """myFirstApp"""

version := "1.0-SNAPSHOT"

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

scalaVersion := "2.11.7"

libraryDependencies ++= Seq(
  javaJdbc,
  cache,
  javaWs,
  // 增加mybatis , google Inject 與 mariadb 需要的jar檔
  "org.mybatis" % "mybatis" % "3.4.1",        
  "org.mybatis" % "mybatis-guice" % "3.8",
  "com.google.inject.extensions" % "guice-multibindings" % "4.1.0",
  "org.mariadb.jdbc" % "mariadb-java-client" % "1.4.6"
)

// Compile the project before generating Eclipse files, so that .class files for views and routes are present
EclipseKeys.preTasks := Seq(compile in Compile)           

// Java project. Don't expect Scala IDE       
EclipseKeys.projectFlavor := EclipseProjectFlavor.Java    

// Use .class files instead of generated .scala files for views and routes        
EclipseKeys.createSrc := EclipseCreateSrc.ValueSet(EclipseCreateSrc.ManagedClasses, EclipseCreateSrc.ManagedResources) 

// if you want to running compile , this "fork in run" must set false
fork in run := false

// 以下這兩段必須要增加到sbt檔案，目的是要把我們mybaits的xml，編譯到classpath
// 讓myBaits知道要執行那個編譯過後的xml檔案
// Add app folder as resource directory so that mapper xml files are in the classpath
unmanagedResourceDirectories in Compile <+= baseDirectory( _ / "app" )

// but filter out java and html files that would then also be copied to the classpath
excludeFilter in Compile in unmanagedResources := "*.java" || "*.html"

Step2 : 設定我們的app/application.conf。增加以下這幾段。並把前面db{}移除掉，以免干擾到我們的設定。

# 設定我們mariaDb的連線資訊
# maria Driver 路徑的寫法
db.play.driver="org.mariadb.jdbc.Driver"
# 預設連線到之前創立好的play資料庫
db.play.url="jdbc:mariadb://localhost:3306/play"
#使用者帳號與秘碼
db.play.user="root"
db.play.password="1234"
#log是否印出sql
db.play.logSql=true


# 我們之後會寫ㄧ隻MyBatisModule，讓Play可以自動呼叫到連線程式與相依的類別
# modules.MyBatisModule 是有對應到我們程式路徑，要注意擺放位置
# We need to enable the MyBatis Module we have defined (also be sure to use `+=` not `=`)
play.modules.enabled += "modules.MyBatisModule"

step 3 : 在app/pojo目錄下新增db資料夾，在app/pojo/db，新增ㄧ隻Users類別。

package pojo.db;

public class User {
    private Long id;
    private String name;

    public User(Long id, String name) {
        this.id = id;
        this.name = name;
    }

    public Long getId() {
        return id;
    }

    public String getName() {
        return name;
    }
}

Step 4 : 在app/services目錄下新增UserService類別，之後使用到Inject方式，來去執行資料庫查尋動作。

package services;

import pojo.db.User;

import java.util.List;

public interface UserService {
    List<User> all();

    User getUserById(Long id);
}

Step 5 : 在conf目錄下，新增ㄧ個services的目錄，在conf/services新增UserService.xml檔案，裡面是寫好的查詢條件。

<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="services.UserService">
    <resultMap id="userMap" type="pojo.db.User">
        <constructor>
            <idArg column="id" javaType="Long"/>
            <arg column="name" javaType="String"/>
        </constructor>
    </resultMap>

    <select id="all" resultMap="userMap">
        SELECT * FROM Users ORDER BY id
    </select>

    <select id="getUserById" parameterType="Long" resultMap="userMap">
        SELECT * FROM Users WHERE id = #{value}
    </select>
</mapper>

Step 6 : 在app目錄下新增modules資料夾，並在app/modules新增MyBatisModule類別。當我們呼叫到有addMapperClass的類別的時候，會自動幫我們建立DB連線後，並查看Interface所呼叫的功能，會對應的XML檔案指定的SQL語法，來完成存取資料庫動作。

package modules;

import com.google.inject.name.Names;
import org.apache.ibatis.transaction.jdbc.JdbcTransactionFactory;
import play.db.Database;
import services.UserService;

import javax.inject.Inject;
import javax.inject.Provider;
import javax.inject.Singleton;
import javax.sql.DataSource;

public class MyBatisModule extends org.mybatis.guice.MyBatisModule {

    @Override
    protected void initialize() {
        environmentId("development");
        bindConstant().annotatedWith(
                Names.named("mybatis.configuration.failFast")).
                to(true);
        bindDataSourceProviderType(PlayDataSourceProvider.class);
        bindTransactionFactoryType(JdbcTransactionFactory.class);
        // 把我們要呼叫DB的類別，增加到MapperClass
        addMapperClass(UserService.class);
    }

     // 產出一個單例模式的連線
    @Singleton
    public static class PlayDataSourceProvider implements Provider<DataSource> {

        final Database db;

        private Configuration configuration;

        @Inject
        public PlayDataSourceProvider(Configuration config) {

            this.configuration = config;

            // 取得conf檔案 資料庫設定
            String dataBaseName = "play";
            String driver        = configuration.getString("db.play.driver");
            String url            = configuration.getString("db.play.url");
            String user            = configuration.getString("db.play.user");
            String password        = configuration.getString("db.play.password");

            // 建立Database物件
            Database database = Databases.createFrom(
                    dataBaseName,
                    driver,
                    url,
                    ImmutableMap.of(
                            "user", user,
                            "password", password
                    )
            );

            this.db = database;
        }

        @Override
        public DataSource get() {
            return db.getDataSource();
        }
    }

}

Step 7 : 在controller下新增ㄧ隻DbController的類別，會去執行資料庫存取動作。

package controllers;

import javax.inject.Inject;

import play.libs.Json;
import play.mvc.Controller;
import play.mvc.Result;

import services.UserService;

public class DbController extends Controller{

    private UserService userService;

    @Inject
    public DbController(UserService userService) {
        this.userService = userService;
    }

    // 取出所有使用者
    public Result listUsers() {
        return ok(Json.toJson(userService.all()));
    }

    // 根據id，找出對應的人
    public Result showUser(Long id) {
        User user = userService.getUserById(id);
        if(user!=null){
            return ok(Json.toJson(user));
        } else {
            return ok(Json.toJson("查無資料"));
        }    
    }
}

Step 8 : 在conf/routes新增以下這兩個呼叫方式。

# http://127.0.0.1:9000/db/listUsers
GET        /db/listUsers                controllers.DbController.listUsers()

# http://127.0.0.1:9000/db/showUser?id=0001
GET        /db/showUser                controllers.DbController.showUser(id : Long)

Step 9 : 再輸入ㄧ次編譯指令activator clean update compile eclipse run。並確保我們的MariaDB與Play服務都是正常啟用狀態。

開始測試是否能順利連線到資料庫。

Request
取得所有的使用者。

GET : http://127.0.0.1:9000/db/listUsers

Consose log

[info] application - Creating Pool for datasource 'default'
[info] p.a.d.DefaultDBApi - Database [default] connected at jdbc:mysql://localhost:3306/play
[info] application - db name = default
[info] application - db url  = jdbc:mysql://localhost:3306/play
[info] application - ApplicationTimer demo: Starting application at 2016-08-28T08:26:31.030Z
[info] play.api.Play - Application started (Dev)

Response

[{"id":1,"name":"Tom"},{"id":2,"name":"Jerry"}]

Request
查詢編號1的使用者是誰

GET : http://127.0.0.1:9000/db/showUser?id=1

Response

{"id":1,"name":"Tom"}

Request
查詢不存在的編號。

GET : http://127.0.0.1:9000/db/showUser?id=3

Response

"查無資料"

以上若能順利連線成功跟可以查詢到資料，那恭喜你，我們又往前了ㄧ大步。若還是不能連線到資料庫，可能要往回看看是否有遺漏的地方。而我們需要特別注意的部份是MyBatisModule這隻程式，為了未來專案可能的Database來源不止一個，就需要用Play.db.database的createFrom方法，去存取application我們寫好的資料庫設定，就可以達到可以存取不同資料庫的功能了。

[Bonus For MacOSX]
以下簡單介紹使用Mac系統時，快速安裝MariaDB的建置指令步驟，若是不清楚可以參閱文章介紹。

Step 1 : 安裝Homebrew的套件管理工具。
ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"

Step 2 : 執行brew update進行更新動作。
brew update

Step 3 : 安裝MariaDB
brew install mariadb

Step 4 : 安裝完之後啟動MySql
mysql.server start

Step 5 : 安全性安裝
mysql_secure_installation

Step 6 : 連線到MariaDB。
mysql -u root -p

Step 7 : 確認版本。
select @@version;

若是關閉MySQL輸入以下指令
mysql.server stop

設定root的密碼
mysqladmin -u root password "1234"

參考文章
1.利用Homebrew方式安裝MariaDB。
網址: https://mariadb.com/blog/installing-mariadb-10010-mac-os-x-homebrew

2.MySQL GUI - workbench
http://www.mysql.com/products/workbench/