RDBでXMLを扱う - しんさんの出張所はてなブログ編

http://kikutaro777.hatenablog.com/entry/2013/07/05/211817
RDBのXMLがいまいちわからないということなのでかるく書いてみる。

使うのはDerby。JDBCのリファレンスとして、JavaDBという名前で利用されているため。ちなみにJava6のJDBC4にいち早く対応はしたものの、Java6のSQLXMLは使えない。文字列を利用して転送する。

生JDBCでもいいけど、たるいのでDB接続情報はpersistence.xmlに記述。JPAでNativeQueryのみを使う。Entityは作らない。

まぁいわゆるJPAらしさはゼロ。Javaである必要性も低いレベル。

テーブルはint型のidとXML型のxml_valueという項目をもつ。

public static void main(String[] args) throws SQLException {
    
    EntityManagerFactory emf = Persistence.createEntityManagerFactory("jpatestPU2");
    EntityManager em = emf.createEntityManager();
    em.getTransaction().begin();
    
    //テーブル作成
    em.createNativeQuery("create table hoge ( id integer, xml_value XML)").executeUpdate();
    
    //サンプルデータ3件作成
    String insert = "insert into hoge (id, xml_value)values(?, XMLPARSE(document CAST (? as varchar(32000)) PRESERVE WHITESPACE))";
    Query insertQuery = em.createNativeQuery(insert);
    insertQuery.
            setParameter(1, 1).
            setParameter(2, "<html><head><title>aaa</title></head><body>あ</body></html>").
            executeUpdate();

    insertQuery.
            setParameter(1, 2).
            setParameter(2, "<html><head><title>bbb</title></head><body>い</body></html>").
            executeUpdate();

    insertQuery.
            setParameter(1, 3).
            setParameter(2, "<html><head><title>aaa</title></head><body>う</body></html>").
            executeUpdate();
    
    
    String sql = "select id , XMLSERIALIZE(XMLQUERY('/html/body/text()' PASSING BY REF xml_value EMPTY ON EMPTY)as varchar(255)) "
            + "from hoge "
            + "where XMLEXISTS('/html/head/title[text()=\"aaa\"] ' PASSING BY REF xml_value)";
    List<Object[]> list = em.createNativeQuery(sql).getResultList();
    for(Object[] row : list){
        System.out.printf("%d/%s%n", row[0], row[1]);
    }

    
    
    em.getTransaction().rollback();

    em.close();
    emf.close();
}

Connectionすら取り出さず扱っている。Queryというのは生JDBCでいうとPreparedStatement相当だということがわかる。

内容はHTMLのtitleタグのテキストが「aaa」のものを検索し、bodyのテキストを取得するというもの。あとidも。

実行結果は以下の通り

1/あ
3/う

複雑な構造を格納したいという場合はまぁありだろう。条件として使いたい場合はパフォーマンスを見てからのほうがよいかもしれない。RDBの苦手な階層の構造を持つというのは結構便利なのはわかってもらえると思う。

ただし、DerbyのJDBCドライバではXMLPARSEしかパラメータが使えないようなので注意。また、XMLの一部のみを取り出したいという場合はEntityやNamedQueryと相性も悪い。更新はいいとして、JPQLでも扱えないわけで。

おそらくこういうのをJPQLで扱いたい場合はストアドやユーザー関数などにして呼び出すというのがスマートだろうか。

ちなみにDerbyでは使わないがSQLXMLという型が登場して便利になったのはJava6から。そのほか、LOB関連も満足に標準APIで使えるようになったのもJava6から。Java5以前はとっくにサポート切れてるし、気にしなくてもよさそうだけどね。