■開発研究記
TOP >  開発研究記一覧 > JDBCサンプル(2)
2016/11/28 JDBCサンプル(2)

 随分と間が空いてしまった。まあ、色々と忙しかったのである。ちなみに「色々と…」というのは理由が複数あるということだ。それらを全て書くと長ったらしくなるので止めておく。

 さて本題である。今回は前回のサンプルよりも少しだけ実用的な例にした。と言っても少しだけである。どんなものかというと2つのテーブルを更新するというものである。技術的には下記の要素を盛り込んでいる。

  1. コネクションを使い回す。
  2. トランザクションを使用する。
  3. 行ロックをかける。
  4. シーケンスを使用する。

 前回は下記のようなテーブル(M_M_MEMBER)を表示するサンプルを作った。

 今回は下記のような追加用テーブル(M_M_MEMBER_ADD)を新たに作り、前回作ったテーブルにその内容を追加する。そして、追加後は追加用テーブルの追加済みフラグ(ADD_FLAG)を1に更新するというサンプルである。

 色々と面倒なので今回は、Eclipseのプロジェクトのアーカイブを作った。下記のリンクからダウンロードして欲しい。Eclipseからインポートすればソースその他が展開されるので試すのが楽だろう。アーカイブの中のdata/ddl/M_M_MEMBER.ddlを実行すると前回のテーブルが作成される。data/ddl/M_M_MEMBER_ADD.ddlを実行すると上記の追加用テーブル(M_M_MEMBER_ADD)が作成される。ついでにシーケンス作成用のDDLであるM_S__MEMBER_SID.ddlを実行すると話が早い。テストデータについては、MEMBER.sqlとMEMBER_ADD.sqlを実行すると良いだろう。

アーカイブ

 今回メインとなる処理のクラス名はSample2である。アーカイブに含まれているSample1は前回のソースである。長ったらしいので、あまり重要ではない行について「// (省略)」とした。あまり重要ではない行を含むソース全体を見たい場合は、アーカイブから取得して欲しい。これでも長いと思われるが、そういうものなので諦めて欲しい。
 前回は一つのメソッドにDBサーバへの接続、SQLの投入、SQLの実行、リソースのクローズまでの処理を記述したが、今回は複数のSQLを実行するという事で、DBサーバへの接続処理をメソッドから追い出した。さらに実行するSQL毎にメソッドを作成した。それぞれ、selectForUpdate()、updateMember()、updateMemberAdd()である。

// (省略)
public class Sample2 {
    public static void main(String[] args) {
        int rtn = 0;
// (省略)
            rtn = new Sample2().exec(args[0]);
// (省略)
        System.exit(rtn);
    }

    public int exec(String filePath) {
        int rtn = 0;
        Connection connection = null;

        try {
            connection = JdbcUtil.getConnection(filePath);

            // 処理対象の追加テーブルをSELECT FOR UPDATEする
            if (selectForUpdate(connection)) {
                // 追加テーブルの内容をメンバーテーブルに追加する
                if (updateMember(connection) > 0) {
                    // 追加テーブルの追加フラグを更新する
                    if (updateMemberAdd(connection) > 0) {
                        connection.commit();
                    }
                }
            }
// (省略)
        } finally {
 // (省略)
        }
        return rtn;
    }

    private boolean selectForUpdate(Connection connection) throws SQLException {
        boolean result = false;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = "SELECT 0 FROM M_M_MEMBER_ADD WHERE ADD_FLAG = 0 FOR UPDATE NOWAIT";

        try {
            ps = connection.prepareStatement(sql);
            rs = ps.executeQuery();
            result = rs.next();
        } finally {
// (省略)
        }
        return result;
    }

    private int updateMember(Connection connection) throws SQLException {
        int rtn = 0;
        PreparedStatement ps = null;
        String sql = "INSERT INTO M_M_MEMBER " //
                + "SELECT" //
                + " M_S_MEMBER_SID.NEXTVAL, CODE, NAME, NAME_KANA, BIRTH, HEIGHT, WEIGHT " //
                + "FROM M_M_MEMBER_ADD " //
                + "WHERE ADD_FLAG = 0";

        try {
            ps = connection.prepareStatement(sql);
            rtn = ps.executeUpdate();
        } finally {
// (省略)
        }

        return rtn;
    }

    private int updateMemberAdd(Connection connection) throws SQLException {
        int rtn = 0;
        PreparedStatement ps = null;
        String sql = "UPDATE M_M_MEMBER_ADD ma " //
                + "SET ADD_FLAG = 1" //
                + "WHERE ADD_FLAG = 0 AND EXISTS (" //
                + " SELECT 0" //
                + " FROM M_M_MEMBER m" //
                + " WHERE ma.CODE = m.CODE)";
        try {
            ps = connection.prepareStatement(sql);
            rtn = ps.executeUpdate();
        } finally {
// (省略)
        }

        return rtn;
    }
}

Sample2.java

 DB接続処理については汎用的に使えるようにjp.miyacho.utils.JdbcUtilというユーティリティクラスを作成した。getConnection()というスタティックメソッドでDBサーバへ接続し、そのコネクションが取得出来るようになっている。DBへの接続処理を行うユーティリティクラスは下記の通りである

// (省略)
public class JdbcUtil {
// (省略)
    public static Connection getConnection(String filePath) throws IOException, SQLException,
        ClassNotFoundException {
        Connection connection = null;
        Properties properties = PropertiesUtil.loadProperties(filePath);
        String clz = properties.getProperty("class");
        String url = properties.getProperty("url");
        String user = properties.getProperty("user");
        String pass = properties.getProperty("password");
 // (省略)
        Class.forName(clz);

        connection = DriverManager.getConnection(url, user, pass);
        connection.setAutoCommit(false);

        return connection;
    }
}

JdbcUtil.java

 今回は、接続設定を変更可能とするためにプロパティファイルに記述する事にした。そのプロパティファイルの読み込み処理もスタティックメソッドを作って共通処理化した。jp.miyacho.utils.PropertiesUtilというクラスのloadProperties()というメソッドである。

// (省略)
public class PropertiesUtil {
// (省略)
    public static Properties loadProperties(String filePath) throws IOException {
        Properties properties = new Properties();

        try (BufferedInputStream is
            = new BufferedInputStream(new FileInputStream(filePath))) {
            properties.load(is);
        }

        return properties;
    }
}

PropertiesUtil.java

1.コネクションを使い回す

 Sample2クラスの赤字部分に注目して貰いたい。まず、「JdbcUtil.getConnection(filePath)」でDBに接続しコネクションを取得し、ローカル変数であるconnectionにセットしている。そのconnection変数をselectForUpdate()、updateMember()、updateMemberAdd()といったメソッドに引数で渡している。各メソッドでは、引数で渡されたコネクションを使ってprepareStatement()を実行している。つまり、いずれのメソッドも同じコネクションを使用している事がわかる。それだけの話である。
 それだけの話であるが、複数のSQLを連続で実行する場合、こうするべきなのである。何故ならばDBの接続処理、つまりDriverManager.getConnection()は、それなりに時間がかかるからである。今回は3回のSQLしか実行しないので差異はあまり感じられないかもしれないが、数十回、数百回と連続してSQLを実行しなくてはならない場合もある。その際、毎回新しくコネクションを生成していては、遅くて使い物にならなくなるからだ。コネクションプーリングを使用するという手もあるのだが、それはまた別の機会に書く事にする。

2.トランザクションを使用する

 トランザクションというのは、この場合、不可分であるDBの一連の処理の事を言う。コンピュータ用語では別の意味でもトランザクションという単語を使うので混同しないように注意が必要である。不可分であるDBの一連の処理と言われてもピンと来ない表現であるが、要は処理を途中で中止してしまうとテーブルに不整合が生じてしまうので、処理が全部終わった時点でテーブルの状態を確定し、処理を途中で中止するならテーブルの状態を全て元に戻すようにしようという事である。
 今回の例で言えば、M_M_MEMBERにレコードを追加したのにM_M_MEMBER_ADDの追加フラグの更新に失敗した場合、両テーブル間で不整合が起こってしまう。それを防止するためにM_M_MEMBER_ADDの更新処理であるupdateMemberAdd()が正常終了した場合にのみ、コネクションに対してcommit()メソッドを実行して確定している。Sample2クラスの緑字の部分である。このページでは省略されているが、SQLExceptionをキャッチした場合には、コネクションに対してrollback()メソッドを実行し、M_M_MEMBERへのレコードの追加を無かった事にしている。つまり追加前の状態に戻る。
 デフォルトではトランザクションが無効というか、自動コミットモードになっている。自動コミットモードというのは、SQLを1回実行する度にコミットが行われるというモードの事である。今回の例では、自動コミットモードのままにしておくとupdateMember()でM_M_MEMBERにレコードを追加すると即座にテーブルの内容が確定してしまう。するとupdateMemberAdd()が失敗してもロールバック、つまり元に戻す事が出来なくなってしまうのである。
 そんなわけで、トランザクションを使用したい場合は、コネクションに対してsetAutoCommit(false)を実行して、自動コミットモードを無効にする必要がある。JdbcUtilクラスの緑字の部分がそれである。

3.行ロックをかける。

 selectForUpdate()メソッドではSELECT文を実行しているが、これは何をしているかというM_M_MEMBER_ADDテーブルの行ロックを行っているのである。SELECT文の最後に「FOR UPDATE」という指定がされているが、これが行ロックをする指定である。SELECT~FOR UPDATE文で抽出されたレコードに対して他のコネクションからSELECT~FOR UPDATEで抽出が出来なくなる。これにより、処理の途中で他の処理が同じレコードを更新してしまう事が無くなる。つまり、排他制御を行っているのである。SELECT~FOR UPDATEは、コネクションがコミット、またはロールバックされるまで有効となる。
 今回は「NOWAIT」を指定している。これは、他の処理で既に行ロックされていた場合、即座に例外を発生させてしまうという指定である。「WAIT」が指定されていた場合、行ロックをした処理がコミット、ロールバックされるまで待つ事になる。「WAIT」の後に秒数を指定すれば、待ち時間を指定することもできる。「WAIT 30」とすれば30秒待っても行ロックをした処理が終わらなければ、例外が発生する。

4.シーケンスを使用する。

 ユニークな項目を定義したいテーブルがある場合、1,2,3……といった連番を振る事が多いが、Oracleの場合、シーケンス(順序)を使って連番を生成するのが一般的である。使い方は簡単で、SQLに「(シーケンス名).NEXTVAL」と書けば、その分がシーケンスから降られた値に置き換わる。Sample2#updateMember()の青字部分がそれである。
 シーケンスは、「CREATE SEQUENCE」というDDLで作成する。先ほど書いたように今回使用したシーケンスは、M_S__MEMBER_SID.ddlというファイルに記述している。「CREATE SEQUENCE」のDDLではシーケンス名、初期値、増分値、最大値等を指定する。

 現在の値が欲しい場合は「(シーケンス名).CURRVAL」を実行するが、一番最初に「NEXTVAL」を指定していないとエラーとなるので注意が必要である。キー項目として複数のテーブルで同じ値を設定する場合、「INSERT INTO TABLE_A SEQ_NAME.NEXTVAL……」、「INSERT INTO TABLE_B SEQ_NAME.NEXTVAL……」と書くのは誤りである。何故ならばTABLE_Bの方は、TABLE_Aに追加された連番の次の値が発行されてしまうためである。TABLE_Aに100という連番を追加したとしたら、TABLE_Bには101の連番が追加されてしまう。違う値を設定してしまってはキー項目にならない。
 だからと言って、「INSERT INTO TABLE_A SEQ_NAME.NEXTVAL……」、「INSERT INTO TABLE_B SEQ_NAME.CURRVAL……」と書いてもダメである。他の処理で「SEQ_NAME.NEXTVAL」が実行されると値がずれてしまうからである。排他制御が頭に無い初心者が陥り勝ちな罠なので要注意である。こういった場合、まず「SELECT SEQ_NAME.NEXTVAL FROM DUAL」としてSELECT文でDUALから連番の値を取得し、それをそれぞれのINSERT文に設定する形式にしなくてはならない。

 さて、今回も知ってる人には当たり前の話である。もちろん徐々に面倒な話を書いていこうと思っているのだが、書いている時間が無さそうな気もしてきた。まあ、10年以内には完結すると思う。たぶん。

Pervious < JDBCサンプル   

お問い合わせは右のボタンをクリック→

Copyright(C) 2016 miyacho.com