一覧用の検索条件からいろいろ自動で取得する

一覧で要素を一つ選択して詳細ページでその内容を表示するようなサイトを作ることは良くあると思うけど、その為のDBアクセス処理を毎回一機能ずつ書くのは面倒なのでどうにかする方法を考える。

今回はフレームワークとしてはCayenneを使うけど、実例を挙げてアイデアを示したかっただけなので別にCayenneでなくてもいいです。真っ当なO/Rマッパなら同じこと出来ると思う*1。というか普段Cayenne使ってるわけではないので使い方もおかしいかもしれない。なのでCayenneのサンプルプログラムとしてなら他所を見てほしい。バージョンも古すぎの2.0.4だし。

テーブルおよびサンプルデータ

日記っぽいものを作ってるとしよう。

 CREATE TABLE weblogs (
   id INTEGER PRIMARY KEY NOT NULL,
   title TEXT NOT NULL,
   owner TEXT NOT NULL
 );
 CREATE TABLE weblog_entries (
   id INTEGER PRIMARY KEY NOT NULL,
   weblog_id INTEGER NOT NULL,
   title TEXT NOT NULL,
   pub_date timestamptz NOT NULL,
   content TEXT,
   CONSTRAINT fk_weblog_id FOREIGN KEY(weblog_id) REFERENCES weblogs(id)
 );

weblogsテーブルが日記サイト、weblog_entriesテーブルが個々の記事です。
1 ownerに付き1 weblogsってことにしておこう。
初期データを入れておく

INSERT INTO weblogs(id,title,owner) VALUES(1,'terazzoの日記','terazzo');
INSERT INTO weblogs(id,title,owner) VALUES(2,'petazzo blog','petazzo');
INSERT INTO weblogs(id,title,owner) VALUES(3,'exazzo labs','exazzo');

INSERT INTO weblog_entries(id,weblog_id,title,pub_date) VALUES(1,1,'関数型のテンプレートエンジンを作ってみる(0.2.3)','2011-04-24');
INSERT INTO weblog_entries(id,weblog_id,title,pub_date) VALUES(2,2,'ついつい集めてしまうもの','2011-01-27');
INSERT INTO weblog_entries(id,weblog_id,title,pub_date) VALUES(3,2,'自己紹介をしてみよう','2011-01-13');
INSERT INTO weblog_entries(id,weblog_id,title,pub_date) VALUES(4,2,'東北地方太平洋沖地震','2011-03-17');
INSERT INTO weblog_entries(id,weblog_id,title,pub_date) VALUES(5,1,'これはモナドですか？','2011-02-18');
INSERT INTO weblog_entries(id,weblog_id,title,pub_date) VALUES(6,2,'私の小さなこだわり','2011-04-21');
INSERT INTO weblog_entries(id,weblog_id,title,pub_date) VALUES(7,2,'人生最大のピンチ！','2011-02-17');
INSERT INTO weblog_entries(id,weblog_id,title,pub_date) VALUES(8,2,'これが私の至福の時','2011-02-03');
INSERT INTO weblog_entries(id,weblog_id,title,pub_date) VALUES(9,2,'この春始めたいこと','2011-03-31');
INSERT INTO weblog_entries(id,weblog_id,title,pub_date) VALUES(10,2,'甘酸っぱい思い出','2011-02-10');
INSERT INTO weblog_entries(id,weblog_id,title,pub_date) VALUES(11,2,'心に残る映画','2011-02-24');
INSERT INTO weblog_entries(id,weblog_id,title,pub_date) VALUES(12,2,'心に残った本','2011-04-14');
INSERT INTO weblog_entries(id,weblog_id,title,pub_date) VALUES(13,2,'笑顔のもと','2011-03-24');
INSERT INTO weblog_entries(id,weblog_id,title,pub_date) VALUES(14,2,'冬の楽しみ','2011-01-20');
INSERT INTO weblog_entries(id,weblog_id,title,pub_date) VALUES(15,2,'今年の抱負','2011-01-06');
INSERT INTO weblog_entries(id,weblog_id,title,pub_date) VALUES(16,2,'ブログと私','2011-03-10');
INSERT INTO weblog_entries(id,weblog_id,title,pub_date) VALUES(17,2,'桜','2011-04-07');
INSERT INTO weblog_entries(id,weblog_id,title,pub_date) VALUES(18,2,'iPad2欲しいですか？','2011-03-03');

Cayenneモデルの作成

http://d.hatena.ne.jp/terazzo/20090427/1240859397と同じようにDBからモデルファイルとクラスを作成する。
※やはりCayenne wayではないけど、PK(idカラム)をObjEntityにも追加する。

一覧(コレクション)を取得する(基本)

では実装に入っていく。
まずはすべての基本となる、コレクションを取得するための検索条件を作成するメソッドを実装する。
検索条件としては、特定のownerの日記を日付順に見る、というものを考える。

public class WeblogEntryLogic {
    // DB接続用のDataContext。普通はセッション毎に作る。
    private DataContext dataContext = DataContext.createDataContext();

    // ownerがweblogOwnerのWeblogEntriesをListで取得
    public List<WeblogEntries> getCollection(String weblogOwner) {
        // 検索条件。
        SelectQuery baseQuery = createCollectionQuery();

        // 入力値をパラメータとして設定
        Map<String, Object> queryParameters = new HashMap<String, Object>();
        queryParameters.put("weblogOwner", weblogOwner);

        // 検索を実行
        SelectQuery query = baseQuery.queryWithParameters(queryParameters);
        return dataContext.performQuery(query);
    }

    // コレクションを取得する為の検索条件(SelectQuery)を生成する。
    // ソート順は日付の降順とする。
    // weblogs.ownerを限定するパラメータ名はweblogOwnerとする。
    private SelectQuery createCollectionQuery() {
        // WeblogEntriesエンティティ検索用のSelectQueryを生成
        SelectQuery proto = new SelectQuery(WeblogEntries.class);

        Expression qualifier =
            ExpressionFactory.matchExp("toWeblogs.owner", new ExpressionParameter("weblogOwner"));
        proto.setQualifier(qualifier);

        proto.addOrderings(Arrays.asList(
                new Ordering("pubDate", false),
                new Ordering("id", true)
                ));
        
        return proto;
    }

owner="petazzo"さんの日記が見たければ、次のように引数で指定してよんでやる。

    WeblogEntryLogic weblogEntryLogic; // 初期化済みとしよう。
    ... 
    List<WeblogEntries> entries = weblogEntryLogic.getCollection("petazzo");
    for (WeblogEntries entry : entries) {
        System.out.printf("%4d: %tF, %s\n", entry.getId(), entry.getPubDate(), entry.getTitle());
    }

   6: 2011-04-21, 私の小さなこだわり
  12: 2011-04-14, 心に残った本
  17: 2011-04-07, 桜
   9: 2011-03-31, この春始めたいこと
  13: 2011-03-24, 笑顔のもと
   4: 2011-03-17, 東北地方太平洋沖地震
  16: 2011-03-10, ブログと私
  18: 2011-03-03, iPad2欲しいですか？
  11: 2011-02-24, 心に残る映画
   7: 2011-02-17, 人生最大のピンチ！
  10: 2011-02-10, 甘酸っぱい思い出
   8: 2011-02-03, これが私の至福の時
   2: 2011-01-27, ついつい集めてしまうもの
  14: 2011-01-20, 冬の楽しみ
   3: 2011-01-13, 自己紹介をしてみよう
  15: 2011-01-06, 今年の抱負

一覧(コレクション)の逆順を取得する

やっぱり日付が古い順の表示をするページも必要になったとする。
日付古い順のQueryを新たに定義するのではなく、日付新しい順のQueryを加工して使いたい。

public class WeblogEntryLogic {
...
    public List<WeblogEntries> getReversedCollection(String weblogOwner) {
        SelectQuery baseQuery = createCollectionQuery();
        // 検索条件のソート順を逆順に変換
        QueryUtils.reverseOrder(baseQuery);

        // 入力値をパラメータとして設定
        Map<String, Object> queryParameters = new HashMap<String, Object>();
        queryParameters.put("weblogOwner", weblogOwner);

        // 検索を実行
        SelectQuery query = baseQuery.queryWithParameters(queryParameters);
        return dataContext.performQuery(query);
    }

public final class QueryUtils {
    private QueryUtils() {
    }
    // queryを逆順にする(副作用有り)
    public static void reverseOrder(SelectQuery query) {
        List<Ordering> orderings = query.getOrderings();
        query.clearOrderings();
        for (Ordering ordering: orderings) {
            boolean ascending = ordering.isAscending();
            Ordering reverseOrdering = new Ordering(ordering.getSortSpec(), !ascending);
            query.addOrdering(reverseOrdering);
        }
    }
}

元のSelectQueryからOrderingをすべて取り出し、ascendingを逆にしたものをセットしている。*2

呼んでみる。

    WeblogEntryLogic weblogEntryLogic; // 初期化済みとしよう。
    ... 
    List<WeblogEntries> entries = weblogEntryLogic.getReversedCollection("petazzo");
    for (WeblogEntries entry : entries) {
        System.out.printf("%4d: %tF, %s\n", entry.getId(), entry.getPubDate(), entry.getTitle());
    }

  15: 2011-01-06, 今年の抱負
   3: 2011-01-13, 自己紹介をしてみよう
  14: 2011-01-20, 冬の楽しみ
   2: 2011-01-27, ついつい集めてしまうもの
...

データを特定する為の情報を取り出す

一覧ページから詳細ページへリンクを貼りたいのだが、詳細ページのURLパラメータはどうやって決めれば良いだろう？
レコードオブジェクト(WeblogEntries)から、レコードを特定する為の情報を生成出来るようにする。

public final class QueryUtils {
...
    // レコードオブジェクトから、そのレコードを特定する情報を生成する
    public static Map<String, Object> getIdentifier(CayenneDataObject record) {
        Map<String, Object> identifier = new HashMap<String, Object>();
        for (String attributeName : pkAttributeNames(record.getObjEntity())) {
            Object value = record.readProperty(attributeName);
            identifier.put(attributeName, value);
        }
        return identifier;
    }
    // objEntityのプライマリキーをObjAttribute名のCollectionで戻す。
    public static Collection<String> pkAttributeNames(ObjEntity objEntity) {
        DbEntity dbEntity = objEntity.getDbEntity();
        List<String> pkAttributeNames = new ArrayList<String>();
        for (DbAttribute dbAttribute : (List<DbAttribute>) dbEntity.getPrimaryKey()) {
            ObjAttribute attribute = objEntity.getAttributeForDbAttribute(dbAttribute);
            if (attribute == null) {
                new IllegalStateException("The objAttribute not set for:" + dbAttribute);
            }
            pkAttributeNames.add(attribute.getName());
        }
        return pkAttributeNames;
    }

CayenneのレコードオブジェクトはCayenneDataObjectのサブクラスになる*3。
レコードオブジェクトからObjEntityを、ObjEntityからDbEntityを取得し、DbEntityからモデルに設定されたプライマリキーの情報を取得し、プライマリキーの各カラムの値をレコードオブジェクトから取り出してMapにセットしている。

呼んでみる。

    List<WeblogEntries> entries = weblogEntryLogic.getCollection("petazzo");

    for (WeblogEntries entry : entries) {
        // identifierを表示
        System.out.printf("%s\n", QueryUtils.getIdentifier(0)); 
    }

{id=6}
{id=12}
{id=17}
{id=9}
...

今回はidカラム一つだが、複合PKの場合、それぞれのカラムに対する値が入ったMapが戻される。
これを元に詳細ページのURLを作れば詳細データを表示出来るようになるだろう。

データを特定する為の情報からデータを取得する

詳細ページにアクセスされた際に、つまり上の情報を用いてデータベースから値を取り出す。
やはり検索条件はコレクション用のものを加工して作る。

public class WeblogEntryLogic {
    private DataContext dataContext = DataContext.createDataContext();
...
    public WeblogEntries findByIdentifier(String weblogOwner, Map<String, Object> identifier) {
        SelectQuery baseQuery = createCollectionQuery();
        // 特定レコードを取得する為の検索条件
        SelectQuery identifierQuery =
            QueryUtils.createIdentifierQuery(baseQuery, dataContext.getEntityResolver());

        // 入力値をパラメータとして設定
        Map<String, Object> queryParameters = new HashMap<String, Object>();
        queryParameters.put("weblogOwner", weblogOwner);
        queryParameters.putAll(identifier);
        SelectQuery query = identifierQuery.queryWithParameters(queryParameters);

        List<WeblogEntries>list = dataContext.performQuery(query);
        return list.isEmpty() ? null : list.get(0);
    }

public final class QueryUtils {
...
    // 特定のレコードを取得する為のSelectQueryを生成して戻す。
    // パラメータ名はPKのObjAttribute名と同じとする
    public static SelectQuery createIdentifierQuery(
            SelectQuery query, EntityResolver entityResolver) {
        QueryMetadata metaData = query.getMetaData(entityResolver);
        Expression expression = query.getQualifier();
        for (String attributeName : pkAttributeNames(metaData.getObjEntity())) {
            expression = expression.andExp(
                ExpressionFactory.matchExp(
                    attributeName, new ExpressionParameter(attributeName)));
        }
        query.setQualifier(expression);
        return query;
    }

SelectQueryとentityResolverからQueryMetadataが、QueryMetadataからObjEntityが取得出来るので、先ほどと同様にPKの情報を取得してQueryにAND条件で追加している。
createIdentifierQuery()が戻すのはParameterized Queryなので、検索時に実際のパラメータをSelectQuery.queryWithParameters()でセットしてやる必要がある。逆に言えばidentifierQueryは再利用可能になっている。

呼んでみる

    int weblogEntryId; // URLなどから取得

    Map<String, Object> identifier = new HashMap<String, Object>();
    identifier.put("id", weblogEntryId); 
    WeblogEntries record = findByIdentifier("petazzo", identifier);

    System.out.printf("%4d: %tF, %s\n", record.getId(), record.getPubDate(), record.getTitle());

結果(weblogEntryId=9の場合)

   9: 2011-03-31, この春始めたいこと

レコードを特定出来る情報があるのになんでわざわざコレクション用のQueryとweblogOwnerの情報を使ってるの？と思った人は理由を考えよう。

前後のレコードを取得する

詳細ページに「<(前のエントリのタイトル)」「(次のエントリのタイトル)>」 という、前後のエントリを表示する為のリンクの表示が必要になったとしよう。
前後のデータを取得するQueryを新たに書き起こすのはどうしても嫌なので、コレクション用のQueryを流用してなんとかしたい。

まず自己結合出来るようにモデルにリレーションを追加する。
名前はなんでも良いけど「toSelf」とする。また、リレーションキーがないと怒られるので無難な感じのを設定しておく(今回は同一日記の前後を読むので、ソース:weblog_id、ターゲット:weblog_id)

自己結合が使えれば、コレクションの検索条件とソート順から、特定のキーの前後一件のレコードを取り出す検索条件を作ることが出来る。

t0が取得しようとしている側のweblog_entries、t2が現在ページで表示しようとしている側のweblog_entriesとすれば、現在ページで表示しているもの以降のデータを検索するには、(ソート順はpubDateの降順、idの昇順にしているので)下のような検索条件になればよい。

t0.weblog_id = t2.weblog_id
AND (t2.id = 「現在ページのID」)
AND ((t0.pub_date < t2.pub_date) OR ((t0.pub_date = t2.pub_date) AND (t0.id > t2.id))))

通常の検索条件に加えて、「現在のページより日付が古いか、または、日付が同じかつIDが大きいもの」という検索条件が増える。

一般にソート順がO=(O0,O1,...)、Onのカラムがpn、Onのasc/descがdn、レコードx,yがカラムpで順序dを満たす関係をR(xp,yp,d)、レコードx,yがカラムpで等しい関係をS(xp,yp)、としたら
xの下界は {y| R(xp0, yp0 d0) ∪ (S(xp0, yp0) ∩ R(xp1, yp1, d1)) ∪ (S(xp0, yp0) ∩ S(xp1, yp1) ∩ R(xp2, yp2, d2)) ∪ ... } ※ただしOに最低一つは候補キーを含む
みたいになってると思うので(たぶん)、これをプログラム化する。

まずOrdering一個から(上で言う)t0とt2の比較条件を作り出すメソッドを実装する。自己結合先を表すtoOriginPathには"toSelf"を指定して呼び出す。

public final class QueryUtils {
...
    // 一つのOrderingから大または小の比較条件を作り出す。
    private static Expression ordiringToComparatorQualifier(Ordering ordering, String toOriginPath) {
        String sortSpec = ordering.getSortSpecString(); // カラム
        return (ordering.isAscending())
                // 昇順なら大なり
                ? ExpressionFactory.greaterExp(sortSpec, new ASTObjPath(toOriginPath + "." + sortSpec))
                // 降順なら小なり
                : ExpressionFactory.lessExp(sortSpec, new ASTObjPath(toOriginPath + "." + sortSpec));
    }
    // 一つのOrderingから等しい比較条件を作り出す。
    private static Expression ordiringToMatcherQualifier(Ordering ordering, String toOriginPath) {
        String sortSpec = ordering.getSortSpecString();
        return ExpressionFactory.matchExp(sortSpec, new ASTObjPath(toOriginPath + "." + sortSpec));
    }

次にこれを使って、Orderingのリストから上界or下界全体を指定する条件を作るメソッドを実装する。
各Orderingに対する比較条件を作りながら、ORで結合していく。二つ目以降のOrdeingについては、一つ前のまでのOrderingのカラムについてはイコールという条件をANDで付けている。

    public static Expression orderingsToQualifer(List<Ordering> orderings , String toOriginPath) {
        Expression orderQueries = null;
        Expression lastEqualQueries = null;
        for (Ordering ordering : orderings) {
            Expression orderQuery =
                nonNullAndQuarifier(
                    lastEqualQueries, QueryUtils.ordiringToComparatorQualifier(ordering, toOriginPath));

            orderQueries = nonNullOrQuarifier(orderQueries, orderQuery);

            lastEqualQueries = nonNullAndQuarifier(
                    lastEqualQueries, QueryUtils.ordiringToMatcherQualifier(ordering, toOriginPath));
        }
        return orderQueries;
    }
    private static Expression nonNullAndQuarifier(Expression base, Expression additional) {
        return (base == null) ? additional : base.andExp(additional);
    }
    private static Expression nonNullOrQuarifier(Expression base, Expression additional) {
        return (base == null) ? additional : base.orExp(additional);
    }

これを使って、「次の一件」を取得する為のQueryを作る。

public final class QueryUtils {
...
    public static SelectQuery createNextQuery(SelectQuery query,
            EntityResolver entityResolver, String toOriginPath) {
        Expression expression = query.getQualifier();
        // origin側をIdentifierで縛る
        for (String attributeName : pkAttributeNames(query.getMetaData(entityResolver).getObjEntity())) {
            expression = expression.andExp( 
                ExpressionFactory.matchExp(
                    toOriginPath + "." + attributeName,
                    new ExpressionParameter(attributeName)));
        }
        // 元の検索順序から、originに対する本テーブルの上界条件を作成して追加
        expression = expression.andExp(orderingsToQualifer(query.getOrderings(), toOriginPath));
        query.setQualifier(expression);

        // 一つ後だけを取得
        query.setFetchLimit(1);

        return query;
    }

実際に「次の一件」を取得する処理

public class WeblogEntryLogic {
    private DataContext dataContext = DataContext.createDataContext();
...
    public WeblogEntries findNextByIdentifier(
            String weblogOwner, Map<String, Object> identifier) {
        SelectQuery baseQuery = createCollectionQuery();
        // 「次の一件」を取得する条件
        SelectQuery nextQuery =
            QueryUtils.createNextQuery(baseQuery, dataContext.getEntityResolver(), "toSelf");

        // 入力値をパラメータとして設定
        Map<String, Object> queryParameters = new HashMap<String, Object>();
        queryParameters.put("weblogOwner", weblogOwner);
        queryParameters.putAll(identifier);
        SelectQuery query = nearestQuery.queryWithParameters(queryParameters);


        List<WeblogEntries>list = dataContext.performQuery(query);
        return list.isEmpty() ? null : list.get(0);
    }

呼んでみる。

    int weblogEntryId; // URLなどから取得

    Map<String, Object> identifier = new HashMap<String, Object>();
    identifier.put("id", weblogEntryId); 
    WeblogEntries next = findNextByIdentifier("petazzo", identifier);

    System.out.printf("%4d: %tF, %s\n", next.getId(), next.getPubDate(), next.getTitle());

結果(weblogEntryId=17の場合)

   9: 2011-03-31, この春始めたいこと

「前の一件」を取得する処理は、検索条件全体を逆順にすれば良い。
ついでに共通で使えるように書き直す。

public class WeblogEntryLogic {
    private DataContext dataContext = DataContext.createDataContext();
...
    // 次の一件を取得する
    private WeblogEntries findNearstByIdentifier(
            SelectQuery baseQuery, String weblogOwner, Map<String, Object> identifier) {
        SelectQuery nearestQuery =
            QueryUtils.createNextQuery(baseQuery, dataContext.getEntityResolver(), "toSelf");

        // 入力値をパラメータとして設定
        Map<String, Object> queryParameters = new HashMap<String, Object>();
        queryParameters.put("weblogOwner", weblogOwner);
        queryParameters.putAll(identifier);
        SelectQuery query = nearestQuery.queryWithParameters(queryParameters);


        List<WeblogEntries>list = dataContext.performQuery(query);
        return list.isEmpty() ? null : list.get(0);
    }
    // 指定したレコードの次の1件を取得する
    public WeblogEntries findNextByIdentifier(
            String weblogOwner, Map<String, Object> identifier) {
        SelectQuery baseQuery = createCollectionQuery();

        return findNearstByIdentifier(baseQuery, weblogOwner, identifier);
    }
    // 指定したレコードの前の1件を取得する
    public WeblogEntries findPreviousByIdentifier(
            String weblogOwner, Map<String, Object> identifier) {
        SelectQuery baseQuery = createCollectionQuery();
        // ソート順を逆順に変換
        QueryUtils.reverseOrder(baseQuery);

        return findNearstByIdentifier(baseQuery, weblogOwner, identifier);
    }

呼んでみる。

    int weblogEntryId; // URLなどから取得

    Map<String, Object> identifier = new HashMap<String, Object>();
    identifier.put("id", weblogEntryId); 
    WeblogEntries previous = findPreviousByIdentifier("petazzo", identifier);

    System.out.printf("%4d: %tF, %s\n",
        previous.getId(), previous.getPubDate(), previous.getTitle());

結果(weblogEntryId=17の場合)

  12: 2011-04-14, 心に残った本

まとめ

今回作ったプログラムでは、実装クラスをWeblogEntryLogicとQueryUtilsに分けている。全ソースコードを振り返れば分かるとおり、QueryUtils側には、エンティティに固有のカラム名や検索条件などに依存している部分は全く無い。*4 つまりエンティティや検索条件が変わっても、QueryUtilsについては再利用出来る。

このように、モデルベースのO/Rマッパを使えば、(一覧から詳細を取得するような)定形のデータアクセスについては、いちいち検索条件を個別に作成する必要をなくすることが出来る。

例にある「次の一件を取得する」にしても、最初から項番や前後のリンク情報を持たせておくとか、カーソルを使うとか、人によっていろいろ実現方法があると思うけど、その場合でも同じ実現方法であれば同じように検索条件を動的に組み立てて対応することが出来る。

まったくSQL書かなくても良くなるとか思わないけど、手を抜けるところはなるべく抜くようにすれば幸せになれるんじゃないかと思う。

*1:WebObjectsのEOFとか

*2:以下も含めてNULLの考慮が抜けてるけど本質的でないので今回はパス

*3:POJOにする方法もあるかもしれないけどここでは取り上げない

*4:パラメータが被る場合があるかもしれないけど少し修正したものを作れば永久に回避出来る。