6.1. データベースアクセス（共通編）

Caution

本バージョンの内容は既に古くなっています。最新のガイドラインはこちらからご参照ください。

Todo

TBD

本章では以下の内容について、現在精査中である。

• 複数データソースについて

  具体的な内容は、Overviewの複数データソースについておよびHow to extendsの複数データソースを使用するための設定を参照されたい。
• JPA利用時の一意制約エラー及び悲観排他エラーのハンドリング方法について

  具体的な内容は、Overviewの例外ハンドリングについてを参照されたい。

6.1.1. Overview

本節では、RDBMSで管理されているデータにアクセスする方法について、説明する。

O/R Mapperに依存する部分については、

• データベースアクセス（MyBatis3編）
• データベースアクセス（JPA編）

を参照されたい。

6.1.1.1. JDBC DataSourceについて

RDBMSにアクセスする場合、アプリケーションからは、JDBCデータソースを参照してアクセスすることになる。

JDBCデータソースを使用することにより、JDBCドライバーのロード、接続情報（接続URL、接続ユーザ、パスワードなど）の設定を、アプリケーションから排除することができる。

そのため、アプリケーションからは、使用するRDBMSやデプロイする環境を、意識する必要がなくなる。

Picture - About JDBC DataSource

JDBCデータソースの実装は、アプリケーションサーバ、OSSライブラリ、Third-Partyライブラリ、Spring Frameworkなどから提供されているので、プロジェクト要件や、デプロイ環境にあったデータソースの選定が必要になる。

以下に、代表的なデータソース3種類の紹介を行う。

• アプリケーションサーバ提供のJDBCデータソース
• OSS/Third-Partyライブラリ提供のJDBCデータソース
• Spring Framework提供のJDBCデータソース

6.1.1.1.1. アプリケーションサーバ提供のJDBCデータソース

Webアプリケーションでデータソースを使用する場合、アプリケーションサーバから提供されるJDBCデータソースを使うのが一般的である。

アプリケーションサーバから提供されるJDBCデータソースは、コネクションプーリング機能など、Webアプリケーションで使うために必要な機能が、標準で提供されている。

アプリケーションサーバから提供されているデータソース

項番	アプリケーションサーバ	参照ページ
	Apache Tomcat 8.5	Apache Tomcat 8.5 User Guide(The Tomcat JDBC Connection Pool)を参照されたい。 Apache Tomcat 8.5 User Guide(JNDI Datasource HOW-TO)(Apache Commons DBCP 2)を参照されたい。
	Apache Tomcat 8.0	Apache Tomcat 8.0 User Guide(The Tomcat JDBC Connection Pool)を参照されたい。 Apache Tomcat 8.0 User Guide(JNDI Datasource HOW-TO)(Apache Commons DBCP 2)を参照されたい。
	Apache Tomcat 7	Apache Tomcat 7 User Guide(The Tomcat JDBC Connection Pool)を参照されたい。 Apache Tomcat 7 User Guide(JNDI Datasource HOW-TO)(Apache Commons DBCP)を参照されたい。
	Oracle WebLogic Server 12c	Oracle WebLogic Server Product Documentationを参照されたい。
	IBM WebSphere Application Server Version 9.0	WebSphere Application Server Online information centerを参照されたい。
	JBoss Enterprise Application Platform 7.0	JBoss Enterprise Application Platform 7.0 Product Documentationを参照されたい。
	JBoss Enterprise Application Platform 6.4	JBoss Enterprise Application Platform 6.4 Product Documentationを参照されたい。

6.1.1.1.2. OSS/Third-Partyライブラリ提供のJDBCデータソース

アプリケーションサーバから提供されるJDBCデータソースを使わない場合は、OSS/Third-Partyライブラリから提供されているJDBCデータソースを使用する。

本ガイドラインでは、「Apache Commons DBCP」のみ紹介するが、他のライブラリを使ってもよい。

OSS/Third-Partyライブラリから提供されているJDBCデータソース

項番	ライブラリ名	説明
	Apache Commons DBCP	Apache Commons DBCPを参照されたい。

6.1.1.1.3. Spring Framework提供のJDBCデータソース

Spring Frameworkから提供されているJDBCデータソースの実装クラスは、コネクションプーリング機能がないため、Webアプリケーションのデータソースとして使用する事はない。

Spring Frameworkでは、JDBCデータソースの実装クラスと、JDBCデータソースのアダプタクラスを提供しているが、利用するケースが限定的なので、AppendixのSpring Frameworkから提供されているJDBCデータソースクラスとして紹介する。

6.1.1.2. トランザクションの管理方法について

Spring Frameworkの機能を使って、トランザクション管理を行う場合、プロジェクト要件や、デプロイ環境にあったPlatformTransactionManagerの選定が必要になる。

詳細は、ドメイン層の実装のトランザクション管理を使うための設定についてを参照されたい。

6.1.1.3. トランザクション境界/属性の宣言について

トランザクション境界及びトランザクション属性の宣言は、Serviceにて、@Transactionalアノテーションを指定することで実現する。

詳細は、ドメイン層の実装のトランザクション管理についてを参照されたい。

6.1.1.4. データの排他制御について

データを更新する場合、データの一貫性および整合性を保障するために、排他制御を行う必要がある。

データの排他制御については、排他制御を参照されたい。

6.1.1.5. 例外ハンドリングについて

Spring Frameworkでは、JDBCの例外(java.sql.SQLException)や、O/R Mapper固有の例外を、Spring Frameworkから提供しているデータアクセス例外(org.springframework.dao.DataAccessExceptionのサブクラス)に変換する機能がある。

Spring Frameworkのデータアクセス例外へ変換しているクラスについては、AppendixのSpring Frameworkから提供されているデータアクセス例外へ変換するクラスを参照されたい。

変換されたデータアクセス例外は、基本的にはアプリケーションコードでハンドリングする必要はないが、一部のエラー（一意制約違反、排他エラーなど）については、要件によっては、ハンドリングする必要がある。

データアクセス例外をハンドリングする場合、DataAccessExceptionをcatchするのではなく、エラー内容を通知するサブクラスの例外をcatchすること。

以下に、アプリケーションコードでハンドリングする可能性がある代表的なサブクラスを紹介する。

ハンドリングする可能性があるDBアクセス例外のサブクラス

項番	クラス名	説明
	org.springframework.dao. DuplicateKeyException	一意制約違反が発生した場合に発生する例外。
	org.springframework.dao. OptimisticLockingFailureException	楽観ロックに成功しなかった場合に発生する例外。他の処理によって同一データが更新されていた場合に発生する。 本例外は、O/R MapperとしてJPAを使用する場合に発生する例外である。MyBatisには楽観ロックを行う機能がないため、O/R Mapper本体から本例外が発生することはない。
	org.springframework.dao. PessimisticLockingFailureException	悲観ロックに成功しなかった場合に発生する例外。他の処理で同一データがロックされており、ロック解放待ちのタイムアウト時間を超えてもロックが解放されない場合に発生する。

Note

O/R MapperにMyBatisを使用して楽観ロックを実現する場合は、ServiceやRepositoryの処理として楽観ロック処理を実装する必要がある。

本ガイドラインでは、楽観ロックに失敗したことを、Controllerに通知する方法として、OptimisticLockingFailureExceptionおよびその子クラスの例外を発生させることを推奨する。

理由は、アプリケーション層の実装(Controllerの実装)を、使用するO/R Mapperに依存させないためである。

Todo

JPA(Hibernate)を使用すると、現状意図しないエラーとなることが発覚している。

• 一意制約違反が発生した場合、DuplicateKeyExceptionではなく、org.springframework.dao.DataIntegrityViolationExceptionが発生する。

下記は、一意制約違反を、ビジネス例外として扱う実装例である。

try {
    accountRepository.saveAndFlash(account);
} catch(DuplicateKeyException e) { // (1)
    throw new BusinessException(ResultMessages.error().add("e.xx.xx.0002"), e); // (2)
}

項番	説明
(1)	一意制約違反が発生した場合に発生する例外（DuplicateKeyException）をcatchする。
(2)	データが重複している旨を伝えるビジネス例外を発生させている。 例外をcatchした場合は、必ず原因例外(“e” ) をビジネス例外に指定すること。

6.1.1.6. 複数データソースについて

アプリケーションによっては、複数のデータソースが必要になる場合がある。

以下に、複数のデータソースが必要になる代表的なケースを紹介する。

複数のデータソースが必要になる代表的なケース

項番	ケース	例	特徴
	データ(テーブル)の分類毎にデータベースやスキーマがわかれている場合。	顧客情報を保持するテーブル群と請求情報を保持するテーブル群が別々のデータベースやスキーマに格納されている場合など。	処理で扱うデータは決まっているので、静的に使用するデータソースを決定することができる。
	利用者（ログインユーザ）によって使用するデータベースやスキーマが分かれている場合。	利用者の分類毎にデータベースやスキーマがわかれている場合など（マルチテナント等）。	利用者によって使用するデータソースが異なるため、動的に使用するデータソースを決定する必要がある。

Todo

TBD

今後、以下の内容を追加する予定である。

• 概念レベルのイメージ図

6.1.1.7. 共通ライブラリから提供しているクラスについて

共通ライブラリから、以下の処理を行うクラスを提供している。

共通ライブラリの詳細については、以下を参照されたい。

• LIKE検索時のエスケープについて
• Sequencerについて

6.1.2. How to use

6.1.2.1. データソースの設定

6.1.2.1.1. アプリケーションサーバで定義したDataSourceを使用する場合の設定

アプリケーションサーバで定義したデータソースを使用する場合は、Bean定義ファイルに、JNDI経由で取得したオブジェクトを、beanとして登録するための設定を行う必要がある。

以下に、データベースはPostgreSQL、アプリケーションサーバはTomcat7を使用する際の、設定例を示す。

• xxx-context.xml (Tomcatの設定ファイル)

  <!-- (1) -->
  <Resource
     type="javax.sql.DataSource"
     name="jdbc/SampleDataSource"
     driverClassName="org.postgresql.Driver"
     url="jdbc:postgresql://localhost:5432/terasoluna"
     username="postgres"
     password="postgres"
     defaultAutoCommit="false"
     /> <!-- (2) -->
  

• xxx-env.xml

<jee:jndi-lookup id="dataSource" jndi-name="jdbc/SampleDataSource" /> <!-- (3) -->

項番	属性名	説明
(1)	-	データソースを定義する。
	type	リソースの種類を指定する。javax.sql.DataSourceを指定する。
	name	リソース名を指定する。ここで指定した名前がJNDI名となる。
	driverClassName	JDBCドライバクラスを指定する。例では、PostgreSQLから提供されているJDBCドライバクラスを指定する。
	url	接続URLを指定する。 【環境に合わせて変更が必要】
	username	接続ユーザ名を指定する。【環境に合わせて変更が必要】
	password	接続ユーザのパスワードを指定する。【環境に合わせて変更が必要】
	defaultAutoCommit	自動コミットフラグのデフォルト値を指定する。falseを指定する。トランザクション管理下であれば強制的にfalseになる。
(2)	-	Tomcat7の場合、factory属性を省略するとtomcat-jdbc-poolが使用される。 設定項目の詳細については、Attributes of The Tomcat JDBC Connection Poolを参照されたい。
(3)	-	データソースのJNDI名を指定する。Tomcatの場合は、データソース定義時のリソース名「(1)-name」に指定した値を指定する。

6.1.2.1.2. Bean定義したDataSourceを使用する場合の設定

アプリケーションサーバから提供されているデータソースを使わずに、

OSS/Third-Partyライブラリから提供されているデータソースや、Spring Frameworkから提供されているJDBCデータソースを使用する場合は、Bean定義ファイルにDataSourceクラスのbean定義が必要となる。

以下に、データベースはPostgreSQL、データソースはApache Commons DBCPを使用する際の、設定例を示す。

• xxx-env.xml

<bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource"
    destroy-method="close">                                           <!-- (1) (8) -->
    <property name="driverClassName" value="org.postgresql.Driver" /> <!-- (2) -->
    <property name="url" value="jdbc:postgresql://localhost:5432/terasoluna" /> <!-- (3) -->
    <property name="username" value="postgres" />                     <!-- (4) -->
    <property name="password" value="postgres" />                     <!-- (5) -->
    <property name="defaultAutoCommit" value="false"/>               <!-- (6) -->
    <!-- (7) -->
</bean>

項番	説明
(1)	データソースの実装クラスを指定する。例では、Apache Commons DBCPから提供されているデータソースクラス(org.apache.commons.dbcp2.BasicDataSource)を指定する。
(2)	JDBCドライバクラスを指定する。例では、PostgreSQLから提供されているJDBCドライバクラスを指定する。
(3)	接続URLを指定する。 【環境に合わせて変更が必要】
(4)	接続ユーザ名を指定する。【環境に合わせて変更が必要】
(5)	接続ユーザのパスワードを指定する。【環境に合わせて変更が必要】
(6)	自動コミットフラグのデフォルト値を指定する。falseを指定する。トランザクション管理下であれば、強制的にfalseになる。
(7)	BasicDataSourceには上記以外に、JDBC共通の設定値の指定、JDBCドライバー固有のプロパティ値の指定、コネクションプーリング機能の設定値の指定を行うことができる。 設定項目の詳細については、DBCP Configurationを参照されたい。
(8)	設定例では値を直接指定しているが、環境によって設定値がかわる項目については、Placeholder(${…})を使用して、実際の設定値はプロパティファイルに指定すること。 Placeholderについては、Spring Reference Documentの PropertyPlaceholderConfigurerを参照されたい。

6.1.2.2. トランザクション管理を有効化するための設定

トランザクション管理を有効化するための基本的な設定は、ドメイン層の実装のトランザクション管理を使うための設定についてを参照されたい。

PlatformTransactionManagerについては、使用するO/R Mapperによって使うクラスがかわるので、詳細設定は、

• データベースアクセス（MyBatis3編）
• データベースアクセス（JPA編）

を参照されたい。

6.1.2.3. JDBCのDebug用ログの設定

O/R Mapper(MyBatis, Hibernate)で出力されるログより、さらに細かい情報が必要な場合、log4jdbc(log4jdbc-remix)を使って出力される情報が有効である。

log4jdbcの詳細については、log4jdbc project pageを参照されたい。

log4jdbc-remixの詳細については、log4jdbc-remix project pageを参照されたい。

Warning

log4jdbc-remixが提供しているLog4jdbcProxyDataSourceを使用していると、ログレベルを”debug”以外に設定しても、オーバーヘッドが少なからず発生する。 そのため、本設定はデバッグ用として使用し、性能試験及び商用環境にリリースする場合はLog4jdbcProxyDataSourceを経由せずにデータベースへ接続することを推奨する。

6.1.2.3.1. log4jdbc提供のデータソースの設定

• xxx-env.xml

<jee:jndi-lookup id="dataSourceSpied" jndi-name="jdbc/SampleDataSource" /> <!-- (1) -->

<bean id="dataSource" class="net.sf.log4jdbc.Log4jdbcProxyDataSource"> <!-- (2) -->
    <constructor-arg ref="dataSourceSpied" /> <!-- (3) -->
</bean>

項番	説明
(1)	データソースの実体を定義する。例では、アプリケーションサーバからJNDI経由で取得したデータソースを使用している。
(2)	log4jdbcより提供されているnet.sf.log4jdbc.Log4jdbcProxyDataSourceを指定する。
(3)	データソースの実体となるbeanを、コンストラクタに指定する。

Warning

性能試験及び商用環境にリリースする場合、データソースとしてLog4jdbcProxyDataSourceは使用しないこと。

具体的には、(2)と(3)の設定を外し、dataSourceSpiedのbean名をdataSourceに変更する。

6.1.2.3.2. log4jdbc用ロガーの設定

• logback.xml

<!-- (1) -->
<logger name="jdbc.sqltiming">
    <level value="debug" />
</logger>

<!-- (2) -->
<logger name="jdbc.sqlonly">
    <level value="warn" />
</logger>

<!-- (3) -->
<logger name="jdbc.audit">
    <level value="warn" />
</logger>

<!-- (4) -->
<logger name="jdbc.connection">
    <level value="warn" />
</logger>

<!-- (5) -->
<logger name="jdbc.resultset">
    <level value="warn" />
</logger>

<!-- (6) -->
<logger name="jdbc.resultsettable">
    <level value="debug" />
</logger>

項番	説明
(1)	バインド変数に値が設定された状態のSQL文と、SQLの実行時間を出力するためのロガー。値がバインドされた形式のSQLが出力されるので、DBアクセスツールに貼りつけて実行する事ができる。
(2)	バインド変数に値が設定された状態のSQL文を、出力するためのロガー。(1)との違いは、実行時間が出力されない。
(3)	ResultSetインタフェースを除く、JDBCインタフェースのメソッド呼び出し（引数と、返り値）を出力するためのロガー。JDBC関連で問題が発生した時の解析に有効なログであるが、出力されるログの量が多い。
(4)	Connectionの接続/切断イベントと使用中の接続数を出力するためのロガー。接続リークが発生時の解析に有効なログであるが、接続リークの問題がなければ、出力する必要はない。
(5)	ResultSetインタフェースに対するメソッド呼び出し（引数と、返り値）を出力するためのロガー。取得結果が、想定と異なった時の解析に有効なログであるが、出力されるログの量が多い。
(6)	ResultSetの中身を確認しやすい形式にフォーマットして出力するためのロガー。取得結果が、想定と異なった時の解析に有効なログであるが、出力されるログの量が多い。

Warning

ロガーによっては大量にログが出力されるので、必要なロガーのみ定義、または出力対象にすること。

上記サンプルでは、開発中の非常に有効なログを出力するロガーについて、ログレベルをdebugに設定している。 その他のロガーについては、必要に応じてdebugに設定する必要がある。

性能試験及び商用環境にリリースする場合、正常終了時にlog4jdbc用のロガーによってログが出力されないようにすること。

具体的には、ログレベルをwarnに設定する。

6.1.2.3.3. log4jdbcのオプションの設定

クラスパス直下に、log4jdbc.propertiesというプロパティファイルを配置することで、log4jdbcのデフォルトの動作をカスタマイズすることができる。

• log4jdbc.properties

# (1)
log4jdbc.dump.sql.maxlinelength=0
# (2)

項番	説明
(1)	SQL分の折り返し文字数を指定する。0を指定すると、折り返しはされない。
(2)	オプションの詳細については、log4jdbc project page -Options-を参照されたい。

6.1.3. How to extend

6.1.3.1. 複数データソースを使用するための設定

Todo

TBD

今後、以下の内容を追加する予定である。

• 処理パターン（複数のデータソースに対して更新あり、更新は１つのデータソース、参照のみ、同時アクセスはなしなど）によってトランザクション管理の方法がかわると思うので、その辺りを中心にブレークダウンする予定である。

6.1.3.2. 動的にデータソースを切り替えるための設定

複数のデータソースを定義し、動的に切り替えを行うには、org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSourceを継承したクラスを作成し、どのような条件でデータソースを切り替えるかを実装する必要がある。

具体的にはdetermineCurrentLookupKeyメソッドの戻り値となるキーとデータソースをマッピングさせることによって、これを実現する。キーの選択には通常、認証ユーザー情報、時間、ロケール等のコンテキスト情報を使用する。

6.1.3.2.1. AbstractRoutingDataSourceの実装

AbstractRoutingDataSourceを拡張して作成したDataSourceを、通常のデータソースと同じように使用することでデータソースの動的な切り替えが実現できる。

以下に、時間によってデータソースを切り替える例を示す。

• AbstractRoutingDataSourceを継承したクラスの実装例`

package com.examples.infra.datasource;

import javax.inject.Inject;

import org.joda.time.DateTime;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import org.terasoluna.gfw.common.date.jodatime.JodaTimeDateFactory;

public class RoutingDataSource extends AbstractRoutingDataSource { // (1)

    @Inject
    JodaTimeDateFactory dateFactory; // (2)

    @Override
    protected Object determineCurrentLookupKey() { // (3)

        DateTime dateTime = dateFactory.newDateTime();
        int hour = dateTime.getHourOfDay();

        if (7 <= hour && hour <= 23) { // (4)
            return "OPEN"; // (5)
        } else {
            return "CLOSE";
        }
    }
}

項番	説明
(1)	AbstractRoutingDataSourceを継承する。
(2)	時刻を取得するため、JodaTimeDateFactoryを使用する。詳細は、システム時刻を参照のこと。
(3)	determineCurrentLookupKeyメソッドを実装する。このメソッドの返り値と後述するbean定義ファイル内のtargetDataSourcesに定義したkeyをマッピングすることにより使用するデータソースが決定される。
(4)	メソッド内で、コンテキスト情報（ここでは時間）を参照し、キーの切り替えを行う。ここは業務用件に合わせて実装する必要がある。このサンプルは、時刻が「7:00から23:59まで」と「0:00から6:59まで」で違うキーを返すように実装されている。
(5)	後述するbean定義ファイル内のtargetDataSourcesとマッピングさせるkeyを返す。

6.1.3.2.2. データソースの定義

作成したAbstractRoutingDataSource拡張クラスをbean定義ファイルに定義する。

• xxx-env.xml

<bean id="dataSource"
    class="com.examples.infra.datasource.RoutingDataSource">  <!-- (1) -->
    <property name="targetDataSources">  <!-- (2) -->
        <map>
            <entry key="OPEN" value-ref="dataSourceOpen" />
            <entry key="CLOSE" value-ref="dataSourceClose" />
        </map>
    </property>
    <property name="defaultTargetDataSource" ref="dataSourceDefault" />  <!-- (3) -->
</bean>

項番	説明
(1)	先ほど作成したAbstractRoutingDataSourceを継承したクラスを定義する。
(2)	使用するデータソースを定義する。keyはdetermineCurrentLookupKeyメソッドで返却しうる値を定義する。value-refにはkeyごとに使用するデータソースを指定する。データソースの設定をもとに切り替えるデータソースの個数分、定義を行う必要がある。
(3)	determineCurrentLookupKeyメソッドで指定したkeyがtargetDataSourcesに存在しない場合は、このデータソースが使用される。実装例の場合、デフォルトが使用されることはないが、今回は説明のため、defaultTargetDataSourceを定義している。

6.1.4. how to solve the problem

6.1.4.1. N+1問題の対策方法

N+1問題とは、データベースから取得するレコード数に比例して実行されるSQLの数が増えることにより、データベースへの負荷およびレスポンスタイムの劣化を引き起こす問題のことである。

以下に、具体的をあげる。

項番	説明
(1)	検索条件に一致するレコードを、メインとなるテーブルから検索する。 上記例では、 MainTableテーブルのcol1カラムが、'Foo'のレコードを取得しており、20件のレコードが取得されている。
(2)	(1)で検索した各レコードに対して、関連レコードを関連テーブルから取得する。 上記例では、SubTableテーブルのidカラムが、(1)で取得したレコードのidカラムと同じレコードを取得している。 このSQLは、(1)で取得されたレコード件数分、実行される。

上記例では、合計で21回のSQLが発行されることになる。

仮に関連テーブルが3テーブルあると、合計で61回のSQLが発行されることになるため、対策が必要となる。

N+1問題の解決方法の代表例を、以下に示す。

6.1.4.1.1. JOIN(Join Fetch)を使用して解決する

関連テーブルをJOINすることで、1回のSQLでメインのテーブルと関連テーブルのレコードを取得する。

関連テーブルとの関係が、1:1の場合は、この方法によって解決することを検討すること。

項番	説明
(1)	検索条件に一致するレコードを検索する際に、関連テーブルをJOINすることで、メインとなるテーブルと関連テーブルから、レコードを一括で取得する。 上記例では、 MainTableテーブルのcol1カラムが'Foo'のレコードと、検索条件に一致したレコードのidが一致するSubTableのレコードを一括で取得している。 カラム名が重複する場合は、別名を付与してどちらのテーブルのカラムなのか識別する必要がある。

JOIN(Join Fetch)を使用すると、1回のSQLの発行で必要なデータを全て取得することができる。

Note

JPQLでJOINする場合

JPQLでJOINする場合の実装例については、JOIN FETCHについてを参照されたい。

Warning

関連テーブルとの関連が、1:Nの場合は、JOIN(Join Fetch)による解決も可能だが、以下の点に注意すること。

• 1:Nの関連をもつレコードをJOINする場合、関連テーブルのレコード数に比例して、無駄なデータを取得することになる。 詳細については、一括取得時の注意事項を参照されたい。
• JPA(Hibernate)使用する際に、1:NのNの部分が、複数ある場合は、Nの部分を格納するコレクション型は、java.util.Listではなく、java.util.Setを使用する必要がある。

6.1.4.1.2. 関連レコードを一括で取得する事で解決する

1:Nの関係が複数あるパターンなどは、関連レコードを一括で取得し、その後プログラミングによって振り分ける方法をとった方がよいケースがある。

関連テーブルとの関係が1:Nの場合は、この方法によって解決することを検討すること。

項番	説明
(1)	検索条件に一致するレコードを、メインとなるテーブルから検索する。 上記例では、 MainTableテーブルのcol1カラムが、'Foo'のレコードを取得しており、20件のレコードが取得されている。
(2)	(1)で検索した各レコードに対して、関連レコードを関連テーブルから取得する。 1レコード毎に取得するのではなく、(1)で取得した各レコードの外部キーに一致するレコードを、一括で取得する。 上記例では、SubTableテーブルのidカラムが、(1)で取得したレコードのidカラムと同じレコードを、IN句を使用して一括取得している。
(3)	(2)で取得したSubTableのレコードを、(1)で取得したレコードに振り分けマージする。

上記例では、合計で2回のSQLの発行で、必要なデータを取得することができる。

仮に、関連テーブルが、3テーブルあっても、合計で4回のSQLの発行で済むことになる。

Note

この方法は、SQLの発行を最小限におさえつつ、必要なデータのみ取得することができるという特徴をもつ。 関連テーブルのレコードをプログラミングによって振り分ける必要があるが、関連テーブルの数が多い場合や、1:NのNのレコード数が多い場合は、この方法で解決する方がよいケースがある。

6.1.5. Appendix

6.1.5.1. LIKE検索時のエスケープについて

LIKE検索を行う場合は、検索条件として使用する値を、LIKE検索用にエスケープする必要がある。

共通ライブラリでは、LIKE検索用のエスケープ処理を行うためのコンポーネントとして、以下のクラスを提供している。

項番	クラス	説明
	org.terasoluna.gfw.common.query. QueryEscapeUtils	SQL及びJPQLのエスケープ処理を行うメソッドを提供するユーティリティクラス。 本クラスでは、 LIKE検索用のエスケープ処理を行うメソッド を提供している。
	org.terasoluna.gfw.common.query. LikeConditionEscape	LIKE検索用のエスケープ処理を行うクラス。

Note

LikeConditionEscapeクラスは、「LIKE検索用のワイルドカード文字の扱いに関するバグ」を修正するために、 terasoluna-gfw-common 1.0.2.RELEASEから追加したクラスである。

LikeConditionEscapeクラスは、データベース及びデータベースのバージョンの違いによるワイルドカード文字の違いを吸収する役割を持つ。

6.1.5.1.1. 共通ライブラリのエスケープ仕様について

共通ライブラリから提供しているエスケープ処理の仕様は、以下の通りである。

• エスケープ文字は「 “~” 」。
• エスケープ対象文字は、デフォルトでは「 “%” , “_”」の2文字。

Note

エスケープ対象文字は、terasoluna-gfw-common 1.0.1.RELEASEまでは「 “%” , “_” , “％” , “＿”」の4文字であったが、 「LIKE検索用のワイルドカード文字の扱いに関するバグ」を修正するために、 terasoluna-gfw-common 1.0.2.RELEASEより「 “%” , “_”」の2文字に変更している。

なお、エスケープ対象文字として全角文字「”％” , “＿”」を含めてエスケープする方法も提供している。

具体的なエスケープ例を以下に示す。

[デフォルト仕様のエスケープ例]

エスケープ対象文字としてデフォルト値を使用する場合のエスケープ例を以下に示す。

項番	対象 文字列	エスケープ後 文字列	エスケープ 有無	解説
	“a”	“a”	無	エスケープ対象文字が含まれていないため、エスケープされない。
	a~	a~~	有	エスケープ文字が含まれているため、エスケープされる。
	a%	a~%	有	エスケープ対象文字が含まれているため、エスケープされる。
	a_	a~_	有	No.3と同様。
	_a%	~_a~%	有	エスケープ対象文字が含まれているため、エスケープされる。エスケープ対象文字が複数存在する場合はすべてエスケープされる。
	a％	a％	無	No.1と同様。 terasoluna-gfw-common 1.0.2.RELEASEより、デフォルト仕様では「”％”」はエスケープ対象外の文字として扱う。
	a＿	a＿	無	No.1と同様。 terasoluna-gfw-common 1.0.2.RELEASEより、デフォルト仕様では「”＿”」はエスケープ対象外の文字として扱う。
	" "	" "	無	No.1と同様。
	""	""	無	No.1と同様。
	null	null	無	No.1と同様。

[全角文字を含める場合のエスケープ例]

エスケープ対象文字として全角文字を含める場合のエスケープ例を以下に示す。 項番6と7以外は、デフォルト仕様のエスケープ例を参照されたい。

項番	対象 文字列	エスケープ後 文字列	エスケープ 有無	解説
	a％	a~％	有	エスケープ対象文字が含まれているため、エスケープされる。
	a＿	a~＿	有	No.6と同様。

6.1.5.1.2. 共通ライブラリから提供しているエスケープ用のメソッドについて

共通ライブラリから提供しているQueryEscapeUtilsクラスとLikeConditionEscapeクラスのLIKE検索用のエスケープメソッドの一覧を、以下に示す。

項番	メソッド名	説明
	toLikeCondition(String)	引数で渡された文字列をLIKE検索用にエスケープする。 SQLやJPQL側で一致方法(前方一致、後方一致、部分一致)を指定する場合は、本メソッドを使用してエスケープのみ行う。
	toStartingWithCondition(String)	引数で渡された文字列をLIKE検索用にエスケープした上で、エスケープ後の文字列の最後尾に “%” を付与する。 前方一致検索用の値に変換する場合に使用するメソッドである。
	toEndingWithCondition(String)	引数で渡された文字列をLIKE検索用にエスケープした上で、エスケープ後の文字列の先頭に “%” を付与する。 後方一致検索用の値に変換する場合に使用するメソッドである。
	toContainingCondition(String)	引数で渡された文字列をLIKE検索用にエスケープした上で、エスケープ後の文字列の先頭と最後尾に “%” を付与する。 部分一致検索用の値に変換する場合に使用するメソッドである。

Note

No.2, 3, 4 については、SQLやJPQL側で一致方法(前方一致、後方一致、部分一致)を指定するのではなく、プログラム側で指定する時に使用するメソッドである。

6.1.5.1.3. 共通ライブラリの使用方法

LIKE検索時のエスケープ処理の実装例については、使用するO/R Mapper向けのドキュメントを参照されたい。

• MyBatis3を使用する場合は、データベースアクセス（MyBatis3編）のLIKE検索時のエスケープを参照されたい。
• JPA(Spring Data JPA)を使用する場合は、データベースアクセス（JPA編）のLIKE検索時のエスケープについてを参照されたい。

Note

エスケープ処理を行うために使用するAPIは、使用するデータベースがサポートしているワイルドカード文字によって使い分ける必要がある。

[ワイルドカードとして「 “%” , “_”」(半角文字)のみをサポートしているデータベースの場合]

String escapedWord = QueryEscapeUtils.toLikeCondition(word);

項番	説明
(1)	QueryEscapeUtilsクラスのメソッドを直接使用して、エスケープ処理を行う。

[ワイルドカードとして「”％” , “＿”」(全角文字)もサポートしているデータベースの場合]

String escapedWord = QueryEscapeUtils.withFullWidth()  // (2)
                        .toLikeCondition(word);        // (3)

項番	説明
(2)	QueryEscapeUtilsメソッドのwithFullWidth()メソッドを呼び出して、LikeConditionEscapeクラスのインスタンスを取得する。
(3)	(2)で取得したLikeConditionEscapeクラスのインスタンスのメソッドを使用して、エスケープ処理を行う。

6.1.5.2. Sequencerについて

Sequencerは、シーケンス値を取得するための共通ライブラリである。

Sequencerから取得したシーケンス値は、データベースのプライマリキーカラムの設定値などとして使用する。

Note

共通ライブラリとしてSequencerを用意した理由

Sequencerを用意した理由は、JPAの機能として提供されているID採番機能において、シーケンス値を文字列としてフォーマットする仕組みがないためである。 実際のアプリケーション開発では、フォーマットされた文字列をプライマリキーに設定するケースもあるため、共通ライブラリとしてSequencerを提供している。

プライマリキーに設定する値が数値の場合は、JPAの機能として提供されているID採番機能を使用することを推奨する。JPAのID採番機能については、データベースアクセス（JPA編）のEntityの追加を参照されたい。

Sequencerを用意した主な目的は、JPAでサポートされていない機能の補完であるが、JPAと関係ない処理で、シーケンス値が必要な場合に、使用することもできる。

6.1.5.2.1. 共通ライブラリから提供しているクラスについて

共通ライブラリから提供しているSequencer機能のクラス一覧を以下に示す。

具体的な使用例については、How to useの共通ライブラリの利用方法を参照されたい。

項番	クラス名	説明
	org.terasoluna.gfw.common.sequencer. Sequencer	次のシーケンス値を取得するメソッド(getNext)とシーケンスの現在値を返却するメソッド(getCurrent)を定義しているインタフェース。
	org.terasoluna.gfw.common.sequencer. JdbcSequencer	Sequencer インタフェースのJDBC用の実装クラス。 データベースにSQLを発行してシーケンス値を取得するためのクラスである。 データベースのシーケンスオブジェクトから値を取得することを想定したクラスではあるが、データベースに登録されているファンクションを呼び出すことで、シーケンスオブジェクト以外から値を取得することもできる。

6.1.5.2.2. 共通ライブラリの利用方法

Sequencerをbean定義する。

• xxx-infra.xml

<!-- (1) -->
<bean id="articleIdSequencer" class="org.terasoluna.gfw.common.sequencer.JdbcSequencer">
     <!-- (2) -->
    <property name="dataSource" ref="dataSource" />
     <!-- (3) -->
    <property name="sequenceClass" value="java.lang.String" />
    <!-- (4) -->
    <property name="nextValueQuery"
        value="SELECT TO_CHAR(NEXTVAL('seq_article'),'AFM0000000000')" />
    <!-- (5) -->
    <property name="currentValueQuery"
        value="SELECT TO_CHAR(CURRVAL('seq_article'),'AFM0000000000')" />
</bean>

項番	説明
(1)	org.terasoluna.gfw.common.sequencer.Sequencerインタフェースを実装したクラスを、bean定義する。 上記例では、SQLを発行してシーケンス値を取得するためのクラス(JdbcSequencer)を指定している。
(2)	シーケンス値を取得するSQLを、実行するデータソースを指定する。
(3)	取得するシーケンス値の型を指定する。 上記例では、SQLで文字列へ変換しているので、java.lang.String型を指定している。
(4)	次のシーケンス値を取得するためのSQLを指定する。 上記例では、データベース(PostgreSQL)のシーケンスオブジェクトから取得したシーケンス値を、文字列としてフォーマットしている。 データベースのから取得したシーケンス値が、”1” の場合、A0000000001がSequencer#getNext()メソッドの返り値として返却される。
(5)	現在のシーケンス値を取得するためのSQLを指定する。 データベースのから取得したシーケンス値が、”2” の場合、A0000000002がSequencer#getCurrent()メソッドの返り値として返却される。

bean定義したSequencerからシーケンス値を取得する。

• Service

// omitted

// (1)
@Inject
@Named("articleIdSequencer") // (2)
Sequencer<String> articleIdSequencer;

// omitted

@Transactional
public Article createArticle(Article inputArticle) {

    String articleId = articleIdSequencer.getNext(); // (3)
    inputArticle.setArticleId(articleId);

    Article savedArticle = articleRepository.save(inputArticle);

    return savedArticle;
}

項番	説明
(1)	bean定義したSequencerオブジェクトをInjectする。 上記例では、シーケンス値は、フォーマットされた文字列として取得するため、Sequencerのジェネリクス型には、java.lang.String型を指定している。
(2)	Injectするbeanのbean名を@javax.inject.Namedアノテーションのvalue属性に指定する。 上記例では、xxx-infra.xmlに定義したbean名(articleIdSequencer)を指定している。
(3)	Sequencer#getNext()メソッドを呼び出し、次のシーケンス値を取得する。 上記例では、取得したシーケンス値を、EntityのIDとして使用している。 現在のシーケンス値を取得する場合は、Sequencer#getCurrent()メソッドを呼び出す。

Tip

bean定義するSequencerが一つの場合は、@Namedアノテーションが省略できる。複数指定する場合は、@Namedアノテーションを使用して、bean名の指定が必要となる。

6.1.5.3. Spring Frameworkから提供されているデータアクセス例外へ変換するクラス

Spring Frameworkのデータアクセス例外へ変換する役割を持つクラスを、以下に示す。

Spring Frameworkのデータアクセス例外への変換クラス

項番	クラス名	説明
	org.springframework.jdbc.support. SQLErrorCodeSQLExceptionTranslator	MyBatisや、JdbcTemplateを使った場合、本クラスによって、JDBC例外が、Spring Frameworkのデータアクセス例外に変換される。変換ルールは、XMLファイルに記載されており、デフォルトで使用されるXMLファイルは、spring-jdbc.jar内のorg/springframework/jdbc/support/sql-error-codes.xmlとなる。 クラスパス直下に、XMLファイル（sql-error-codes.xml）を配置することで、デフォルトの動作を変更することもできる。
	org.springframework.orm.jpa.vendor. HibernateJpaDialect	JPA(Hibernateの実装)を使った場合、本クラスによって、O/R Mapper例外(Hibernateの例外)がSpring Frameworkのデータアクセス例外に変換される。
	org.springframework.orm.jpa. EntityManagerFactoryUtils	HibernateJpaDialectで変換できない例外が発生した場合は、本クラスによって、JPA例外がSpring Frameworkのデータアクセス例外に変換される。
	org.hibernate.dialect.Dialect のサブクラス	JPA(Hibernateの実装)を使った場合、本クラスによって、JDBC例外とO/R Mapper例外に変換される。

6.1.5.4. Spring Frameworkから提供されているJDBCデータソースクラス

Spring Frameworkでは、JDBCデータソースの実装を提供しているが、非常にシンプルなクラスなので、商用環境で使われることは少ない。

主に単体試験時に使用されるクラスである。

Spring Frameworkから提供されているJDBCデータソース

項番	クラス名	説明
	org.springframework.jdbc.datasource. DriverManagerDataSource	アプリケーションからコネクションの取得依頼があったタイミングで、java.sql.DriverManager#getConnectionを呼び出し、新しいコネクションを生成するデータソースクラス。 コネクションのプーリングが必要な場合は、アプリケーションサーバのデータソース、または、OSS/Third-Partyライブラリから提供されているデータソースを使用すること。
	org.springframework.jdbc.datasource. SingleConnectionDataSource	DriverManagerDataSourceの子クラスで、一つのコネクションを使いまわす実装になっており、シングルスレッドで動くユニットテスト向けのデータソースクラスである。 ユニットテストでも、マルチスレッドでデータソースにアクセスする場合は、本クラスを使用すると、期待した動作にならないことがあるので、注意が必要である。
	org.springframework.jdbc.datasource. SimpleDriverDataSource	アプリケーションからコネクションの取得依頼があったタイミングで、java.sql.Driver#getConnectionを呼び出し、新しいコネクションを生成するデータソースクラス。 コネクションのプーリングが必要な場合は、アプリケーションサーバのデータソース、または、OSS/Third-Partyライブラリから提供されているデータソースを使用すること。

Spring Frameworkでは、JDBCデータソースの動作を拡張したアダプタークラスを提供している。

以下に、代表的なアダプタークラスを紹介する。

Spring Frameworkから提供されているJDBCデータソースのアダプター

項番	クラス名	説明
	org.springframework.jdbc.datasource. TransactionAwareDataSourceProxy	トランザクション管理されていないデータソースを、Spring Frameworkのトランザクション管理対象にするためのアダプタークラス。
	org.springframework.jdbc.datasource.lookup. IsolationLevelDataSourceRoute	実行中のトランザクションの独立性レベルによって、使用するデータソースを切り替えるためのアダプタークラス。