in the mythosil

Rhythm & Biology.

WildFly: MySQLをデータソースとして利用 (CLI設定)

CLIツールにこだわって、WildFlyへのMySQLのドライバインストール、データソース追加までやってみます。

WildFlyのバージョンは8.1.0.Finalです。

ドライバのダウンロード

WildFlyにはドライバは標準ではついてこない(?)ため、ダウンロードしてきます。

$ cd $WILDFLY_HOME/bin
$ curl -LO http://dev.mysql.com/get/Downloads/Connector-J/mysql-connector-java-5.1.31.tar.gz
$ tar xvfz mysql-connector-java-5.1.31.tar.gz

ドライバのデプロイ

さきほどダウンロードしたドライバファイルをWildFlyにデプロイします。

$ ./jboss-cli.sh -c
[standalone@localhost:9990 /] module add --name=org.mysql --resources=mysql-connector-java-5.1.31/mysql-connector-java-5.1.31-bin.jar --dependencies=javax.api,javax.transaction.api
[standalone@localhost:9990 /] /subsystem=datasources/jdbc-driver=mysql:add(driver-module-name=org.mysql,driver-name=mysql,driver-class-name=com.mysql.jdbc.Driver)
{"outcome" => "success"}
[standalone@localhost:9990 /] /subsystem=datasources/jdbc-driver=mysql:read-resource
{
    "outcome" => "success",
    "result" => {
        "deployment-name" => undefined,
        "driver-class-name" => "com.mysql.jdbc.Driver",
        "driver-datasource-class-name" => undefined,
        "driver-major-version" => undefined,
        "driver-minor-version" => undefined,
        "driver-module-name" => "org.mysql",
        "driver-name" => "mysql",
        "driver-xa-datasource-class-name" => undefined,
        "jdbc-compliant" => undefined,
        "module-slot" => undefined,
        "xa-datasource-class" => undefined
    }
}

データソースの追加

データソースの追加から、接続テストまでやります。

[standalone@localhost:9990 /] /subsystem=datasources/data-source=mysqlds:add(jndi-name=java:jboss/datasources/mysqlds,driver-name=mysql,connection-url=jdbc:mysql://localhost:3306/dbname,user-name=dbuser,password=dbpass)            
{"outcome" => "success"}
[standalone@localhost:9990 /] /subsystem=datasources/data-source=mysqlds:test-connection-in-pool
{
    "outcome" => "success",
    "result" => [true]
}
[standalone@localhost:9990 /] /subsystem=datasources/data-source=mysqlds:read-resource
{
    "outcome" => "success",
    "result" => {
        "allocation-retry" => undefined,
        "allocation-retry-wait-millis" => undefined,
        "allow-multiple-users" => false,
        "background-validation" => undefined,
        "background-validation-millis" => undefined,
        "blocking-timeout-wait-millis" => undefined,
        "capacity-decrementer-class" => undefined,
        "capacity-decrementer-properties" => undefined,
        "capacity-incrementer-class" => undefined,
        "capacity-incrementer-properties" => undefined,
        "check-valid-connection-sql" => undefined,
        "connection-listener-class" => undefined,
        "connection-listener-property" => undefined,
        "connection-properties" => undefined,
        "connection-url" => "jdbc:mysql://localhost:3306/dbname",
        "datasource-class" => undefined,
        "driver-class" => undefined,
        "driver-name" => "mysql",
        "enabled" => true,
        "exception-sorter-class-name" => undefined,
        "exception-sorter-properties" => undefined,
        "flush-strategy" => undefined,
        "idle-timeout-minutes" => undefined,
        "initial-pool-size" => undefined,
        "jndi-name" => "java:jboss/datasources/mysqlds",
        "jta" => true,
        "max-pool-size" => undefined,
        "min-pool-size" => undefined,
        "new-connection-sql" => undefined,
        "password" => "dbpass",
        "pool-prefill" => undefined,
        "pool-use-strict-min" => undefined,
        "prepared-statements-cache-size" => undefined,
        "query-timeout" => undefined,
        "reauth-plugin-class-name" => undefined,
        "reauth-plugin-properties" => undefined,
        "security-domain" => undefined,
        "set-tx-query-timeout" => false,
        "share-prepared-statements" => false,
        "spy" => false,
        "stale-connection-checker-class-name" => undefined,
        "stale-connection-checker-properties" => undefined,
        "track-statements" => "NOWARN",
        "transaction-isolation" => undefined,
        "url-delimiter" => undefined,
        "url-selector-strategy-class-name" => undefined,
        "use-ccm" => true,
        "use-fast-fail" => false,
        "use-java-context" => true,
        "use-try-lock" => undefined,
        "user-name" => "dbuser",
        "valid-connection-checker-class-name" => undefined,
        "valid-connection-checker-properties" => undefined,
        "validate-on-match" => false,
        "statistics" => {
            "jdbc" => undefined,
            "pool" => undefined
        }
    }
}

追加された設定

$ diff standalone/configuration/standalone.xml{.org,}
148a149,156
>                 <datasource jndi-name="java:jboss/datasources/mysqlds" pool-name="mysqlds" enabled="true">
>                     <connection-url>jdbc:mysql://localhost:3306/dbname</connection-url>
>                     <driver>mysql</driver>
>                     <security>
>                         <user-name>dbuser</user-name>
>                         <password>dbpass</password>
>                     </security>
>                 </datasource>
152a161,163
>                     <driver name="mysql" module="org.mysql">
>                         <driver-class>com.mysql.jdbc.Driver</driver-class>
>                     </driver>

サンプルアプリケーション

MySQL内のデータを読み書きする小さなWebアプリを作ってみます。

テーブル作成

idフィールドとnameフィールドだけを持つbookテーブルを作成。

create table book (
  id int primary key auto_increment,
  name varchar(128) not null
);

プロジェクト作成

今回はNetBeans8.0を使います。

  1. 新規プロジェクト
  2. Java Web
  3. Webアプリケーション
  4. プロジェクト名「SampleApp」
  5. サーバはWildFlyを選択
  6. Pluginをインストールし、WildFlyをサーバに追加しておく

Webサービス作成

  1. 新規
  2. データベースからのRESTful Webサービス
  3. データソースはmysqldsを選択
  4. テーブルはbookを選択
  5. パッケージ名は今回はcom.mythosilとする
  6. ApplicationConfigの作成
  7. BookFacadeRESTクラス上で「Alt+Enter」で作成

デプロイ・動作確認

デプロイはNetBeans上で「実行」ボタンを押すだけ。

動作確認はcurlで行います。

$ curl 'http://localhost:8080/SampleApp/webresources/com.mythosil.book' -X POST -H "Content-Type: application/json" -d '{"name":"Book1"}'
$ curl 'http://localhost:8080/SampleApp/webresources/com.mythosil.book' -X POST -H "Content-Type: application/json" -d '{"name":"Book2"}'
$ curl 'http://localhost:8080/SampleApp/webresources/com.mythosil.book' -H "Accept: application/json" | python -mjson.tool
[
    {
        "id": 1,
        "name": "Book1"
    },
    {
        "id": 2,
        "name": "Book2"
    }
]

補足1: 設定変更

write-attributeコマンドを利用します。
設定変更後は:reloadが必要です。

[standalone@localhost:9990 /] /subsystem=datasources/data-source=mysqlds:write-attribute(name=connection-url,value=jdbc:mysql://localhost:3306/otherdb)
{
    "outcome" => "success",
    "response-headers" => {
        "operation-requires-reload" => true,
        "process-state" => "reload-required"
    }
}
[standalone@localhost:9990 /] :reload
{
    "outcome" => "success",
    "result" => undefined
}

補足2: データソースの削除

データソースに対してremoveコマンドを実行するだけです。

[standalone@localhost:9990 /] /subsystem=datasources/data-source=mysqlds:remove
{"outcome" => "success"}

参考資料