Mybatis.NET-01-入门介绍

老马啸西风2017年4月9日大约 9 分钟

Mybatis.NET

MyBatis 是一个数据映射工具。

它将数据库查询（包括存储过程）的列映射到业务对象的属性。

映射器的定义之一是“在两个独立对象之间建立通信的对象”。

数据映射器是“一个映射层，它在对象和数据库之间移动数据，同时使它们彼此独立，并且独立于映射器本身”。

为什么？

.NET 平台已经提供了一个强大的库来访问数据库，无论是通过 SQL 语句还是存储过程，但在使用 ADO.NET 时，仍有一些事情很难做好，包括：

将 SQL 代码与编程代码分离
向库类传递输入参数并提取输出
将数据访问类与业务逻辑类分离
缓存经常使用的数据直到它发生变化
管理事务和线程

iBATIS DataMapper 通过使用 XML 文档在普通对象和 SQL 语句或存储过程之间创建映射，解决了这些问题——以及更多问题。

“普通对象”可以是 IDictionary 或属性对象。

Hello World

本文使用 Mysql 进行测试。如果是 SQL server 请直接参考本文最后的一系列文章。你可以在这里查看完整代码。

先来看一下项目文件结构

.
├── ./mybatisNet
│   ├── ./mybatisNet/Program.cs
│   ├── ./mybatisNet/Properties
│   │   └── ./mybatisNet/Properties/AssemblyInfo.cs
│   ├── ./mybatisNet/Providers.config
│   ├── ./mybatisNet/SqlMap.config
│   ├── ./mybatisNet/app.config
│   ├── ./mybatisNet/bin
│   │   └── ./mybatisNet/bin/Debug
│   │       └── ./mybatisNet/bin/Debug/mappers
│   ├── ./mybatisNet/domain
│   │   └── ./mybatisNet/domain/Mybatis.cs
│   ├── ./mybatisNet/mappers
│   │   └── ./mybatisNet/mappers/Mybatis.xml
│   ├── ./mybatisNet/mybatisNet.csproj
│   ├── ./mybatisNet/obj
│   │   └── ./mybatisNet/obj/x86
│   │       └── ./mybatisNet/obj/x86/Debug
│   ├── ./mybatisNet/packages.config
│   └── ./mybatisNet/service
│       └── ./mybatisNet/service/MybatisService.cs
├── ./mybatisNet.sln
├── ./mybatisNet.userprefs
└── ./packages
    ├── ./packages/MyBatis.NET.1.6.4
    │   ├── ./packages/MyBatis.NET.1.6.4/MyBatis.NET.1.6.4.nupkg
    │   └── ./packages/MyBatis.NET.1.6.4/lib
    │       ├── ./packages/MyBatis.NET.1.6.4/lib/IBatisNet.Common.dll
    │       ├── ./packages/MyBatis.NET.1.6.4/lib/IBatisNet.Common.pdb
    │       ├── ./packages/MyBatis.NET.1.6.4/lib/IBatisNet.Common.xml
    │       ├── ./packages/MyBatis.NET.1.6.4/lib/IBatisNet.DataMapper.dll
    │       ├── ./packages/MyBatis.NET.1.6.4/lib/IBatisNet.DataMapper.pdb
    │       └── ./packages/MyBatis.NET.1.6.4/lib/IBatisNet.DataMapper.xml
    └── ./packages/MySql.Data.6.9.9
        ├── ./packages/MySql.Data.6.9.9/CHANGES
        ├── ./packages/MySql.Data.6.9.9/MySql.Data.6.9.9.nupkg
        ├── ./packages/MySql.Data.6.9.9/Readme.txt
        ├── ./packages/MySql.Data.6.9.9/content
        │   ├── ./packages/MySql.Data.6.9.9/content/app.config.transform
        │   └── ./packages/MySql.Data.6.9.9/content/web.config.transform
        └── ./packages/MySql.Data.6.9.9/lib
            ├── ./packages/MySql.Data.6.9.9/lib/net40
            │   └── ./packages/MySql.Data.6.9.9/lib/net40/MySql.Data.dll
            └── ./packages/MySql.Data.6.9.9/lib/net45
                └── ./packages/MySql.Data.6.9.9/lib/net45/MySql.Data.dll

零、准备一张表用来测试。

mybatis

CREATE TABLE `mybatis` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL COMMENT '名称',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB COMMENT='mybatis测试表';

一、引入对应的 DLL

1、引入 Mybatis.NET

直接NUGET搜索【mybatis】安装即可。当前版本信息为 1.6.4.

2、引入 System.Data 和 Mysql.Data

二、指定配置文件

Attention

在c#中要将 provider.config、sqlmap.config 的属性设置为始终上传和内容，将xxx.xml设置为始终上传和嵌入的资源。

换言之，比如保证编译后的文件里包含这些文件。

MONO里，文件上右键->【生成操作】->【EmbeddedResource】; 文件上右键->【快捷属性】->【复制到输出目录】.

1、 First thing you need for the datamapper work is data map definition file (SqlMap.config).

SqlMap.config

-->

Providers.config

Mybatis.xml & Mybatis.cs




  
    
-->
		
      
    
  

  

	
		SELECT id, name
			FROM `mybatis` 
			WHERE (id = #value#)
		

    
      INSERT INTO `mybatis` (id, name) VALUES (
		  #id#, #name#
		);
    

	
      DELETE FROM `mybatis`
      WHERE
      (id = #id#)
    

	
      UPDATE `mybatis` 
	  SET
      name = #name#
      WHERE
      (id = #id#)

using System;
namespace mybatisNet.doamin
{
	[Serializable]
	public class Mybatis
	{
		/// 
		/// The identifier.
		/// 
		private int id;

		/// 
		/// The name.
		/// 
		private string name;		


		public int Id
		{
			get
			{
				return id;
			}

			set
			{
				id = value;
			}
		}

		public string Name
		{
			get
			{
				return name;
			}

			set
			{
				name = value;
			}
		}

		public override string ToString()
		{
			return string.Format("[Mybatis: id={0}, name={1}]", id, name);
		}
	}
}

MybatisService.cs

using System;
using IBatisNet.DataMapper;
using IBatisNet.DataMapper.Configuration;
using mybatisNet.doamin;

namespace mybatisNet
{
	public class MybatisService
	{
		/// 
		/// Inserts the mybatis.
		/// 
		/// Mybatis.
		public void InsertMybatis(Mybatis mybatis)
		{
			ISqlMapper mapper = EntityMapper;

			try
			{
				mapper.Insert("InsertMybatis", mybatis);
			}
			catch (Exception ex)
			{ 
				Console.WriteLine("ex when InsertMybatis:{0}", ex);
			}
		}


		/// 
		/// Queries the mybatis.
		/// 
		/// The mybatis.
		/// Identifier.
		public Mybatis QueryMybatis(int id)
		{ 
			ISqlMapper mapper = EntityMapper;

			try
			{
				Mybatis result = mapper.QueryForObject("QueryMybatis", id);
				return result;
			}
			catch (Exception ex)
			{
				Console.WriteLine("ex:{0}", ex);
				return null;
			}
		}

		/// 
		/// Updates the mybatis.
		/// 
		/// The mybatis.
		/// Mybatis.
		public int UpdateMybatis(Mybatis mybatis)
		{
			ISqlMapper mapper = EntityMapper;

			try
			{
				int result = mapper.Update("UpdateMybatis", mybatis);
				return result;
			}
			catch (Exception ex)
			{
				Console.WriteLine("ex:{0}", ex);
				return 0;
			}
		}

		public int DeleteMybatis(Mybatis mybatis)
		{
			ISqlMapper mapper = EntityMapper;

			try
			{
				int result = mapper.Delete("DeleteMybatis", mybatis);
				return result;
			}
			catch (Exception ex)
			{
				Console.WriteLine("ex:{0}", ex);
				return 0;
			}
		}



		/// 
		/// Gets the entity mapper.
		/// 
		/// The entity mapper.
		public static ISqlMapper EntityMapper
		{
			get
			{
				try
				{
					ISqlMapper mapper = Mapper.Instance();
					return mapper;
				}
				catch (Exception ex)
				{
					Console.WriteLine("ex:{0}", ex);
					//throw ex;
					return null;
				}
			}
		}
	}
}

Simple Test code

这里偷懒了。没有写对应的单元测试。都是简单的调用。

using System;
using IBatisNet.DataMapper;
using IBatisNet.DataMapper.Configuration;
using mybatisNet.doamin;

namespace mybatisNet
{
	class MainClass
	{
		public static void Main(string[] args)
		{
			MybatisService service = new MybatisService();

			//Mybatis mybatis = new Mybatis();
			//mybatis.Id = 3;
			//mybatis.Name = "hello mybatis .net update";

			//service.InsertMybatis(mybatis);
			//service.Q

			//service.UpdateMybatis(mybatis);
			//service.DeleteMybatis(mybatis);



			Console.WriteLine("result: {0}", service.QueryMybatis(1));
		}
	}
}

最后：

比较而言，C# 版本的 mybatis 已经停止更新了。文档也不是很全。

如果你知道 java 版本的，肯定不会去手写这里的 model,mapper。C# 可以借助codesmithtools，Mac上安装不了，此刻跳过。

动态SQL

也就是说，此时的mybatisNet(V1.6.4.0) 和以前的 Java版本 ibatis 保持一致。==

IBatis.net动态SQL语句

打印SQL的LOG

mybatisNet with log4net

Download & BLOG

Download MyBatis.NET 1.6.2

MyBatisNet的安装使用

Mybatis.NET

Mybatis.NET 入门系列3

Mybatis.NET CRUD

Mybatis.NET 环境配置

神奇的BUG

情景说明：

当涉及到2张表时：且二者的字段不完全相同。

当后者查询时，而且查询的是自己的表。就会莫名其妙的出现第一张的字段。(name 这个字段在 mybatis这张表中)报错如下：

（初步猜测，会将所有的 statement 执行一遍。）但是这个问题又是不可能出现的，因为这么多人用。不知道我什么地方配置出错了。

ex:System.IndexOutOfRangeException: Could not find specified column in results: name
  at MySql.Data.MySqlClient.ResultSet.GetOrdinal (System.String name) [0x0002f] in :0 
  at MySql.Data.MySqlClient.MySqlDataReader.GetOrdinal (System.String name) [0x00020] in :0 
  at IBatisNet.DataMapper.Commands.DataReaderDecorator.System.Data.IDataRecord.GetOrdinal (System.String name) [0x00000] in :0 
  at IBatisNet.DataMapper.TypeHandlers.StringTypeHandler.GetValueByName (IBatisNet.DataMapper.Configuration.ResultMapping.ResultProperty mapping, System.Data.IDataReader dataReader) [0x00007] in :0 
  at IBatisNet.DataMapper.Configuration.ResultMapping.ResultProperty.GetDataBaseValue (System.Data.IDataReader dataReader) [0x00015] in :0 
  at IBatisNet.DataMapper.MappedStatements.PropertyStrategy.DefaultStrategy.Get (IBatisNet.DataMapper.Scope.RequestScope request, IBatisNet.DataMapper.Configuration.ResultMapping.IResultMap resultMap, IBatisNet.DataMapper.Configuration.ResultMapping.ResultProperty mapping, System.Object& target, System.Data.IDataReader reader) [0x0008a] in :0 
  at IBatisNet.DataMapper.MappedStatements.PropertyStrategy.DefaultStrategy.Set (IBatisNet.DataMapper.Scope.RequestScope request, IBatisNet.DataMapper.Configuration.ResultMapping.IResultMap resultMap, IBatisNet.DataMapper.Configuration.ResultMapping.ResultProperty mapping, System.Object& target, System.Data.IDataReader reader, System.Object keys) [0x00000] in :0 
  at IBatisNet.DataMapper.MappedStatements.ResultStrategy.ResultMapStrategy.Process (IBatisNet.DataMapper.Scope.RequestScope request, System.Data.IDataReader& reader, System.Object resultObject) [0x0008f] in :0 
  at IBatisNet.DataMapper.MappedStatements.ResultStrategy.MapStrategy.Process (IBatisNet.DataMapper.Scope.RequestScope request, System.Data.IDataReader& reader, System.Object resultObject) [0x0002a] in :0 
  at IBatisNet.DataMapper.MappedStatements.MappedStatement.RunQueryForObject[T] (IBatisNet.DataMapper.Scope.RequestScope request, IBatisNet.DataMapper.ISqlMapSession session, System.Object parameterObject, T resultObject) [0x00013] in :0 
result: 

Press any key to continue...

原因如下：

这里的 resultMap.id 也请务必保持唯一。

执行

Console.WriteLine("result: {0}", new MethodInfoDao().QueryMethodInfo(1));

查询方法如下

public MethodInfo QueryMethodInfo(int id)
{
    try
    {
        ISqlMapper mapper = Mapper.Instance();
        MethodInfo result = mapper.QueryForObject("QueryMethodInfo", id);
        return result;
    }
    catch (Exception ex)
    {
        Console.WriteLine("ex:{0}", ex);
        return null;
    }
}

MethodInfo.xml




  
    
	  
      
    
  

  

	
		SELECT id, methodName
			FROM `methodInfo` 
			WHERE (id = #value#)

猜想的验证

配置 log4net for mybatisNet

修改 app.config

日志如下：

2017/04/12 22:33:38:SSS [DEBUG] IBatisNet.Common.Utilities.ConfigWatcherHandler - Adding file [SqlMap.config] to list of watched files.
2017/04/12 22:33:39:SSS [DEBUG] IBatisNet.Common.Utilities.ConfigWatcherHandler - Adding file [Mybatis.xml] to list of watched files.
2017/04/12 22:33:39:SSS [DEBUG] IBatisNet.DataMapper.Configuration.Statements.PreparedStatementFactory - Statement Id: [QueryMybatis] Prepared SQL: [SELECT id, name    FROM `mybatis`     WHERE (id =  @param0 )]
2017/04/12 22:33:39:SSS [DEBUG] IBatisNet.DataMapper.Configuration.Statements.PreparedStatementFactory - Statement Id: [InsertMybatis] Prepared SQL: [INSERT INTO `mybatis` (id, name) VALUES (      @param0 ,  @param1    );]
2017/04/12 22:33:39:SSS [DEBUG] IBatisNet.DataMapper.Configuration.Statements.PreparedStatementFactory - Statement Id: [UpdateMybatis] Prepared SQL: [UPDATE `mybatis`     SET       name =  @param0        WHERE       (id =  @param1 )]
2017/04/12 22:33:39:SSS [DEBUG] IBatisNet.DataMapper.Configuration.Statements.PreparedStatementFactory - Statement Id: [DeleteMybatis] Prepared SQL: [DELETE FROM `mybatis`       WHERE       (id =  @param0 )]
2017/04/12 22:33:39:SSS [DEBUG] IBatisNet.Common.Utilities.ConfigWatcherHandler - Adding file [MethodInfo.xml] to list of watched files.
2017/04/12 22:33:39:SSS [DEBUG] IBatisNet.DataMapper.Configuration.Statements.PreparedStatementFactory - Statement Id: [QueryMethodInfo] Prepared SQL: [SELECT id, methodName    FROM `methodInfo`     WHERE (id =  @param0 )]

2017/04/12 22:33:39:SSS [DEBUG] IBatisNet.DataMapper.Commands.DefaultPreparedCommand - Statement Id: [QueryMethodInfo] PreparedStatement : [SELECT id, methodName    FROM `methodInfo`     WHERE (id =  @param0 )]
2017/04/12 22:33:39:SSS [DEBUG] IBatisNet.DataMapper.Commands.DefaultPreparedCommand - Statement Id: [QueryMethodInfo] Parameters: [@param0=[value,1]]
2017/04/12 22:33:39:SSS [DEBUG] IBatisNet.DataMapper.Commands.DefaultPreparedCommand - Statement Id: [QueryMethodInfo] Types: [@param0=[Int32, System.Int32]]
2017/04/12 22:33:39:SSS [DEBUG] IBatisNet.DataMapper.SqlMapSession - Open Connection "21023919" to "MySQL, MySQL provider V6.9.9.0".
2017/04/12 22:33:39:SSS [DEBUG] IBatisNet.DataMapper.SqlMapSession - Close Connection "21023919" to "MySQL, MySQL provider V6.9.9.0".

ex:System.IndexOutOfRangeException: Could not find specified column in results: name
  at MySql.Data.MySqlClient.ResultSet.GetOrdinal (System.String name) [0x0002f] in :0 
  at MySql.Data.MySqlClient.MySqlDataReader.GetOrdinal (System.String name) [0x00020] in :0 
  at IBatisNet.DataMapper.Commands.DataReaderDecorator.System.Data.IDataRecord.GetOrdinal (System.String name) [0x00000] in :0 
  at IBatisNet.DataMapper.TypeHandlers.StringTypeHandler.GetValueByName (IBatisNet.DataMapper.Configuration.ResultMapping.ResultProperty mapping, System.Data.IDataReader dataReader) [0x00007] in :0 
  at IBatisNet.DataMapper.Configuration.ResultMapping.ResultProperty.GetDataBaseValue (System.Data.IDataReader dataReader) [0x00015] in :0 
  at IBatisNet.DataMapper.MappedStatements.PropertyStrategy.DefaultStrategy.Get (IBatisNet.DataMapper.Scope.RequestScope request, IBatisNet.DataMapper.Configuration.ResultMapping.IResultMap resultMap, IBatisNet.DataMapper.Configuration.ResultMapping.ResultProperty mapping, System.Object& target, System.Data.IDataReader reader) [0x0008a] in :0 
  at IBatisNet.DataMapper.MappedStatements.PropertyStrategy.DefaultStrategy.Set (IBatisNet.DataMapper.Scope.RequestScope request, IBatisNet.DataMapper.Configuration.ResultMapping.IResultMap resultMap, IBatisNet.DataMapper.Configuration.ResultMapping.ResultProperty mapping, System.Object& target, System.Data.IDataReader reader, System.Object keys) [0x00000] in :0 
  at IBatisNet.DataMapper.MappedStatements.ResultStrategy.ResultMapStrategy.Process (IBatisNet.DataMapper.Scope.RequestScope request, System.Data.IDataReader& reader, System.Object resultObject) [0x0008f] in :0 
  at IBatisNet.DataMapper.MappedStatements.ResultStrategy.MapStrategy.Process (IBatisNet.DataMapper.Scope.RequestScope request, System.Data.IDataReader& reader, System.Object resultObject) [0x0002a] in :0 
  at IBatisNet.DataMapper.MappedStatements.MappedStatement.RunQueryForObject[T] (IBatisNet.DataMapper.Scope.RequestScope request, IBatisNet.DataMapper.ISqlMapSession session, System.Object parameterObject, T resultObject) [0x00013] in :0 
result:

本来我应该执行的只有一句 [SELECT id, methodName FROM methodInfo WHERE (id = @param0 )]，可是前面另一张表的各种SQL也被预打印了一遍？？？

如果只是将查询改成这样

public MethodInfo QueryMethodInfo(int id)
{
 try
 {
     ISqlMapper mapper = Mapper.Instance();
     //MethodInfo result = mapper.QueryForObject("QueryMethodInfo", id);
     //return result;
     return null;
 }
 catch (Exception ex)
 {
     Console.WriteLine("ex:{0}", ex);
     return null;
 }
}

LOG如下，且不会报错。

2017/04/12 22:33:38:SSS [DEBUG] IBatisNet.Common.Utilities.ConfigWatcherHandler - Adding file [SqlMap.config] to list of watched files.
2017/04/12 22:33:39:SSS [DEBUG] IBatisNet.Common.Utilities.ConfigWatcherHandler - Adding file [Mybatis.xml] to list of watched files.
2017/04/12 22:33:39:SSS [DEBUG] IBatisNet.DataMapper.Configuration.Statements.PreparedStatementFactory - Statement Id: [QueryMybatis] Prepared SQL: [SELECT id, name    FROM `mybatis`     WHERE (id =  @param0 )]
2017/04/12 22:33:39:SSS [DEBUG] IBatisNet.DataMapper.Configuration.Statements.PreparedStatementFactory - Statement Id: [InsertMybatis] Prepared SQL: [INSERT INTO `mybatis` (id, name) VALUES (      @param0 ,  @param1    );]
2017/04/12 22:33:39:SSS [DEBUG] IBatisNet.DataMapper.Configuration.Statements.PreparedStatementFactory - Statement Id: [UpdateMybatis] Prepared SQL: [UPDATE `mybatis`     SET       name =  @param0        WHERE       (id =  @param1 )]
2017/04/12 22:33:39:SSS [DEBUG] IBatisNet.DataMapper.Configuration.Statements.PreparedStatementFactory - Statement Id: [DeleteMybatis] Prepared SQL: [DELETE FROM `mybatis`       WHERE       (id =  @param0 )]
2017/04/12 22:33:39:SSS [DEBUG] IBatisNet.Common.Utilities.ConfigWatcherHandler - Adding file [MethodInfo.xml] to list of watched files.
2017/04/12 22:33:39:SSS [DEBUG] IBatisNet.DataMapper.Configuration.Statements.PreparedStatementFactory - Statement Id: [QueryMethodInfo] Prepared SQL: [SELECT id, methodName    FROM `methodInfo`     WHERE (id =  @param0 )]

参考资料

c# mybatis net +mysql 的一个初步摸索

贡献者

binbin.hou