3.5 Result Maps

语法

<resultMap id="resultMapIdentifier" 
           [class="fullyQualifiedClassName, assembly|typeAlias"] 
           [extends="[sqlMapNamespace.]resultMapId"]>

   <constructor > 
       <argument property="argumentName" 
           column="columnName"
           [columnIndex="columnIndex"] 
           [dbType="databaseType"] 
           [type="propertyCLRType"]
           [resultMapping="resultMapName"]
           [nullValue="nullValueReplacement"] 
           [select="someOtherStatementName"] 
           [typeHandler="fullyQualifiedClassName, assembly|typeAlias"] />
   </constructor > 

   <result property="propertyName" 
           column="columnName"
           [columnIndex="columnIndex"] 
           [dbType="databaseType"] 
           [type="propertyCLRType"]
           [resultMapping="resultMapName"]
           [nullValue="nullValueReplacement"] 
           [select="someOtherStatementName"] 
           [lazyLoad="true|false"]
           [typeHandler="fullyQualifiedClassName, assembly|typeAlias"]
   />
   <result ... .../>
   <result ... .../>
    // Inheritance support
   <discriminator column="columnName" 
                     [type|typeHandler="fullyQualifiedClassName, assembly|typeAlias"]
   />
    <subMap value="discriminatorValue" 
               resultMapping="resultMapName"
   />
   <subMap .../> 
</resultMap>

3.5.1. Extending resultMaps

使用extends属性扩展

3.5.2. <resultMap> attributes

3.5.2.1. id

3.5.2.2. class

3.5.2.3. extends

生命继承自哪个<resultMap>

3.5.2.4. groupBy 

3.5.13 

3.5.3. <constructor> element

<resultMap id="account-result-constructor" class="Account" >
	 <constructor>
		<argument argumentName="id" column="Account_ID"/>
		<argument argumentName="firstName" column="Account_FirstName"/>
		<argument argumentName="lastName" column="Account_LastName"/>
	</constructor>
	<result property="EmailAddress" column="Account_Email" nullValue="no_email@provided.com"/>
	<result property="BannerOption" column="Account_Banner_Option" dbType="Varchar" type="bool"/>
	<result property="CartOption"	column="Account_Cart_Option" typeHandler="HundredsBool"/>
</resultMap>

3.5.3.1. argumentName

3.5.3.2. column

3.5.3.3. columnIndex

column index 列序号，提升有限的性能，99%的程序都不会以牺牲可读性和可维护性为代价来提升性能

3.5.3.4. dbType

3.5.3.5. type

3.5.3.6. resultMapping

当Property不是基元数据类型（primitive）时，而是一个复杂数据类型，需要给他指定一个完整的resultMap。

使用时注意

    • 使用fully qualified name 完全限定名（namespace.resultMapId）
    • 注意argument大小写敏感

同<result>标签下的resultMapping属性用法相同

3.5.3.7. nullValue

3.5.3.8. select

lazyload 属性一起使用

3.5.3.9. typeHandler

3.5.4. <result> Elements

3.5.4.1. property

3.5.4.2. column

3.5.4.3. columnIndex

3.5.4.4. dbType

3.5.4.5. type

3.5.4.6. resultMapping

使用时注意

    • 使用fully qualified name 完全限定名（namespace.resultMapName）
    • 注意argument大小写敏感

3.5.4.7. nullValue

数据库结果中为空，则替换为设置的值

3.5.4.8. select

3.5.4.9. lazyLoad

3.5.4.10. typeHandler

1.实现IBatisNet.DataMapper.TypeHandlers.ITypeHandlerCallback接口

using System;
using IBatisNet.DataMapper.TypeHandlers;

namespace BigApp.Common.TypeHandlers
{
 /// <summary>
 /// GuidVarcharTypeHandlerCallback.
 /// </summary>
 public class GuidVarcharTypeHandlerCallback : ITypeHandlerCallback
 {
  private const string GUIDNULL = "00000000-0000-0000-0000-000000000000";

  public object ValueOf(string nullValue)
  {
   if (GUIDNULL.Equals(nullValue)) 
   {
    return Guid.Empty;
   } 
   else 
   {
    throw new Exception(
     "Unexpected value " + nullValue + 
     " found where "+GUIDNULL+" was expected to represent a null value.");
   }  
  }

  public object GetResult(IResultGetter getter)
  {
   try {
    Guid result = new Guid(getter.Value.ToString());
    return result;
   } 
   catch
   {
     throw new Exception(
     "Unexpected value " + getter.Value.ToString() + 
     " found where a valid GUID string value was expected.");
   }
  }

  public void SetParameter(IParameterSetter setter, object parameter)
  {
   setter.Value = parameter.ToString();
  }

 }
}
View Code

SqlMap.xml配置

2.配置

方法1

<alias>
 <typeAlias alias="GuidVarchar" 
         type="BigApp.Common.TypeHandlers.GuidVarcharTypeHandlerCallback,
               BigApp.Common"/>
</alias>
 
<resultMaps>                                    
 <resultMap id="boc-result"  class="BudgetObjectCode">
  <result property="Code" column="BOC_CODE" dbType="Varchar2"/>
  <result property="Description" column="BOC_DESC" dbType="Varchar2"/>
  <result property="GuidProperty" column="BOC_GUID" typeHandler="GuidVarchar"/>
 </resultMap>
</resultMaps>
View Code

方法2

[Our SqlMap.config]
<alias>
 <typeAlias alias="GuidVarchar" 
         type="BigApp.Common.TypeHandlers.GuidVarcharTypeHandlerCallback,
               BigApp.Common"/>
</alias>

<typeHandlers>
 <typeHandler type="guid" dbType="Varchar2" callback="GuidVarchar"/>
</typeHandlers> 


[One of our SqlMap.xml files]
<parameterMaps>
 <parameterMap id="boc-params">
  <parameter property="Code" dbType="Varchar2" size="10"/>
  <parameter property="Description" dbType="Varchar2" size="100"/>
  <parameter property="GuidProperty" dbType="Varchar2" type="guid"/>
 </parameterMap>
</parameterMaps>

<resultMaps>                                    
 <resultMap id="boc-result"  class="BudgetObjectCode">
  <result property="Code" column="BOC_CODE" dbType="Varchar2"/>
  <result property="Description" column="BOC_DESC" dbType="Varchar2"/>
  <result property="GuidProperty" column="BOC_GUID" dbType="Varchar2" type="guid"/>
 </resultMap>
</resultMaps>
View Code

 

3.5.6. Inheritance Mapping

数据库表

USE [Test]
GO
/****** Object:  Table [dbo].[Documents]    Script Date: 2020/3/25 12:22:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Documents](
    [Id] [int] NOT NULL,
    [Title] [varchar](32) NULL,
    [Type] [varchar](32) NULL,
    [PageNumber] [int] NULL,
    [City] [varchar](32) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[Documents] ([Id], [Title], [Type], [PageNumber], [City]) VALUES (1, N'title1', N'City1', 0, N'北京')
GO
INSERT [dbo].[Documents] ([Id], [Title], [Type], [PageNumber], [City]) VALUES (2, N'title2', N'City2', 0, N'河北')
GO
INSERT [dbo].[Documents] ([Id], [Title], [Type], [PageNumber], [City]) VALUES (3, N'title3', N'City1', 0, N'天津')
GO
INSERT [dbo].[Documents] ([Id], [Title], [Type], [PageNumber], [City]) VALUES (4, N'title4', N'Book1', 4, N'0')
GO
INSERT [dbo].[Documents] ([Id], [Title], [Type], [PageNumber], [City]) VALUES (5, N'title5', N'Book2', 5, N'0')
GO
INSERT [dbo].[Documents] ([Id], [Title], [Type], [PageNumber], [City]) VALUES (6, N'title6', N'doc', NULL, NULL)
GO
View Code

实体

 public class BaseDoc
    {
        private int id;

        public int Id
        {
            get { return id; }
            set { id = value; }
        }

        private string title;

        public string Title
        {
            get { return title; }
            set { title = value; }
        }
    }

    public class Book : BaseDoc
    {
        private int pageNumber;

        public int PageNumber
        {
            get { return pageNumber; }
            set { pageNumber = value; }
        }

    }

    public class CityC : BaseDoc
    {
        private string city;

        public string City
        {
            get { return city; }
            set { city = value; }
        }
    }
View Code

SqlMap.xml

 <typeAlias alias="BaseDoc" type="ConsoleApp5.BaseDoc, ConsoleApp5"></typeAlias>
    <typeAlias alias="Book" type="ConsoleApp5.Book, ConsoleApp5"></typeAlias>
    <typeAlias alias="CityC" type="ConsoleApp5.CityC, ConsoleApp5"></typeAlias>

<resultMap id="BaseDocResult" class="BaseDoc">
      <result property="Id" column="Id"></result>
      <result property="Title" column="Title" />
      <discriminator column="Type" type="string"/>
      <subMap value="Book" resultMapping="BookResult"/>
      <subMap value="City" resultMapping="CityCResult"/>
    </resultMap>

    <resultMap id="BookResult" class="Book" extends="BaseDocResult">
      <result property="PageNumber" column="PageNumber"></result>
    </resultMap>
    <resultMap id="CityCResult" class="CityC" extends="BaseDocResult">
      <result property="City" column="City"></result>
    </resultMap>

<select id="GetAllDocument" resultMap="BaseDocResult">
      select
      Id, Title, Type,
      PageNumber, City
      from Test.dbo.Documents
      order by Type,Id
    </select>
View Code

比较结果集中discriminator列（Type列）的值和subMap设置的值（Book，City）自动映射为不同的实体，如果没有匹配的SubMapValue则为BaseDoc

修改disciminator 使用Custom Type Handler 配置

 public class CustomInheritanceTypeHandler : ITypeHandlerCallback
    {
        public object NullValue => throw new NotImplementedException();

        public object GetResult(IResultGetter getter)
        {
            if (getter.Value == null)
                return "Doc";

            string type = getter.Value.ToString();

            switch (type)
            {
                case "Book":
                case "Book1":
                case "Book2":
                    return "Book";
                case "City":
                case "City1":
                case "City2":
                    return "City";
                default:
                    return "Doc";
            }
        }

        public void SetParameter(IParameterSetter setter, object parameter)
        {
            throw new NotImplementedException();
        }

        public object ValueOf(string s)
        {
            throw new NotImplementedException();
        }
    }
View Code
<typeAlias alias="CustomInheritanceTypeHandler" type="ConsoleApp5.CustomInheritanceTypeHandler,ConsoleApp5"></typeAlias>
<resultMap id="BaseDocResult2" class="BaseDoc">
      <result property="Id" column="Id"></result>
      <result property="Title" column="Title" />
      <discriminator column="Type" typeHandler="CustomInheritanceTypeHandler"/>
      <subMap value="Book" resultMapping="BookResult"/>
      <subMap value="City" resultMapping="CityCResult"/>
</resultMap>
View Code

 3.5.7 Implicit Result Maps

隐式 ResultMap  例如：

<statement id="selectProduct" resultClass="Product">
  select
    id,
    description
  from PRODUCT
  where id = #value#
</statement>

<statement id="selectProduct" resultClass="Product">
  select
  PRD_ID as id,
  PRD_DESCRIPTION as description
  from PRODUCT
  where PRD_ID = #value#
</statement>
View Code

有两个问题需注意

    • 大小写问题 如两个字段为“Firstname”、“FirstName” Ibatis 在匹配时是启发式的大小写不敏感的匹配，不能保证匹配哪一个属性。（当然这种两个属性的情况很少）
    • 自动匹配列和属性名有一定的性能开销

3.5.8. Primitive Results (i.e. String, Integer, Boolean)

例1：返回一个简单类型数据

<select id="selectProductCount" resultClass="System.Int32">
  select count(1)
  from PRODUCT
</select>
View Code

例2：返回一列简单类型数据

<resultMap id="SimpleResult" class="System.String">
      <result property="value" column="Title"></result>
</resultMap>

<select id="GetTitles" parameterType="map" resultMap="SimpleResult" >
      select * from Test.dbo.Documents
</select>
View Code

3.5.9. Maps with ResultMaps

例1：Result Maps can use generic "entry-type" objects

<resultMap id="select-product-result" class="HashTable">
  <result property="id" column="PRD_ID"/>
  <result property="code" column="PRD_CODE"/>
  <result property="description" column="PRD_DESCRIPTION"/>
  <result property="suggestedPrice" column="PRD_SUGGESTED_PRICE"/>
</resultMap>
View Code

例2：Implicit Result Maps can use "entry-type" objects too

<statement id="GetHashtableImplicit" resultClass="map">
      select * from Test.dbo.Documents
</statement>
View Code

3.5.10  Complex Properties

Types that nest other types are called "complex types" 

USE [Test]
GO
/****** Object:  Table [dbo].[Documents]    Script Date: 2020/3/26 0:43:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Documents](
    [Id] [int] NOT NULL,
    [Title] [varchar](32) NULL,
    [Type] [varchar](32) NULL,
    [PageNumber] [int] NULL,
    [City] [varchar](32) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Types]    Script Date: 2020/3/26 0:43:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Types](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](5) NULL,
    [Description] [nvarchar](20) NULL,
PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[Documents] ([Id], [Title], [Type], [PageNumber], [City]) VALUES (1, N'title1', N'City1', 0, N'北京')
GO
INSERT [dbo].[Documents] ([Id], [Title], [Type], [PageNumber], [City]) VALUES (2, N'title2', N'City2', 0, N'河北')
GO
INSERT [dbo].[Documents] ([Id], [Title], [Type], [PageNumber], [City]) VALUES (3, N'title3', N'City1', 0, N'天津')
GO
INSERT [dbo].[Documents] ([Id], [Title], [Type], [PageNumber], [City]) VALUES (4, N'title4', N'Book1', 4, N'0')
GO
INSERT [dbo].[Documents] ([Id], [Title], [Type], [PageNumber], [City]) VALUES (5, N'title5', N'Book2', 5, N'0')
GO
INSERT [dbo].[Documents] ([Id], [Title], [Type], [PageNumber], [City]) VALUES (6, N'title6', N'doc', NULL, NULL)
GO
SET IDENTITY_INSERT [dbo].[Types] ON 

GO
INSERT [dbo].[Types] ([Id], [Name], [Description]) VALUES (1, N'City1', N'这时City1')
GO
INSERT [dbo].[Types] ([Id], [Name], [Description]) VALUES (2, N'City2', N'this is City2')
GO
INSERT [dbo].[Types] ([Id], [Name], [Description]) VALUES (3, N'City3', N'This is City3')
GO
INSERT [dbo].[Types] ([Id], [Name], [Description]) VALUES (4, N'Book1', N'This is Book1')
GO
INSERT [dbo].[Types] ([Id], [Name], [Description]) VALUES (5, N'Book2', N'This is Book2')
GO
INSERT [dbo].[Types] ([Id], [Name], [Description]) VALUES (6, N'Book3', N'This is Book3')
GO
SET IDENTITY_INSERT [dbo].[Types] OFF
GO


public class DocExt
    {
        private int id;

        public int Id
        {
            get { return id; }
            set { id = value; }
        }

        private string title;

        public string Title
        {
            get { return title; }
            set { title = value; }
        }

        private Types type;

        public Types Type
        {
            get { return type; }
            set { type = value; }
        }

    }

    public class Types
    {
        private int id;

        public int Id
        {
            get { return id; }
            set { id = value; }
        }


        private string name;

        public string Name
        {
            get { return name; }
            set { name = value; }
        }

        private string description;

        public string Description
        {
            get { return description; }
            set { description = value; }
        }

    }
View Code
<resultMap id="DocExtResult" class="DocExt">
      <result property="Title" column="Title"></result>
      <result property="Id" column="Id"></result>
      <result property="Type" column="Type" select="ns_1.GetTypes"></result>
    </resultMap>
    <resultMap id="TypesResult" class="Types">
      <result property="Id" column="Id"/>
      <result property="Name" column="Name"/>
      <result property="Description" column="Description"/>
    </resultMap>

<select id="GetTypes" parameterClass="string" resultMap="TypesResult">
      select * from Test.dbo.[Types] where Name=#value#
    </select>
    <select id="GetDocExt" resultMap="DocExtResult">
      select * from Test.dbo.Documents
    </select>
View Code

the framework will use the "selectCategory" statement to populate the "category" property. The value of each category is passed to the "selectCategory" statement, and the object returned is set to the category property. When the process completes, each Product instance will have the the appropriate category object instance set.

3.5.11. Avoiding N+1 Selects (1:1)

A problem with Example 3.39 may be that whenever you load a Product, two statements execute: one for the Product and one for the Category. For a single Product, this issue may seem trivial. But if you load 10 products, then 11 statements execute. For 100 Products, instead of one statement product statement executing, a total of 101 statements execute. The number of statements executing for Example 3.40 will always be N+1: 100+1=101.

 <resultMap id="DocExtResult" class="DocExt">
    <result property="Title" column="Title"></result>
    <result property="Id" column="Id"></result>
    <result property="Type" column="Type" select="ns_1.GetTypes"></result>
  </resultMap>
  <resultMap id="TypesResult" class="Types">
    <result property="Id" column="Id"/>
    <result property="Name" column="Name"/>
    <result property="Description" column="Description"/>
  </resultMap>

  
  <!-- This statement executes 1 time -->
  <select id="GetDocExt" resultMap="DocExtResult">
    select * from Test.dbo.Documents
  </select>
  <!-- This statement executes N times (once for each product returned above) -->
  <select id="GetTypes" parameterClass="string" resultMap="TypesResult">
    select * from Test.dbo.[Types] where Name=#value#
  </select>
View Code

解决方法1：使用缓存，将“selectCategory” 的结果缓存，虽然N次查询仍会执行，但是不会查询数据库了。

解决方法2：使用Join

<resultMap id="DocExtResult2" class="DocExt">
      <result property="Title" column="Title"></result>
      <result property="Id" column="Id"></result>
      <result property="Type" resultMapping="ns_1.TypesResult"></result>
    </resultMap>

 <select id="GetDocExtJoin" resultMap="DocExtResult2">
      SELECT * FROM Test.dbo.Documents,Test.[dbo].[Types]
      WHERE Documents.Type=[Types].Name
    </select>
View Code

如果使用JOIN ON 写法注意SELECT列名字映射问题

 

3.5.12. Complex Collection Properties

结果集IList<Types> 每个Types里有一个属于该类Types的Doc集合（请查看一下实体代码）

 public class DocExt
    {
        private int id;

        public int Id
        {
            get { return id; }
            set { id = value; }
        }

        private string title;

        public string Title
        {
            get { return title; }
            set { title = value; }
        }

        private Types type;

        public Types Type
        {
            get { return type; }
            set { type = value; }
        }
    }

    public class Types
    {
        private int id;

        public int Id
        {
            get { return id; }
            set { id = value; }
        }


        private string name;

        public string Name
        {
            get { return name; }
            set { name = value; }
        }

        private string description;

        public string Description
        {
            get { return description; }
            set { description = value; }
        }

        private IList<DocExt> docs;

        public IList<DocExt> Docs
        {
            get { return docs; }
            set { docs = value; }
        }
    }
View Code
 <resultMap id="TypesResult2" class="Types">
      <result property="Id" column="Id" />
      <result property="Name" column="Name" />
      <result property="Description" column="Description"/>
      <result property="Docs" column="Name" select="ns_1.GetDocs2" />
    </resultMap>
    <resultMap id="DocsResult2" class="DocExt">
      <result property="Title" column="Title"></result>
      <result property="Id" column="Id"></result>
    </resultMap>
<select id="GetTypes2" resultMap="TypesResult2">
     select * from Test.dbo.[Types] 
    </select>
    <select id="GetDocs2" parameterClass="string" resultMap="DocsResult2">
      select * from Test.dbo.Documents where Type=#value#
    </select>
View Code
IList<Types> list = mapper.QueryForList<Types>("ns_1.GetTypes2", null);
View Code

和3.5.10类似的设置方式

3.5.13. Avoiding N+1 Select Lists (1:M and M:N)

 <resultMap id="TypesResult2Mapping" class="Types" groupBy="Name">
      <result property="Id" column="Type_Id" />
      <result property="Name" column="Name" />
      <result property="Description" column="Description"/>
      <result property="Docs" column="Name" resultMapping="ns_1.DocsResult2" />
    </resultMap>

<select id="GetTypesWithMapping" resultMap="TypesResult2Mapping">
      SELECT Types.Id as Type_Id,Types.Name,Types.Description,Documents.Id,Documents.Title
      FROM Test.dbo.Types LEFT JOIN Test.dbo.Documents ON types.Name=Documents.Type
    </select>
View Code

要点：

使用 groupBy=“Name” 属性

设置 resultMapping 属性 值为NameSpace.ResultMapName

 

3.5.14. Composite Keys or Multiple Complex Parameters Properties

以上例子中只有一个参数传递到resultMap 中，这就意味着表之间只通过一列关联。然而有的表之间通过多个列进行关联，此时需要传入多个参数

Mapping a composite key：

<resultMaps>
  <resultMap id="select-order-result" class="order">
    <result property="id" column="ORD_ID"/>
    <result property="customerId" column="ORD_CST_ID"/>
    ...
    <result property="payments" column="itemId=ORD_ID, custId=ORD_CST_ID"
      select="selectOrderPayments"/>
  </resultMap>
<resultMaps>

<statements>

  <statement id="selectOrderPayments" resultMap="select-payment-result">
    select * from PAYMENT
    where PAY_ORD_ID = #itemId#
    and PAY_CST_ID = #custId#
  </statement>
</statements>
View Code

 

posted @ 2020-03-24 00:07  vvf  阅读(190)  评论(0)  编辑  收藏  举报