Symfoware

Symfowareについての考察blog

Firebird 2.5.2にJava1.7 + JaybirdでXMLの登録と取得を行う

JavaからFirebirdに接続してみました。
Firebird 2.5.2にJava1.7からJaybirdで接続する

今回はXMLなテキストを登録してみます。


テーブルの作成



Firebirdには今のところxml列は無いようです。
BLOBのSUB_TYPEに「TEXT」を指定したフィールドを作成。
ここにxmlな文字列を登録してみることにしました。


CREATE TABLE test(
doc BLOB SUB_TYPE TEXT
)





登録



xmlドキュメントを作成してxmlな文字列に変換。
その内容を登録します。


  1. import java.io.StringWriter;
  2. import java.sql.Connection;
  3. import java.sql.DriverManager;
  4. import java.sql.PreparedStatement;
  5. import javax.xml.parsers.DocumentBuilder;
  6. import javax.xml.parsers.DocumentBuilderFactory;
  7. import javax.xml.transform.Transformer;
  8. import javax.xml.transform.TransformerFactory;
  9. import javax.xml.transform.dom.DOMSource;
  10. import javax.xml.transform.stream.StreamResult;
  11. import org.w3c.dom.Document;
  12. import org.w3c.dom.Element;
  13. public class FBXmlSample {
  14.     
  15.     public static void main(String[] args) throws Exception {
  16.         
  17.         String url = "jdbc:firebirdsql:192.168.1.101/3050:/usr/local/firebird/post.fdb?encoding=UTF8";
  18.         String user = "sysdba";
  19.         String password = "masterkey";
  20.         
  21.         String sql = "INSERT INTO test(doc) VALUES (?)";
  22.         
  23.         // XML構築用
  24.         DocumentBuilder builder = DocumentBuilderFactory.newInstance().newDocumentBuilder();
  25.         
  26.         // Documentを文字列に変換する用
  27.         TransformerFactory tfactory = TransformerFactory.newInstance();
  28.         Transformer transformer = tfactory.newTransformer();
  29.         
  30.         
  31.         try (Connection con = DriverManager.getConnection(url, user, password);
  32.                 PreparedStatement pstmt = con.prepareStatement(sql)){
  33.             
  34.             // 登録用のXMLドキュメントを生成
  35.             Document doc = builder.newDocument();
  36.             
  37.             // docタグをルートに設定
  38.             Element root = doc.createElement("doc");
  39.             doc.appendChild(root);
  40.             
  41.             Element em = doc.createElement("test");
  42.             em.setTextContent("testタグの値です");
  43.             root.appendChild(em);
  44.             
  45.             // Document => xml文字列に変換
  46.             StringWriter sw = new StringWriter();
  47.             transformer.transform(new DOMSource(doc), new StreamResult(sw));
  48.             String xml = sw.toString();
  49.             
  50.             pstmt.setString(1, xml);
  51.             pstmt.execute();
  52.             
  53.         }
  54.     }
  55. }




ちゃんとxmlな文字列が登録されました。

348_01.png





検索



データを取得し、プログラムから扱いやすいようMap形式に変換してみます。

文字列からXML DOMへの変換は、過去の記事を参考にしました。
Javaで文字列(String)からInputStreamを生成し、DOMオブジェクトを作る



  1. import java.io.ByteArrayInputStream;
  2. import java.io.InputStream;
  3. import java.sql.Connection;
  4. import java.sql.DriverManager;
  5. import java.sql.ResultSet;
  6. import java.sql.Statement;
  7. import java.util.HashMap;
  8. import java.util.Map;
  9. import javax.xml.parsers.DocumentBuilder;
  10. import javax.xml.parsers.DocumentBuilderFactory;
  11. import org.w3c.dom.Document;
  12. import org.w3c.dom.Node;
  13. import org.w3c.dom.NodeList;
  14. public class FBXmlReadSample {
  15.     
  16.     public static void main(String[] args) throws Exception {
  17.         
  18.         String url = "jdbc:firebirdsql:192.168.1.101/3050:/usr/local/firebird/post.fdb?encoding=UTF8";
  19.         String user = "sysdba";
  20.         String password = "masterkey";
  21.         
  22.         String sql = "SELECT doc FROM test";
  23.         
  24.         // XML構築用
  25.         DocumentBuilder builder = DocumentBuilderFactory.newInstance().newDocumentBuilder();
  26.         
  27.         try (Connection con = DriverManager.getConnection(url, user, password);
  28.                 Statement stmt = con.createStatement()){
  29.             
  30.             try(ResultSet rs = stmt.executeQuery(sql)) {
  31.                 
  32.                 while(rs.next()) {
  33.                     
  34.                     // xml文字列からDocumentオブジェクトに変換
  35.                     String xml = rs.getString("doc");
  36.                     InputStream bais = new ByteArrayInputStream(xml.getBytes("utf-8"));
  37.                     Document doc = builder.parse(bais);
  38.                     
  39.                     // 適当にMapへ変換
  40.                     Map<String, String> map = getMap(doc);
  41.                     
  42.                     // デバッグ用に表示
  43.                     System.out.println(map.get("test"));
  44.                     
  45.                 }
  46.             }
  47.         }
  48.     }
  49.     
  50.     private static Map<String, String> getMap(Document doc) {
  51.         Map<String, String> map = new HashMap<String, String>();
  52.         
  53.         NodeList nl = doc.getFirstChild().getChildNodes();
  54.         for(int i = 0; i < nl.getLength(); i++) {
  55.             Node node = nl.item(i);
  56.             String nodeName = node.getNodeName();
  57.             map.put(nodeName, node.getTextContent());
  58.         }
  59.         
  60.         return map;
  61.     }
  62. }




実行してみると、そっけないですがちゃんと値が復元できています。


testタグの値です







郵便番号データの登録



PostgreSQLで行った時と同様のことをやってみます。
PostgreSQLのXML列をあいまい検索する(contains)

こんなxmlドキュメントを作成して12万件ほど登録します。


  1. <doc>
  2.     <全国地方公共団体コード>01101</全国地方公共団体コード>
  3.     <郵便番号>0600000</郵便番号>
  4.     <都道府県名カナ>ホッカイドウ</都道府県名カナ>
  5.     <市区町村名カナ>サッポロシチュウオウク</市区町村名カナ>
  6.     <町域名カナ>イカニケイサイガナイバアイ</町域名カナ>
  7.     <都道府県名>北海道</都道府県名>
  8.     <市区町村名>札幌市中央区</市区町村名>
  9.     <町域名>以下に掲載がない場合</町域名>
  10. </doc>





登録するテーブルの定義はこれです。


CREATE TABLE post_xml (
    address BLOB SUB_TYPE TEXT
)




ソースはこんな感じになりました。


  1. package com.fc2.blog68.symfoware.fb.xml;
  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.io.StringWriter;
  8. import java.sql.Connection;
  9. import java.sql.DriverManager;
  10. import java.sql.PreparedStatement;
  11. import javax.xml.parsers.DocumentBuilder;
  12. import javax.xml.parsers.DocumentBuilderFactory;
  13. import javax.xml.transform.Transformer;
  14. import javax.xml.transform.TransformerFactory;
  15. import javax.xml.transform.dom.DOMSource;
  16. import javax.xml.transform.stream.StreamResult;
  17. import org.w3c.dom.Document;
  18. import org.w3c.dom.Element;
  19. public class PostUpdateXML {
  20.     
  21.     public static void main(String[] args) throws Exception {
  22.         
  23.         String url = "jdbc:firebirdsql:192.168.1.101/3050:/usr/local/firebird/post.fdb?encoding=UTF8";
  24.         String user = "sysdba";
  25.         String password = "masterkey";
  26.         
  27.         String sql = "INSERT INTO post_xml(address) VALUES (?)";
  28.         
  29.         try (Connection con = DriverManager.getConnection(url, user, password);
  30.                 PreparedStatement pstmt = con.prepareStatement(sql)){
  31.             
  32.             load(con, pstmt);
  33.             
  34.         }
  35.     }
  36.     
  37.     private static void load(Connection con, PreparedStatement pstmt) throws Exception {
  38.         
  39.         
  40.         try (InputStream in = new FileInputStream("data.csv");
  41.                 InputStreamReader isr = new InputStreamReader(in, "utf8");
  42.                 BufferedReader reader = new BufferedReader(isr)){
  43.             
  44.             // Documentを文字列に変換する用
  45.             DocumentBuilder builder = DocumentBuilderFactory.newInstance().newDocumentBuilder();
  46.             
  47.             // Documentを文字列に変換する用
  48.             TransformerFactory tfactory = TransformerFactory.newInstance();
  49.             Transformer transformer = tfactory.newTransformer();
  50.             
  51.             String line;
  52.             while((line = reader.readLine()) != null) {
  53.                 String[] ary = line.split(",");
  54.                 
  55.                 // 登録用のXMLドキュメントを生成
  56.                 Document doc = builder.newDocument();
  57.                 
  58.                 // docタグをルートに設定
  59.                 Element root = doc.createElement("doc");
  60.                 doc.appendChild(root);
  61.                 
  62.                 Element em = doc.createElement("全国地方公共団体コード");
  63.                 em.setTextContent(ary[0]);
  64.                 root.appendChild(em);
  65.                 
  66.                 em = doc.createElement("郵便番号");
  67.                 em.setTextContent(ary[1]);
  68.                 root.appendChild(em);
  69.                 
  70.                 em = doc.createElement("都道府県名カナ");
  71.                 em.setTextContent(ary[2]);
  72.                 root.appendChild(em);
  73.                 
  74.                 em = doc.createElement("市区町村名カナ");
  75.                 em.setTextContent(ary[3]);
  76.                 root.appendChild(em);
  77.                 
  78.                 em = doc.createElement("町域名カナ");
  79.                 em.setTextContent(ary[4]);
  80.                 root.appendChild(em);
  81.                 
  82.                 em = doc.createElement("都道府県名");
  83.                 em.setTextContent(ary[5]);
  84.                 root.appendChild(em);
  85.                 
  86.                 em = doc.createElement("市区町村名");
  87.                 em.setTextContent(ary[6]);
  88.                 root.appendChild(em);
  89.                 
  90.                 em = doc.createElement("町域名");
  91.                 em.setTextContent(ary[7]);
  92.                 root.appendChild(em);
  93.                 // Document => xml文字列に変換
  94.                 StringWriter sw = new StringWriter();
  95.                 transformer.transform(new DOMSource(doc), new StreamResult(sw));
  96.                 String xml = sw.toString();
  97.                 
  98.                 pstmt.setString(1, xml);
  99.                 pstmt.execute();
  100.                 
  101.                 pstmt.clearParameters();
  102.                 
  103.             }
  104.             
  105.         } catch (IOException e) {
  106.             e.printStackTrace();
  107.         }
  108.         
  109.     }
  110. }




実行した結果、けっこう時間がかかりましたが無事登録が終了しました。





登録したデータの読み込み



登録したデータを検索。
XMLを解析しながら、町域名に「銀座」という文字列が含まれている住所を探します。


  1. package com.fc2.blog68.symfoware.fb.xml;
  2. import java.io.ByteArrayInputStream;
  3. import java.io.InputStream;
  4. import java.sql.Connection;
  5. import java.sql.DriverManager;
  6. import java.sql.ResultSet;
  7. import java.sql.Statement;
  8. import java.util.HashMap;
  9. import java.util.Map;
  10. import javax.xml.parsers.DocumentBuilder;
  11. import javax.xml.parsers.DocumentBuilderFactory;
  12. import org.w3c.dom.Document;
  13. import org.w3c.dom.Node;
  14. import org.w3c.dom.NodeList;
  15. public class PostSearchXML {
  16.     
  17.     public static void main(String[] args) throws Exception {
  18.         
  19.         String url = "jdbc:firebirdsql:192.168.1.101/3050:/usr/local/firebird/post.fdb?encoding=UTF8";
  20.         String user = "sysdba";
  21.         String password = "masterkey";
  22.         
  23.         String sql = "SELECT address FROM post_xml";
  24.         
  25.         // XML構築用
  26.         DocumentBuilder builder = DocumentBuilderFactory.newInstance().newDocumentBuilder();
  27.         
  28.         try (Connection con = DriverManager.getConnection(url, user, password);
  29.                 Statement stmt = con.createStatement()){
  30.             
  31.             try (ResultSet rs = stmt.executeQuery(sql)) {
  32.                 
  33.                 while(rs.next()) {
  34.                     // xml文字列からDocumentオブジェクトに変換
  35.                     String xml = rs.getString("address");
  36.                     InputStream bais = new ByteArrayInputStream(xml.getBytes("utf-8"));
  37.                     Document doc = builder.parse(bais);
  38.                     
  39.                     // 適当にMapへ変換
  40.                     Map<String, String> map = getMap(doc);
  41.                     
  42.                     if (map.get("町域名").indexOf("銀座") == -1) {
  43.                         continue;
  44.                     }
  45.                     StringBuilder sb = new StringBuilder();
  46.                     sb.append(map.get("都道府県名"));
  47.                     sb.append(map.get("市区町村名"));
  48.                     sb.append(map.get("町域名"));
  49.                     System.out.println(sb.toString());
  50.                     
  51.                 }
  52.                 
  53.             }
  54.         }
  55.     }
  56.     
  57.     private static Map<String, String> getMap(Document doc) {
  58.         Map<String, String> map = new HashMap<String, String>();
  59.         
  60.         NodeList nl = doc.getFirstChild().getChildNodes();
  61.         for(int i = 0; i < nl.getLength(); i++) {
  62.             Node node = nl.item(i);
  63.             String nodeName = node.getNodeName();
  64.             map.put(nodeName, node.getTextContent());
  65.         }
  66.         
  67.         return map;
  68.     }
  69. }




プログラムを実行すると、ちゃんと狙い通りの結果が得られました。





実行速度



やけに実行速度が遅い気がしたので、ひょっとしてBLOBがマズイのかな?
と思い、こんな定義に変更してみました。


CREATE TABLE post_xml (
address VARCHAR(8000)
)




約12万件の登録速度比較です。
(単位はms)

パターン1回目2回目3回目4回目5回目平均
BLOB(TEXT)552,510528,895526,130534,868534,868525,472
VARCHAR(8000)577,546607,553566,488565,142572,975577,941


登録はどうやらBLOBのほうが性能が良いようです。


次は検索です。
パターン1回目2回目3回目4回目5回目平均
BLOB(TEXT)38,30037,48036,87536,76337,32837,349
VARCHAR(8000)4,4843,8053,7313,7483,7003,894


10倍差が付きました。
フェッチはVARCHARのほうが圧倒しています。





一括コミット



登録がやけに遅いなと思ったら、一括コミットにするのを忘れてました。
connection.setAutoCommit(false)
を指定して、insertクエリを全件実行した後コミットするよう変更します。

パターン1回目2回目3回目4回目5回目平均
BLOB(TEXT)57,23472,97773,54572,43872,58669,756
VARCHAR(8000)59,28159,34758,68858,72059,04659,016


かなり高速になりました。


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

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

  1. 2014/03/02(日) 18:40:16|
  2. Firebird
  3. | トラックバック:0
  4. | コメント:0
  5. | 編集
<<Firebird 2.5.2にJava1.7 + JaybirdでJSONの登録と取得を行う(JSONIC使用) | ホーム | Firebird 2.5.2にJava1.7 + Jaybirdでバイナリデータの登録と取得を行う>>

コメント

コメントの投稿


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

トラックバック

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