まだプログラマーですが何か?

プログラマーネタとアスリートネタ中心。たまに作成したウェブサービス関連の話も http://twitter.com/dotnsf

タグ:jdbc

Apache Derby(或いは "Cloudscape")という RDB をご存知でしょうか?
derby-logo-web


最近は SQLite や HTML5 のローカルデータストアの台頭であまり名前を聞かなくなりましたが、Pure Java で記述された軽量の RDB です。元々は Cloudscape Inc. によって開発されましたが、Informix Software を経て IBM 製品として扱われていた時代もあります。その影響もあってか "DB2 互換 SQL" に対応し、DB2 の SQL が動く軽量の Java RDB という立ち位置でした。軽量であるが故に組み込み系のアプリケーション内で使われることが多いようです。歴史的には 2004 年に IBM から Apache 財団へソースコードが寄贈され、現在の Apache Derby という名称のプロジェクトになりました。また Oracle JDK 1.6 以降に(オプションとして)組み込まれている JavaDB の実装はこの Apache Derby です。

私自身は "Cloudscape" と呼ばれていた頃に使ったことがありました。今回、久しぶりに Apache Derby を使ってみました。

JDK のオプションに組み込まれているとはいえ、せっかくなので最新版を使ってみることにしました。まずは Apache Derby のダウンロードページから最新バージョン(2016/Oct/07 時点では 10.12.1.1)のリンクをクリックします:
2016100601


最新版のアーカイブファイル:db-derby-(バージョン番号)-bin.zip をクリックしてダウンロードします:
2016100602


ダウンロードした zip ファイルを展開し、lib フォルダ内の必要な JAR ファイルを取り出します。今回は本体である derby.jar と、日本語ロケールが含まれた derbyLocale_ja_JP.jar の2ファイルを取り出します:
2016100603


この2ファイルを Java の開発環境から使えるようにします。J2SE/EE プロジェクトであれば、WebContent/WEB-INF/lib 以下にコピーするなどして、コンパイル/実行時に参照できるようにしておきます:
2016100604


試しに以下のような index.jsp ファイルを用意してみました:
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ page import="java.util.*" %>
<%@ page import="java.io.*" %>
<%@ page import="java.sql.*" %>
<%@ page import="me.juge.derby.*" %>
<%
  request.setCharacterEncoding("utf-8");
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3c.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xmlns:v="urn:schemas-microsoft-com:vml">
<head>
<title>Derby JDBC Sample</title>
</head>
<body>

<table border="1">
<tr><th>ID</th><th>NAME</th><th>PRICE</th></tr>
<%
final String driverName = org.apache.derby.jdbc.EmbeddedDriver.class.getCanonicalName();
final String dbName = "derbydb";
final String connURL = "jdbc:derby:" + dbName + ";create=true";  //. DBが存在していない場合は作成するオプション

try{
  Class.forName( driverName );
  Connection conn = null;

  try{
    conn = DriverManager.getConnection( connURL );
  }catch( Exception e ){
  }

  if( conn != null ){
    ResultSet rs = null;
    //. 初期化
    try{
      //. 試しに items テーブルにアクセス
      Statement s0 = conn.createStatement();
      rs = s0.executeQuery( "select count(*) from items" );
    }catch( SQLException e ){
      //e.printStackTrace();

      String state = e.getSQLState();
      if( state.equals( "42X05" ) ){
        //. テーブルが存在しない
        try{
          Statement s1 = conn.createStatement();
          s1.execute( "create table items("
            + " id int generated always as identity primary key"  //. この辺りが DB2 互換 SQL
            + ", name varchar(100)"
            + ", price int"
            + " )");

          PreparedStatement s2 = conn.prepareStatement( "insert into items( name, price ) values( ?, ? )" );
          s2.setString( 1, "シャンプー" );
          s2.setInt( 2, 1000 );
          s2.execute();

          PreparedStatement s3 = conn.prepareStatement( "insert into items( name, price ) values( ?, ? )" );
          s3.setString( 1, "石鹸" );
          s3.setInt( 2, 500 );
          s3.execute();
        }catch( Exception e1 ){
        }
      }else if( state.equals( "42X14" ) || state.equals( "42821" ) ){
        //. テーブル定義が不正

      }else{
        //. その他の想定外の例外

      }
    }finally{
      if( rs != null ) rs.close();
    }

    //. レコード表示
    try{
      rs = conn.createStatement().executeQuery( "select * from items" );
      while( rs.next() ){
        int id = rs.getInt( 1 );
        String name = rs.getString( 2 );
        int price = rs.getInt( 3 );
%>
<tr><td><%= id %></td><td><%= name %></td><td><%= price %></td></tr>
<%
      }
    }catch( SQLException e ){
    }finally{
      if( rs != null ) rs.close();
    }
  }
}catch( Exception e ){
  e.printStackTrace();
}
 %>
</table>
</body>
</html>

コードそのものは一般的な JDBC プログラミングです。DB を開く際に目的の DB が存在していない場合はその場で作成するようなオプション("create=true")を付与しています。また items というテーブルのレコード数を取得して、エラーが出るようであればテーブルが存在していないと判断し、新規に items テーブルを定義して作成し、2つほどデータを insert するようにしています。 最終的には items テーブル内の全レコードを画面に出力する、という内容にしています。


このプロジェクトを実行して、index.jsp にブラウザでアクセスすると以下のような表形式で2つのレコード内容が確認できます:
2016100701

まあ普通に RDB として使うこともできますが、組み込み系以外であればフットプリントの小ささからローカルレプリカ DB として利用する、というケースも考えられます。特にサーバー側が DB2 だったりすると SQL 互換が便利に使えたりしますね。

久しぶりに Cloudscape を使ってみました。相変わらずですが( Java が動いている前提はありますが)JAR ファイル置くだけで使える RDB は便利ですね。



Java で(JDBCで)MySQL データベースに日本語文字を書き込んだ時に文字化けする、という問題に遭遇した時の対処方法を紹介します。

まず、MySQL データベースを自分で用意する場合にはあまり起こらないことだと思っています。起こらないというか、起こっても自分で MySQL サーバーやクライアントの文字コード設定を変えれば対処できることだと思っています。

ところが最近は PaaS が流行っていることもあり、自分でデータベースサーバーを構築するのではなく、「用意されたデータベースのインスタンスを作って使う」という使い方が可能になり、用意された(自分で設定したわけではない)データベースを使うだけ、という手法も多く使われはじめています(IBM Bluemix とか、AWS の RDS とか、・・)。これはこれで簡単で便利なのですが、いざ問題が起こった場合にサーバーの管理者ではないため、設定を変更できる部分とできない部分があったり、そもそもどういう設定で動いているのかを確認できたりできなかったりします。そしてその結果として、今回のような文字化けの問題が発生したりします(デフォルトのデータベース文字コードが分からない、当然のように UTF-8 だと思って UTF-8 で書き込んだら文字化けした、というパターン)。

これを回避するにはデータベースの設定がどのようになっていたとしても UTF-8 前提で接続して、UTF-8 を明示して書き込む、という実装が必要になるわけです。


これが Java(JDBC) と MySQL で問題になったりします(同じ MySQL でも PHP では特に意識せずに文字化けも回避して使えていたが、同じ処理を Java で行うと文字化けする、といったことが起こります)。コードのテキストそのものが UTF-8 で記述されていても起こります。要は上記で書いたような部分が自動判別では実現できておらず、コードとして実装する必要があるからです。

Java(JDBC) で MySQL に対して UTF-8 を明示して接続するには、このように記述する必要があります:
    :
    :
  Connection conn = null;

  String dburi = "mysql://mysql.test.com/mydb?useUnicode=true&characterEncoding=utf8";

  try{
    Class.forName( "com.mysql.jdbc.Driver" );
    conn = DriverManager.getConnection( "jdbc:" + dburi, mysql_username, mysql_password );
  }catch( Exception e ){
    e.printStackTrace();
  }

    :
    :

上記青字部分が指定している箇所です。useUnicode でユニコード指定を名言し、characterEncoding でそのエンコードを utf8 に指定しています。

この方法でコネクションを取得すると、読み書き時の文字化けを回避できました。まあ全ての文字化けのケースにこの方法が有効とはいえませんが、原因不明の文字化けが発生した際の回避方法の1つとして。


統合開発環境である eclipse に、リレーショナルデータベースのデータビューワである DBViewer プラグインを導入することで、開発中のアプリケーションのデータを開発環境からも比較的簡単に参照することができるようになります。

このプラグインを導入する前提として、まず Eclipse Marketplace プラグインが必要です。メニューから Help - Eclipse Marketplace が選択できるようになっていれば導入済みなので次のステップへ。
2014100301


もし Help メニューに Eclipse Marketplace が存在しない場合は最初に同プラグインを導入する必要があります。 その場合はメニューから Help - Install New Software を選びます。ダイアログではまず Work with 欄で使っている eclipse SDK のバージョン(junoとか)を選び、その下では General Purpose Tools - Marketplace Client にチェックを入れて Next > Next > 利用規格に同意 > Install です。 しばらく待つとインストールが完了し、eclipse を再起動すると Eclipse Marketplace がメニューから選択できるようになっているはずです。
2014100302


改めて eclipse のメニューから Help - Eclipse Marketplace を選択し、Search タブの Find 欄に "DBViewer" と入力して "Go" をクリックします。DBViewer プラグインが見つかるので、Install > Next > 利用規格 > Finish でインストールします。しばらく待つとインストールが完了し、eclipse を再起動すると DBViewer プラグインの導入が完了します。
2014100303


実際に DBViewer を使ってデータを参照するには、参照先データベースに対応した JDBC ドライバが必要になります。例えば接続先データベースが MySQL であれば、以下のサイトから JDBC Connector をダウンロードして JAR ファイルを展開しておきます:
MySQL : Download Connector/J

他の(Oracle や DB2, PostgreSQL などの)データベースを利用する場合も、それぞれ対応した JDBC ドライバが必要です。これらをあらかじめ用意しておきます。


これで DBViewer を利用してデータベースを参照するための準備が整いました。では実際に DBViewer でデータベースサーバーに接続して、eclipse 環境からデータの中身を参照してみます。

eclipse のメニューから Windows - Open Perspective - Other を選択し、一覧から "DBViewer" を選択して OK をクリックします。
2014100304


すると eclipse 全体が DBViewer パースペクティブに切り替わります。この段階ではまだ何の接続先定義もしていないので何も表示されていません。
2014100305


では接続先データベーステーブルを追加してみます。DBViewer パースペクティブの左上ペインの登録ボタン(上記図の赤枠部分)をクリックして「データベース定義」ダイアログを表示します。
2014100306


「データベース定義名」欄にはデータベース参照先を表す適当な名称を入力します。また「JDBC Driver」欄には接続先の JDBC ドライバを指定する必要があります。「ファイルの追加」ボタンをクリックして、先程用意した JDBC Driver の JAR ファイルを指定して Next ボタンをクリックします。なおここで指定した JDBC Driver は、この後で同じ種類のデータベースを追加する時には「登録済み Driver から選択する」をクリックするだけで指定できるようになります。
2014100307


こんな感じのダイアログが表示されたら OK をクリックします。
2014100308


次の画面で具体的な接続情報を指定します。この例では MySQL データベースに接続するので、そのような内容担っていますが、ここは実際のドライバに合わせた指定をしてください。この例では接続文字列として jdbc:mysql://(MySQL サーバー名):3306/(データベース名) を入力して、接続ユーザー名とパスワードを指定しています。念のため「テスト接続」ボタンをクリックして、実際にこの内容で接続できることを確認し、Finish ボタンをクリックします。
2014100309


今作成した内容が DB ツリービューに追加されるはずです。この DB 名部分をダブルクリックすると接続します。
2014100310


正しく接続できるとデータベース一覧、テーブル/ビュー一覧が階層的に表示され、テーブル名をダブルクリックすると、その中身が画面右側に一覧表示されます。
2014100311


接続状態を終了(切断)するにはデータベース定義名部分を右クリックして「切断」を選択します。
2014100312






 

最近のマイブームの1つになっている、全文検索エンジン ElasticSearch に MySQL のデータを取り込んで、MySQL データベースの全文検索エンジンとして ElasticSearch を使う手順の紹介です。
2014070401


まず検索エンジンである ElasticSearch を導入します。日本語形態素解析エンジンである Kuromoji まで含めてのインストール手順を別エントリで紹介しているので、こちらを参照ください:
ElasticSearch に Kuromoji プラグインを導入する


また取り込み先である MySQL サーバーについても環境は構築済みであると仮定します。こちらの構築手順についても、こちらのエントリを参照ください:
CentOS に MySQL をインストール/セットアップする

なお、自分個人的には MySQL ではなく MariaDB を使って同じことをできているので、以下の内容に関しては MariaDB でも同様に可能だと思っています。


さて、ElasticSearch に MySQL データを取り込むための準備として ElasticSearch 自体のインストール後に以下のステップを行う必要があります:
1. (MySQL クライアントと)JDBC ドライバの導入
2. JDBC River プラグインのインストール


最終的には ElasticSearch の River プラグインと呼ばれる拡張機能を使って MySQL からのデータ取り込みを行います。このプラグインの動作に必要な MySQL JDBC ドライバを先にインストールしておく、というステップになります。


まず、これは必須ではありませんが、あると確認に便利なので MySQL のクライアント環境を ElasticSearch サーバー内に構築しておきます。MySQL サーバーに接続する機能があればいいので、MySQL サーバー機能は不要で、クライアント機能だけが必要、ということになります。もし MySQL サーバーと ElasticSearch サーバーが同じサーバーだとすると、既に MySQL クライアント環境は導入済みだと思うので、インストールは不要です。 MySQL クライアントが導入されていない場合は以下のコマンドで MySQL クライアントをインストールします:
# yum install mysql
# vi /etc/my.cnf
  :
(以下の2行を追加)
[mysql]
default-character-set=utf8

MySQL クライアントが導入できた所で、取り込み元の MySQL サーバーへ接続してみます。仮に今回取り込むデータの内容が以下であると仮定します:
 MySQL サーバー: mysql.mylocal.com
 ユーザー名: username
 パスワード: password
 データベース名: mydb
 取り込む内容: samples テーブル


実際に MySQL クライアントで目的のデータベースにアクセスしてみます。ここまでが出来るようであればファイアウォールなども含めて接続準備ができているといえます:
# mysql -h mysql.mylocal.com -u username -ppassword mydb
> select * from samples;
:
:
(samples テーブルの内容)
:
: > quit


次に MySQL サーバーへ Java 環境から接続するための JDBC ドライバー(MySQL Connector/J)を導入します。ドライバー自体はこちらのサイトからダウンロードできます:
MySQL :: Download Connector/J 


ダウンロードしたファイルを展開して JAR ファイルを取り出し、/usr/share/java にコピーします:
# unzip mysql-connector-java-5.1.30.zip
# cd mysql-connector-java-5.1.30
# cp mysql-connector-java-5.1.30-bin.jar /usr/share/java

環境変数 CLASSPATH に、この JAR ファイルを追加します:
# vi /etc/bashrc
  :
(以下の1行を追加)
export CLASSPATH=$CLASSPATH:/usr/share/java/mysql-connector-java-5.1.30-bin.jar
 

# source /etc/bashrc

JDBC ドライバの準備が出来た所で River プラグインをインストールします。リポジトリを確認したところ、2014/07/01 時点での最新バージョンは 1.2.1.1 だったので、このバージョンを指定して導入します:
# /usr/share/elasticsearch/bin/plugin --install jdbc --url http://xbib.org/repository/org/xbib/elasticsearch/plugin/elasticsearch-river-jdbc/1.2.1.1/elasticsearch-river-jdbc-1.2.1.1-plugin.zip
# cp /usr/share/java/mysql-connector-java-5.1.30-bin.jar /usr/share/elasticsearch/plugins/jdbc/

これで必要なソフトウェアは揃いました。では実際に MySQL からデータを取り込んでみましょう

まず ElasticSearch 側に kuromoji を使った検索インデックス(kuromoji_sample)を作成します。インデックスの作成については別エントリでも紹介しましたが、まだこの内容を実行していない場合は以下のコマンドを実行します(コマンドは黒字部分で、青字はレスポンスを表しています):
# curl -XPUT http://localhost:9200/kuromoji_sample -d '{ "index": { "analysis": { "tokenizer": { "kuromoji_user_dict" : { "type":"kuromoji_tokenizer" } }, "analyzer": { "analyzer": { "type":"custom", "tokenizer": "kuromoji_user_dict" } } } } }'
{"acknowledged":true}

次に River を使って、作成した kuromoji_sample インデックスに MySQL データベースサーバーからデータを取り込みます:
# curl -XPUT http://localhost:9200/_river/my_jdbc_river/_meta -d '{ "type": "jdbc", "jdbc": { "url": "jdbc:mysql://mysql.mylocal.com:3306/mydb", "user": "username", "password": "password", "sql": "select * from samples", "index": "kuromoji_sample", "type": "samples" } }'
{"_index": "_river", "_type": "my_jdbc_river", "_id": "_meta", "_version": 1, "created": true}

上記入力パラメータ(JSON)の中で選択(select)の SQL を発行しています。この SQL の実行結果が ElasticSearch に取り込まれることになります。

取り込みができたら、最後に検索してみます。この例では取り込んだデータの name フィールドに「ほげほげ」が含まれているデータを検索しています:
# curl -XPOST http://localhost:9200/kuromoji_sample/_search?pretty -d '{ "query": { "query_string": { "query": "name:ほげほげ" } } }'
{
  "took" : 2,
  "timed_out" : false,
  "_shards" : {
    "total" : 5,
    "successful" : 5,
    "failed" : 0
  },
  "hits" : {
    "total" : 29,
    "max_score" : 3.687642,
    "hits" : [ {
      "_index" : "kuromoji_sample",
      "_id" : "XXXXXXXXXXXXXX",
      "_score" : 3.687642,
      "_source":{"id":"1234","name":"ほげほげ"}
    }, {
      "_index" : "kuromoji_sample",
        :
    } ]
  }
}

こんな感じで実現できました。

MySQL の like 節を使った単純検索をしていた頃と比べると、以前は localhost 内の MySQL に対して検索していたのでネットワークによる遅延はほとんどなかったはずで、今回作ってみた環境はリモートの ElasticSearch 環境にアクセスしているので、ネットワークの遅延影響がでるはずです。

にも関わらず、検索パフォーマンスは 10 倍程度になりました。これはでかい!

ElasticSearch の検索パターンやその API 実行方法についてはいずれまたプログに書く予定です。


 

このページのトップヘ