2010年08月29日

JDBCドライバの差異 queryForMap()の返すMapのキー

またしても驚くべき事実に遭遇しました。
どうも PostgreSQL を普段から使用している方には当たり前のことのようですが、10年以上 Oracle 一辺倒で、最近 H2 で頑張っている私としては、衝撃の事実でした。

では何が衝撃の事実だったのか?
Spring freamework を利用して JDBC 経由でDBにアクセスしているのですが、queryForMap() で返されるMapのキーの文字列が、PostgreSQL を利用すると小文字で格納されます。
それまで H2 で動作していたものを PostgreSQL で動作させるため、SQL文の差異を修正して実行したところ、値が取れなかったため発覚しました。
今となっては結論がわかっているので当たり前ですが、当初はSQL文を疑ったり、そこへ至る分岐を疑ったりと、少し手間取ってしまいました。
Springを使用して実行したコードは下記のようになります。
JdbcTemplate db = getSimpleJdbcTemplate();
String sql 
    = "SELECT TO_CHAR(CURRENT_TIMESTAMP, 'yyyy/mm/dd') AS DSTMP";
Map value = db.queryForMap(sql);
String today = value.get("DSTMP");
実際に Map に詰められている内容は、「dstmp=2010/08/29」なわけですから、"DSTMP"でget()しても一致するキーがないので today には null が詰められるわけです。
もう少し丁寧に説明すると、各JDBCドライバが返すResultSetMetaDataに詰められているカラムラベルを用いてSpringがMapを作成します。
そのため、PostgreSQLのJDBCドライバが小文字で返すことにより、結果的に上記のような動作になった次第です。
SQL文に大文字でカラムのエイリアスまで切っているのに、わざわざ小文字に変換して返すとは想像だにできませんでした。

ちょこっと調査してみたところ、PostgreSQL は小文字のキーにして返します。
DB2は大文字・小文字を区別しているようなので、Mapに返すキーも大文字・小文字交じりになると予想します。(すみません確認できていません)

RDBMSに依存しない方法はないのでしょうか?
実は複数DBに対応したアプリを作成していて、それぞれのRDBMSの差異を吸収するようにしたいので、対策を考えてみます。
いくつか解決方法を挙げてみます。
 1.SQL文で解決:エイリアスをダブルクォート「"」で囲んで使用する
 2.Mapから値を取得する際に、JDBCに合わせてキーを大文字小文字変換する
 3.ResultSetを用いて検索する
それぞれの方法について少し考えてみます。

SQL文で解決:エイリアスをダブルクォート「"」で囲んで使用する。
結論からいうと、(DB2は試していないので判別できませんが)期待通り値を取得できました。
この方法の場合、各RDBMSの差異はSQL文のみになり、新規で処理を作成する場合には有効だと思います。
ただし、既存のプログラムを他DBに対応する場合は、SQL文をすべて変更しなければなりません。
また、複数RDBMSに対応する場合、工夫をしなければ各RDBMS用のSQL文を用意することになってしまいそうです。

Mapから値を取得する際に、JDBCに合わせてキーを大文字小文字変換する
一見あまり使いたくない手法ですが、既に複数RDBMS対応を設計に盛り込んでいた場合、その他の場面でも同様の手法で統一的なコーディングにすることができます。
例えばシーケンスやシステム日付を取得する際のSQL文の差異を吸収することと、今回の値を取得する場面で同じ手法が使えます。
具体的にはインターフェースを定義し、各RDBMSに対応したサポートクラスを用意しておきます。
Springフレームワークの中でも各RDBMS間の差異を吸収する手段として、一部にこの方法を用いています。

ResultSetを用いて検索する
java.sql.ResultSetインターフェースは、
 「getter メソッドへの入力として使用される列名では、大文字と小文字は区別されません。」
と明記されています。
実装は各JDBCドライバ開発元に依存しますが、仕様で明記されているので信用することにします。

どの方法も一長一短でしょうし、解決したい内容やスコープが異なるので、それぞれの課題に適した方法を採用すればよいでしょう。
私が遭遇した今回のケースでは、
 ・設計当初から複数RDBMSを想定していたこと
 ・フレームワークを作成しており、JdbcTemplateは使用者からは一切意識する必要がない
ため、解決策2の方法を採用しています。
続きを読む
posted by しん at 22:49| Comment(0) | Spring | このブログの読者になる | 更新情報をチェックする

2010年08月26日

DDLとトランザクションの関係

不勉強だったのですが、正直驚きました。
RDBMSによってDDL(Data Definition Language)を発行した際に動作が異なるとは想像もしていませんでした。
15年もOracle一辺倒で、最近H2で頑張っている私としては、DDLを発行したら暗黙的にcommitがかけられるものと信じきっていました。

では各RDBMSの動作はどのようになっているのでしょうか?
調査した結果を表にしてみました。
RDBMS 動作
Oracle暗黙commit
MS SQLServer※トランザクションを継続
DB2※トランザクションを継続
PostgreSQLトランザクションを継続
MySQL暗黙commit
H2暗黙commit
Derby※トランザクションを継続
※:実際の動作確認はしていません
posted by しん at 06:06| Comment(0) | DB | このブログの読者になる | 更新情報をチェックする

2010年08月19日

POIでセルの値をとるのは大変 数式編

前回までに数値と日付について、表示されている内容で取得することが困難なことを述べててきました。
今回ご紹介する数式の場合も厄介です。

数式そのものを取得するには Cell#getCellFormula() で取得できますが、欲しいのは計算された結果(表示されている内容)です。
数式の結果が数値になるとあらかじめ判別できれば、Cell#getNumericCellValue()で取得できますが、必ずしも数値になるとは限りませんし、先の数値編で説明したとおり、表示されている内容での取得は、残念ながらこのままではできません。
また、数式の結果が文字列の場合にも、Cell#getRichStringCellValue().getString()として取得しようとすると、数式が返ってきます。
ここでちょっと検索してみると、FormulaEvaluatorというインターフェースがあることがわかりました。
数式の結果を取得するためのインターフェースです。
処理概要は、
 1.ワークブックオブジェクトからCrationHelperインターフェースを取得
 2.CrationHelper#createFormulaEvaluator()でFormulaEvaluatorを取得
 3.FormulaEvaluator#evaluate()を利用してCellValueを取得
 4.Cellと同様にCellValue#getCellType()に対応したメソッドを使用して値を取得
となります。
これでめでたしめでたしとなりそうですが、ちょっと問題があります。

何が問題なのでしょうか?
上記手順をコーディングすると下記のようになります。
  switch(cell.getCellType()) {
  case
   ・・・
  case Cell.CELL_TYPE_FORMULA:
    Workbook wb = cell.getSheet().getWorkbook();
    CreationHelper crateHelper = wb.getCreationHelper();
    FormulaEvaluator evaluator = crateHelper.createFormulaEvaluator();
    CellValue cellValue = evaluator.evaluate(cell);
    switch (cellValue.getCellType()) {
    case Cell.CELL_TYPE_BOOLEAN:
      return String.valueOf(cellValue.getBooleanValue());
    case Cell.CELL_TYPE_NUMERIC:
      if (DateUtil.isCellDateFormatted(cell)
        || CellDateFormat.contains(cell.getCellStyle().getDataFormat())
      ) {      //日付
        Date theDate = cell.getDateCellValue();
        DateFormat dateFormat = CellDateFormat.getFormt(cellStyle.getDataFormat()).getDateFormat();
        return dateFormat.format(theDate);
      } else { // 数値
        DataFormatter formatter = new DataFormatter();
        return formatter.formatCellValue(cell);
      }
      break;
     ・・・
  case
   ・・・
数式の処理についてのみ注目して、さらにだいぶ省略して記載していますが、それでも長いソースです。
もちろん関数化を行って少しはすっきりすると思いますが、やっぱり冗長していると感じます。
見た目が美しくないことが問題なのでしょうか?
すでにお気づきの方もいらっしゃると思いますが、このソースにはバグが含まれています。
日付・数値の判定処理・値取得に、Cellオブジェクトではなく、CellValueオブジェクトを使用しています。
このままでは正しく判定して値取得ができません。

ではどうやって解決すればよいのでしょう?
ここまで根気よく読んできてくれた方は想像がつくかもしれませんが、ここでもやはり妥協します。
どのような妥協をするかというと、値の取得しか行わないことを前提に、FormulaEvaluator#evaluateInCell()を使用して、セルの値を計算式の結果にしてしまいます
今回の妥協はかなり強引ですので、使用方法は十分に注意してください。
セルから値を取得するメソッドに対し、数式の場合のみFormulaEvaluator#evaluateInCell()を使用してセルを計算結果に置き換えて再帰的に値取得メソッドを呼び出します。
極力見た目どおりにString型で取得する場合のコーディング例を記載します。
public String getCellValue(Cell cell) {
  switch(cell.getCellType()) {
  case Cell.CELL_TYPE_BLANK:
    return "";
  case Cell.CELL_TYPE_STRING:
    return cell.getRichStringCellValue().getString();
  case Cell.CELL_TYPE_BOOLEAN:
    return String.valueOf(cell.getBooleanCellValue());
  case Cell.CELL_TYPE_NUMERIC:
    // 日付・整数・少数の判別を行う
    if (DateUtil.isCellDateFormatted(cell)
      || CellDateFormat.contains(cell.getCellStyle().getDataFormat())
    ) {       //日付
      Date theDate = cell.getDateCellValue();
      DateFormat dateFormat = CellDateFormat.getFormt(cellStyle.getDataFormat()).getDateFormat();
      return dateFormat.format(theDate);
    } else {  // 数値
      DataFormatter formatter = new DataFormatter();
      String retValue = formatter.formatCellValue(cell);
      if (retValue.endsWith("_ ")) {
        retValue = retValue.substring(0, retValue.length() -2);
      }
      return retValue;
    }
  case Cell.CELL_TYPE_FORMULA:
    Workbook wb = cell.getSheet().getWorkbook();
    CreationHelper crateHelper = wb.getCreationHelper();
    FormulaEvaluator evaluator = crateHelper.createFormulaEvaluator();
    return getCellValue(evaluator.evaluateInCell(cell));
  default:
    return "";
  }
}
ここまでの感想として、苦労して値を取得してもすべてを救えないなら、
 予め決められた書式を使用し、対応するプログラムを書く
ようにしたほうが良いのではないかと思ってしまいます。
ExcelとPOJOマッピングライブラリでは、これまでのことを踏まえた上で、取得する型を指定し、値変換可能な処理にしています。
興味がございましたら、こちらもご参考にしてください。
ラベル:Poi java ExcelPOJO
posted by しん at 00:31| Comment(1) | POI | このブログの読者になる | 更新情報をチェックする
×

この広告は1年以上新しい記事の投稿がないブログに表示されております。