2013年01月27日
Excel(xls, xlsx)をJDBCドライバで操作できる sqlsheet - JDBC driver for MS Excel
JDBCドライバでエクセルをデータベースとして使えたら便利でしょうか?、いや別に・・・と言われそうですがsqlsheetを使えば実現できますよ!
小さなデスクトップ・アプリケーションにデーターベース機能を持たせたい、何かあれば特別な準備をせずにユーザーにもデータを確認・編集できるようにしたい。
そんな時には使えるかもしれませんね。
小さなデスクトップ・アプリケーションにデーターベース機能を持たせたい、何かあれば特別な準備をせずにユーザーにもデータを確認・編集できるようにしたい。
そんな時には使えるかもしれませんね。
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の使い方
まずは、次のコードで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テーブル(シート)が作成されています。
へー面白いですね。 同じようにして、もう一つORDERSテーブル(シート)を作って、JOIN(結合)できるか試してみます。
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 2013ID:2.0 NAME:NAME-2 AGE:22.0 REGDATE:Fri Jan 25 17:57:49 JST 2013ID: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 sheetsat 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ライブラリ
posted with amazlet at 13.01.27
竹添 直樹 島本 多可子 小津 美夕紀 亀井 隆司
翔泳社
売り上げランキング: 67,426
翔泳社
売り上げランキング: 67,426
攻略Jakarta Commons
posted with amazlet at 13.01.27
たなか ひろゆき
ソフトバンククリエイティブ
売り上げランキング: 879,371
ソフトバンククリエイティブ
売り上げランキング: 879,371