PreparedStatement を close しないとカーソルが close されない？ : PreparedStatement と ResultSet の関係

前回の続き。

Java プログラムで PreparedStatement を使って SQL を発行した時も、Oracle DB 側では内部的にカーソルオブジェクトが生成されていることは分かった。

ではどうして、「ORA-01000 最大オープン・カーソル数を超えました」というエラーが出てしまったのだろうか。

PreparedStatement が close されるまではカーソルもオープンなまま残る

…これが答え。close() メソッドを呼ぶまでは、JDBC のリソースを掴みっぱなしになるらしい。

ということは、問題のコードは PreparedStatement を閉じていなかったものと思われる。次にそのコードのイメージを再現してみたので、詳しく見てみよう。

問題となったコードのイメージ

とてつもないクソコードで、自分だったら絶対こんな作りにしねぇぞっていうコードの山なのだが、それを少し再現。まず呼び出し側の処理。

// 結果を詰めるリスト
List<MyDTO> resultList = new ArrayList<MyDTO>();

// なぜか1件ずつ SELECT して resutList にデータを詰めていく処理がある
for(int i = 0; i < 1500; i++) {
  try {
    // SQL 文の用意
    String sql = "SELECT hoge FROM my_table WHERE id = ?";
    // パラメータの準備
    List<String> param = new ArrayList<String>();
    param.add(i);
    
    // SQL の実行 (ResultSet の独自拡張クラスと DB 接続を隠蔽した独自クラスがある)
    MyResultSet rs = new MyResultSet(MyDbUtil.execute(sql, param));
    
    // ResultSet の値を MyDTO に詰め替えている
    while(rs.next()) {
      MyDTO result = new MyDTO();
      result.setId( rs.getString("ID") );
      /* 中略 */
      // 結果リストに追加する
      resultList.add(result);
    }
    
    // なんで全部 try 句の中でやるんですかね…
    if(rs != null) {
      rs.close();
    }
  }
  catch(Exception e) {
    // でました! Pokemon Exception Handling です!! Eclipse に怒られるから書いただけのセミコロン!!!
    ;
  }
}

// 1画面全体の処理が終わると Connection を close (コネクションプールへの返却) する処理を呼ぼうとする
MyDbUtil.closeConnection();

独自のユーティリティクラスがあちこちに登場してキモさ倍増。あくまでも再現なので、細かなところは無視してくだしあ〜。

少なくとも、上のクラスの中では ResultSet はクローズし (ようとし) ているが PreparedStatement や Connection に関しては触れていない、というところだけ押さえておいてほしい。

ココでミソになるのは MyResultSet に SQL の実行結果を渡す MyDbUtil の処理だろう。

/* MyDbUtil クラスの #execute() メソッド */
public static MyResultSet execute(String sql, List<String> param) {
  // データソースから1つの Connection を取得するメソッドを呼び出す
  Connection con = getConnection();
  
  // PreparedStatement の生成
  PreparedStatement pstmt = con.prepareStatement(sql);
  
  /* 細かな書き方忘れたが setString() とかもやってるテイで… */
  return pstmt.executeQuery();
}

実物を見ずにクソコードを再現で書くの難しい…。とりあえず、このメソッドでは PreparedStatement を作って ResultSet を返すだけしかしていない。つまりここでも PreparedStatement を close していない。

このような作りにすると、for 文でループした数だけ PreparedStatement が作られ、それらがクローズされないまま蓄積する。それにより、Connection を close するための処理を呼ぶより前に、for ループの最中にオープン・カーソル数の最大数を超えてしまい、エラーになってしまっていた。

なんで PreparedStatement を閉じなかったのだろう？PreparedStatement と ResultSet の関係

PreparedStatement を閉じないことでカーソルも閉じられないことは分かった。

では、MyDbUtil#execute() の中で pstmt.executeQuery(); と書いた後、pstmt.close(); を呼んで PreparedStatement を close してあげたらどうなるか。

答えは、PreparedStatement を閉じると ResultSet も同時に閉じられてしまい、結果を受け取ることができない。

これは JDBC API の仕様でそのように決まっているらしく、PreparedStatement#close() を呼ぶと連鎖的に ResultSet#close() も呼びに行くようだ (古い JDBC だとやってくれないこともあるとか)。

ちなみに、GC (ガベージコレクション) でオブジェクトが整理される時も、それぞれの close() メソッドを自動的に呼んでくれるらしい。JVM や JDBC がそれぞれのオブジェクトを操作するときは不正な close 漏れが発生しないように考慮してくれてるのね。

ただし、GC はオブジェクトに null を代入したからといって即座に行われるわけではないので、やっぱりきちんと自分で ResultSet#close() → PreparedStatement#close() の順に close させてあげるのがベスト。

Connection#close() も連鎖的に PreparedStatement や ResultSet を close する

上述のクソコードの中に // 1画面全体の処理が終わると Connection を close (コネクションプールへの返却) する処理を呼ぼうとする という処理がある。これは1つの画面繊維が終了する直前に必ず呼ばれるように記述されており、ラッパーメソッドの中では Connection#close() を呼んでいる。Connection をクローズすると、一度コネクションプールに接続が返されるのだが、この時にも PreparedStatement と ResultSet の close() メソッドを同時に呼んできちんと閉じてくれているようだ。

クソコードの山を覗いていると、同様に PreparedStatement を close しないで使っているコードが散見されるのだが、そこでは for ループで回していないため、最大オープン・カーソル数に達する前に1画面の処理が全て終わり、Connection#close() のタイミングでうまいこと PreparedStatement も close されることで、エラーには至っていないようであった。かなりギリギリセーフな作りだったのだ。怖い怖い。それでも「動いていて問題がないなら直さないこと」と言ってのけるチームリーダはもっと怖い怖い。

標準SQL規格では、トランザクションを終了するとカーソルは破棄される仕様である。

どう書いたらカーソルを適切にクローズできるか

こういうクソコードな状態があちこちに蔓延していて修正箇所が膨大な量になる。なのに「動いているコードはなるべく変えるな」の一点張りな、コードが書けない読めない無能なリーダからの指示で、今回の問題が起こる箇所だけ何とかすることになった。

結論からいくと、for ループの中の rs.close() を行ったあとに、独自の DB 接続クラス MyDbUtil が持っていた、PreparedStatement を close するラッパーメソッドを呼び出してあげることで直せた。ResultSet を扱い終わってから、呼び出し側から PreparedStatement の close を要求するのだ。

ちなみに、その他の画面でも似たような、for ループで SELECT 文を発行しまくる処理があるのだが、そこでは上述の close 処理が正しく書かれていたので、PreparedStatement はループごとに解放され、カーソルも一緒に閉じられていた。ホントにベタベタに JDBC 接続の基本コードを書いている感じで、それなのに中途半端にラッパークラスがあるせいでややこしいことになっていた。

DBUtils だと PreparedStatement を意識しなくて良い

前回の冒頭に「Update の時は DBUtils を使っている」といったことを書いた。DBUtils というのは Apache Commons のライブラリの1つで、PreparedStatement や Connection などを隠蔽し、DB 接続をしやすくしてくれるためのもの。

DBUtils のメソッドを通して Execute (SELECT 文の発行) などをやってやると、DBUtils 内部で PreparedStatement を適切に close してから結果を渡してくれる作りになっている。これは、生の ResultSet を返すのではなく、ResultSetHandler という別のオブジェクトを生成し、それを返すようにしているから、PreparedStatement と ResultSet を close できる作りになっている。

DBUtils は気軽に使えて分かりやすいので、またいつか紹介しようと思っている。

本当は上述のようなクソコードも DBUtils を使った SELECT 処理にすればよかったんだろうけど、多分最初に作った人たちが DBUtils の ResultSetHandler が上手く扱えなかったから、SELECT の時だけは生の PreparedStatement を使うように書き直していたのかなと思う。マジでクソコードだ。

その他参考