Symfoware

Symfowareについての考察blog

Firebird 2.5.2にJava1.7 + JaybirdでMap形式のデータ登録方法の考察

XML、JSON、MessagePackを使用して、Firebirdに
Map形式のデータを登録してみました。

Firebird 2.5.2にJava1.7 + JaybirdでXMLの登録と取得を行う
Firebird 2.5.2にJava1.7 + JaybirdでJSONの登録と取得を行う(JSONIC使用)
Firebird 2.5.2にJava1.7 + JaybirdでMessagePackの登録と取得を行う

もうひとつ案を思いついたので試してみます。



メインテーブルとサブテーブル



シリアルキーを保持するメインテーブルを定義。
サブテーブルでは、シリアルキー + key名をPrimary Keyとして、
値を保持する方法を考えてみます。

作成するテーブルはこの2つ。


CREATE TABLE post_hash_owner (
id int,
primary key(id)
);

CREATE TABLE post_hash (
id int,
hash_key varchar(100),
hash_value varchar(100),
primary key(id, hash_key)
);




post_hash_ownerにキーを束ねるためのシリアルを連番で登録。
post_hashに実体を登録します。




登録サンプル



郵便番号データを登録するサンプルは以下のようになりました。


  1. package com.fc2.blog68.symfoware.fb.hash;
  2. import java.io.BufferedReader;
  3. import java.io.FileInputStream;
  4. import java.io.IOException;
  5. import java.io.InputStream;
  6. import java.io.InputStreamReader;
  7. import java.sql.Connection;
  8. import java.sql.DriverManager;
  9. import java.sql.PreparedStatement;
  10. import java.util.HashMap;
  11. import java.util.Map;
  12. public class PostUpdateHash {
  13.     public static void main(String[] args) {
  14.         String url = "jdbc:firebirdsql:192.168.1.101/3050:/usr/local/firebird/post.fdb?encoding=UTF8";
  15.         String user = "sysdba";
  16.         String password = "masterkey";
  17.         String sqlOwner = "INSERT INTO post_hash_owner(id) VALUES (?)";
  18.         String sqlHash = "INSERT INTO post_hash(id, hash_key, hash_value) VALUES (?,?,?)";
  19.         try (Connection con = DriverManager.getConnection(url, user, password)) {
  20.             con.setAutoCommit(false);
  21.             try (PreparedStatement stmtOwner = con.prepareStatement(sqlOwner);
  22.                     PreparedStatement stmtHash = con.prepareStatement(sqlHash)) {
  23.                 load(con, stmtOwner, stmtHash);
  24.             } catch (Exception e) {
  25.                 con.rollback();
  26.                 throw e;
  27.             }
  28.             con.commit();
  29.         } catch (Exception e) {
  30.             
  31.             e.printStackTrace();
  32.         }
  33.     }
  34.     private static void load(Connection con, PreparedStatement stmtOwner, PreparedStatement stmtHash)
  35.             throws Exception {
  36.         try (InputStream in = new FileInputStream("data.csv");
  37.                 InputStreamReader isr = new InputStreamReader(in, "utf8");
  38.                 BufferedReader reader = new BufferedReader(isr)) {
  39.             
  40.             // 連番
  41.             int id = 0;
  42.             String line;
  43.             while ((line = reader.readLine()) != null) {
  44.                 String[] ary = line.split(",");
  45.                 // 他のプログラムと似たような処理にするため、
  46.                 // 一旦Mapに展開
  47.                 Map<String, String> map = new HashMap<String, String>();
  48.                 map.put("全国地方公共団体コード", ary[0]);
  49.                 map.put("郵便番号", ary[1]);
  50.                 map.put("都道府県名カナ", ary[2]);
  51.                 map.put("市区町村名カナ", ary[3]);
  52.                 map.put("町域名カナ", ary[4]);
  53.                 map.put("都道府県名", ary[5]);
  54.                 map.put("市区町村名", ary[6]);
  55.                 map.put("町域名", ary[7]);
  56.                 // シリアルID登録
  57.                 stmtOwner.setInt(1, ++id);
  58.                 stmtOwner.execute();
  59.                 stmtOwner.clearParameters();
  60.                 
  61.                 // ハッシュ情報登録
  62.                 for(Map.Entry<String, String> ent : map.entrySet()) {
  63.                     
  64.                     stmtHash.setInt(1, id);
  65.                     stmtHash.setString(2, ent.getKey());
  66.                     stmtHash.setString(3, ent.getValue());
  67.                     
  68.                     stmtHash.addBatch();
  69.                     stmtHash.clearParameters();
  70.                 }
  71.                 
  72.                 stmtHash.executeBatch();
  73.             }
  74.         } catch (IOException e) {
  75.             e.printStackTrace();
  76.         }
  77.     }
  78. }



実行したところ、想定通りの件数が登録されました。






検索サンプル



データを復元するサンプルはこんな感じになります。


  1. package com.fc2.blog68.symfoware.fb.hash;
  2. import java.sql.Connection;
  3. import java.sql.DriverManager;
  4. import java.sql.ResultSet;
  5. import java.sql.SQLException;
  6. import java.sql.Statement;
  7. import java.util.HashMap;
  8. import java.util.Iterator;
  9. import java.util.Map;
  10. public class PostSearchHash {
  11.     
  12.     public static void main(String[] args) throws Exception {
  13.         
  14.         String url = "jdbc:firebirdsql:192.168.1.101/3050:/usr/local/firebird/post.fdb?encoding=UTF8";
  15.         String user = "sysdba";
  16.         String password = "masterkey";
  17.         try (Connection con = DriverManager.getConnection(url,user, password);
  18.                 Statement stmt = con.createStatement()) {
  19.             StringBuilder query = new StringBuilder();
  20.             query.append("SELECT");
  21.             query.append(" post_hash.*");
  22.             query.append(" FROM");
  23.             query.append(" post_hash_owner,");
  24.             query.append(" post_hash");
  25.             query.append(" WHERE");
  26.             query.append(" (post_hash_owner.id = post_hash.id)");
  27.             query.append(" ORDER BY");
  28.             query.append(" post_hash.id");
  29.             // データ取得
  30.             ResultSet rs = stmt.executeQuery(query.toString());
  31.             
  32.             // イテレーター作成
  33.             PostList list = (new PostSearchHash()).new PostList(rs);
  34.             
  35.             for (Map<String, String> map : list) {
  36.                 
  37.                 if (map.get("町域名").indexOf("銀座") == -1) {
  38.                     continue;
  39.                 }
  40.                 StringBuilder sb = new StringBuilder();
  41.                 sb.append(map.get("都道府県名"));
  42.                 sb.append(map.get("市区町村名"));
  43.                 sb.append(map.get("町域名"));
  44.                 System.out.println(sb.toString());
  45.             }
  46.         } catch (Exception e) {
  47.             e.printStackTrace();
  48.         }
  49.         
  50.     }
  51.     
  52.     public class PostList implements Iterable<Map<String, String>> {
  53.         
  54.         private PostIterater it;
  55.         
  56.         public PostList(ResultSet rs) throws SQLException {
  57.             it = new PostIterater(rs);
  58.         }
  59.         
  60.         @Override
  61.         public Iterator<Map<String, String>> iterator() {
  62.             return it;
  63.         }
  64.         
  65.     }
  66.     
  67.     class PostIterater implements Iterator<Map<String, String>> {
  68.         
  69.         private ResultSet rs;
  70.         private boolean hasNext = false;
  71.         
  72.         public PostIterater(ResultSet rs) throws SQLException {
  73.             this.rs = rs;
  74.             hasNext = rs.next();
  75.         }
  76.         
  77.         @Override
  78.         public boolean hasNext() {
  79.             return hasNext;
  80.         }
  81.         @Override
  82.         public Map<String, String> next() {
  83.             
  84.             Map<String, String> map = new HashMap<String, String>();
  85.             
  86.             try {
  87.             
  88.                 int id = rs.getInt("id");
  89.                 
  90.                 while(hasNext) {
  91.                     
  92.                     map.put(rs.getString("hash_key"), rs.getString("hash_value"));
  93.                     
  94.                     hasNext = rs.next();
  95.                     
  96.                     if (id != rs.getInt("id")) {
  97.                         break;
  98.                     }
  99.                 }
  100.             
  101.             } catch (Exception e) {
  102.                 hasNext = false;
  103.             }
  104.             
  105.             
  106.             return map;
  107.         }
  108.         @Override
  109.         public void remove() {
  110.         }
  111.         
  112.     }
  113.     
  114. }








直接検索



キーと値を各々フィールドで持っているので、上記の地名に「銀座」を
含む値の検索は、直接SQLで問い合わせることも可能です。



  1. package com.fc2.blog68.symfoware.fb.hash;
  2. import java.sql.Connection;
  3. import java.sql.DriverManager;
  4. import java.sql.ResultSet;
  5. import java.sql.SQLException;
  6. import java.sql.Statement;
  7. import java.util.HashMap;
  8. import java.util.Iterator;
  9. import java.util.Map;
  10. public class PostSearchDirectHash {
  11.     
  12.     public static void main(String[] args) throws Exception {
  13.         
  14.         String url = "jdbc:firebirdsql:192.168.1.101/3050:/usr/local/firebird/post.fdb?encoding=UTF8";
  15.         String user = "sysdba";
  16.         String password = "masterkey";
  17.         try (Connection con = DriverManager.getConnection(url,user, password);
  18.                 Statement stmt = con.createStatement()) {
  19.             StringBuilder query = new StringBuilder();
  20.             query.append("SELECT");
  21.             query.append(" a.*");
  22.             query.append(" FROM");
  23.             query.append(" post_hash a");
  24.             query.append(" ,post_hash b");
  25.             
  26.             query.append(" WHERE");
  27.             // 「銀座」を含むデータを取得
  28.             query.append(" (b.hash_key = '町域名')");
  29.             query.append(" AND (b.hash_value LIKE '%銀座%')");
  30.             query.append(" AND (a.id = b.id)");
  31.             query.append(" ORDER BY");
  32.             query.append(" a.id");
  33.             // データ取得
  34.             ResultSet rs = stmt.executeQuery(query.toString());
  35.             
  36.             // イテレーター作成
  37.             PostList list = (new PostSearchDirectHash()).new PostList(rs);
  38.             
  39.             for (Map<String, String> map : list) {
  40.                 StringBuilder sb = new StringBuilder();
  41.                 sb.append(map.get("都道府県名"));
  42.                 sb.append(map.get("市区町村名"));
  43.                 sb.append(map.get("町域名"));
  44.                 System.out.println(sb.toString());
  45.             }
  46.         } catch (Exception e) {
  47.             e.printStackTrace();
  48.         }
  49.         
  50.     }
  51.     
  52.     public class PostList implements Iterable<Map<String, String>> {
  53.         
  54.         private PostIterater it;
  55.         
  56.         public PostList(ResultSet rs) throws SQLException {
  57.             it = new PostIterater(rs);
  58.         }
  59.         
  60.         @Override
  61.         public Iterator<Map<String, String>> iterator() {
  62.             return it;
  63.         }
  64.         
  65.     }
  66.     
  67.     class PostIterater implements Iterator<Map<String, String>> {
  68.         
  69.         private ResultSet rs;
  70.         private boolean hasNext = false;
  71.         
  72.         public PostIterater(ResultSet rs) throws SQLException {
  73.             this.rs = rs;
  74.             hasNext = rs.next();
  75.         }
  76.         
  77.         @Override
  78.         public boolean hasNext() {
  79.             return hasNext;
  80.         }
  81.         @Override
  82.         public Map<String, String> next() {
  83.             
  84.             Map<String, String> map = new HashMap<String, String>();
  85.             
  86.             try {
  87.             
  88.                 int id = rs.getInt("id");
  89.                 
  90.                 while(hasNext) {
  91.                     
  92.                     map.put(rs.getString("hash_key"), rs.getString("hash_value"));
  93.                     
  94.                     hasNext = rs.next();
  95.                     
  96.                     if (id != rs.getInt("id")) {
  97.                         break;
  98.                     }
  99.                 }
  100.             
  101.             } catch (Exception e) {
  102.                 hasNext = false;
  103.             }
  104.             
  105.             
  106.             return map;
  107.         }
  108.         @Override
  109.         public void remove() {
  110.         }
  111.         
  112.     }
  113.     
  114. }







通常のテーブル構成



速度を比較するため、一般的なテーブルを作成し、データを登録してみました。


CREATE TABLE post_normal(
lasdeccode CHAR(5),
zip_code CHAR(7),
prefkn VARCHAR(20),
citykn VARCHAR(100),
areakn VARCHAR(100),
pref VARCHAR(20),
city VARCHAR(100),
area VARCHAR(100)
)




ソースは割愛。





登録速度比較



まずは登録から。単位はmsです。

パターン1回目2回目3回目4回目5回目平均
XML(BLOB)57,23472,97773,54572,43872,58669,756
XML(VARCHAR)59,28159,34758,68858,72059,04659,016
JSON(BLOB)53,70762,68962,21161,70061,13960,289
JSON(VARCHAR)31,49653,59251,98652,27054,83848,836
MessagePack51,47358,54659,41058,99757,93057,271
HASH290,553293,528296,088291,952291,940292,812
NORMAL22,61422,53521,64322,61222,48022,377


350_01.png

いいアイデアだと思ったテーブル分割方式は芳しくなかったです。
やはり通常の登録方法が最速。
ほかはどれを選んでも大勢に影響はない感じかと思います。





検索速度比較



次は検索速度の比較です。

パターン1回目2回目3回目4回目5回目平均
XML(BLOB)38,30037,48036,87536,76337,32837,349
XML(VARCHAR)4,4843,8053,7313,7483,7003,894
JSON(BLOB)39,67239,54939,07039,14837,68639,025
JSON(VARCHAR)4,4404,2994,3694,3994,4024,382
MessagePack45,48745,13145,29845,55745,24445,343
HASH17,26917,16717,71017,37617,15817,336
HASH(DIRECT)7,6977,6737,5727,6757,6857,660
NORMAL2,1452,0892,2162,1072,1722,146
NORMAL(DIRECT)216213209209198209


350_02.png

HASH、NORMALのDIRECTとはクエリで絞り込みを行った結果です。
それ以外は全件フェッチして、取得したいデータを探した時の時間です。

登録では、純粋に登録レコード数が多いためテーブル分割は不利でしたが、
検索時はなかなかの性能を見せていれています。

だた、XMLやJSONをVARCHARに登録したものには及びません。

なかなかいい勉強になりました。




サンプルプログラムはここに置いておきます。
https://bitbucket.org/symfo/firebird-map-sample/src/
関連記事

テーマ:プログラミング - ジャンル:コンピュータ

  1. 2014/03/03(月) 00:32:15|
  2. Firebird
  3. | トラックバック:0
  4. | コメント:0
  5. | 編集
<<Firebird embeddedにJava1.7 + Jaybird + Ubuntuで接続する | ホーム | Firebird 2.5.2にJava1.7 + JaybirdでMessagePackの登録と取得を行う>>

コメント

コメントの投稿


管理者にだけ表示を許可する

トラックバック

トラックバック URL
http://symfoware.blog68.fc2.com/tb.php/1311-e1fc458a
この記事にトラックバックする(FC2ブログユーザー)