はじめに
チームのメンバーが作成したプログラムを見ていて「DBアクセス禁止のレイヤ(プレゼンテーション層・サービス層など)からSQLを実行していた」とか「問題を引き起こしているSQLの特定が困難だった」といった経験はありませんか。本稿では、このような問題の解決に有効な「iBATIS.NET」というオープンソースのプロダクトについて紹介します。「iBATIS.NET」は「SQL Maps」と「DAO Framework」というの2つのフレームワークから構成されており、DBアクセスを簡単にすることができます。
SQL Maps
XMLに定義したSQLを実行し、その実行結果とオブジェクトをマッピングする機能を提供します。NHibernateやGentle.NETといった他のO/Rマッピングツールとは異なり開発者がSQLを記述する必要がありますが、既存のSQLを再利用できたり、パフォーマンスチューニングを行いやすいといったメリットがあります。
DAO Framework
パーシステンス層(DBアクセス)とサービス層(ビジネスロジック)の依存性を下げる簡易のDI(Dependency Injectioun)機能を提供します(DIとはインターフェースと実装を分離して、実際のコンポーネントの生成を設定ファイルなどで管理しようという考え方です)。
「iBATIS.NET」を導入すると「プログラムからSQLを分離し設定ファイルで集中管理できる」「サービス層のプログラミングがシンプルになる」「パーシステンス層の変更がサービス層へ影響を与えにくい」といったメリットがあります。また、SQLを実行した際にはログとしてSQL文と入力パラメータが表示されるため、デバッグが行いやすというメリットもあります。今後、データベースにアクセスするアプリケーションを開発する際には選択肢の1つとして検討してみてはいかがでしょうか。
本稿では、この「iBATIS.NET」のうち、SQL Maps機能について紹介します。DAO Framework機能については、別稿「iBATIS.NETにてO/Rマッピングを行う(DAO Framework編)」を参照してください。
対象読者
.NETにて開発を行っている方、また基本的なSQLを理解している方を対象としています。
必要な環境
サンプルはVisual Studio .NET 2003で作成し、.NET Framework 1.1で動作確認をしています。
サンプルアプリケーションについて
本稿のサンプルは書籍マスタのメンテナンスを行うWindowsアプリケーションです。
「SQL Maps」を用いて、MDB(Microsoft Accessのデータベース形式)にアクセスを行い、書籍テーブルへの追加・更新・参照・削除といったデータベース操作を実行します。なお、iBATIS.NET(DataMapper-1.2.1)はサンプルに同梱されているため、改めてiBATIS.NETを入手する必要はありません。
もし、「SQL Maps」と「DAO Framework」の両方を用いたプログラムの例をご覧になりたい場合には別稿のサンプルをダウンロードしてください。
iBATIS.NETの入手
iBATIS.NETはApacheのWebサイトからダウンロード可能です。「SQL Maps」と「DAO Framework」という2つのフレームワークから構成されていますので、最新バージョンの「DataAccess-bin-X.X.X.XXX.zip」と「DataMapper-bin-X.X.X.XXXX.zip」を入手してください。なおiBATISにはJava版と.NET版がありますので「iBATIS for Java」ではなく「iBATIS.NET」をダウンロードするようにしてください。また、必要に応じてソースコード・ドキュメント・チュートリアルなどをダウンロードすると良いでしょう。
VisualStudio.NETの設定
VisualStudioから使用する場合は、ソリューションエクスプローラの[参照設定]を右クリックして[参照の追加]を行います。「SQL Maps」を使用するときは「IBatisNet.Common」と「IBatisNet.DataMapper」を、「DAO Framework」を使用するときは「IBatisNet.Common」と「IBatisNet.DataAccess」を追加してください。
iBATIS.NETの対応データベース
iBATISはADO.NETを介して動作するため、対応するデータベースはADO.NETのデータプロバイダに依存します。
プロバイダ | 説明 |
sqlServer1.0 | Microsoft SQL Server 7.0/2000[.NET V1.0] |
sqlServer1.1 | Microsoft SQL Server 7.0/2000[.NET V1.1] |
OleDb1.1 | OleDb(Access)[.NET V1.1] |
Odbc1.1 | ODBC[.NET V1.1] |
oracle9.2 | Oracle V9.2(OracleProvider) |
oracle10.1 | Oracle V10.1(OracleProvider) |
oracleClient1.0 | Oracle Microsoft provider |
ByteFx | MySQL ByteFx provider |
MySql | MySQL provider |
SQLite3 | SQLite.NET provider |
Firebird1.7 | Firebird SQL |
PostgreSql0.7 | PostgreSql, Npgsql provider |
iDb2.10 | IBM.Data.DB2.iSeries |
執筆時点でiBATIS.NETは上記のデータベースに対応しています。ただし、SQLServer、OleDb、Odbc以外に接続する場合は、そのデータベース固有のライブラリ(DLL)が必要になります。接続できるデータプロバイダの詳細については、iBATISのFAQと「providers.config」ファイルをご覧ください。
「SQL Maps」の実装(更新系:INSERTの場合)
それでは「SQL Maps」でO/Rマッピングをする方法を紹介します。「SQL Maps」のメリットは以下の通りです。
- SQLとプログラムの分離。
- SQLの入力パラメータの設定の省略と、実行結果のオブジェクトへのマップの実現。
- ビジネスロジッククラスとデータアクセスクラスの分離。
ここでは「書籍(BOOK)」テーブルのマッピングを行う例を紹介します。
列名 | 型 | 桁数 | 説明 |
ISBN | 文字列型 | 10 | 図書コード(主キー) |
TITLE | 文字列型 | 50 | 書籍名 |
SALE_DATE | 日付時刻型 | 8 | 発売日 |
PRICE | 数値型 | 8 | 価格 |
更新処理に必要な手順は以下の3つです。
- マッピング対象のクラス「Book.cs」の記述。
- マッピングファイル「Book.xml」に
INSERT
文の記述。 - 管理ファイル「SqlMap.config」に「Book.xml」を登録。
マッピング対象オブジェクト「Book.cs」の記述
「BOOK」テーブルのデータを格納するBook
クラスを記述します。
using System; namespace iBatisSample.Domain.User { public class Book { private string _Isbn; private string _Title; private DateTime _SaleDate; private int _Price; public string Isbn { get{ return _Isbn; } set{_Isbn = value; } } //(以下同様にget/set設定) } }
このクラスはアクセサメソッドにて構成されるシンプルなオブジェクトです。層間のデータの受け渡しに使用されるため、DTO(Data Transfer Object)とも呼ばれます。
マッピングファイル「Book.xml」にINSERT文を記述
マッピングファイルと呼ばれる設定ファイルに、SQLを<insert>
要素として記述します。id
属性にはSQLの定義名を、parameterClass
属性には引数用のクラスを指定します。
<insert id="InsertBook" parameterClass="Book"> insert into BOOK (ISBN,TITLE,SALE_DATE,PRICE) values (#Isbn#, #Title#, #SaleDate#,#Price#) </insert>
ここでは「#Isbn#
」のように「#
」で囲まれている項目に注目してください。これはSQLの実行時に動的に設定されるパラメータで内部的には以下のようなSQLに展開されます。
--OLE DBの場合 insert into BOOK (ISBN,TITLE,SALE_DATE,PRICE) values ( ? , ? , ? , ? ) --SQL Serverの場合 insert into BOOK (ISBN,TITLE,SALE_DATE,PRICE) values ( @Isbn ,@Title , @SaleDate , @Price )
「SQL Maps」は、このSQLのバインド変数にBook
クラスのプロパティの値を自動的にセットしてくれます(「#Isbn#
」というパラメータに「Book
クラスのIsbn
プロパティ」の値をセットします)。
このSQLのパラメータに値を簡単にセットできるところが、「SQL Maps」の便利な機能の1つといえるでしょう。
管理ファイル「SqlMap.config」に「Book.xml」を登録
マッピングファイルを新しく定義した場合は、SQL Mapsの管理ファイル「SqlMap.config」に登録します。
<!-- マッピング定義ファイル --> <sqlMaps> <sqlMap resource="./Maps/Book.xml"/> <sqlMap resource="./Maps/Hoge.xml"/> <sqlMap resource="./Maps/Moge.xml"/> </sqlMaps>
このように<sqlMaps>
要素として「MapsフォルダのBook.xmlファイル」を登録します。また、「SqlMap.config」には以下のようにデータベースの接続先を設定することができます。
<database> <!-- MDBへの接続例 --> <provider name="OleDb1.1"/> <dataSource name="Access" connectionString= "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=iBatisSample.mdb"/> </database>
ただし、別稿の「DAO Framework」を使用する場合は「dao.config」の接続先が優先されますのでご注意ください。
クライアント側のプログラム
SQL Mapsで定義したクラスを利用する側(クライアント側)のプログラムは以下のようなイメージになります。
//オブジェクトに値をセット Book book = new Book(); book.Isbn = "4123456789"; book.Title = "iBATIS.NET入門"; book.Price = 2800; book.SaleDate = new DateTime(2005,4,1); //SQLの実行 Mapper.Instance().Insert("InsertBook",book);
「Mapper.Instance().Insert("InsertBook",book)
」では、iBATISが提供しているクラスSqlMapper
のInsert
メソッドを使用してデータを登録しています。「Book.xml」にて定義したid
の名称を1つ目の引数に、parameterClass
のクラス変数名を2つ目の引数に設定する必要があります。
ここまではINSERT
の実行方法について説明をしました。なお、UPDATE
、DELETE
についても同様の設定(<update>
要素、<delete>
要素)にて実行することができます。
「SQL Maps」の実装(参照系:SELECTの場合)
引き続き、SELECT
を実行し、結果をオブジェクトにマッピングする方法を紹介します。
マッピングファイル「Book.xml」にSELECT文を記述
SELECT
の場合は、マッピングファイルの<select>
要素にてSQLを定義します。
<statements> <select id="GetBookListByTitle" resultMap="BookResult" parameterClass="System.String"> select ISBN, TITLE, SALE_DATE, PRICE from BOOK where TITLE LIKE #VALUE# order by ISBN </select> </statements>
ここではINSERT
には存在しなかった「resultMap」という属性があることに注目してください。これは、SELECT
の実行結果をオブジェクトにマッピングするための定義名です。マッピングの定義方法は以下のように「class
」に対象クラス、「property
」にクラスのプロパティ名、「column
」に列名を設定します。
<alias> <typeAlias alias="Book" type="IBatisSample.Domain.Books.Book,IBatisSqlMapSample" /> </alias> <resultMaps> <resultMap id="BookResult" class="Book"> <result property="Isbn" column="ISBN" /> <result property="Title" column="TITLE" /> <result property="SaleDate" column="SALE_DATE" /> <result property="Price" column="PRICE" /> </resultMap> </resultMaps>
この例では、SELECTした結果の「ISBN
」列の値が「Book
クラスのISBN
プロパティ」へ、「TITLE
」列の値が「Book
クラスのTitle
プロパティ」へと自動的にセットされます。
クライアント側のプログラム
クライアント側のプログラムは以下のようなイメージになります。
//SQLの実行 IList books = Mapper.Instance().QueryForList("GetBookListByTitle","%NET%"); //結果を出力 foreach (Book book in books) { Console.WriteLine(book.Title); }
「IList books = Mapper.Instance().QueryForList("GetBookListByTitle","%NET%")
」では、SqlMapper
のQueryForList
メソッドを使用して、タイトルに「NET」が含まれる人をリストにて取得しています。
マッピングファイルの設定によってSELECT
の結果がオブジェクトへ簡単にセットできるところが「SQL Maps」の長所と言えるでしょう。
高度なSQLの作成
iBATIS.NETにはSQL文のWHERE句などを動的に組み立てる機能もあります。この機能を使うと条件が違うだけのSQLを定義する必要がなくなるので大変便利です。この動的なSQL生成は要素<dynamic>
を用いて実現することができます。
//3000円以上の書籍を抽出 Hashtable param = new Hashtable(); param.Add("price","3000"); param.Add("priceCompare","以上"); return ExecuteQueryForList("GetBookListByPrice",param);
<select id="GetBookListByPrice" resultMap="BookResult" parameterClass="System.Collections.Hashtable"> select ISBN, TITLE, SALE_DATE, PRICE from BOOK <dynamic prepend="where"> <isEqual property="priceCompare" compareValue="以上"> <![CDATA[ PRICE >= #price# ]]> </isEqual> <isEqual property="priceCompare" compareValue="以下"> <![CDATA[ PRICE <= #price# ]]> </isEqual> </dynamic> order by PRICE </select>
この例では、引数「priceCompare
」に「以上」が設定されていれば、WHERE句の条件に「PRICE >= #price#
」が設定され、「以下」が設定されていれば「PRICE <= #price#
」が設定されます。なお<dynamic>
要素の属性prepend
は動的に追加するSQL文の前に付加する文字列になります。つまり引数「priceCompare
」に「以上」か「以下」という文字列がセットされていればwhere
という文字も追加されます。なお、SQLの条件には今回紹介した<isEqual>
以外にも<isGreaterThan><isPropertyAvailable><isNull><isEmpty>
など様々な条件式が用意されています。
<![CDATA[
」と「]]>
」という文字はXMLのエスケープ文字です。このサンプルでは不等号の>
や<
を使用していますので、XMLファイルのタグと誤認されないように、「<![CDATA[
」と「]]>
」によって文字列を囲んでいます。「SQL Maps」の整理
Sql Mapsの主要クラスと設定ファイルについて下図に整理します。
SQL Mapsの主要メソッド
Mapper
クラスのInstance
メソッドによって取得できるSqlMapper
クラスの主要メソッドは下表の通りです。
メソッド名 | 処理内容 |
QueryForObject(statementName ,parameterObject) | 単一オブジェクトの取得SQL実行 |
QueryForList(statementName ,parameterObject) | 複数オブジェクトの取得SQL実行 |
Update(statementName ,parameterObject) | 更新SQLの実行 |
Insert(statementName ,parameterObject) | 挿入SQLの実行 |
Delete(statementName ,parameterObject) | 削除SQLの実行 |
BeginTransaction | トランザクションの開始 |
CommitTransaction | トランザクションのコミット |
RollBackTransaction | トランザクションのロールバック |
実行したい処理に応じて、SqlMapper
クラスのメソッドを呼び出すようにしてください。
引数の型と戻り値の型
使用可能な引数と戻り値の型は「プリミティブ型(int
/string
など)」「オブジェクト(Book
など)」「ハッシュテーブル(HashTable
など)」の3パターンで、マッピングファイルに設定できるSQL文の属性は下表の通りです。
属性 | 説明 | insert | update | delete | select | procedure |
id | SQLの定義名 | ○ | ○ | ○ | ○ | ○ |
parameterMap(parameterClass) | 引数用のマップ(クラス)名 | ○ | ○ | ○ | ○ | ○ |
resultMap(resultClass) | 結果取得用のマップ(クラス)名 | × | × | × | ○ | ○ |
- ○・・・設定可
- ×・・・設定不可
select
とprocedure
ではデータを取得するため「parameterMap(parameterClass)
」と「resultMap(resultClass)
」の両方が指定できますが、insert
やupdate
やdelete
では「parameterMap(parameterClass)
」のみ指定できます。
設定ファイル
iBATIS.NETには以下のような設定ファイル(*.config/*.xml)が存在します。
ファイル名 | 設定する内容 |
SqlMap.config | 接続DBの設定(DAO Frameworkを使用しない場合)・マッピング定義ファイルの登録 |
マッピング定義(*.xml) | 実行するSQLの定義・取得結果とオブジェクトのマッピング定義 |
dao.config | 接続DBの設定・プロダクト(SQL Maps/NHibernate/ADO)の選択・インターフェースと実装クラスの定義 |
database.config | 接続DB名やユーザIDを設定するプロパティファイル(このファイル名は「dao.config」にて設定) |
providers.config | iBATIS.NETで動作させるデータベースプロバイダの設定 |
今回紹介した「SQL Maps」では「SqlMap.config」と「マッピング定義ファイル」の2つの設定ファイルの編集が必要です。なお、これらのファイルは「exe/dll」と同じフォルダに配置するようにしてください。
まとめ
iBATIS.NETのSQL Mapsを利用することで、SQL定義を外部のXMLファイルに設定することができ、画面やビジネスロジックの開発者はSQLを意識することなくオブジェクトを用いた開発に専念することができるようになります。また、本稿で紹介した機能以外にも「プライマリキーの生成」「$~$によるパラメータのセット」「SQL文字列の継承」「NULLの際のデフォルト設定」「キャッシュ」など、さらに便利な機能もありますので、興味のある方はマニュアルに目を通して見ると良いでしょう。