2013年01月27日

Excel(xls, xlsx)をJDBCドライバで操作できる sqlsheet - JDBC driver for MS Excel

JDBCドライバでエクセルをデータベースとして使えたら便利でしょうか?、いや別に・・・と言われそうですがsqlsheetを使えば実現できますよ!

ms-excel

小さなデスクトップ・アプリケーションにデーターベース機能を持たせたい、何かあれば特別な準備をせずにユーザーにもデータを確認・編集できるようにしたい。

そんな時には使えるかもしれませんね。

sqlsheetの概要


まずはRDBMSとsqlsheetにおけるExcelファイルの対応を整理しておきましょうか。

RDBMS sqlsheet( JDBC driver for MS Excel
Database XLS/XLSX file
Table Sheet in XLS/XLSX file
Column Column in sheet (first row must have column names)
Row Row in sheet starting from second row

sqlsheetでは、RDBMSでいうところのデータベース(ファイル、スキーマ)はExcel単一ファイル、テーブルはシート、カラムはExcel列、1レコードはExcel行にマッピングされます(カラム名は先頭行で定義する)。

Columnは別にして、RowはExcelの最大許容行数(Offce 2007以降で最大104万8576行。それ以前では6万5536)では足りない場合もあるかもしれません。


sqlsheetのダウンロードと設定


sqlsheet JDBCドライバは code.google.com にあるプロジェクト・ページからダウンロード出来ます。

sqlsheet - JDBC driver for MS Excel - Google Project Hosting

これに追加で依存ライブラリであるApache POIを入手します。 全部は必要無いので、dom4j、poi、poi-ooxml-schemas、stax-api、jsqlparser、poi-ooxml、sqlsheet、xmlbeansをクラス・パスに通せば準備はおしまい。

sqlsheet


sqlsheetの使い方


まずは、次のコードでdatabase.xlsxファイルにCUSTERMERテーブルと幾つかのデータを作成してみます。

  public void createCustomers() throws ClassNotFoundException, SQLException {

    Connection conn = null;
    Statement stmt = null;
    PreparedStatement pstmt = null;

    try {
      conn = DriverManager.getConnection("jdbc:xls:file:database.xlsx");
      // conn.setAutoCommit(false);

      stmt = conn.createStatement();
      stmt.executeUpdate("CREATE TABLE CUSTOMERS (" +
          "ID INT, NAME VARCHAR(255), AGE INT, REGDATE DATE)");

      pstmt = conn
          .prepareStatement("INSERT INTO CUSTOMERS " +
              "(ID, NAME, AGE, REGDATE) VALUES (?, ?, ?, ?)");

      for (int i = 1; i < 4; i++) {
        pstmt.setInt(1, i);
        pstmt.setString(2, "NAME-" + String.valueOf(i));
        pstmt.setInt(3, 20 + i);
        pstmt.setDate(4,
            new java.sql.Date(new java.util.Date().getTime()));
        pstmt.execute();
      }
      // conn.commit();
    } finally {
      if (conn != null)
        conn.close();
      if (stmt != null)
        stmt.close();
      if (pstmt != null)
        pstmt.close();
    }

  }

流石にトランザクション制御はできないようで conn.setAutoCommit(false); を呼び出すと例外が発生するのでコメントアウト。
(conn.commit(); は呼び出すことはできますが、実態はAuto Commit)

このコードを実行すると、指定したパスにxlsx/xlsファイル、中身をみるとCUSTEMERテーブル(シート)が作成されています。

sqlsheet01

へー面白いですね。 同じようにして、もう一つORDERSテーブル(シート)を作って、JOIN(結合)できるか試してみます。

sqlsheet02

Javaコードはこちら。

    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;

    try {
      conn = DriverManager.getConnection("jdbc:xls:file:database.xlsx");

      String sql = "SELECT * FROM " +
          "CUSTOMERS C INNER JOIN ORDERS O ON C.ID=O.CUST_ID";
      stmt = conn.createStatement();
      rs = stmt.executeQuery(sql);
      ResultSetMetaData rsm = rs.getMetaData();
      int numColumns = rsm.getColumnCount();
      while (rs.next()) {
        for (int i = 1; i <= numColumns; i++) {
          System.out.print(rsm.getColumnName(i) + ":"+ rs.getObject(i) + "\t");
        }
        System.out.println();
      }

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

実行すると正しく結果が表示されます。

ID:1.0  NAME:NAME-1  AGE:21.0  REGDATE:Fri Jan 25 17:57:49 JST 2013
ID:2.0  NAME:NAME-2  AGE:22.0  REGDATE:Fri Jan 25 17:57:49 JST 2013
ID:3.0  NAME:NAME-3  AGE:23.0  REGDATE:Fri Jan 25 17:57:49 JST 2013

ちなみに、SELECT句で指定できるのは*のみ。これ以外では例外が発生します。

java.sql.SQLException: Only 'SELECT *' is supported on Excel sheets
at net.pcal.sqlsheet.parser.SqlSheetParser.parse(SqlSheetParser.java:85)
at net.pcal.sqlsheet.XlsStatement.parse(XlsStatement.java:82)
at net.pcal.sqlsheet.XlsStatement.executeQuery(XlsStatement.java:65)
at SQLSheet.queryByJoinTable(SQLSheet.java:115)
at SQLSheet.main(SQLSheet.java:26)

もう少しSQLパーサーの実装が賢ければ便利なんだけどなぁ。。。

後一つ注意が必要な点として、日付(Date)型の取り扱いがあります。 ResultSetオブジェクトからDateオブジェクトを取得するにはrs.getDate()メソッドではNGで、rs.getObject()からキャストして取得する必要があります。

while (rs.next()) {
  //Date date = rs.getDate(columnIndex);
  Date date = (Date) rs.getObject(columnIndex);
}

微妙に癖があるので、アプリケーション開発時のモックアップ用データベースとして使うのは厳しそうですでね。

最後にビッグ・データを扱う場合の注意点。

Apache POIはExcelデータの量が増えると非常に多くのメモリーを消費します(xlsファイルが数十MBもあれば、JavaヒープはGBに達することも)。

このような場合、sqlsheetでは読み込み、書き込みに応じてJDBC URLにreadStreaming、writeStreamingパラメーターを渡します。

url="jdbc:xls:file:test.xls?readStreaming=true"

値をtrueにすると、一定のサイズ(1KB)でPOIオブジェクトの利用が制限され、メモリ消費を抑えることができます(詳しくはこちら)。


発想は面白いですから、これからに期待したいですね。

それでは、また今度。

 
現場で使えるJavaライブラリ
竹添 直樹 島本 多可子 小津 美夕紀 亀井 隆司
翔泳社
売り上げランキング: 67,426

攻略Jakarta Commons
攻略Jakarta Commons
posted with amazlet at 13.01.27
たなか ひろゆき
ソフトバンククリエイティブ
売り上げランキング: 879,371

Posted by netbuffalo at 15:00│Comments(0)TrackBack(0) Java | プログラミング


この記事へのトラックバックURL

コメントする

名前
 
  絵文字