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:mm: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] <%X{auth}> - %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>
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>
把这个文件复制到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>
这个文件也复制到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;
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>
它的作用就是指定各种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 }
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 }
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
});
//查单条记录
var model = BaseDA.Get<Product>("SelectByProductId", insertProductId);
ShowProduct(model);
Response.Write("<hr/>");
//修改记录
if (model != null)
{
model.ProductName = (new Random().Next(0, 99999999)).ToString().PadLeft(10, '0');
int updateResult = BaseDA.Update<Product>("UpdateProduct", model);
Response.Write("update影响行数:" + updateResult + "<br/><hr/>");
}
//查列表
var products = BaseDA.QueryForList<Product>("SelectAllProduct");
foreach (var pro in products)
{
ShowProduct(pro);
}
Response.Write("<hr/>");
//删除记录
int deleteResult = BaseDA.Delete("DeleteProductById", insertProductId);
Response.Write("delete影响行数:" + deleteResult + "<br/><hr/>");
}
void ShowProduct(Product pro)
{
if (pro == null) return;
Response.Write(string.Format("{0} , {1} , {2} , {3} , {4}<br/>",
pro.ProductId, pro.ProductName, pro.ProductCompany, pro.SignDate, pro.UpdateDate));
}
}
}
整个项目的目录结构如下: