Spring Boot×MyBatis×FreeMarker を使って、データベースの接続先を 安全に変更します。 中尾正剛

自己紹介 2011/04 中小SIer 2015/01 上場Web系 2020/06 ベンチャーWeb系 2020/10 エキサイト株式会社 入社 ...今に至る エキサイト株式会社 メディアプラットフォーム事業部 システムグループ 　中尾正剛 趣味：ロードバイク、ボルダリング

はじめに エキサイトブログを2023/3/31 にシステム全体 のリビルドが完了しました。 Spring Boot×MyBatis×FreeMarkerを使っ て、DBをSQL Server -> PostgreSQL に変更し ました。 切り替えを安全に行うために工夫した点があり ますので、その説明をさせていただきます。

01 エキサイトブログのリビルド 02 データ移行 03 SQL ServerとPostgreSQL 04 MyBatis x FreeMarker 05 目次 06 まとめ アプリケーション移行

01 エキサイトブログの リビルド

Azure -> AWS に SQL Server -> PostgreSQL に 社内で主に使われているクラウ ドサービスはAWSであるため コスト削減のため リビルドの目的 01 02

02 アプリケーションの リビルド

PHPで動いているAPIをSpring Bootにします 3. ストアドをAPIに VMで動いているWebサーバーをコンテナに します WebサーバからDB接続を止めます ストアドプロシージャーをSpring Bootにしま す アプリケーション移行 APIをSpring Boot(Java)に 1. 2. Webサーバから DB接続を分離 4. コンテナ化

データ移行 既存のアプリケーションはphpで動いていま す。連想配列を多用した作りになっていて、機 能拡張しにくいので、作り直します。 Sp ring Boot(Java)に移行する理由は、静的型付 け言語で安定したパフォーマンスと、Spring Bootという強力なフレームワークで開発効率を 上げたいからです。 APIをSpring Boot(Java)に

データ移行 Webサー バは記事本体、管理画面、バッチ、ア プリAPIなどいろいろ分かれています。それぞ れがDBに接続しています。 SQLServerからPostgreSQLに変更する際の修正 範囲が多すぎため、新規でAPIを作成し、そこ からしかDBに接続しないようにします。 WebサーバからDB接続を分離

データ移行 ストアドプロシージャーはこのままデータベー スと一緒に移行することができないため、新し くAPIを作成します。 ストアドをAPIに

データ移行 azure -> aws間のアプリケーションの移行を簡 単にするためです。 コンテナ化

アプリケーション移行 ストアドプロシージャー Webサーバー Webサーバー Webサーバー Webサーバー APIサーバー DB

アプリケーション移行 Webサーバー APIサーバー DB ストアドプロシージャーは SpringBootに移行 Webサーバー Webサーバー Webサーバー

アプリケーション移行 DBが変わってもAPIのレスポ ンスが同じであれば、アプリ ケーションは同じ挙動をしま す。

アプリケーション移行 APIサーバー SQLServer PostgreSQL DBの向き先を変更するだけ Webサーバー Webサーバー Webサーバー Webサーバー

アプリケーション移行 アプリケーションとデータベ ースの依存関係をAPIにまと め、移行時にアプリケーショ ンのコードの修正をしないよ うにしましょう。

03 データ移行

データ移行 Embulkを使ってデータ移行

データ移行 bulk load データ転送を支援するオープンソースの並列バ ルクデータローダーです。

データ移行 データ移行中はブログ閲覧を止めない。 データ移行完了は1時間30分以内に収める。 データ移行で12時間かかりました。 目標を大きく超える結果となったため 移行方法を考えます。 目標 移行試験

データ移行 ブログ本体の記事取得などのGETメソッドのみ 許可します。 ユーザ管理画面、コメント投稿、いいねなど POST/PUT/DELETEメソッドはメンテナンス モードにします。 DBを接続している部分をAPIに全て集約し ているため、GETメソッドだけであればデ ータの更新が発生しません。 データの更新が発生しなければ、ブログ閲 覧を止めずにデータ移行ができます。 データ移行中はブログ閲覧を止めません。

データ移行 ブログ本体 APIサーバー 移行前のDB 移行後のDB 閲覧に必要なAPIは取得可能です。 ユーザー管理画面 更新に必要なAPIは止めています。 bulk load (insert or merge)

データ移行 まず時間のかかっているテーブルを洗い出します。 データ移行完了は1時間30分以内に収める。 テーブル名 移行時間 補足 AAA 0:00:06 BBB 0:00:06 CCC 0:00:06 DDD 0:16:14 10分以上経過 EEE 2:45:03 1時間以上経過 FFF 0:00:08 GGG 0:00:12 HHH 0:07:05 1分以上経過

データ移行 差分更新とは、事前に全レコードを挿入し updated_atを条件に毎日差分のあったレコードだ け挿入 or 更新します。 Embulkに全件更新のmode:insert、差分更新の mode:mergeがありますので、利用します。 テーブルごとに移行時間を計測する10分以上かかるテ ーブルを目安に、全件更新 or 差分更新します。

事前投入 一日目 データ移行 二日目 三日目 事前挿入するデータは時間かかりますが、更新のあっ たデータは少ないので、データ移行の時間は短くてす みます。 事前投入 一日目 二日目 三日目 移行前 移行後

事前投入 一日目 データ移行 二日目 三日目 問題は削除です。既存データの削除を同期するために は毎回全件チェックする必要があるのではないか？ と疑問に思うかもしれません。 事前投入 一日目 二日目 三日目 移行前 移行後 削 除 同期されないのでは？

事前投入 一日目 データ移行 二日目 三日目 削除履歴テーブルを別途用意し、移行後は削除用テー ブルに入っているPKを基準に削除します。 トリガーを利用しました。 事前投入 一日目 二日目 三日目 移行前 移行後 削 除 削 除 別テーブルに削除履歴を保存します 削 除 削 除 削除テーブルに入るとトリガーが発火し、削除されます

テーブル名 移行時間 補足 AAA 0:00:06 BBB 0:00:06 CCC 0:00:06 DDD 0:00:13 差分更新 EEE 0:01:00 差分更新 FFF 0:00:08 GGG 0:00:12 HHH 0:00:05 差分更新 データ移行

データ移行 ブログ本体 APIサーバー 移行前のDB 移行後のDB 閲覧に必要なAPIは取得可能です。 ユーザー管理画面 更新に必要なAPIは止めています。 bulk load (insert or merge) 1時間30分以内に終わる

04 SQL Serverと PostgreSQL

SQLServer PostgreSQL 取得件数指定 top句 limit句 isolation Level with(NOLOCK) なし 文字列変更 convert to_char ランダム値の生成 newId() random() ID 値の取得 SCOPE_IDENTIT Y() RETURNING ID 照合順序 Japanese_CI_AS ja_JP.UTF-8 null判定 isnull coalesce SQL ServerとPostgreSQL SQL構文の違い

SQL ServerとPostgreSQL SQLServerだったらSQLServerのクエリ、 PostgreSQLならPostgreSQLのクエリを実行したい。 アプリケーションでやりたいので、 MyBatis x FreeMarkerを使います。

MyBatis x FreeMarker 05

MyBatis x FreeMarker MyBatisとは - Javaでデータベースを扱うためのフレームワークです。 - 直接 JDBC を扱うコードを書きません。 - クエリ引数やクエリ結果を手動で設定する必要がほとんどありません。 FreeMarkerとは - Javaのテンプレートエンジンです。 組み合わせると、テンプレートエンジンにSQLを書くことができます。 MyBatis x FreeMarkerを使うのは、SQL以外の決まった記述をなくして見 やすいコードにするためです。

デフォルト(xml) FreeMarker SELECT id , name FROM user WHERE id = #{id} and now() birth_month SELECT id , name FROM user WHERE id = <@p name="id" /> and now() < birth_month MyBatis x FreeMarker FreeMarkerを使うとxmlの決まった記述がなくなり、見通 しが良くなりました。

テキストブロック以前 テキストブロック @Mapper public interface UserCustomMapper { @Lang(FreeMarkerLanguageDriver.class) @Select("findById.ftl") List findById(@Param("id") Long id); } @Mapper public interface UserCustomMapper { @Lang(FreeMarkerLanguageDriver.class) @Select(""" SELECT id , name FROM user WHERE id = <@p name="id" /> AND now() < birth_month """) List findById(@Param("id") Long id); } MyBatis x FreeMarker テキストブロックを使うと、さらに見通しをよくできるよ うになりました。

MyBatis x FreeMarker MyBatis x FreeMarkerを使うと、SQLの見通しが良くなりました。 続いて、SQL ServerとPostgreSQLでどちらの接続先になっても同じ 結果が返却されるように、コードを修正します。

MyBatis x FreeMarker MyBatisの機能でデータベースごとにSQLを分けることができます。 使用する場合は、databaseIdProvider プロパティを設定する必要が あります。

MyBatis x FreeMarker databaseIdProviderの登録 /** * JDBCドライバーを読み込んだ時、データベースの種別でdatabaseIdProviderを設定する * * @return databaseIdProvider */ @Bean(name = "databaseIdProvider") public VendorDatabaseIdProvider vendorDatabaseIdProvider() { VendorDatabaseIdProvider databaseIdProvider = new VendorDatabaseIdProvider(); Properties vendorProperties = new Properties(); vendorProperties.put("PostgreSQL", "PostgreSQL"); vendorProperties.put("SQL Server", "sqlserver"); databaseIdProvider.setProperties(vendorProperties); return databaseIdProvider; }

MyBatis x FreeMarker JDBCの設定を 　url: jdbc:PostgreSQL://127.0.0.1:5432/ にすると、databaseIdProvider=PostgreSQL 　url: jdbc:sqlserver://127.0.0.1:1433; にすると、databaseIdProvider=sqlserver が設定されます。 振り分けるには、アノテーションにdatab aseIdを設定します。

MyBatis x FreeMarker @Lang(FreeMarkerLanguageDriver.class) @Select(""" SELECT top 1 id , name FROM user WHERE id = <@p name="id" /> AND now() < birth_month """, databaseId = "sqlserver") @Select(value = """ SELECT id , name FROM user WHERE id = <@p name="id" /> AND now() < birth_month limit 1 """, databaseId = "PostgreSQL") List findById(@Param("id") Long id); Top句とLimit句の場合

MyBatis x FreeMarker 続いて、with(NOLOCK)の場合を記述します。

MyBatis x FreeMarker @Lang(FreeMarkerLanguageDriver.class) @Select(""" SELECT id , name FROM user with(nolock) WHERE id = <@p name="id" /> AND now() < birth_month """, databaseId = "sqlserver") @Select(value = """ SELECT id , name FROM user WHERE id = <@p name="id" /> AND now() < birth_month """, databaseId = "PostgreSQL") List findById(@Param("id") Long id); wit h(nolock)の場合

MyBatis x FreeMarker Top句とLimit句の場合では、クエリがselect句の中と最終行の中で全然 違います。 しかし、wi th(nolock)の場合は、ほぼ同じようなクエリが2つ続いてい ます。 クエリは分かれていますが、見通しが悪いように見えます。 よって、SQL内で分岐をします。

MyBatis x FreeMarker @Lang(FreeMarkerLanguageDriver.class) @Select(""" SELECT id , name FROM user <#if '${_databaseId}' == 'sqlserver'> WITH (NOLOCK) #if> WHERE id = <@p name="id" /> AND now() < birth_month """) List findById(@Param("id") Long id); wit h(nolock)の場合

MyBatis x FreeMarker エラーになります freemarker.core.InvalidReferenceException: The following has evaluated to null or missing: ==> _databaseId [in nameless template at line 4, column 21] _databaseIdが使えないエラーが発生しました。

MyBatis x FreeMarker デフォルトのFreemarkerLanguageDriverでは_databaseIdが使えな いので、使えるようにカスタマイズします。

MyBatis x FreeMarker /** * FreemarkerLanguageDriverを独自にカスタマイズする設定 * * カスタマイズしているのは以下の通り * - ${_databaseId}にdatabaseIdの文字列を設定 * - http://mybatis.org/freemarker-scripting/jacoco/org.mybatis.scripting.freemarker/FreeMarkerSqlSource.java.html */ public static class CustomSqlSource extends FreeMarkerSqlSource { private String dbms; public CustomSqlSource(Template template, org.apache.ibatis.session.Configuration configuration, Version version) { super(template, configuration, version); this.dbms = configuration.getDatabaseId(); } @Override protected Object preProcessDataContext(Object dataContext, boolean isMap) { dataContext = super.preProcessDataContext(dataContext, isMap); if (isMap) { ((Map) dataContext).put("_databaseId", new SimpleScalar(this.dbms)); return dataContext; } ((ParamObjectAdapter) dataContext).putAdditionalParam("_databaseId", new SimpleScalar(this.dbms)); return dataContext; } }

MyBatis x FreeMarker /** * FreemarkerLanguageDriverを独自にカスタマイズした設定を呼び出す */ public static class CustomFreemarkerLanguageDriver extends FreeMarkerLanguageDriver { @Override protected SqlSource createSqlSource(Template template, org.apache.ibatis.session.Configuration configuration) { return new CustomSqlSource( template, configuration, freemarkerCfg.getIncompatibleImprovements() ); } }

MyBatis x FreeMarker @Lang(CustomFreemarkerLanguageDriver.class) @Select(""" SELECT id , name FROM user <#if '${_databaseId}' == 'sqlserver'> WITH (NOLOCK) #if> WHERE id = <@p name="id" /> AND now() < birth_month """) List findById(@Param("id") Long id); wit h(nolock)の場合

アノテーションで分離 SQL内で分岐 全く違うSQL構文の場合はアノテーションの方が見通しが良い 一部のSQL構文しか差分がない場合、SQL内の方が見通しが良 い MyBatis x FreeMarker 実行できました。 アノテーションで分離するか、SQL内で分岐するか判断基準を 記載します。 上記を踏まえて、SQL構文を違いをアノテーションで分離する か、SQL内で分岐するのかの一例を記載します。

SQLServer PostgreSQL FreeMarker 取得件数指定 top句 limit句 アノテーションで分離 isolation Level with(NOLOCK) なし SQL内で分岐 文字列変更 convert to_char SQL内で分岐 ランダム値の生成 newId() random() SQL内で分岐 ID 値の取得 SCOPE_IDENTITY() RETURNING ID SQL内で分岐 照合順序 Japanese_CI_AS ja_JP.UTF-8 SQL内で分岐 null判定 isnull coalesce SQL内で分岐 MyBatis x FreeMarker SQL構文の違い

MyBatis x FreeMarker データベースごとにSQLを簡 単に分けることができまし た。

MyBatis x FreeMarker MyBatisの機能の databaseIdProviderはかなり 活躍するので、活用しましょ う。

まとめ 06

アプリケーションとデータベースの依存関係をAPIにまと め、移行時にアプリケーションのコードの修正をしないよ うにしましょう。 MyBatisの機能のdatabaseIdProviderはかなり活躍するの で、活用しましょう。 Spring Boot×MyBatis×FreeMarkerを使って、 データベースの接続先を安全に変更するには？ まとめ