Spring Boot上でマテリアライズドビューを利用してみた

今回は、Spring Boot上でマテリアライズドビューを利用するサンプルプログラムを作成してみたので、共有する。

マテリアライズドビューを利用すると、データベースでSELECTした結果をテーブルとして保持できるため、複雑な集計処理の高速化やデータ整合性の確保を簡単に実現しつつ、SELECT処理を効率的に行うことができる。

前提条件

下記記事の「マテリアライズドビューの作成」が完了していること。

マテリアライズドビューを作成してみたマテリアライズドビューを利用すると、データベースでSELECTした結果をテーブルとして保持できるため、複雑な集計処理の高速化やデータ整合...

また、下記記事の実装が完了していること。

Spring Bootで全角チェック処理を行う独自アノテーションを作成してみたSpring Bootの独自アノテーションで、特定のフィールドに対するチェック処理も実装することができる。今回は、特定のフィールドの全角...

サンプルプログラムの作成

作成したサンプルプログラムの構成は以下の通り。

なお、上記の赤枠は、前提条件のプログラムから変更したプログラムである。

UserDataMapperクラスの内容は以下の通りで、マテリアライズドビュー(mv_user_data)をリフレッシュするメソッド「refreshMvUserData」を追加している。

package com.example.demo;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.springframework.data.domain.Pageable;
import java.util.Collection;

@Mapper
public interface UserDataMapper {

    /**
     * ユーザーデータテーブル(user_data)から検索条件に合うデータを取得する
     * @param searchForm 検索用Formオブジェクト
     * @param pageable ページネーションオブジェクト
     * @return ユーザーデータテーブル(user_data)の検索条件に合うデータ
     */
    Collection<UserData> findBySearchForm(
            @Param("searchForm") SearchForm searchForm
          , @Param("pageable") Pageable pageable);

    /**
     * 指定したIDをもつユーザーデータテーブル(user_data)のデータを取得する
     * @param id ID
     * @return ユーザーデータテーブル(user_data)の指定したIDのデータ
     */
    UserData findById(Long id);

    /**
     * 指定したIDをもつユーザーデータテーブル(user_data)のデータを削除する
     * @param id ID
     */
    void deleteById(Long id);

    /**
     * 指定したユーザーデータテーブル(user_data)のデータを追加する
     * @param userData ユーザーデータテーブル(user_data)の追加データ
     */
    void create(UserData userData);

    /**
     * 指定したユーザーデータテーブル(user_data)のデータを更新する
     * @param userData ユーザーデータテーブル(user_data)の更新データ
     */
    void update(UserData userData);

    /**
     * ユーザーデータテーブル(user_data)の最大値IDを取得する
     * @return ユーザーデータテーブル(user_data)の最大値ID
     */
    long findMaxId();

    /**
     * マテリアライズドビュー(mv_user_data)をリフレッシュする
     */
    void refreshMvUserData();
}

また、UserDataMapperのXMLファイルの内容は以下の通り。

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.demo.UserDataMapper">
    <resultMap id="userDataResultMap" type="com.example.demo.UserData" >
        <id column="id" property="id" jdbcType="BIGINT" />
        <result column="name" property="name" jdbcType="VARCHAR" />
        <result column="birthY" property="birthY" jdbcType="VARCHAR" />
        <result column="birthM" property="birthM" jdbcType="VARCHAR" />
        <result column="birthD" property="birthD" jdbcType="VARCHAR" />
        <result column="sex" property="sex" jdbcType="VARCHAR" />
        <result column="memo" property="memo" jdbcType="VARCHAR" />
        <result column="sex_value" property="sex_value" jdbcType="VARCHAR" />
    </resultMap>
    <select id="findBySearchForm" resultMap="userDataResultMap">
        SELECT u.id, u.name, u.birth_year as birthY, u.birth_month as birthM
               , u.birth_day as birthD, u.sex, u.memo, u.sex_value
        FROM
          ( SELECT
                u1.id, u1.name, u1.birth_year, u1.birth_month, u1.birth_day
              , u1.sex, u1.memo, m.sex_value
              , ROW_NUMBER() OVER (ORDER BY u1.id) AS rn
            FROM mv_user_data u1, M_SEX m
            WHERE u1.sex = m.sex_cd
            <if test="searchForm.searchName != null and searchForm.searchName != ''">
                AND u1.name like '%' || #{searchForm.searchName} || '%'
            </if>
            <if test="searchForm.fromBirthYear != null 
                      and searchForm.fromBirthYear != ''">
                AND #{searchForm.fromBirthYear} 
                   || lpad(#{searchForm.fromBirthMonth}, 2, '0')
                   || lpad(#{searchForm.fromBirthDay}, 2, '0')
               &lt;= u1.birth_year || lpad(u1.birth_month, 2, '0') 
                   || lpad(u1.birth_day, 2, '0')
            </if>
            <if test="searchForm.toBirthYear != null and searchForm.toBirthYear != ''">
                AND u1.birth_year || lpad(u1.birth_month, 2, '0') 
                      || lpad(u1.birth_day, 2, '0')
                   &lt;= #{searchForm.toBirthYear} 
                        || lpad(#{searchForm.toBirthMonth}, 2, '0')
                        || lpad(#{searchForm.toBirthDay}, 2, '0')
            </if>
            <if test="searchForm.searchSex != null and searchForm.searchSex != ''">
                AND u1.sex = #{searchForm.searchSex}
            </if>
            ORDER BY u1.id
          ) u
        <if test="pageable != null and pageable.pageSize > 0">
            <where>
                u.rn between #{pageable.offset} 
                     and (#{pageable.offset} + #{pageable.pageSize} - 1)
            </where>
        </if>
    </select>
    <select id="findById" resultMap="userDataResultMap">
        SELECT id, name, birth_year as birthY, birth_month as birthM
             , birth_day as birthD, sex, memo
        FROM mv_user_data
        WHERE id = #{id}
    </select>
    <delete id="deleteById" parameterType="java.lang.Long">
        DELETE FROM USER_DATA WHERE id = #{id}
    </delete>
    <insert id="create" parameterType="com.example.demo.UserData">
        INSERT INTO USER_DATA ( id, name, birth_year, birth_month
              , birth_day, sex, memo )
        VALUES (#{id}, #{name}, #{birthY}, #{birthM}
              , #{birthD}, #{sex}, #{memo,jdbcType=VARCHAR})
    </insert>
    <update id="update" parameterType="com.example.demo.UserData">
        UPDATE USER_DATA SET name = #{name}, birth_year = #{birthY}
            , birth_month = #{birthM}, birth_day = #{birthD}
            , sex = #{sex}, memo = #{memo,jdbcType=VARCHAR}
        WHERE id = #{id}
    </update>
    <select id="findMaxId" resultType="long">
        SELECT NVL(max(id), 0) FROM mv_user_data
    </select>
    <select id="refreshMvUserData" statementType="CALLABLE">
        {call dbms_mview.refresh('mv_user_data')}
    </select>
</mapper>

idが「findBySearchForm」「findById」「findMaxId」であるSQL文は、「user_data」テーブルから参照していた部分を、マテリアライズドビュー「mv_user_data」から参照するように修正している。また、idが「refreshMvUserData」であるSQL文では、マテリアライズドビューのリフレッシュを行う処理を呼び出している。

さらに、サービスクラスの内容は以下の通りで、「deleteById」「createOrUpdate」の各メソッド内で、user_dataテーブルの更新後に、マテリアライズドビューのリフレッシュを行う処理を追加している。

package com.example.demo;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.data.domain.Sort;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.data.domain.Pageable;
import org.springframework.util.StringUtils;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;

@Service
public class DemoServiceImpl implements DemoService{

    /**
     * ユーザーデータテーブル(user_data)へアクセスするマッパー
     */
    @Autowired
    private UserDataMapper mapper;

    /**
     * 1ページに表示する行数(application.propertiesから取得)
     */
    @Value("${demo.list.pageSize}")
    private String listPageSize;

    /**
     * {@inheritDoc}
     */
    @Override
    public List<DemoForm> demoFormList(SearchForm searchForm, Pageable pageable) {
        List<DemoForm> demoFormList = new ArrayList<>();
        //ユーザーデータテーブル(user_data)から検索条件に合うデータを取得する
        Collection<UserData> userDataList 
            = mapper.findBySearchForm(searchForm, pageable);
        for (UserData userData : userDataList) {
            demoFormList.add(getDemoForm(userData));
        }
        return demoFormList;
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public DemoForm findById(String id) {
        Long longId = stringToLong(id);
        UserData userData = mapper.findById(longId);
        return getDemoForm(userData);
    }

    /**
     * {@inheritDoc}
     */
    @Override
    @Transactional(readOnly = false)
    public void deleteById(String id){
        Long longId = stringToLong(id);
        //指定したIDのデータを削除
        mapper.deleteById(longId);
        //マテリアライズドビューのリフレッシュ
        mapper.refreshMvUserData();
    }

    /**
     * {@inheritDoc}
     */
    @Override
    @Transactional(readOnly = false)
    public void createOrUpdate(DemoForm demoForm){
        //更新・追加処理を行うエンティティを生成
        UserData userData = getUserData(demoForm);
        //追加・更新処理
        if(demoForm.getId() == null){
            userData.setId(mapper.findMaxId() + 1);
            mapper.create(userData);
        }else{
            mapper.update(userData);
        }
        //マテリアライズドビューのリフレッシュ
        mapper.refreshMvUserData();
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public Pageable getPageable(int pageNumber){
        Pageable pageable = new Pageable() {
            @Override
            public int getPageNumber() {
                //現在ページ数を返却
                return pageNumber;
            }

            @Override
            public int getPageSize() {
                //1ページに表示する行数を返却
                //listPageSizeは、本プログラムの先頭に定義している
                return Integer.parseInt(listPageSize);
            }

            @Override
            public int getOffset() {
                //表示開始位置を返却
                //例えば、1ページに2行表示する場合の、2ページ目の表示開始位置は
                //(2-1)*2+1=3 で計算される
                return ((pageNumber - 1) * Integer.parseInt(listPageSize) + 1);
            }

            @Override
            public Sort getSort() {
                //ソートは使わないのでnullを返却
                return null;
            }
        };
        return pageable;
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public int getAllPageNum(SearchForm searchForm) {
        //1ページに表示する行数を取得
        int listPageSizeNum = Integer.parseInt(listPageSize);
        if(listPageSizeNum == 0){
            return 1;
        }
        //一覧画面に表示する全データを取得
        //第二引数のpageableにnullを設定することで、一覧画面に表示する
        //全データが取得できる
        Collection<UserData> userDataList = mapper.findBySearchForm(searchForm, null);
        //全ページ数を計算
        //例えば、1ページに2行表示する場合で、全データ件数が5の場合、
        //(5+2-1)/2=3 と計算される
        int allPageNum = (userDataList.size() + listPageSizeNum - 1) / listPageSizeNum;
        return allPageNum == 0 ? 1 : allPageNum;
    }

    /**
     * DemoFormオブジェクトに引数のユーザーデータの各値を設定する
     * @param userData ユーザーデータ
     * @return DemoFormオブジェクト
     */
    private DemoForm getDemoForm(UserData userData){
        if(userData == null){
            return null;
        }
        DemoForm demoForm = new DemoForm();
        demoForm.setId(String.valueOf(userData.getId()));
        demoForm.setName(userData.getName());
        demoForm.setBirthYear(String.valueOf(userData.getBirthY()));
        demoForm.setBirthMonth(String.valueOf(userData.getBirthM()));
        demoForm.setBirthDay(String.valueOf(userData.getBirthD()));
        demoForm.setSex(userData.getSex());
        demoForm.setMemo(userData.getMemo());
        demoForm.setSex_value(userData.getSex_value());
        return demoForm;
    }

    /**
     * UserDataオブジェクトに引数のフォームの各値を設定する
     * @param demoForm DemoFormオブジェクト
     * @return ユーザーデータ
     */
    private UserData getUserData(DemoForm demoForm){
        UserData userData = new UserData();
        if(!StringUtils.isEmpty(demoForm.getId())){
            userData.setId(Long.valueOf(demoForm.getId()));
        }
        userData.setName(demoForm.getName());
        userData.setBirthY(Integer.valueOf(demoForm.getBirthYear()));
        userData.setBirthM(Integer.valueOf(demoForm.getBirthMonth()));
        userData.setBirthD(Integer.valueOf(demoForm.getBirthDay()));
        userData.setSex(demoForm.getSex());
        userData.setMemo(demoForm.getMemo());
        userData.setSex_value(demoForm.getSex_value());
        return userData;
    }

    /**
     * 引数の文字列をLong型に変換する
     * @param id ID
     * @return Long型のID
     */
    private Long stringToLong(String id){
        try{
            return Long.parseLong(id);
        }catch(NumberFormatException ex){
            return null;
        }
    }

}

その他のソースコード内容は、以下のサイトを参照のこと。
https://github.com/purin-it/java/tree/master/spring-boot-materialized-view/demo

サンプルプログラムの実行結果

画面上でのサンプルプログラム実行結果は以下の通り。

SQLログ出力内容をカスタマイズしてみた今回は、SQLログ出力内容をカスタマイズし、SQLの実行時間や呼出メソッドをSQLログに出力してみたので、そのサンプルプログラムを共有す...

また、データ追加を行った場合のログ出力内容は以下の通りで、追加後にマテリアライズドビューの更新処理が実施されている。

要点まとめ

• マテリアライズドビューのデータを取得するには、select文のfrom句で直接、マテリアライズドビューを指定すればよい。
• マテリアライズドビューのリフレッシュするには、PL/SQLのdbms_mview.refresh句を利用すればよい。