Top > MyPage
 

PostgresのViewに対するDTO

PostgresのVIEW

VIEWとは各テーブルから任意の条件で抽出したデータを持つ仮想的なテーブルといったものである。

次のようなSQLでcreateする。(今回、使った例である)

    CREATE VIEW staff_role AS
      SELECT staffs.id AS staff_id, 
             roles.id AS roles_id, 
             staffs.staff_name, 
             roles.role_name, 
             roles.display_name, 
             roles.group_num
      FROM staffs staffs, authorities at, roles roles
      WHERE staffs.id = at.staff_id 
      AND at.authority = roles.role_name;

これはMPプロジェクトのDBを拝借して、STAFF_ROLEというVIEWを作った例である。この例で使ったテーブルのsqlを次に示す。

CREATE TABLE STAFFS (
       ID INTEGER DEFAULT nextval(('SEQ_STAFFS'::text)::regclass) NOT NULL
     , STAFF_NAME VARCHAR(20) NOT NULL
     , PASSWORD VARCHAR(32) NOT NULL
     , BIRTH_DAY DATE
     , NOTE VARCHAR(256)
     , NAME VARCHAR(96)
     , NAME2 VARCHAR(96)
     , NAME_READ VARCHAR(96)
     , NAME_READ2 VARCHAR(96)
     , ENABLED BOOLEAN NOT NULL
     , PASSWORD_CHANGE_DATE DATE
     , LAST_LOGIN_DATE DATE
     , RECORD_CREATED_DATE DATE NOT NULL
     , RECORD_CREATED_STAFF_NAME VARCHAR(20) NOT NULL
     , LAST_MODIFIED_DATE DATE NOT NULL
     , LAST_MODIFIED_STAFF_NAME VARCHAR(20) NOT NULL
     , RESERVE1 VARCHAR(256)
     , RESERVE2 VARCHAR(256)
     , RESERVE3 VARCHAR(256)
     , PRIMARY KEY (ID)
);

CREATE TABLE AUTHORITIES (
       ID INTEGER DEFAULT nextval(('SEQ_AUTHORITIES'::text)::regclass) NOT NULL
     , STAFF_NAME VARCHAR(20) NOT NULL
     , AUTHORITY VARCHAR(20) NOT NULL
     , STAFF_ID INTEGER
     , RECORD_CREATED_DATE DATE NOT NULL
     , RECORD_CREATED_STAFF_NAME VARCHAR(20) NOT NULL
     , LAST_MODIFIED_DATE DATE NOT NULL
     , LAST_MODIFIED_STAFF_NAME VARCHAR(20) NOT NULL
     , RESERVE1 VARCHAR(256)
     , RESERVE2 VARCHAR(256)
     , RESERVE3 VARCHAR(256)
     , PRIMARY KEY (ID)
--     , CONSTRAINT FK_AUTHORITIES_1 FOREIGN KEY (STAFF_ID)
--                  REFERENCES STAFFS (ID)
);

CREATE TABLE ROLES (
       ID INTEGER DEFAULT nextval(('SEQ_ROLES'::text)::regclass) NOT NULL
     , ROLE_NAME VARCHAR(20) NOT NULL
     , DISPLAY_NAME VARCHAR(90) NOT NULL
     , GROUP_NUM INT2 NOT NULL
     , ENABLED BOOLEAN NOT NULL
     , RECORD_CREATED_DATE DATE NOT NULL
     , RECORD_CREATED_STAFF_NAME VARCHAR(20) NOT NULL
     , LAST_MODIFIED_DATE DATE NOT NULL
     , LAST_MODIFIED_STAFF_NAME VARCHAR(20) NOT NULL
     , RESERVE1 VARCHAR(256)
     , RESERVE2 VARCHAR(256)
     , RESERVE3 VARCHAR(256)
     , PRIMARY KEY (ID)
);
  • AUTHORITIES.STAFF_ID = STAFFS.ID
  • AUTHORITIES.AUTHORITY = ROLES.ROLE_NAME

という関係がある。あと、AUTHORITIES.STAFF_NAME = STAFFS.STAFF_NAMEという関係もあるが、今回は関係ない。

DTOとマッピング

VIEW STAFF_ROLEに対応したDTOとして以下のようなクラスを作成した。わかりやすくするため、クラス変数だけ残して、セッターや、ゲッターは省略している。

    
public class StaffRoleView implements Serializable {

    
    //staffsのidとrolesのidで複合キーにする
    private StaffRoleViewId staffRoleViewId;
    
    private String staffName;
    
    private String roleName;
    
    private String displayName;
    
    private Integer groupNum;
}

StaffRoleView内で使用しているStaffRoleViewIdは次のようになっている。

public class StaffRoleViewId implements Serializable {

    /**
     * serialVersionUID
     */
    private static final long serialVersionUID = -7723263729737202134L;

    /**
     * デフォルトコンストラクタ
     */
    public StaffRoleViewId() {
    }
    
    private Integer staffId;

    private Integer rolesId;

    /**
     * @return the staffId
     * @hibernate.key-property column="STAFF_ID"
     */
    public Integer getStaffId() {
        return staffId;
    }

    /**
     * @param staffId the staffId to set
     */
    public void setStaffId(Integer staffId) {
        this.staffId = staffId;
    }

    /**
     * @return the rolesId
     * @hibernate.key-property column="ROLES_ID"
     */
    public Integer getRolesId() {
        return rolesId;
    }

    /**
     * @param rolesId the rolesId to set
     */
    public void setRolesId(Integer rolesId) {
        this.rolesId = rolesId;
    }
    
    @Override
    public boolean equals(Object o) {
        if (o == null) {
            return false;
        }
        if (this == o) {
            return true;
        }
        if (!(o instanceof StaffRoleViewId)) {
            return false;
        }
        final StaffRoleViewId staffRoleViewId = (StaffRoleViewId) o;
        if (!this.staffId.equals(staffRoleViewId.getStaffId())) {
            return false;
        }
        if (!this.rolesId.equals(staffRoleViewId.getRolesId())) {
            return false;
        }
        return true;
    }

    @Override
    public int hashCode() {
        int hash = 1;
        hash = hash * 31 + this.staffId.hashCode();
        hash = hash * 31 + this.rolesId.hashCode();
        return hash;
    }
    

}

STAFF_ROLEを、STAFF_IDとROLES_IDの複合キーを持つテーブルとみなして、このようなDTOにした。

マッピングは次のようになる。

    
<hibernate-mapping>
  <class table="STAFF_ROLE" name="com.chikkun.common.login.database.StaffRoleView">
    <composite-id name="staffRoleViewId" class="com.chikkun.common.login.database.StaffRoleViewId">
      <key-property name="staffId" column="STAFF_ID"/>
      <key-property name="rolesId" column="ROLES_ID"/>
    </composite-id>
    <property name="staffName" column="STAFF_NAME"/>
    <property name="roleName" column="ROLE_NAME"/>
    <property name="displayName" column="DISPLAY_NAME"/>
    <property name="groupNum" column="GROUP_NUM"/>
  </class>
</hibernate-mapping>

データを取得して比較

作成したDTOを使う、次のようなメソッドを作成した。このメソッドで2061件のレコードがヒットする条件で実行して、要した時間を計測した。

    
    public List<StaffRoleView> findByRoleName(String roleName) {
        List<StaffRoleView> result = null;
        result = getHibernateTemplate().find(
                "FROM StaffRoleView AS sv " +
                " WHERE sv.roleName = ? " +
                "ORDER BY sv.staffRoleViewId.staffId,sv.staffRoleViewId.rolesId",roleName);
        return result;
    }

実行した結果、約12.5秒かかった。今度は、同等の情報を普通にテーブルを結合して探すHQLを考えて、次のようなメソッドを作成して、同じように計測した。

    
    public List<Map> findByRoleNameSelect(String roleName) {
        List<Map> result = null;
        result = getHibernateTemplate().find(
"SELECT new Map( staff.id AS staffId, role.id AS roleId, staff.staffName AS staffName, role.roleName AS roleName,role.roleDisplayName AS \
    displayName, role.groupNum AS groupNum )" +
                " FROM Staff AS staff, StaffRole AS sr, Role AS role" +
                " WHERE staff.id = sr.staff.id" +
                " AND sr.roleName = role.roleName" +
                " AND sr.roleName = ?" +
                "ORDER BY staff.id,role.id",roleName);
        return result;
    }

中身は同等であるが、返しているものが違う。さきほどはList<StaffRoleView>で、今度はList<Map>。要した時間は、約8.4秒であり、こちらの方が大分はやい。

hibernateを使った場合、DBから取得してきてから、インスタンス化するところで大きなコストがかかる。StaffRoleViewは複合キーを表すためのクラスも抱えているため、その分のコストがかさみ、このような結果になったのだと思われる。

このことから、高速化のためにVIEWを使おうという場合、複合キーにならないよう気をつけるべきであるということがわかる。とおもったが、この後で、複合キーをやめて、計測してみたが、あまり早くならなかった。

VIEWを作り直す

上の反省を生かして、今度は複合キーにならないように、VIEWをつくる。

    CREATE VIEW staff_role AS
      SELECT at.id AS id,
             staffs.id AS staff_id, 
             roles.id AS roles_id, 
             staffs.staff_name, 
             roles.role_name, 
             roles.display_name, 
             roles.group_num
      FROM staffs staffs, authorities at, roles roles
      WHERE staffs.id = at.staff_id 
      AND at.authority = roles.role_name;

こんどは、AUTHORITIESテーブルのIDをVIEWのキーとして扱うことにする。

DTOとマッピングを示す。

    
public class StaffRoleView implements Serializable {

    
    private Integer id;
    
    private Integer staffId;

    private Integer rolesId;
        
    private String staffName;
    
    private String roleName;
    
    private String displayName;
    
    private Integer groupNum;
}
    
<hibernate-mapping>
  <class table="STAFF_ROLE" name="com.chikkun.common.login.database.StaffRoleView">
    <id unsaved-value="null" name="id" type="java.lang.Integer" column="ID">
      <generator class="assigned"/>
    </id>
    <property name="staffName" column="STAFF_NAME"/>
    <property name="roleName" column="ROLE_NAME"/>
    <property name="displayName" column="DISPLAY_NAME"/>
    <property name="groupNum" column="GROUP_NUM"/>
    <property name="staffId" column="STAFF_ID"/>
    <property name="rolesId" column="ROLES_ID"/>
  </class>
</hibernate-mapping>
    
    
<hibernate-mapping>
  <class table="STAFF_ROLE" name="com.chikkun.common.login.database.StaffRoleView">
    <id unsaved-value="null" name="id" type="java.lang.Integer" column="ID">
      <generator class="assigned"/>
    </id>
    <property name="staffName" column="STAFF_NAME"/>
    <property name="roleName" column="ROLE_NAME"/>
    <property name="displayName" column="DISPLAY_NAME"/>
    <property name="groupNum" column="GROUP_NUM"/>
    <property name="staffId" column="STAFF_ID"/>
    <property name="rolesId" column="ROLES_ID"/>
  </class>
</hibernate-mapping>
    

そして、また、同じメソッドを使って計測した。

    
    public List<StaffRoleView> findByRoleName(String roleName) {
        List<StaffRoleView> result = null;
        result = getHibernateTemplate().find(
                "FROM StaffRoleView AS sv " +
                " WHERE sv.roleName = ? " +
                "ORDER BY sv.staffRoleViewId.staffId,sv.staffRoleViewId.rolesId",roleName);
        return result;
    }

その結果、要した時間は約11.3秒。わずかながら早くなっている。

そこで、Mapを使う方も、selectするカラムを増やして実行してみた。

    
    public List<Map> findByRoleNameSelect(String roleName) {
        List<Map> result = null;
        result = getHibernateTemplate().find(
"SELECT new Map( sr.id AS id, staff.id AS staffId, role.id AS roleId, staff.staffName AS staffName, role.roleName AS \
    roleName,role.roleDisplayName AS displayName, role.groupNum AS groupNum )" +
                " FROM Staff AS staff, StaffRole AS sr, Role AS role" +
                " WHERE staff.id = sr.staff.id" +
                " AND sr.roleName = role.roleName" +
                " AND sr.roleName = ?" +
                "ORDER BY staff.id,role.id",roleName);
        return result;
    }

結果、約9.0秒だった。前よりも差が縮まったので、複合キーではなくしたことで、軽くはなったのではなかろうか。

次に、普通にテーブルを結合してselectして、StaffRoleViewクラスに値を入れてListで返すようなメソッドを試してみた。(この方法を使うためには、そのためのコンストラクタをDTOに作る必要がある)

    
    public List<StaffRoleView> findByRoleNameSelect2(String roleName) {
        List<StaffRoleView> result = null;
        result = getHibernateTemplate().find(
"SELECT new StaffRoleView( sr.id AS id, staff.id AS staffId, role.id AS roleId, staff.staffName AS staffName, role.roleName AS \
    roleName,role.roleDisplayName AS displayName, role.groupNum AS groupNum )" +
                " FROM Staff AS staff, StaffRole AS sr, Role AS role" +
                " WHERE staff.id = sr.staff.id" +
                " AND sr.roleName = role.roleName" +
                " AND sr.roleName = ?" +
                "ORDER BY staff.id,role.id",roleName);
        return result;
    }
    

このメソッドの実行結果は、約9.1秒。VIEWのときよりはやい。

使いどころはよく考えなければならないということが、よくわかる結果となってしまった。

VIEWのつかいどころ

以上から、VIEWは速度を上げるため使用するのではなく、プログラム的に使い勝手をよくするために使用するのであるということがわかる。

上の例からわかるように、VIEWを使った方が、明らかにHQLが短くなり、簡単になっている。このほうが、後から見たときもスッキリしていてわかりやすいだろう。

さらにいえば、一度に取得する行数が少なければ、VIEWを使っても差が出ないので、複数のテーブルからパラメータを取得する必要があり、かつ、取得する行数は少ないといった場合にVIEWを使うととても効果的だろう。