いろいろ備忘録日記

主に .NET とか Go とか Flutter とか Python絡みのメモを公開しています。

iBatis.NET奮闘記-010 (動的SQL(DynamicSQL-01(UnaryConditionElements, ParameterPresentElements))) (QueryForObject, QueryForList, dynamic, isNull, isNotNull)


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);
        }
    }
}


[Author.ibatis.xml]

<?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".