Spring Boot DB連携

Spring BootのMyBatis上でPL/SQLを呼び出してみた

 

今回は、Spring BootのMyBatis上でPL/SQLプログラムを呼び出すサンプルプログラムを作成してみたので、共有する。以前作成したプログラムの、user_dataテーブルへのデータ作成・更新・削除処理をストアドプログラムに変更している。

前提条件

下記記事の実装が完了していること

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

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

今回作成したPL/SQLプログラムの内容は以下の通りで、user_data_sql パッケージとuser_data_sql パッケージ本体を作成し、user_dataテーブルへのデータ作成・更新・削除処理を記載している。

CREATE OR REPLACE PACKAGE user_data_sql AS
   -- ユーザーデータに追加するプロシージャ
   PROCEDURE insert_data (
       p_id IN user_data.id%TYPE
     , p_name IN user_data.name%TYPE
     , p_birth_year IN user_data.birth_year%TYPE
     , p_birth_month IN user_data.birth_month%TYPE
     , p_birth_day IN user_data.birth_day%TYPE
     , p_sex IN user_data.sex%TYPE
     , p_memo IN user_data.memo%TYPE
   );
   -- ユーザーデータを更新するプロシージャ
   PROCEDURE update_data (
       p_id IN user_data.id%TYPE
     , p_name IN user_data.name%TYPE
     , p_birth_year IN user_data.birth_year%TYPE
     , p_birth_month IN user_data.birth_month%TYPE
     , p_birth_day IN user_data.birth_day%TYPE
     , p_sex IN user_data.sex%TYPE
     , p_memo IN user_data.memo%TYPE
   );
   -- ユーザーデータから削除するプロシージャ
   PROCEDURE delete_data (
       p_id IN user_data.id%TYPE
   );
END user_data_sql;
/
CREATE OR REPLACE PACKAGE BODY user_data_sql AS
    -- ユーザーデータに追加するプロシージャ
    PROCEDURE insert_data (
       p_id IN user_data.id%TYPE
     , p_name IN user_data.name%TYPE
     , p_birth_year IN user_data.birth_year%TYPE
     , p_birth_month IN user_data.birth_month%TYPE
     , p_birth_day IN user_data.birth_day%TYPE
     , p_sex IN user_data.sex%TYPE
     , p_memo IN user_data.memo%TYPE
   ) IS 
   BEGIN
     -- ユーザーデータテーブルに引数のデータを追加
     -- ユーザーデータテーブルへのデータ追加が正常に行えた場合はコミット、
     -- 何らかの例外が発生した場合はロールバック
     insert into user_data 
        ( id, name, birth_year, birth_month
        , birth_day, sex, memo )
     values (p_id, p_name, p_birth_year, p_birth_month
        , p_birth_day, p_sex, p_memo );
     commit;
   EXCEPTION
     WHEN OTHERS THEN
     rollback;
   END;
   -- ユーザーデータを更新するプロシージャ
   PROCEDURE update_data (
       p_id IN user_data.id%TYPE
     , p_name IN user_data.name%TYPE
     , p_birth_year IN user_data.birth_year%TYPE
     , p_birth_month IN user_data.birth_month%TYPE
     , p_birth_day IN user_data.birth_day%TYPE
     , p_sex IN user_data.sex%TYPE
     , p_memo IN user_data.memo%TYPE
   ) IS 
   BEGIN
     -- ユーザーデータテーブルの引数のデータを更新
     -- ユーザーデータテーブルのデータ更新が正常に行えた場合はコミット、
     -- 何らかの例外が発生した場合はロールバック
     update user_data set 
        name = p_name, birth_year = p_birth_year, 
        birth_month = p_birth_month, birth_day = p_birth_day, 
        sex = p_sex, memo = p_memo
     where id = p_id;
     commit;
   EXCEPTION
     WHEN OTHERS THEN
     rollback;
   END;
   -- ユーザーデータから削除するプロシージャ
   PROCEDURE delete_data (
       p_id IN user_data.id%TYPE
   ) IS
   BEGIN
     -- ユーザーデータテーブルの引数のidのデータを削除
     -- ユーザーデータテーブルのデータ削除が正常に行えた場合はコミット、
     -- 何らかの例外が発生した場合はロールバック
     delete from user_data where id = p_id;
     commit;
   EXCEPTION
     WHEN OTHERS THEN
     rollback;
   END;
END user_data_sql;
/



また、上記PL/SQLプログラムのコンパイルは、下図のように実行している。
user_data_packageコンパイル

user_data_package_bodyコンパイル



さらに、Spring Bootのサンプルプログラムの構成は以下の通り。
サンプルプログラムの構成

上記の赤枠「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 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 USER_DATA
        WHERE id = #{id}
    </select>
    <select id="deleteById" parameterType="java.lang.Long" statementType="CALLABLE">
        {call user_data_sql.delete_data(#{id})}
    </select>
    <select id="create" parameterType="com.example.demo.UserData" 
            statementType="CALLABLE">
        {call user_data_sql.insert_data(
             #{id}, #{name}, #{birthY}, #{birthM}, #{birthD}
           , #{sex}, #{memo,jdbcType=VARCHAR})}
    </select>
    <select id="update" parameterType="com.example.demo.UserData" 
            statementType="CALLABLE">
        {call user_data_sql.update_data(
             #{id}, #{name}, #{birthY}, #{birthM}, #{birthD}
           , #{sex}, #{memo,jdbcType=VARCHAR})}
    </select>
    <select id="findMaxId" resultType="long">
        SELECT NVL(max(id), 0) FROM USER_DATA
    </select>
</mapper>

deleteById・create・update内で、「call (パッケージ名).(プロシージャ名)」という形で、PL/SQLプログラムの呼び出しを行っている。また、PL/SQLプログラムを呼び出すタグはselectタグとし、「statementType=”CALLABLE”」を付与している。



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

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

画面上でのサンプルプログラム実行結果は以下の記事と同じ結果となる。

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

また、ログ出力内容は、データ追加を行った場合は以下の通り。
ログ出力内容

要点まとめ

  • MyBatis上でPL/SQLプログラムを呼び出すためには、MyBatisのXMLプログラム内で、selectタグで「statementType=”CALLABLE”」を付与した上で、「call (PL/SQLプログラム名)」という形で呼び出せばよい。
RELATED POST