.NET 常用ORM之iBatis

最新推荐文章于 2021-01-26 10:53:43 发布

banggujuan3308

最新推荐文章于 2021-01-26 10:53:43 发布

阅读量298

点赞数

文章标签：数据库 java

原文链接：http://www.cnblogs.com/become/p/8873386.html

版权

ibatis 一词来源于“internet”和“abatis”的组合，是一个由Clinton Begin在2001年发起的开放源代码项目，到后面发展的版本叫MyBatis但都是指的同一个东西。最初侧重于密码软件的开发，现在是一个基于Java的持久层框架，渐渐的也把这种模式转移到了net。相对NHibernate等“一站式”ORM解决方案而言，ibatis 是一种“半自动化”的ORM实现。性能不错。

下载地址：http://code.google.com/p/mybatisnet/downloads/list?can=3

一、新建一个项目工程，导入iBatis所需的动态库，IBatisNet.Common.dll和IBatisNet.DataMapper.dll（当然你也可以直接导入下载的iBatis文件包的dll文件）

二、添加iBatis配置文件（三种）

1.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"
        description="Microsoft SQL Server, provider V1.0.3300.0 in framework .NET V1.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="@"
        allowMARS="false"
  />
  <provider
        name="sqlServer1.1"
        description="Microsoft SQL Server, provider V1.0.5000.0 in framework .NET V1.1"
        enabled="false"
        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="@"
        allowMARS="false"
  />
  <provider
      name="sqlServer2.0"
      enabled="true"
      description="Microsoft SQL Server, provider V2.0.0.0 in framework .NET V2.0"
      assemblyName="System.Data, Version=2.0.0.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="@"
      allowMARS="false"
    />
  <provider
     name="sqlServer2005"
     enabled="false"
     description="Microsoft SQL Server, provider V2.0.0.0 in framework .NET V2.0"
     assemblyName="System.Data, Version=2.0.0.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="@"
     allowMARS="true"
    />
  <provider name="OleDb1.1"
      description="OleDb, provider V1.0.5000.0 in framework .NET V1.1"
      enabled="false"
      assemblyName="System.Data, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
      connectionClass="System.Data.OleDb.OleDbConnection"
      commandClass="System.Data.OleDb.OleDbCommand"
      parameterClass="System.Data.OleDb.OleDbParameter"
      parameterDbTypeClass="System.Data.OleDb.OleDbType"
      parameterDbTypeProperty="OleDbType"
      dataAdapterClass="System.Data.OleDb.OleDbDataAdapter"
      commandBuilderClass="System.Data.OleDb.OleDbCommandBuilder"
      usePositionalParameters="true"
      useParameterPrefixInSql="false"
      useParameterPrefixInParameter="false"
      parameterPrefix=""
      allowMARS="false"
    />
  <provider name="OleDb2.0"
    description="OleDb, provider V2.0.0.0 in framework .NET V2"
    enabled="false"
    assemblyName="System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
    connectionClass="System.Data.OleDb.OleDbConnection"
    commandClass="System.Data.OleDb.OleDbCommand"
    parameterClass="System.Data.OleDb.OleDbParameter"
    parameterDbTypeClass="System.Data.OleDb.OleDbType"
    parameterDbTypeProperty="OleDbType"
    dataAdapterClass="System.Data.OleDb.OleDbDataAdapter"
    commandBuilderClass="System.Data.OleDb.OleDbCommandBuilder"
    usePositionalParameters="true"
    useParameterPrefixInSql="false"
    useParameterPrefixInParameter="false"
    parameterPrefix=""
    allowMARS="false"
    />
  <provider
    name="Odbc1.1"
    description="Odbc, provider V1.0.5000.0 in framework .NET V1.1"
    enabled="false"
    assemblyName="System.Data, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
    connectionClass="System.Data.Odbc.OdbcConnection"
    commandClass="System.Data.Odbc.OdbcCommand"
    parameterClass="System.Data.Odbc.OdbcParameter"
    parameterDbTypeClass="System.Data.Odbc.OdbcType"
    parameterDbTypeProperty="OdbcType"
    dataAdapterClass="System.Data.Odbc.OdbcDataAdapter"
    commandBuilderClass="System.Data.Odbc.OdbcCommandBuilder"
    usePositionalParameters="true"
    useParameterPrefixInSql="false"
    useParameterPrefixInParameter="false"
    parameterPrefix="@"
    allowMARS="false"
    />
  <provider
    name="Odbc2.0"
    description="Odbc, provider V2.0.0.0 in framework .NET V2"
    enabled="false"
    assemblyName="System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
    connectionClass="System.Data.Odbc.OdbcConnection"
    commandClass="System.Data.Odbc.OdbcCommand"
    parameterClass="System.Data.Odbc.OdbcParameter"
    parameterDbTypeClass="System.Data.Odbc.OdbcType"
    parameterDbTypeProperty="OdbcType"
    dataAdapterClass="System.Data.Odbc.OdbcDataAdapter"
    commandBuilderClass="System.Data.Odbc.OdbcCommandBuilder"
    usePositionalParameters="true"
    useParameterPrefixInSql="false"
    useParameterPrefixInParameter="false"
    parameterPrefix="@"
    allowMARS="false"
  />
  <provider
    name="oracle9.2"
    description="Oracle, Oracle provider V9.2.0.401"
    enabled="false"
    assemblyName="Oracle.DataAccess, Version=9.2.0.401, Culture=neutral, PublicKeyToken=89b483f429c47342" connectionClass="Oracle.DataAccess.Client.OracleConnection"
    commandClass="Oracle.DataAccess.Client.OracleCommand"
    parameterClass="Oracle.DataAccess.Client.OracleParameter"
    parameterDbTypeClass="Oracle.DataAccess.Client.OracleDbType"
    parameterDbTypeProperty="OracleDbType"
    dataAdapterClass="Oracle.DataAccess.Client.OracleDataAdapter"
    commandBuilderClass="Oracle.DataAccess.Client.OracleCommandBuilder"
    usePositionalParameters="false"
    useParameterPrefixInSql="true"
    useParameterPrefixInParameter="false"
    parameterPrefix=":"
    useDeriveParameters="false"
    allowMARS="false"
  />
  <provider
    name="oracle10.1"
    description="Oracle, oracle provider V10.1.0.301"
    enabled="false"
    assemblyName="Oracle.DataAccess, Version=10.1.0.301, Culture=neutral, PublicKeyToken=89b483f429c47342" connectionClass="Oracle.DataAccess.Client.OracleConnection"
    commandClass="Oracle.DataAccess.Client.OracleCommand"
    parameterClass="Oracle.DataAccess.Client.OracleParameter"
    parameterDbTypeClass="Oracle.DataAccess.Client.OracleDbType"
    parameterDbTypeProperty="OracleDbType"
    dataAdapterClass="Oracle.DataAccess.Client.OracleDataAdapter"
    commandBuilderClass="Oracle.DataAccess.Client.OracleCommandBuilder"
    usePositionalParameters="true"
    useParameterPrefixInSql="true"
    useParameterPrefixInParameter="true"
    parameterPrefix=":"
    useDeriveParameters="false"
    allowMARS="false"
  />
  <provider
    name="oracleClient1.0"
    description="Oracle, Microsoft provider V1.0.5000.0"
    enabled="false"
    assemblyName="System.Data.OracleClient, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" connectionClass="System.Data.OracleClient.OracleConnection"
    commandClass="System.Data.OracleClient.OracleCommand"
    parameterClass="System.Data.OracleClient.OracleParameter"
    parameterDbTypeClass="System.Data.OracleClient.OracleType"
    parameterDbTypeProperty="OracleType"
    dataAdapterClass="System.Data.OracleClient.OracleDataAdapter"
    commandBuilderClass="System.Data.OracleClient.OracleCommandBuilder"
    usePositionalParameters="false"
    useParameterPrefixInSql="true"
    useParameterPrefixInParameter="false"
    parameterPrefix=":"
    allowMARS="false"
  />
  <provider
    name="ByteFx"
    description="MySQL, ByteFx provider V0.7.6.15073"
    enabled="false"
    assemblyName="ByteFX.MySqlClient, Version=0.7.6.15073, Culture=neutral, PublicKeyToken=f2fef6fed1732fc1" connectionClass="ByteFX.Data.MySqlClient.MySqlConnection"
    commandClass="ByteFX.Data.MySqlClient.MySqlCommand"
    parameterClass="ByteFX.Data.MySqlClient.MySqlParameter"
    parameterDbTypeClass="ByteFX.Data.MySqlClient.MySqlDbType"
    parameterDbTypeProperty="MySqlDbType"
    dataAdapterClass="ByteFX.Data.MySqlClient.MySqlDataAdapter"
    commandBuilderClass="ByteFX.Data.MySqlClient.MySqlCommandBuilder"
    usePositionalParameters="false"
    useParameterPrefixInSql="true"
    useParameterPrefixInParameter="true"
    parameterPrefix="@"
    allowMARS="false"
  />
  <provider
    name="MySql"
    description="MySQL, MySQL provider 1.0.7.30072"
    enabled="false"
    assemblyName="MySql.Data, Version=1.0.7.30072, Culture=neutral, PublicKeyToken=c5687fc88969c44d" connectionClass="MySql.Data.MySqlClient.MySqlConnection"
    commandClass="MySql.Data.MySqlClient.MySqlCommand"
    parameterClass="MySql.Data.MySqlClient.MySqlParameter"
    parameterDbTypeClass="MySql.Data.MySqlClient.MySqlDbType"
    parameterDbTypeProperty="MySqlDbType"
    dataAdapterClass="MySql.Data.MySqlClient.MySqlDataAdapter"
    commandBuilderClass="MySql.Data.MySqlClient.MySqlCommandBuilder"
    usePositionalParameters="false"
    useParameterPrefixInSql="true"
    useParameterPrefixInParameter="true"
    parameterPrefix="?"
    allowMARS="false"
  />
  <provider name="SQLite3 Finisar"
    description="SQLite, SQLite.NET provider V0.21.1869.3794"
    enabled="false"
    assemblyName="SQLite.NET, Version=0.21.1869.3794, Culture=neutral, PublicKeyToken=c273bd375e695f9c"
    connectionClass="Finisar.SQLite.SQLiteConnection"
    commandClass="Finisar.SQLite.SQLiteCommand"
    parameterClass="Finisar.SQLite.SQLiteParameter"
    parameterDbTypeClass="System.Data.DbType, System.Data"
    parameterDbTypeProperty="DbType"
    dataAdapterClass="Finisar.SQLite.SQLiteDataAdapter"
    commandBuilderClass="Finisar.SQLite.SQLiteCommandBuilder"
    usePositionalParameters="false"
    useParameterPrefixInSql="true"
    useParameterPrefixInParameter="true"
    parameterPrefix="@"
    setDbParameterPrecision="false"
    setDbParameterScale="false"
    allowMARS="false"
  />
  <provider name="SQLite3"
    description="SQLite, SQLite.NET provider V1.0.43.0"
    enabled="false"
    assemblyName="System.Data.SQLite, Version=1.0.43.0, Culture=neutral, PublicKeyToken=db937bc2d44ff139"
    connectionClass="System.Data.SQLite.SQLiteConnection"
    commandClass="System.Data.SQLite.SQLiteCommand"
    parameterClass="System.Data.SQLite.SQLiteParameter"
    parameterDbTypeClass="System.Data.SQLite.SQLiteType"
    parameterDbTypeProperty="DbType"
    dataAdapterClass="System.Data.SQLite.SQLiteDataAdapter"
    commandBuilderClass="System.Data.SQLite.SQLiteCommandBuilder"
    usePositionalParameters="false"
    useParameterPrefixInSql="true"
    useParameterPrefixInParameter="true"
    parameterPrefix="@"
    setDbParameterPrecision="false"
    setDbParameterScale="false"
    allowMARS="false"
  />
  <provider
    name="Firebird1.7"
    description="Firebird, Firebird SQL .NET provider V1.7.0.33200"
    enabled="false"
    assemblyName="FirebirdSql.Data.Firebird, Version=1.7.0.33200, Culture=neutral, PublicKeyToken=fa843d180294369d" connectionClass="FirebirdSql.Data.Firebird.FbConnection"
    commandClass="FirebirdSql.Data.Firebird.FbCommand"
    parameterClass="FirebirdSql.Data.Firebird.FbParameter"
    parameterDbTypeClass="FirebirdSql.Data.Firebird.FbDbType"
    parameterDbTypeProperty="FbDbType"
    dataAdapterClass="FirebirdSql.Data.Firebird.FbDataAdapter"
    commandBuilderClass="FirebirdSql.Data.Firebird.FbCommandBuilder"
    usePositionalParameters="false"
    useParameterPrefixInSql="true"
    useParameterPrefixInParameter="true"
    parameterPrefix="@"
    allowMARS="false"
  />
  <provider
    name="PostgreSql0.99.1.0"
    description="PostgreSql, Npgsql provider V0.99.1.0"
    enabled="false"
    assemblyName="Npgsql, Version=0.99.1.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7"
    connectionClass="Npgsql.NpgsqlConnection"
    commandClass="Npgsql.NpgsqlCommand"
    parameterClass="Npgsql.NpgsqlParameter"
    parameterDbTypeClass="NpgsqlTypes.NpgsqlDbType"
    parameterDbTypeProperty="NpgsqlDbType"
    dataAdapterClass="Npgsql.NpgsqlDataAdapter"
    commandBuilderClass="Npgsql.NpgsqlCommandBuilder"
    usePositionalParameters="false"
    useParameterPrefixInSql="true"
    useParameterPrefixInParameter="true"
    parameterPrefix=":"
    allowMARS="true"
  />
  <provider
    name="iDb2.10"
    description="IBM DB2 Provider, V 10.0"
    enabled="false"
    assemblyName="IBM.Data.DB2.iSeries, Version=10.0.0.0,Culture=neutral, PublicKeyToken=9cdb2ebfb1f93a26, Custom=null" connectionClass="IBM.Data.DB2.iSeries.iDB2Connection"
    commandClass="IBM.Data.DB2.iSeries.iDB2Command"
    parameterClass="IBM.Data.DB2.iSeries.iDB2Parameter"
    parameterDbTypeClass="IBM.Data.DB2.iSeries.iDB2DbType"
    parameterDbTypeProperty="iDB2DbType"
    dataAdapterClass="IBM.Data.DB2.iSeries.iDB2DataAdapter"
    commandBuilderClass="IBM.Data.DB2.iSeries.iDB2CommandBuilder"
    usePositionalParameters="true"
    useParameterPrefixInSql="false"
    useParameterPrefixInParameter="false"
    parameterPrefix=""
    allowMARS="false"
  />
  <provider
    name="Informix"
    description="Informix NET Provider, 2.81.0.0"
    enabled="false"
    assemblyName="IBM.Data.Informix, Version=2.81.0.0, Culture=neutral, PublicKeyToken=7c307b91aa13d208"
    connectionClass="IBM.Data.Informix.IfxConnection"
    commandClass="IBM.Data.Informix.IfxCommand"
    parameterClass="IBM.Data.Informix.IfxParameter"
    parameterDbTypeClass="IBM.Data.Informix.IfxType"
    parameterDbTypeProperty="IfxType"
    dataAdapterClass="IBM.Data.Informix.IfxDataAdapter"
    commandBuilderClass="IBM.Data.Informix.IfxCommandBuilder"
    usePositionalParameters = "true"
    useParameterPrefixInSql = "false"
    useParameterPrefixInParameter = "false"
    useDeriveParameters="false"
    allowMARS="false"
    />
</providers>

View Code

2.SQLMap.config[文件名不可修改]，配置连接数据库字符串与每个实体类（表）的映射文件配置，复制文件到网站的根目录，代码如下：

<?xml version="1.0" encoding="utf-8"?>
<sqlMapConfig
  xmlns="http://ibatis.apache.org/dataMapper"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <settings>
    <!--/是否使用Satement命名空间，这里的命名空间指的是映射文件中sqlMap节点的namespace属性，默认是false-->
    <setting useStatementNamespaces="false"/>
    <!--是否启用DataMapper的缓存机制,针对全部的SqlMap，默认是true-->
    <setting cacheModelsEnabled="true"/>
    <!--是否启用SqlMapConfig.xsd schema验证映射文件，默认是false-->
    <setting validateSqlMap="false"/>
  </settings>
  <!--配置数据驱动提供类配置文件的路径和文件名-->
  <providers resource="providers.config"/>
  <!--如果在providers.config文件中指定了默认的数据库驱动，那么provider节点就不需要设置了，它的作用是在换数据库驱动时不需要修改providers.config文件。datasource节点用于指定ADO.NET Connection String.-->
  <database>
    <provider name="sqlServer2.0"/>
    <dataSource name="mydb" connectionString="data source=127.0.0.1;database=Test;user id=sa;password=xxxxxx;"/>
  </database>
  <!--指定映射的文件的位置-->
  <sqlMaps>
    <sqlMap resource="Maps/RoleMap.xml"/>
    <sqlMap resource="Maps/UserInfoMap.xml"/>
  </sqlMaps>
</sqlMapConfig>

View Code

3.每个实体类（表）都有一个单独的配置文件类，类型为xml。实体类的配置文件格式如下，在网站的根目录创建一个Map的文件夹，新建xxxMap.xml格式的配置类，比如：UserInfoMap.xml、RoleMap.xml文件，示例代码如下：

<?xml version="1.0" encoding="utf-8" ?> 
<!--namespace必须用否则会报错（读取配置文件报：未将对象引用设置到对象的实例）-->
<sqlMap namespace="" xmlns="http://ibatis.apache.org/mapping" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <statements>
        <select id="GetUserInfoById" parameterClass="string" resultClass="HashTable">
            SELECT * FROM UserInfo WHERE ID=#ID#
        </select>
        <insert id="AddUserInfo" parameterClass="hashtable">
           insert into UserInfo  values(#Name#,#Age#,#Sex#,#Men#,#Remark#)
           <selectKey resultClass="int" type="post" keyProperty="ID">
        select @@IDENTITY as value
           </selectKey>
        </insert>
        <delete id="DeleteUserInfo" parameterClass="int" restultClass="int">
            DELETE FROM dbo.UserInfo WHERE ID=#ID#
        </delete>
        <update id="UpdateUserInfo" parameterClass="hashtable" restltClass="int">
            update UserInfo set Name=#Name#,Age=#Age#,Sex=#Sex#,Men=#Men#,Remark=#Remark# where ID=#ID#
        </update>
        <select id="GetAllUserInfo" resultClass="HashTable">
            SELECT * FROM UserInfo
        </select>
    </statements>
</sqlMap>

View Code

<?xml version="1.0" encoding="utf-8" ?>
<!--namespace必须用否则会报错（读取配置文件报：未将对象引用设置到对象的实例）-->
<sqlMap namespace="" xmlns="http://ibatis.apache.org/mapping" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <statements>
    <select id="GetRoleById" parameterClass="string" resultClass="HashTable">
      SELECT * FROM Role WHERE ID=#ID#
    </select>
    <insert id="AddRole" parameterClass="hashtable">
      insert into Role  values(#Uid#,#RoleName#,#Remark#)
      <selectKey resultClass="int" type="post" keyProperty="ID">
        select @@IDENTITY as value
      </selectKey>
    </insert>
    <delete id="DeleteRole" parameterClass="int" restultClass="int">
      DELETE FROM dbo.Role WHERE ID=#ID#
    </delete>
    <update id="UpdateRole" parameterClass="hashtable" restltClass="int">
      update Role set Uid=#Uid#,RoleName=#RoleName#,Remark=#Remark# where ID=#ID#
    </update>
    <select id="GetAllRole" resultClass="HashTable">
      SELECT * FROM Role
    </select>
  </statements>
</sqlMap>

View Code

4.加入一个DB辅助类IBatisHepler.cs，代码如下：

public class IBatisHepler
    {
        private static ISqlMapper mapper = null;
        static IBatisHepler()
        {
            DomSqlMapBuilder builder = new DomSqlMapBuilder();//其作用是根据配置文件创建SqlMap实例。
            mapper = builder.Configure("SQLMap.config") as SqlMapper;//SqlMapper是iBatisNet的核心组件，提供数据库操作的基础平台。SqlMapper可通过DomSqlMapBuilder创建。
        }
        /// <summary>
        /// 执行添加操作
        /// </summary>
        /// <param name="tag">语句ID</param>
        /// <param name="paramObject">语句所需要的参数</param>
        /// <returns>添加的主键</returns>
        public static object ExcuteInsert(string tag, Hashtable paramObject)
        {
            return mapper.Insert(tag, paramObject);
        }
        /// <summary>
        /// 执行删除操作
        /// </summary>
        /// <param name="tag">语句ID</param>
        /// <param name="paramObject">语句所需要的参数</param>
        /// <returns>影响行数</returns>
        public static int ExcuteDelete(string tag, object paramObject)
        {
            return mapper.Delete(tag, paramObject);
        }
        /// <summary>
        /// 执行修改操作
        /// </summary>
        /// <param name="tag">语句ID</param>
        /// <param name="paramObject">语句所需要的参数</param>
        /// <returns>影响行数</returns>
        public static int ExcuteUpdate(string tag, Hashtable paramObject)
        {
            return mapper.Update(tag, paramObject);
        }
        /// <summary>
        /// 查询单个实体对象
        /// </summary>
        /// <param name="tag">语句ID</param>
        /// <param name="paramObject">语句所需要的参数</param>
        /// <returns>得到的Object</returns>
        public static object ExcuteSelectObj(string tag, object paramObject)
        {
            object obj = mapper.QueryForObject(tag, paramObject);
            return obj;
        }
        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="tag">语句ID</param>
        /// <param name="paramObject">语句所需要的参数</param>
        /// <param name="pageIndex">页索引</param>
        /// <param name="pageSize">每页显示的记录数</param>
        /// <returns>得到的List集合</returns>
        public static IList ExcuteSelect(string tag, object paramObject, int pageIndex, int pageSize)
        {
            return mapper.QueryForList(tag, paramObject, pageIndex, pageSize);
        }
        /// <summary>
        /// 通用的以DataTable的方式得到Select的结果(xml文件中参数要使用$标记的占位参数)
        /// </summary>
        /// <param name="tag">语句ID</param>
        /// <param name="paramObject">语句所需要的参数</param>
        /// <returns>得到的DataTable</returns>
        public static DataTable ExcuteSelect(string tag, object paramObject)
        {
            DataSet ds = new DataSet();
            bool isSessionLocal = false;
            ISqlMapSession session = mapper.LocalSession;
            if (session == null)
            {
                session = mapper.CreateSqlMapSession();
                session.OpenConnection();
                isSessionLocal = true;
            }
            try
            {
                IDbCommand cmd = session.CreateCommand(CommandType.Text);
                cmd.Connection = session.Connection;
                cmd.CommandText = GetSql(tag, paramObject);
                IDbDataAdapter adapter = session.CreateDataAdapter(cmd);
                adapter.Fill(ds);
            }
            finally
            {
                if (isSessionLocal)
                {
                    session.CloseConnection();
                }
            }
            return ds.Tables[0];
        }
        /// <summary>
        /// 得到参数化后的SQL
        /// </summary>
        /// <param name="tag">语句ID</param>
        /// <param name="paramObject">语句所需要的参数</param>
        /// <returns>sql语句</returns>
        protected static string GetSql(string tag, object paramObject)
        {
            IStatement statement = mapper.GetMappedStatement(tag).Statement;
            IMappedStatement mapStatement = mapper.GetMappedStatement(tag);
            ISqlMapSession session = mapper.CreateSqlMapSession();
            RequestScope request = statement.Sql.GetRequestScope(mapStatement, paramObject, session);
            return request.PreparedStatement.PreparedSql;
        }
        /// <summary>
        /// 用于分页控件使用
        /// </summary>
        /// <param name="tag">语句ID</param>
        /// <param name="paramObject">语句所需要的参数</param>
        /// <param name="PageSize">每页显示数目</param>
        /// <param name="curPage">当前页</param>
        /// <param name="recCount">记录总数</param>
        /// <returns>得到的DataTable</returns>
        public static DataTable ExcuteSelect(string tag, object paramObject, int PageSize, int curPage, out int recCount)
        {
            IDataReader dr = null;
            bool isSessionLocal = false;
            string sql = GetSql(tag, paramObject);
            string strCount = "select count(*) " + sql.Substring(sql.ToLower().IndexOf("from"));
            IDalSession session = mapper.LocalSession;
            DataTable dt = new DataTable();
            if (session == null)
            {
                session = mapper.CreateSqlMapSession();
                session.OpenConnection();
                isSessionLocal = true;
            }
            try
            {
                IDbCommand cmdCount = session.CreateCommand(CommandType.Text);
                cmdCount.Connection = session.Connection;
                cmdCount.CommandText = strCount;
                object count = cmdCount.ExecuteScalar();
                recCount = Convert.ToInt32(count);
                IDbCommand cmd = session.CreateCommand(CommandType.Text);
                cmd.Connection = session.Connection;
                cmd.CommandText = sql;
                dr = cmd.ExecuteReader();
                dt = Paging(dr, PageSize, curPage);
            }
            finally
            {
                if (isSessionLocal)
                {
                    session.CloseConnection();
                }
            }
            return dt;
        }

        /**/
        /// <summary>
        /// 取回合适数量的数据
        /// </summary>
        /// <param name="dataReader"></param>
        /// <param name="PageSize"></param>
        /// <param name="curPage"></param>
        /// <returns></returns>
        protected static DataTable Paging(IDataReader dataReader, int PageSize, int curPage)
        {
            DataTable dt;
            dt = new DataTable();
            int colCount = dataReader.FieldCount;
            for (int i = 0; i < colCount; i++)
            {
                dt.Columns.Add(new DataColumn(dataReader.GetName(i), dataReader.GetFieldType(i)));
            }
            // 读取数据。将DataReader中的数据读取到DataTable中
            object[] vald = new object[colCount];
            int iCount = 0; // 临时记录变量
            while (dataReader.Read())
            {
                // 当前记录在当前页记录范围内
                if (iCount >= PageSize * (curPage - 1) && iCount < PageSize * curPage)
                {
                    for (int i = 0; i < colCount; i++)
                        vald[i] = dataReader.GetValue(i);
                    dt.Rows.Add(vald);
                }
                else if (iCount > PageSize * curPage)
                {
                    break;
                }
                iCount++; // 临时记录变量递增
            }
            if (!dataReader.IsClosed)
            {
                dataReader.Close();
                dataReader.Dispose();
            }
            return dt;
        }
    }

View Code

三、代码中实际使用，代码如下：

public ActionResult Index()
        {
            //1.新增
            Hashtable ht_add = new Hashtable();
            ht_add["Uid"] = 3;  //Key一定要与配置文件中的名称一致
            ht_add["RoleName"] = "操作员";
            ht_add["Remark"] = "iBatis操作员";

            object obj = IBatisHepler.ExcuteInsert("AddRole", ht_add);
            bool bl = Common.Tools.IsNumber(obj.ToString());

            ViewBag.Status = bl;

            //2.查询
            object obj2 = IBatisHepler.ExcuteSelectObj("GetRoleById", "3");
            Hashtable hs = obj2 as Hashtable;
            
            //3.删除
            int num = IBatisHepler.ExcuteDelete("DeleteRole", "4");
            ViewBag.Status = num > 0 ? true : false;


            //4.修改
            Hashtable ht_Up = new Hashtable();
            ht_Up["ID"] = 3;
            ht_Up["Uid"] = 3;
            ht_Up["RoleName"] = "操作员（修改）";
            //ht_add["Remark"] = "iBatis操作员（修改）";
            int num2 = IBatisHepler.ExcuteUpdate("UpdateRole", ht_Up);
            ViewBag.Status = num2 > 0 ? true : false;

            //5.查询所有
            DataTable dt= IBatisHepler.ExcuteSelect("GetAllRole","");

            return View();
        }