"MyBatis.NET 的前身为 IBatis，是 JAVA 版 MyBatis 在.NET 平台上的翻版，相对 NHibernate、EntityFramework 等重量级 ORM 框架而言，MyBatis.NET 必须由开发人员手动写 SQL，相对灵活性更大，更容易保证 DB 访问的性能，适用 ...."

someone1
AI专业者开发社区 1 号成员
AI开发者社区 • 0 回帖 • 0 浏览 • 4 个月前

MyBatis.Net 学习手记

MyBatis.NET 的前身为 IBatis，是 JAVA 版 MyBatis 在.NET 平台上的翻版，相对 NHibernate、EntityFramework 等重量级 ORM 框架而言，MyBatis.NET 必须由开发人员手动写 SQL，相对灵活性更大，更容易保证 DB 访问的性能，适用开发团队里有 SQL 熟手的场景。

下面是使用步骤：

1、到官网http://code.google.com/p/mybatisnet/ 下载相关 dll 和文档

Doc-DataAccess-1.9.2.zip
Doc-DataMapper-1.6.2.zip
IBatis.DataAccess.1.9.2.bin.zip
IBatis.DataMapper.1.6.2.bin.zip

一共有 4 个 zip 包

2、创建一个 Web 应用，参考下图添加程序集引用

3、修改 web.config，主要是配置 log4net，参考下面的内容：

 1 <?xml version="1.0"?>
 2 <configuration>
 3     <configSections>
 4         <sectionGroup name="iBATIS">
 5             <section name="logging" type="IBatisNet.Common.Logging.ConfigurationSectionHandler, IBatisNet.Common"/>
 6         </sectionGroup>
 7         <section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler, log4net"/>
 8     </configSections>
 9     <system.web>
10         <compilation debug="true" targetFramework="4.0"/>
11     </system.web>
12     <iBATIS>
13         <logging>
14             <logFactoryAdapter type="IBatisNet.Common.Logging.Impl.Log4NetLoggerFA, IBatisNet.Common.Logging.Log4Net">
15                 <arg key="configType" value="inline"/>
16                 <arg key="showLogName" value="true"/>
17                 <arg key="showDataTime" value="true"/>
18                 <arg key="level" value="ALL"/>
19                 <arg key="dateTimeFormat" value="yyyy/MM/dd HH🇲🇲ss:SSS"/>
20             </logFactoryAdapter>
21         </logging>
22     </iBATIS>
23     <log4net>
24         <!-- Define some output appenders -->
25         <appender name="RollingLogFileAppender" type="log4net.Appender.RollingFileAppender">
26             <param name="File" value="mybatis.log"/>
27             <param name="AppendToFile" value="true"/>
28             <param name="MaxSizeRollBackups" value="2"/>
29             <param name="MaximumFileSize" value="100KB"/>
30             <param name="RollingStyle" value="Size"/>
31             <param name="StaticLogFileName" value="true"/>
32             <layout type="log4net.Layout.PatternLayout">
33                 <param name="Header" value="[Header]\r\n"/>
34                 <param name="Footer" value="[Footer]\r\n"/>
35                 <param name="ConversionPattern" value="%d [%t] %-5p %c [%x] - %m%n"/>
36             </layout>
37         </appender>
38         <appender name="ConsoleAppender" type="log4net.Appender.ConsoleAppender">
39             <layout type="log4net.Layout.PatternLayout">
40                 <param name="ConversionPattern" value="%d [%t] %-5p %c [%x] &lt;%X{auth}&gt; - %m%n"/>
41             </layout>
42         </appender>
43         <!-- Set root logger level to ERROR and its appenders -->
44         <root>
45             <level value="DEBUG"/>
46             <appender-ref ref="RollingLogFileAppender"/>
47             <appender-ref ref="ConsoleAppender"/>
48         </root>
49         <!-- Print only messages of level DEBUG or above in the packages -->
50         <logger name="IBatisNet.DataMapper.Configuration.Cache.CacheModel">
51             <level value="DEBUG"/>
52         </logger>
53         <logger name="IBatisNet.DataMapper.Configuration.Statements.PreparedStatementFactory">
54             <level value="DEBUG"/>
55         </logger>
56         <logger name="IBatisNet.DataMapper.LazyLoadList">
57             <level value="DEBUG"/>
58         </logger>
59         <logger name="IBatisNet.DataAccess.DaoSession">
60             <level value="DEBUG"/>
61         </logger>
62         <logger name="IBatisNet.DataMapper.SqlMapSession">
63             <level value="DEBUG"/>
64         </logger>
65         <logger name="IBatisNet.Common.Transaction.TransactionScope">
66             <level value="DEBUG"/>
67         </logger>
68         <logger name="IBatisNet.DataAccess.Configuration.DaoProxy">
69             <level value="DEBUG"/>
70         </logger>
71     </log4net>
72 </configuration>

View Code

4、添加 Providers.config

把从官方下载的压缩包解开，就能找到 providers.config 文件，里面定义了 MyBatis.Net 支持的各种数据库驱动，本例以 oracle 为例，把其它不用的 db provider 全删掉，只保留下 oracleClient1.0，同时把 enabled 属性设置成 true，参考下面这样：

 1 <?xml version="1.0"?>
 2 <providers xmlns="http://ibatis.apache.org/providers"
 3     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 4 
 5     <clear/>    
 6     
 7     <!--Oracle Support-->
 8     <provider
 9       name="oracleClient1.0"
10       description="Oracle, Microsoft provider V1.0.5000.0"
11       enabled="true"
12       assemblyName="System.Data.OracleClient, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" connectionClass="System.Data.OracleClient.OracleConnection"
13       commandClass="System.Data.OracleClient.OracleCommand"
14       parameterClass="System.Data.OracleClient.OracleParameter"
15       parameterDbTypeClass="System.Data.OracleClient.OracleType"
16       parameterDbTypeProperty="OracleType"
17       dataAdapterClass="System.Data.OracleClient.OracleDataAdapter"
18       commandBuilderClass="System.Data.OracleClient.OracleCommandBuilder"
19       usePositionalParameters="false"
20       useParameterPrefixInSql="true"
21       useParameterPrefixInParameter="false"
22       parameterPrefix=":"
23       allowMARS="false"
24   />
25     
26 </providers>

View Code

把这个文件复制到 Web 项目根目录下

5、添加 SqlMap.config，内容如下：

 1 <?xml version="1.0" encoding="utf-8"?>
 2 <sqlMapConfig xmlns="http://ibatis.apache.org/dataMapper"
 3               xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 4 
 5     <settings>
 6         <setting useStatementNamespaces="false"/>
 7         <setting cacheModelsEnabled="true"/>
 8     </settings>
 9 
10     <!--db provider 配置文件路径-->
11     <providers resource="providers.config"/>
12 
13     <!--db provider 类型及连接串-->
14     <database>
15         <provider name="oracleClient1.0" />
16         <dataSource name="oracle" connectionString="Data Source=ORCL;Persist Security Info=True;User ID=scott;Password=tiger;Unicode=True" />
17     </database>
18 
19     <!--db 与 Entity 的映射文件-->
20     <sqlMaps>
21         <sqlMap resource="Maps/ProductMap.xml"/>
22     </sqlMaps>
23 
24 </sqlMapConfig>

View Code

这个文件也复制到 Web 项目根目录下，它的作用主要是指定 db 连接串，告诉系统 providers.config 在哪? 以及 db 与 entity 的映射文件在哪?（映射文件后面会讲到，这里先不管）

6、在 Oraccle 中先建表 Product 以及 Sequence，方便接下来测试

 1 -- CREATE TABLE
 2 CREATE TABLE PRODUCT
 3 (
 4   PRODUCTID      NUMBER NOT NULL,
 5   PRODUCTNAME    VARCHAR2(100),
 6   PRODUCTCOMPANY VARCHAR2(100),
 7   SIGNDATE       DATE,
 8   UPDATEDATE     DATE
 9 );
10 -- CREATE/RECREATE PRIMARY, UNIQUE AND FOREIGN KEY CONSTRAINTS 
11 ALTER TABLE PRODUCT
12   ADD CONSTRAINT PK_PRODUCT_ID PRIMARY KEY (PRODUCTID);
13 
14 -- CREATE SEQUENCE 
15 CREATE SEQUENCE SQ_PRODUCT
16 MINVALUE 1
17 MAXVALUE 9999999999999999999999999
18 START WITH 1
19 INCREMENT BY 1
20 CACHE 20;

View Code

7、创建 Maps 目录，并在该目录下，添加映射文件 ProductMap.xml，内容如下：

 1 <?xml version="1.0" encoding="utf-8" ?>
 2 <sqlMap namespace="EntityModel" xmlns="http://ibatis.apache.org/mapping"
 3         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 4 
 5     <alias>
 6         <!--类的别名-->
 7         <typeAlias alias="Product" type="Web.Product,Web"/>
 8     </alias>
 9 
10     <resultMaps>
11         <!--Product 类与 db 表的映射-->
12         <resultMap id="SelectAllResult" class="Product">
13             <result property="ProductId" column="ProductId"/>
14             <result property="ProductName" column="ProductName"/>
15             <result property="ProductCompany" column="ProductCompany" />
16             <result property="SignDate" column="SignDate"  />
17             <result property="UpdateDate" column="UpdateDate" />
18         </resultMap>
19     </resultMaps>
20 
21     <statements>
22 
23         <!--查询所有记录-->
24         <select id="SelectAllProduct" resultMap="SelectAllResult">
25             <![CDATA[SELECT ProductId,ProductName,ProductCompany,SignDate,UpdateDate FROM Product]]>
26         </select>
27 
28         <!--查询单条记录-->
29         <select id="SelectByProductId" parameterClass="int" resultMap="SelectAllResult" extends="SelectAllProduct">
30             <![CDATA[ where ProductId = #value#]]>
31         </select>
32 
33         <!--插入新记录-->
34         <insert id="InsertProduct" parameterClass="Product">    
35             <!--oracle sequence 的示例用法-->
36             <selectKey property="ProductId" type="pre" resultClass="int">
37                 select SQ_Product.nextval as ProductId from dual
38             </selectKey>
39             <![CDATA[INSERT into Product(ProductId,ProductCompany,ProductName,SignDate,UpdateDate)
40             VALUES(#ProductId#,#ProductCompany#, #ProductName# , #SignDate# , #UpdateDate#)]]>
41         </insert>
42 
43         <!--更新单条记录-->
44         <update id="UpdateProduct" parameterClass="Product">
45             <![CDATA[Update Product SET ProductName=#ProductName#,
46             ProductCompany=#ProductCompany#,            
47             SignDate=#SignDate#,
48             UpdateDate=#UpdateDate#
49             Where ProductId=#ProductId#]]>
50         </update>
51 
52         <!--根据主键删除单条记录-->
53         <delete id="DeleteProductById" parameterClass="int">
54             <![CDATA[Delete From Product Where ProductId=#value#]]>
55         </delete>
56 
57 
58     </statements>
59 
60 </sqlMap>

View Code

它的作用就是指定各种 sql，以及 db 表与 entity 的映射规则，注意下 insert 中 Sequence 的用法！

8、创建实体类 Product

 1 using System;
 2 
 3 namespace Web
 4 {
 5    public class Product
 6     {
 7        public int ProductId { get; set; }
 8        public string ProductName { get; set; }
 9        public string ProductCompany { get; set; }
10        public DateTime SignDate { get; set; }
11        public DateTime UpdateDate { get; set; }
12 
13        public Product(){}
14     }
15 }

View Code

9、写一个通用的 BaseDA 类，对 MyBatis.Net 做些基本的封装

 1 using IBatisNet.DataMapper;
 2 using System.Collections.Generic;
 3 
 4 namespace Web
 5 {
 6     public static class BaseDA
 7     {
 8         public static int Insert<T>(string statementName, T t)
 9         {
10             ISqlMapper iSqlMapper = Mapper.Instance();
11             if (iSqlMapper != null)
12             {
13                 return (int)iSqlMapper.Insert(statementName, t);
14             }
15             return 0;
16         }
17 
18         public static int Update<T>(string statementName, T t)
19         {
20             ISqlMapper iSqlMapper = Mapper.Instance();
21             if (iSqlMapper != null)
22             {
23                 return iSqlMapper.Update(statementName, t);
24             }
25             return 0;
26         }
27 
28         public static int Delete(string statementName, int primaryKeyId)
29         {
30             ISqlMapper iSqlMapper = Mapper.Instance();
31             if (iSqlMapper != null)
32             {
33                 return iSqlMapper.Delete(statementName, primaryKeyId);
34             }
35             return 0;
36         }
37 
38         public static T Get<T>(string statementName, int primaryKeyId) where T : class
39         {
40             ISqlMapper iSqlMapper = Mapper.Instance();
41             if (iSqlMapper != null)
42             {
43                 return iSqlMapper.QueryForObject<T>(statementName, primaryKeyId);
44             }
45             return null;
46         }
47 
48         public static IList<T> QueryForList<T>(string statementName, object parameterObject = null)
49         {
50             ISqlMapper iSqlMapper = Mapper.Instance();
51             if (iSqlMapper != null)
52             {
53                 return iSqlMapper.QueryForList<T>(statementName, parameterObject);
54             }
55             return null;
56         }
57     }
58 }

View Code

10、然后就可以在 Default.aspx.cs 上测试了，参考下面的代码：

using System;
using System.Web.UI;
namespace Web
{
public partial class Default : Page
{
protected void Page_Load(object sender, EventArgs e)
{
//插入
var insertProductId = BaseDA.Insert<Product>("InsertProduct", new Product()
{
ProductCompany = "INFOSKY",
ProductName = "iGSA2",
SignDate = DateTime.Now,
UpdateDate = DateTime.Now
});
        </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)">查单条记录</span>
        <span style="color: rgba(0, 0, 255, 1)">var</span> model = BaseDA.Get&lt;Product&gt;(<span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">SelectByProductId</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">, insertProductId);
        ShowProduct(model);

        Response.Write(</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">&lt;hr/&gt;</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">);

        </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)">修改记录</span>
        <span style="color: rgba(0, 0, 255, 1)">if</span> (model != <span style="color: rgba(0, 0, 255, 1)">null</span><span style="color: rgba(0, 0, 0, 1)">)
        {
            model.ProductName </span>= (<span style="color: rgba(0, 0, 255, 1)">new</span> Random().Next(<span style="color: rgba(128, 0, 128, 1)">0</span>, <span style="color: rgba(128, 0, 128, 1)">99999999</span>)).ToString().PadLeft(<span style="color: rgba(128, 0, 128, 1)">10</span>, <span style="color: rgba(128, 0, 0, 1)">'</span><span style="color: rgba(128, 0, 0, 1)">0</span><span style="color: rgba(128, 0, 0, 1)">'</span><span style="color: rgba(0, 0, 0, 1)">);
            </span><span style="color: rgba(0, 0, 255, 1)">int</span> updateResult = BaseDA.Update&lt;Product&gt;(<span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">UpdateProduct</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">, model);
            Response.Write(</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">update影响行数:</span><span style="color: rgba(128, 0, 0, 1)">"</span> + updateResult + <span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">&lt;br/&gt;&lt;hr/&gt;</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">);
        }

        </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)">查列表</span>
        <span style="color: rgba(0, 0, 255, 1)">var</span> products = BaseDA.QueryForList&lt;Product&gt;(<span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">SelectAllProduct</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">);

        </span><span style="color: rgba(0, 0, 255, 1)">foreach</span> (<span style="color: rgba(0, 0, 255, 1)">var</span> pro <span style="color: rgba(0, 0, 255, 1)">in</span><span style="color: rgba(0, 0, 0, 1)"> products)
        {
            ShowProduct(pro);
        }

        Response.Write(</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">&lt;hr/&gt;</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">);

        </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)">删除记录</span>
        <span style="color: rgba(0, 0, 255, 1)">int</span> deleteResult = BaseDA.Delete(<span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">DeleteProductById</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">, insertProductId);
        Response.Write(</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">delete影响行数:</span><span style="color: rgba(128, 0, 0, 1)">"</span> + deleteResult + <span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">&lt;br/&gt;&lt;hr/&gt;</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">);

    }

    </span><span style="color: rgba(0, 0, 255, 1)">void</span><span style="color: rgba(0, 0, 0, 1)"> ShowProduct(Product pro)
    {
        </span><span style="color: rgba(0, 0, 255, 1)">if</span> (pro == <span style="color: rgba(0, 0, 255, 1)">null</span>) <span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)">;
        Response.Write(</span><span style="color: rgba(0, 0, 255, 1)">string</span>.Format(<span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">{0}&amp;nbsp;,&amp;nbsp;{1}&amp;nbsp;,&amp;nbsp;{2}&amp;nbsp;,&amp;nbsp;{3}&amp;nbsp;,&amp;nbsp;{4}&lt;br/&gt;</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">,
            pro.ProductId, pro.ProductName, pro.ProductCompany, pro.SignDate, pro.UpdateDate));
    }
}

}

View Code

整个项目的目录结构如下：

示例源代码下载：https://files.cnblogs.com/yjmyzz/MyBatisSample.zip

MyBatis.Net 学习手记

相关帖子

随便看看

MyBatis.Net 学习手记