iBatisには、動的SQLを作成する機能があります。
これが使えるようになると、iBatisがグッと使いやすくなります。
動的SQLは通常以下のようにして作成します。
<sql id="xxxx" resultClass="int"> select * from xxxx <dynamic prepend="where"> <!-- 動的条件を記述. --> </dynamic> </sql>
上記を見ていただいたらお分かりのように
<dynamic prepend="where"> </dynamic>
としている部分が動的SQLの宣言部分です。
属性としているprependの部分は省略可能です。
これがどのように動作するかというと、dynamic要素の中の要素の
一つでもtrueとなった際にSQL本体にwhereの文字が追加されます。
つまりどの要素もfalseの場合は、whereは追加されません。
prependの動作は、他の動的要素でも同じ事になります。
prependの部分にandやorを指定すると、その要素が条件的に
trueとなった際に、SQLにand・orが追加されます。
で、どのような動的要素が存在するかといいますと
iBatisには、大きく分けて4つのカテゴリの動的要素が存在します。
- Binary Condition Elements
- 2項比較の要素達です。isEqualsやisLessThanなどが存在します。
- Unary Condition Elements
- 単項評価の要素達です。isNullやisNotNullなどが存在します。最もよく使うタイプです。
- Parameter Present Elements
- クエリに指定したパラメータの存在確認を行う要素達です。isParameterPresent/isNotParameterPresentの2つがあります。
- Iterate Element
- リストデータをループさせる際に利用する要素です。in句などを展開する際に利用します。
今回は、Unary Condition ElementsとParameter Present Elementsの2つを扱います。
Unary Condition Elementsの要素は、共通して以下の属性を持ちます。
- prepend
- property
- 評価を行うプロパティを指定します。
Parameter Present Elementsの要素は、共通して以下の属性を持ちます。
- prepend
以下サンプルです。
このサンプルでは、3つのクエリにそれぞれ動的SQLを設定し、いろいろなパターンで呼んでいます。
[Author.cs]
using System; using System.Collections.Generic; namespace Gsf.Samples.IBatisNet.Models { public class Author { int? _id; string _name; int? _age; public int? Id { get{ return _id; } set{ _id = value; } } public string Name{ get{ return _name; } set{ _name = value; } } public int? Age { get{ return _age; } set{ _age = value; } } public override string ToString() { return string.Format("id:{0}, name:{1}, age:{2}", Id, Name, Age); } } }
<?xml version="1.0" encoding="utf-8" ?> <sqlMap namespace="Author" xmlns="http://ibatis.apache.org/mapping" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <alias> <typeAlias type="Gsf.Samples.IBatisNet.Models.Author" alias="Author"/> </alias> <statements> <sql id="author-sql-basic"> <![CDATA[ select a.AuthorId as Id ,a.Name as Name ,a.Age as Age from Authors a ]]> </sql> <sql id="author-sql-orderby"> <![CDATA[ order by a.AuthorId ]]> </sql> <select id="CountAuthors" parameterClass="Author" resultClass="int"> <![CDATA[ select count(AuthorId) from Authors ]]> <!-- 動的SQLの設定. --> <dynamic prepend="where"> <isNotNull prepend="and" property="Name"> <![CDATA[ Name like '%$Name$%' ]]> </isNotNull> <isNotNull prepend="and" property="Age"> <![CDATA[ Age = #Age# ]]> </isNotNull> </dynamic> </select> <select id="FindAuthors" parameterClass="int" resultClass="Author"> <!-- SQLの基本部分を読み込み. --> <include refid="author-sql-basic"/> <!-- 動的SQLの設定 --> <dynamic prepend="where"> <isParameterPresent prepend="and"> <![CDATA[ a.AuthorId = #value# ]]> </isParameterPresent> </dynamic> <!-- ソート条件を読み込み --> <include refid="author-sql-orderby"/> </select> <select id="SearchAuthors" parameterClass="Author" resultClass="Author"> <!-- SQLの基本部分を読み込み. --> <include refid="author-sql-basic"/> <!-- 動的SQLの設定 --> <dynamic prepend="where"> <!-- Idが指定されている場合は、そちらを最優先とする。 --> <isNotNull prepend="and" property="Id"> <![CDATA[ a.AuthorId = #Id# ]]> </isNotNull> <!-- Idが指定されていない場合は、NameとAgeを検索条件に加える。 --> <isNull property="Id"> <isNotNull prepend="and" property="Name"> <![CDATA[ a.Name like '%$Name$%' ]]> </isNotNull> <isNotNull prepend="and" property="Age"> <![CDATA[ a.Age = #Age# ]]> </isNotNull> </isNull> </dynamic> <!-- ソート条件を読み込み --> <include refid="author-sql-orderby"/> </select> </statements> </sqlMap>
上記のSQL定義の中で、$Name$と指定している部分がありますが、これはプロパティの値を
そのまま置換しろと指定しています。$$で囲っている場合は、例えば、文字列の場合は''で
囲むというなどの処理が行われず、生の値がそのまま置換されます。
[テストユニットクラス]
using System; using System.Collections.Generic; using NUnit.Framework; using IBatisNet.DataMapper; using Gsf.Samples.IBatisNet.Models; namespace Gsf.Samples.IBatisNet { [TestFixture] public class IBatisSample010 { [Test] public void 動的SQLの動作確認(){ // // 複数のクエリを一つのコネクション内で発行する為に、セッションを開く. // using(ISqlMapSession session = Mapper.Instance().OpenConnection()){ // // マッパーオブジェクトを取得. // ISqlMapper sqlMap = session.SqlMapper; // // 現在のAuthorの件数を取得. // int allAuthorsCount = sqlMap.QueryForObject("Author.CountAuthors", null); // // FindAuthorsクエリに対して、IDパラメータを指定せずにクエリを発行し、全件取得できるかどうか? // Assert.AreEqual(allAuthorsCount, sqlMap.QueryForList ("Author.FindAuthors", null).Count); // // FindAuthorsクエリに対して、IDパラメータを指定してクエリを発行し、そのIDの分のみが結果として取得できているか? // Assert.AreEqual(1, sqlMap.QueryForList ("Author.FindAuthors", 1).Count); ///////////////////////////////////////////////////////////////////////////////////// // // 検索条件をAuthorオブジェクトとして指定し、該当する結果が取得できるかどうか? // // Authorオブジェクトをnullにしてクエリを実行し、全件取得できるかどうか? Assert.AreEqual(allAuthorsCount, sqlMap.QueryForList ("Author.SearchAuthors", null).Count); // // Authorオブジェクトを指定するが、全プロパティの値をnullに設定してクエリを発行し、全件取得できるかどうか? // Author aAuthor = new Author(); aAuthor.Id = null; aAuthor.Name = null; aAuthor.Age = null; Assert.AreEqual(allAuthorsCount, sqlMap.QueryForList ("Author.SearchAuthors", aAuthor).Count); // // IDパラメータを指定し、IDパラメータの検索条件が優先されて結果が1件となるかどうか? // (Nameがgsfで始まるデータが複数存在するとします。) // aAuthor.Id = 1; aAuthor.Name = "gsf"; Assert.AreEqual(1, sqlMap.QueryForList ("Author.SearchAuthors", aAuthor).Count); // // IDパラメータを指定せずに、Nameプロパティを指定し、マッチするデータが取得できているかどうか? // // 予め、目的のクエリ結果の件数を取得しておく。 aAuthor.Id = null; int nameMatchedCount = sqlMap.QueryForObject ("Author.CountAuthors", aAuthor); Assert.AreEqual(nameMatchedCount, sqlMap.QueryForList ("Author.SearchAuthors", aAuthor).Count); // // Nameプロパティに加え、Ageプロパティも指定し、マッチするデータが取得できているかどうか? // // 予め、目的のクエリ結果の件数を取得しておく。 aAuthor.Age = 28; int nameAndAgeMatchedCount = sqlMap.QueryForObject ("Author.CountAuthors", aAuthor); Assert.AreEqual(nameAndAgeMatchedCount, sqlMap.QueryForList ("Author.SearchAuthors", aAuthor).Count); } } } class DummyEntryPoint010{ static void Main(){ // // nop; // } } }
実行すると、例えば以下のようになります。指定した条件を元に同じクエリで
発行されているSQLが異なっていますね。
[DEBUG] IBatisNet.DataMapper.Configuration.Statements.PreparedStatementFactory - Statement Id: [Author.CountAuthors] Prepared SQL: [select count(AuthorId) from Authors] [DEBUG] IBatisNet.DataMapper.Commands.DefaultPreparedCommand - Statement Id: [Author.CountAuthors] PreparedStatement : [select count(AuthorId) from Authors] [DEBUG] IBatisNet.DataMapper.SqlMapSession - Open Connection "51156490" to "Microsoft SQL Server, provider V2.0.0.0 in framework .NET V2.0". [DEBUG] IBatisNet.DataMapper.Configuration.Statements.PreparedStatementFactory - Statement Id: [Author.FindAuthors] Prepared SQL: [select a.AuthorId as Id ,a.Name as Name ,a.Age as Age from Authors a order by a.AuthorId] [DEBUG] IBatisNet.DataMapper.Commands.DefaultPreparedCommand - Statement Id: [Author.FindAuthors] PreparedStatement : [select a.AuthorId as Id ,a.Name as Name ,a.Age as Age from Authors a order by a.AuthorId] [DEBUG] IBatisNet.DataMapper.Configuration.Statements.PreparedStatementFactory - Statement Id: [Author.FindAuthors] Prepared SQL: [select a.AuthorId as Id ,a.Name as Name ,a.Age as Age from Authors a where a.AuthorId = @param0 order by a.AuthorId] [DEBUG] IBatisNet.DataMapper.Commands.DefaultPreparedCommand - Statement Id: [Author.FindAuthors] PreparedStatement : [select a.AuthorId as Id ,a.Name as Name ,a.Age as Age from Authors a where a.AuthorId = @param0 order by a.AuthorId] [DEBUG] IBatisNet.DataMapper.Commands.DefaultPreparedCommand - Statement Id: [Author.FindAuthors] Parameters: [@param0=[value,1]] [DEBUG] IBatisNet.DataMapper.Commands.DefaultPreparedCommand - Statement Id: [Author.FindAuthors] Types: [@param0=[Int32, System.Int32]] [DEBUG] IBatisNet.DataMapper.Configuration.Statements.PreparedStatementFactory - Statement Id: [Author.SearchAuthors] Prepared SQL: [select a.AuthorId as Id ,a.Name as Name ,a.Age as Age from Authors a order by a.AuthorId] [DEBUG] IBatisNet.DataMapper.Commands.DefaultPreparedCommand - Statement Id: [Author.SearchAuthors] PreparedStatement : [select a.AuthorId as Id ,a.Name as Name ,a.Age as Age from Authors a order by a.AuthorId] [DEBUG] IBatisNet.DataMapper.Configuration.Statements.PreparedStatementFactory - Statement Id: [Author.SearchAuthors] Prepared SQL: [select a.AuthorId as Id ,a.Name as Name ,a.Age as Age from Authors a order by a.AuthorId] [DEBUG] IBatisNet.DataMapper.Commands.DefaultPreparedCommand - Statement Id: [Author.SearchAuthors] PreparedStatement : [select a.AuthorId as Id ,a.Name as Name ,a.Age as Age from Authors a order by a.AuthorId] [DEBUG] IBatisNet.DataMapper.Configuration.Statements.PreparedStatementFactory - Statement Id: [Author.SearchAuthors] Prepared SQL: [select a.AuthorId as Id ,a.Name as Name ,a.Age as Age from Authors a where a.AuthorId = @param0 order by a.AuthorId] [DEBUG] IBatisNet.DataMapper.Commands.DefaultPreparedCommand - Statement Id: [Author.SearchAuthors] PreparedStatement : [select a.AuthorId as Id ,a.Name as Name ,a.Age as Age from Authors a where a.AuthorId = @param0 order by a.AuthorId] [DEBUG] IBatisNet.DataMapper.Commands.DefaultPreparedCommand - Statement Id: [Author.SearchAuthors] Parameters: [@param0=[Id,1]] [DEBUG] IBatisNet.DataMapper.Commands.DefaultPreparedCommand - Statement Id: [Author.SearchAuthors] Types: [@param0=[Int32, System.Int32]] [DEBUG] IBatisNet.DataMapper.Configuration.Statements.PreparedStatementFactory - Statement Id: [Author.CountAuthors] Prepared SQL: [select count(AuthorId) from Authors where Name like '%gsf%'] [DEBUG] IBatisNet.DataMapper.Commands.DefaultPreparedCommand - Statement Id: [Author.CountAuthors] PreparedStatement : [select count(AuthorId) from Authors where Name like '%gsf%'] [DEBUG] IBatisNet.DataMapper.Configuration.Statements.PreparedStatementFactory - Statement Id: [Author.SearchAuthors] Prepared SQL: [select a.AuthorId as Id ,a.Name as Name ,a.Age as Age from Authors a where a.Name like '%gsf%' order by a.AuthorId] [DEBUG] IBatisNet.DataMapper.Commands.DefaultPreparedCommand - Statement Id: [Author.SearchAuthors] PreparedStatement : [select a.AuthorId as Id ,a.Name as Name ,a.Age as Age from Authors a where a.Name like '%gsf%' order by a.AuthorId] [DEBUG] IBatisNet.DataMapper.Configuration.Statements.PreparedStatementFactory - Statement Id: [Author.CountAuthors] Prepared SQL: [select count(AuthorId) from Authors where Name like '%gsf%' and Age = @param0] [DEBUG] IBatisNet.DataMapper.Commands.DefaultPreparedCommand - Statement Id: [Author.CountAuthors] PreparedStatement : [select count(AuthorId) from Authors where Name like '%gsf%' and Age = @param0] [DEBUG] IBatisNet.DataMapper.Commands.DefaultPreparedCommand - Statement Id: [Author.CountAuthors] Parameters: [@param0=[Age,28]] [DEBUG] IBatisNet.DataMapper.Commands.DefaultPreparedCommand - Statement Id: [Author.CountAuthors] Types: [@param0=[Int32, System.Int32]] [DEBUG] IBatisNet.DataMapper.Configuration.Statements.PreparedStatementFactory - Statement Id: [Author.SearchAuthors] Prepared SQL: [select a.AuthorId as Id ,a.Name as Name ,a.Age as Age from Authors a where a.Name like '%gsf%' and a.Age = @param0 order by a.AuthorId] [DEBUG] IBatisNet.DataMapper.Commands.DefaultPreparedCommand - Statement Id: [Author.SearchAuthors] PreparedStatement : [select a.AuthorId as Id ,a.Name as Name ,a.Age as Age from Authors a where a.Name like '%gsf%' and a.Age = @param0 order by a.AuthorId] [DEBUG] IBatisNet.DataMapper.Commands.DefaultPreparedCommand - Statement Id: [Author.SearchAuthors] Parameters: [@param0=[Age,28]] [DEBUG] IBatisNet.DataMapper.Commands.DefaultPreparedCommand - Statement Id: [Author.SearchAuthors] Types: [@param0=[Int32, System.Int32]] [DEBUG] IBatisNet.DataMapper.SqlMapSession - Dispose SqlMapSession [DEBUG] IBatisNet.DataMapper.SqlMapSession - Close Connection "51156490" to "Microsoft SQL Server, provider V2.0.0.0 in framework .NET V2.0".