IBatisNet 的相关使用方法
1. IBatisNet 简介
IBatisNet 是一个 ORM(Object Relational Mapping, 对象关系 映射 ) 框架,着重于 ORM 中的 M(Mapping) , 通过使用 XML 文档在 sql 语句和实体对象之间建立映射。
常用的图解:
2. IBatisNet 配置文件详解
SqlMap.config Map 配置文件
<? xml version = "1.0 " encoding = "utf-8 "?>
< sqlMapConfig xmlns = "http://ibatis.apache.org/dataMapper " xmlns:xsi = "http://www.w3.org/2001/XMLSchema-instance ">
< settings >
< setting useStatementNamespaces = "false "/> 是否使用 sqlMap 节点中的 namespace
< setting cacheModelsEnabled = "true "/> 是否启用缓存机制
< setting validateSqlMap = "false "/> 是否需要使用 SqlMapConfig.xsd schema 验证隐射
</ settings >
< database >
< provider name = "sqlServer1.1 "/> provider 获取 providers.config 中对应的属性
< dataSource name = "ConnectString " connectionString = "server=.;database=Test;user id=sa;password=sa;Connection Reset=FALSE;Min Pool Size=1;Max Pool Size=100 "/>
数据库链接
</ database >
< sqlMaps >
< sqlMap resource = "maps/Member.xml "/> Map 文件加载的路径,如果要加载多个,按此格式写下去
</ sqlMaps >
</ sqlMapConfig >
providers.config 数据库链接配置文件
<? xml version = "1.0 " encoding = "utf-8 "?>
< providers xmlns = "http://ibatis.apache.org/providers " xmlns:xsi = "http://www.w3.org/2001/XMLSchema-instance ">
< clear />
< provider
name = "sqlServer1.0 "
enabled = "false "
assemblyName = "System.Data, Version=1.0.3300.0, Culture=neutral, PublicKeyToken=b77a5c561934e089 "
connectionClass = "System.Data.SqlClient.SqlConnection "
commandClass = "System.Data.SqlClient.SqlCommand "
parameterClass = "System.Data.SqlClient.SqlParameter "
parameterDbTypeClass = "System.Data.SqlDbType "
parameterDbTypeProperty = "SqlDbType "
dataAdapterClass = "System.Data.SqlClient.SqlDataAdapter "
commandBuilderClass = "System.Data.SqlClient.SqlCommandBuilder "
usePositionalParameters = "false "
useParameterPrefixInSql = "true "
useParameterPrefixInParameter = "true "
parameterPrefix = "@ "
/>
< provider
name = "sqlServer1.1 "
enabled = "true "
assemblyName = "System.Data, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089 "
connectionClass = "System.Data.SqlClient.SqlConnection "
commandClass = "System.Data.SqlClient.SqlCommand "
parameterClass = "System.Data.SqlClient.SqlParameter "
parameterDbTypeClass = "System.Data.SqlDbType "
parameterDbTypeProperty = "SqlDbType "
dataAdapterClass = "System.Data.SqlClient.SqlDataAdapter "
commandBuilderClass = "System.Data.SqlClient.SqlCommandBuilder "
usePositionalParameters = "false "
useParameterPrefixInSql = "true "
useParameterPrefixInParameter = "true "
parameterPrefix = "@ "
/>
< providers>
可以获取官方文件,获得相应数据库的provider
3. 相关 Map 文件配置
Member.xml
<? xml version = "1.0 " encoding = "utf-8 " ?>
< sqlMap namespace = "MemberInfo " xmlns = "http://ibatis.apache.org/mapping " xmlns:xsi = "http://www.w3.org/2001/XMLSchema-instance ">
namespace 就是useStatementNamespaces 对应的namespace
< alias >
< typeAlias alias = "Member " 此map 映射的类名
assembly = "ETest.dll " 引用的程序集
type = "ETest.test.Member " 对应项目中的累
/>
</ alias >
< resultMaps > 返回的所有结果类
< resultMap 返回的一个结果类
id = "MemberMap " id ,对应下面select 等语句返回的map
class = "Member "> 对应alias 中映射的类名
< result property = "Name " 对应alias 中映射的类的属性
column = "Name " /> 对应返回结果中的列表对应到类的属性
< result property = "CardNO "
column = "CardNO " /> 为了解释采用这种格式,见谅
</ resultMap >
</ resultMaps >
< statements >
< select id = "SelectMember " 方法id, 在程序中被调用
resultMap = "MemberMap " 对应resultMap 中相应的id
parameterClass = "int "> 参数集合,如为int ,string 等单值,则语句中
参数位置使用#value#
SELECT CardNo,Name FROM Member Where UserID = #value#
</ select >
< update id = "SaveMember "
parameterClass = "Member " 参数集合Member ,对应相应操作的类
resultClass = "int ">
UPDATE Member set
CardNo = #CardNo#, Member 的属性CardNo
Name =#Name# Member 的属性Name
where UserID =#UserID# Member 的属性UserID
</ update >
< delete id = "DeleteMember " parameterClass = "int ">
delete
from Member
where UserID = #value#
</ delete >
</ statements >
</ sqlMap >
<insert> 、<delete> 、<update> 、<select> 四个节点分别代表对数据库进行的4 个操作
4. 相关类以及 Member 表
Create table Member
( User_ID int identity ( 1, 1), -- 自增
CardNo nvarchar ( 50) not null,
[Name] nvarchar ( 50) not null)
namespace ETest.test {
[Serializable ]
public class Member { Member 类。包括属性存取
private int _user_ID;
private string _name;
private string _cardNO;
public string CardNO
{
get { return _cardNO; }
set { _cardNO = value ; }
}
public string Name
{
get { return _name; }
set { _UserName = value ; }
public int User_ID
{
get { return _user_ID; }
set { _user_ID = value ; }
}
}
public class MemberLogic 相关逻辑类,包含所有逻辑
{
private SqlMapper mapper = null ;
public MemberLogic () { 构造函数
DomSqlMapBuilder builder = new DomSqlMapBuilder ();
mapper = builder.Configure(); mapping
}
public Member Select(int User_ID)
{
return (Member )mapper.QueryForObject(" SelectMember " , User_ID);
map 的自有方法,返还Member.xml 中id 为“SelectMember ”的执行结果,返还结果为对象
}
public void Update(Member member)
{
mapper.Update(" SaveMember " , member);
map 的自有方法, 直接对应 Member.xml 中id 为“SaveMember ”的执行.
}
public void Delete(int User_ID)
{
mapper.Update(" DeleteMember " , member);
map 的自有方法, 直接对应 Member.xml 中id 为“DeleteMember ”的执行.
}
}
下面简单说一下mapper 的几个常用方法:
QueryForObject 用于返回单个实体类,
QueryForList 返回实现IList 接口的ArrayList,
QueryForDictionary 用于返回实现IDictionary 的Hashtable ,
5. 存储过程的使用
先写个简单的存储过程
create proc InsertMember
(
@UserID int ,
@Cardno nvarchar ( 50),
@Name nvarchar ( 10)
)
as
begin
insert memeber select @Cardno, @Name
select @UserID= @@identity
end
以下文件配置在 Member.xml 中进行操作存储过程
< procedure id = "InsertMember " 存储过程名称
parameterMap = "Member_Par " 对应的parameterMap ,接来下马上登场
resultClass = "int "> 返回值,同正常返回值
InsertMember
</ procedure >
在procedure 中必须设置parameterMap 属性,即使存储不带任何参数也需要设置一下。
对应的parameterMap :
< parameterMaps >
< parameterMap id =" Member_Par " 对应 procedure 中的parameterMap
class ="Member"> 对应的类Member
< parameter property = " CardNo " 对应类中属性
column = " CardNo " 对应参数的属性
dbType = "NVARCHAR " 数据库字段类型
type = "string " 自动类型
direction = "INPUT " /> 参数类型
< parameter property = " Name " 对应类中属性
column = "Name " 对应参数的属性
dbType = "NVARCHAR " 数据库字段类型
type = "string " 自动类型
direction = "INPUT " /> 参数类型
< parameter property = " User_ID " 对应类中属性
column = " User_ID " 对应参数的属性
dbType = "INT " 数据库字段类型
type = "int " 自动类型
direction = "INPUTOUTPUT " /> 返回值参数
</ parameterMap >
</ parameterMaps >
6. 动态 SQL
动态 sql 就可以解决相关的多表链接查询以及模糊查询的问题
< select id = "SelectEemployee " parameterClass = "string " resultMap = "MemberMap ">
select CardNo,Name from Member
< dynamic prepend = "WHERE ">
< isParameterPresent >
User_ID = #value#
</ isParameterPresent >
</ dynamic >
</ select >
动态sql 写法
< dynamic prepend = "WHERE "> 表示动态sql 开始,如果关联表 prepend = "AND ", 可以为 ””
< 关键字
<! — 关键字可以为以下 相关含义
<isEqual> 参数等于值
<isNotEqual> 参数不等于值
<isGreaterThan> 参数 大于值
<isGreaterEqual> 参数 大于等于值
<isLessEqual> 参数 小于等于值
<isPropertyAvailable> 参数 被使用
<isNotPropertyAvailable> 参数 没有使用
<isNull> 参数 is null
<isNotNull> 参数 is not null
<isEmpty> 参数 为空
<isNotEmpty> 参数 不为空
<isParameterPresent> 参数类不为 NULL
<isNotParameterPresent> 参数 类为 NULL
-- >
prepend = "AND " 连接关键字
property = "UserID " 参数
compareValue = "20 "> 值, 也可以为属性名来比较
相关sql ,就是动态sql 要添加的条件,比如:
Name = #value#
</ 关键字>
</dynamic>