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を使います。

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

Webサービス作成

新規
データベースからのRESTful Webサービス
データソースはmysqldsを選択
テーブルはbookを選択
パッケージ名は今回はcom.mythosilとする
ApplicationConfigの作成
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"}

Rhythm & Biology

Engineering, Science, et al.