IBatis 简易框架搭建

1、练习框架

 ibatis主要dll介绍

IBatisNet.Common.dll	由DataAccess和DataMapper组成的共享程序集
IBatisNet.Common.Logging.Log4Net.dll	Log4Net集成记录器，和Log4Net配合使用
IBatisNet.DataMapper.dll	DataMapper主要框架
IBatisNet.DataAccess.dll	DataAccess框架

2、IBatisDao 

1、DaoBase.cs Ibatis接口的封装

namespace IBatisDao
{
    public class DaoBase<T> : MarshalByRefObject where T : EntityBase
    {
        private static ISqlMapper SqlMap;
        /// <summary>
        /// 构造函数
        /// </summary>         
        /// 
        static DaoBase()
        {
            if (SqlMap == null)
            {
                SqlMap = SqlMapperCreater.SqlMap;
            }
        }

        /// <summary>
        ///  开始事务
        /// </summary>
        protected void BeginTransaction()
        {
            try
            {
                SqlMap.BeginTransaction();
            }
            catch
            {
                SqlMap.RollBackTransaction();
                SqlMap.BeginTransaction();
            }
        }

        /// <summary>
        /// 提交事务
        /// </summary> 
        protected void CommitTransaction()
        {
            SqlMap.CommitTransaction();
        }

        /// <summary>
        /// 回滚事务
        /// </summary>
        protected void RollBackTransaction()
        {
            SqlMap.RollBackTransaction();
        }

        /// <summary>
        /// 批量保存多个实体.
        /// </summary>
        /// <param name="list">实体列表</param>
        /// <param name="insertCmdId">insert语句的id</param>
        /// <param name="updateCmdId">update语句的id</param>
        /// <param name="deleteCmdId">delete语句的id</param>
        protected void Save(IList<T> list, string insertCmdId, string updateCmdId, string deleteCmdId)
        {
            //删除
            foreach (T t in list)
            {
                if (t.EntityState == EntityStateEnum.Deleted && !string.IsNullOrEmpty(deleteCmdId))
                {
                    this.Delete(deleteCmdId, t);
                }
            }
            //更新
            foreach (T t in list)
            {
                if (t.EntityState == EntityStateEnum.Modified && !string.IsNullOrEmpty(updateCmdId))
                {
                    this.Update(updateCmdId, t);
                }
            }
            //新增
            foreach (T t in list)
            {
                if (t.EntityState == EntityStateEnum.Added && !string.IsNullOrEmpty(insertCmdId))
                {
                    this.Insert(insertCmdId, t);
                }
            }
        }
        /// <summary>
        /// 保单个实体
        /// </summary>
        /// <param name="list">实体列表</param>
        /// <param name="insertCmdId">insert语句的id</param>
        /// <param name="updateCmdId">update语句的id</param>
        /// <param name="deleteCmdId">delete语句的id</param>
        protected void Save(T obj, string insertCmdId, string updateCmdId, string deleteCmdId)
        {
            //删除
            if (obj.EntityState == EntityStateEnum.Deleted && !string.IsNullOrEmpty(deleteCmdId))
            {
                this.Delete(deleteCmdId, obj);
            }
            //更新
            if (obj.EntityState == EntityStateEnum.Modified && !string.IsNullOrEmpty(updateCmdId))
            {
                this.Update(updateCmdId, obj);
            }
            //新增
            if (obj.EntityState == EntityStateEnum.Added && !string.IsNullOrEmpty(insertCmdId))
            {
                this.Insert(insertCmdId, obj);
            }
        }


        /// <summary>
        /// 通用执行Select语句
        /// </summary>
        /// <param name="tag">语句ID</param>
        /// <param name="paramObject">语句所需要的参数</param>
        /// <returns>结果集合</returns>
        protected IList<T> Select(string tag, object paramObject)
        {
            return SqlMap.QueryForList<T>(tag, paramObject);
        }

        /// <summary>
        /// 通用执行skip Select语句
        /// </summary>
        /// <param name="tag">语句ID</param>
        /// <param name="paramObject">语句所需要的参数</param>
        /// <param name="skipResults">忽略个数</param>
        /// <param name="maxResults">最大个数</param>
        /// <returns>结果集合</returns>
        protected IList<T> Select(string tag, object paramObject, int skipResults, int maxResults)
        {
            return SqlMap.QueryForList<T>(tag, paramObject, skipResults, maxResults);
        }

        /// <summary>
        /// 通用执行Select语句
        /// </summary>
        /// <param name="tag">语句ID</param>
        /// <param name="paramObject">语句所需要的参数</param>
        /// <returns>单个结果</returns>
        protected T SelectOne(string tag, object paramObject)
        {
            return SqlMap.QueryForObject<T>(tag, paramObject);
        }

        /// <summary>
        /// 通用执行Update语句(强制检查数据并发)
        /// </summary>
        /// <param name="tag">语句ID</param>
        /// <param name="paramObject">语句所需要的参数</param>
        /// <returns>更新的行数</returns>
        protected int Update(string tag, T paramObject)
        {
            return this.Update(tag, paramObject, true);
        }

        /// <summary>
        /// 通用执行Update语句
        /// </summary>
        /// <param name="tag">语句ID</param>
        /// <param name="paramObject">语句所需要的参数</param>
        /// <returns>更新的行数</returns>
        protected int Update(string tag, object paramObject)
        {
            int iReturn = SqlMap.Update(tag, paramObject);

            // 若更新出现并发且要检查并发，则抛出对应的异常
            if (iReturn <= 0)
            {
                throw new Exception("数据已被修改，请重新加载.");
            }
            // 改变状态为Unchanged
            return iReturn;
        }

        /// <summary>
        /// 通用执行Update语句
        /// </summary>
        /// <param name="tag">语句ID</param>
        /// <param name="paramObject">语句所需要的参数</param>
        /// <param name="checkConcurrency">是否要检查数据并发</param>
        /// <returns>更新的行数</returns>
        protected int Update(string tag, T paramObject, bool checkConcurrency)
        {
            int iReturn = SqlMap.Update(tag, paramObject);

            // 若更新出现并发且要检查并发，则抛出对应的异常
            if (iReturn <= 0 && checkConcurrency)
            {
                throw new Exception("数据已被修改，请重新加载.");
            }
            // 改变状态为Unchanged
            paramObject.EntityState = EntityStateEnum.Unchanged;
            return iReturn;
        }
        /// <summary>
        /// 通用执行Update语句
        /// </summary>
        /// <param name="tag">语句ID</param>
        /// <param name="paramObject">语句所需要的参数</param>
        /// <param name="checkConcurrency">是否要检查数据并发</param>
        /// <returns>更新的行数</returns>
        protected int Update(string tag, object paramObject, bool checkConcurrency)
        {
            int iReturn = SqlMap.Update(tag, paramObject);

            // 若更新出现并发且要检查并发，则抛出对应的异常
            if (iReturn <= 0 && checkConcurrency)
            {
                throw new Exception("数据已被修改，请重新加载.");
            }
            return iReturn;
        }


        /// <summary>
        /// 通用执行Deelte语句
        /// </summary>
        /// <param name="tag">语句ID</param>
        /// <param name="paramObject">语句所需要的参数</param>
        /// <returns>删除的行数</returns>
        protected int Delete(string tag, T paramObject)
        {
            return SqlMap.Delete(tag, paramObject);
        }

        /// <summary>
        /// 通用执行Insert语句
        /// </summary>
        /// <param name="tag">语句ID</param>
        /// <param name="paramObject">语句所需要的参数</param>
        /// <returns>插入行的PK对象</returns>
        protected object Insert(string tag, T paramObject)
        {
            object result = SqlMap.Insert(tag, paramObject);
            paramObject.EntityState = EntityStateEnum.Unchanged;
            return result;
        }

        /// <summary>
        /// 通用执行Insert语句
        /// </summary>
        /// <param name="tag">语句ID</param>
        /// <param name="paramObject">语句所需要的参数</param>
        /// <returns>插入行的PK对象</returns>
        protected object Insert(string tag, object paramObject)
        {
            object result = SqlMap.Insert(tag, paramObject);

            return result;
        }

        #region GetSql/GetDataTable

        /// <summary>
        /// 通用得到参数化后的SQL(xml文件中参数要使用$标记的占位参数)
        /// </summary>
        /// <param name="tag">语句ID</param>
        /// <param name="paramObject">语句所需要的参数</param>
        /// <returns>获得的SQL</returns>
        protected string GetSql(string tag, object paramObject)
        {
            string sql = GetPreparedSql(tag, paramObject);
            //ServiceObject.Log.Logs.DebugLog(tag + ": " + sql);

            return sql;
        }

        /// <summary>
        /// 返回结果集中的第一行的第一列
        /// </summary>
        /// <param name="tag">语句ID</param>
        /// <param name="paramObject">语句所需要的参数</param>
        /// <returns>结果集中的第一行的第一列</returns>
        protected object QueryScalar(string tag, object paramObject)
        {
            bool isSessionLocal = false;
            object result;

            if (SqlMap.LocalSession == null)
            {
                isSessionLocal = true;
                SqlMap.OpenConnection();
            }

            //记录SQL语句
            GetSql(tag, paramObject);

            try
            {
                IDbCommand cmd = GetDbCommand(tag, paramObject);
                result = cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                throw new Exception("Can't QueryScalar, tag = " + tag, ex);
            }
            finally
            {
                if (isSessionLocal)
                {
                    SqlMap.CloseConnection();
                }
            }

            return result;
        }

        /// <summary>
        /// 通用的以DataSet的方式得到Select的结果(xml文件中参数要使用$标记的占位参数)
        /// </summary>
        /// <param name="tag">语句ID</param>
        /// <param name="paramObject">语句所需要的参数</param>
        /// <returns>得到的DataSet</returns>
        protected DataSet GetDataSet(string tag, object paramObject)
        {
            bool isSessionLocal = false;
            DataSet result = new DataSet(); ;

            if (SqlMap.LocalSession == null)
            {
                isSessionLocal = true;
                SqlMap.OpenConnection();
            }
            try
            {
                IDbCommand cmd = GetDbCommand(tag, paramObject);
                IDbDataAdapter adapter = SqlMap.LocalSession.CreateDataAdapter(cmd);
                adapter.Fill(result);
            }
            catch (Exception ex)
            {
                throw new Exception("Can't GetDataSet, tag = " + tag, ex);
            }
            finally
            {
                if (isSessionLocal)
                {
                    SqlMap.CloseConnection();
                }
            }

            return result;
        }

        /// <summary>
        /// 通用的以DataTable的方式得到Select的结果(xml文件中参数要使用$标记的占位参数)
        /// </summary>
        /// <param name="tag">语句ID</param>
        /// <param name="paramObject">语句所需要的参数</param>
        /// <returns>得到的DataTable</returns>
        protected DataTable GetDataTable(string tag, object paramObject)
        {
            return GetDataSet(tag, paramObject).Tables[0];
        }

        /// <summary>
        /// 通用的以DataTable的方式得到skip Select的结果(xml文件中参数要使用$标记的占位参数)
        /// </summary>
        /// <param name="tag">语句ID</param>
        /// <param name="paramObject">语句所需要的参数</param>
        /// <param name="skipResults">忽略个数</param>
        /// <param name="maxResults">最大个数</param>
        /// <returns>得到的DataTable</returns>
        protected DataTable GetDataTable(string tag, object paramObject, int skipResults, int maxResults)
        {
            bool isSessionLocal = false;
            DataSet result = new DataSet();

            if (SqlMap.LocalSession == null)
            {
                isSessionLocal = true;
                SqlMap.OpenConnection();
            }

            //记录SQL语句
            GetSql(tag, paramObject);

            try
            {
                IDbCommand cmd = GetDbCommand(tag, paramObject);
                DbDataAdapter adapter = SqlMap.LocalSession.CreateDataAdapter(cmd) as DbDataAdapter;
                if (adapter == null)
                {
                    throw new NotSupportedException("Not support skip GetDataTable");
                }
                adapter.Fill(result, skipResults, maxResults, "result");
            }
            catch (Exception ex)
            {
                throw new Exception("Can't GetDataTable, tag = " + tag, ex);
            }
            finally
            {
                if (isSessionLocal)
                {
                    SqlMap.CloseConnection();
                }
            }

            return result.Tables["result"];
        }

        private IDbCommand GetDbCommand(string statementName, object paramObject)
        {
            IStatement statement = SqlMap.GetMappedStatement(statementName).Statement;

            IMappedStatement mapStatement = SqlMap.GetMappedStatement(statementName);

            ISqlMapSession session = new SqlMapSession(SqlMap);

            if (SqlMap.LocalSession != null)
            {
                session = SqlMap.LocalSession;
            }
            else
            {
                session = SqlMap.OpenConnection();
            }

            RequestScope request = statement.Sql.GetRequestScope(mapStatement, paramObject, session);
            mapStatement.PreparedCommand.Create(request, session, statement, paramObject);

            IDbCommand command = request.IDbCommand;

            // Ibatis 这里做了个装饰,所以得到的类型不是SqlCommand之类的类型
            // 只能暂时使用反射把它装饰的类型(即真实类型)反射出来
            Type t = command.GetType();
            FieldInfo commandField = t.GetField("_innerDbCommand", BindingFlags.Instance | BindingFlags.NonPublic);
            IDbCommand innerDbCommand = commandField.GetValue(command) as IDbCommand;

            return innerDbCommand; // request.IDbCommand;
        }

        /// <summary>
        /// 获取查询语句
        /// </summary>
        /// <param name="statementName"></param>
        /// <param name="parameterObject"></param>
        /// <returns></returns>
        protected string GetPreparedSql(string statementName, object parameterObject)
        {
            IMappedStatement mappedStatement = SqlMap.GetMappedStatement(statementName);
            ISqlMapSession localSession = SqlMap.LocalSession;
            IStatement statement = mappedStatement.Statement;
            if (localSession == null)
            {
                localSession = new SqlMapSession(SqlMap);
            }
            return statement.Sql.GetRequestScope(mappedStatement, parameterObject, localSession).PreparedStatement.PreparedSql;
        }

        protected IList<TableEntity> QueryWithRowDelegate(string tag, object paramObject, Action<object, object, IList<TableEntity>> action)
        {
            return SqlMap.QueryWithRowDelegate<TableEntity>(tag, paramObject, new RowDelegate<TableEntity>(action));
        }
        #endregion
    }
}

 

2、SqlMapperCreater.cs 初始化Ibatis操作类

    public class SqlMapperCreater
    {
        protected static ISqlMapper m_sqlMap = null;

        public static ISqlMapper SqlMap
        {
            get
            {
                if (m_sqlMap == null)
                {
                    string fileName = "Config\\sqlMap.config";
                    DomSqlMapBuilder builder = new DomSqlMapBuilder();
                    m_sqlMap = builder.Configure(fileName);
                }
                return m_sqlMap;
            }
        }
    }

 

3、IBatisDataAccess 

1、TableAccess.cs 数据库访问层

namespace IBatisDataAccess
{
    public class TableAccess : DaoBase<TableEntity>
    {
        public object Insert(TableEntity obj)
        {
            return base.Insert("TableEntity_Insert", obj);
        }

        public IList<TableEntity> Select(TableEntity obj)
        {
            return base.Select("TableEntity_Select", obj);
        }

        public IList<TableEntity> SelectWithRowDelegate(TableEntity obj)
        {
            return base.QueryWithRowDelegate("TableEntity_Select", obj, new Action<object, object, IList<TableEntity>>(RowDelegate));
        }

        public DataTable GetDataTable(TableEntity obj)
        {
            return base.GetDataTable("TableEntity_Select_Datatable", obj);
        }

        public void RowDelegate(object obj, object parameterObject, IList<TableEntity> list)
        {
            TableEntity tb = obj as TableEntity;
            tb.COL_1 = 77;
            list.Add(tb);
        }
    }
}

 

4、IBatisEntity

1、EntityBase.cs 实体基类

namespace IBatisEntity
{
    /// <summary>
    /// 实体基类
    /// </summary>
    [Serializable]
    public class EntityBase
    {
        // Fields
        protected EntityBase OldValue;

        protected EntityStateEnum _entityState = EntityStateEnum.Unchanged;
        /// <summary>
        /// 实体的数据版本,默认为未改变
        /// </summary>
        public EntityStateEnum EntityState
        {
            get { return _entityState; }
            set { _entityState = value; }
        }

        /// <summary>
        /// 默认构造函数
        /// </summary>
        protected EntityBase()
        {

        }

        /// <summary>
        /// 实现克隆接口
        /// </summary>
        /// <returns></returns>
        public virtual object Clone()
        {
            MemoryStream serializationStream = new MemoryStream();
            BinaryFormatter formatter = new BinaryFormatter();
            formatter.Serialize(serializationStream, this);
            serializationStream.Position = 0;
            return formatter.Deserialize(serializationStream);
        }

        /// <summary>
        /// 在实体与数据表做相互转换时,制定表的列名称模式
        /// </summary>
        public enum ColumnNameEnum
        {
            /// <summary>
            /// 数据库对应列名称
            /// </summary>
            DBName = 1,
            /// <summary>
            /// 实体对应字段名称
            /// </summary>
            PropertyName = 2
        }
    }

    /// <summary>
    /// 实体版本枚举
    /// </summary>
    public enum EntityStateEnum
    {
        /// <summary>
        /// 新增
        /// </summary>
        Added = 1,
        /// <summary>
        /// 被修改
        /// </summary>
        Modified = 2,
        /// <summary>
        /// 被删除
        /// </summary>
        Deleted = 3,
        /// <summary>
        /// 未修改
        /// </summary>
        Unchanged = 4,
        /// <summary>
        /// 新增的但是未加入任何table中
        /// </summary>
        Detached = 5
    }
}

 

2、TableEntity.cs 实体类

namespace IBatisEntity
{
    [Serializable]
    public class TableEntity : EntityBase
    {
        public long ID { get; set; }
        public int COL_1 { get; set; }
        public int COL_2 { get; set; }
        public int COL_3 { get; set; }
        public DateTime REFRESH_DATE { get; set; }
    }
}

 

5、IBatisApp界面层访问

1、Form1.cs

namespace IBatisApp
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            TableAccess tableAccess = new TableAccess();
            object a = tableAccess.Insert(new TableEntity() { COL_1 = -1, COL_2 = -2, COL_3 = -3, REFRESH_DATE = DateTime.Now });
            MessageBox.Show(a.ToString());
        }

        private void button2_Click(object sender, EventArgs e)
        {
            TableAccess tableAccess = new TableAccess();
            IList<TableEntity> a = tableAccess.Select(new TableEntity() { ID = 66 });

            a = tableAccess.SelectWithRowDelegate(new TableEntity() { ID = 66 });
            MessageBox.Show(a.Count.ToString());

            DataTable dt = tableAccess.GetDataTable(new TableEntity() { ID = 66 });
        }
    }
}

 

 

6、xml文件配置

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"
      default="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="@"
      allowMARS="false"
  />
<provider
    name="sqlServer2.0"
    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="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="oracleClient2.0" 
        description="Oracle, Microsoft provider V2.0.50727.42" 
        enabled="true" 
        assemblyName="System.Data.OracleClient, Version=2.0.50727.42, 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=":"/>
  <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>

 

2、sqlMap.config sqlmap的基本信息

<?xml version="1.0" encoding="utf-8"?>
<sqlMapConfig xmlns="http://ibatis.apache.org/dataMapper"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" >

  <!-- Rem : If used via a DataAccess context, properties tag will be ignored -->
  <!--数据库配置参数 属性-->
  <properties resource="Config//DataBase.config"/>

  <settings>
    <!--setting useStatementNamespaces="false"/-->
    <setting cacheModelsEnabled="true"/>
    <setting validateSqlMap="false"/>
  </settings>

  <providers resource="config//providers.config"/>

  <database>
    <provider name="oracleClient2.0"/>
    <dataSource name="${DatabaseName}" connectionString="Data Source=${DataSource}; User ID=${UserID}; Password=${Password}"/>
  </database>

  <alias>
    <typeAlias alias="ArrayList" type="System.Collections.ArrayList,mscorlib"/>
  </alias>
  <sqlMaps>
    <!--===== sqlMap文件=======-->    
    <sqlMap resource="config//TableEntity.xml"/>
  </sqlMaps>
</sqlMapConfig>

 

3、DataBase.config 数据库链接字符串

<?xml version="1.0" encoding="utf-8" ?>
<settings>

    <!--  定义4个属性，在sqlMap.config中调用 -->
    <add key="DatabaseName" value="DatabaseName"/>
    <add key="DataSource" value="DataSource"/>
    <add key="UserID" value="UserID"/>
    <add key="Password" value="Password"/>
    
</settings>

 

4、TableEntity.xml 具体操作的xml

<?xml version="1.0" encoding="UTF-8" ?>
<sqlMap namespace="TableEntity"   xmlns="http://ibatis.apache.org/mapping" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <alias>
    <typeAlias alias="TableEntity"  type="IBatisEntity.TableEntity" />
    <!--<typeAlias alias="TableEntity.Key" type="long" />-->
  </alias>
  <resultMaps>
    <resultMap id="TableEntity_AllColumns" class="TableEntity">
      <result property="ID"  column="ID" />
      <result property="COL_1"  column="COL_1" />
      <result property="COL_2"  column="COL_2" />
      <result property="COL_3"  column="COL_3" />
      <result property="REFRESH_DATE"  column="REFRESH_DATE" />
    </resultMap>
  </resultMaps>
  <parameterMaps>
    <parameterMap id="TableEntityParam" class="HashTable">
      <parameter property="ID"  column="ID" direction="Input" />
      <parameter property="COL_1"  column="COL_1" direction="Input" />
      <parameter property="COL_2"  column="COL_2" direction="Input" />
      <parameter property="COL_3"  column="COL_3" direction="Input" />
      <parameter property="REFRESH_DATE"  column="REFRESH_DATE" direction="Input" />
    </parameterMap>
  </parameterMaps>
  <statements>
    <insert id="TableEntity_Insert"  parameterClass="TableEntity" resultClass="int">
      <!--     按照规范序列应该为[TABLE]_S -->
      <selectKey property="ID" type="pre" resultClass="long">
        select pto_kit_s.nextval as value from dual
      </selectKey>
      INSERT INTO A_TEST_1
      (ID, COL_1, COL_2, COL_3)
      VALUES
      (#ID#, #COL_1#, #COL_2#, #COL_3#)
    </insert>

    <select id="TableEntity_Select" parameterClass="TableEntity" resultMap="TableEntity_AllColumns">
      select id,
      col_1,
      col_2,
      col_3,
      refresh_date
      from scp.A_TEST_1 t where id=#ID#
    </select>

    <select id="TableEntity_Select_Datatable" parameterClass="TableEntity">
      select id,
      col_1,
      col_2,
      col_3,
      refresh_date
      from A_TEST_1 t where id=#ID#
    </select>
  </statements>
</sqlMap>

<?xml version="1.0" encoding="UTF-8" ?>
<sqlMap namespace="TableEntity"   xmlns="http://ibatis.apache.org/mapping" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <alias>
    <typeAlias alias="TableEntity"  type="IBatisEntity.TableEntity" />
    <!--<typeAlias alias="TableEntity.Key" type="long" />-->
  </alias>
  <resultMaps>
    <resultMap id="TableEntity_AllColumns" class="TableEntity">
      <result property="ID"  column="ID" />
      <result property="COL_1"  column="COL_1" />
      <result property="COL_2"  column="COL_2" />
      <result property="COL_3"  column="COL_3" />
      <result property="REFRESH_DATE"  column="REFRESH_DATE" />
    </resultMap>
  </resultMaps>
  <parameterMaps>
    <parameterMap id="TableEntityParam" class="HashTable">
      <parameter property="ID"  column="ID" direction="Input" />
      <parameter property="COL_1"  column="COL_1" direction="Input" />
      <parameter property="COL_2"  column="COL_2" direction="Input" />
      <parameter property="COL_3"  column="COL_3" direction="Input" />
      <parameter property="REFRESH_DATE"  column="REFRESH_DATE" direction="Input" />
    </parameterMap>
  </parameterMaps>
  <statements>
    <insert id="TableEntity_Insert"  parameterClass="TableEntity" resultClass="int">
      <!--     按照规范序列应该为[TABLE]_S -->
      <selectKey property="ID" type="pre" resultClass="long">
        select pto_kit_s.nextval as value from dual
      </selectKey>
      INSERT INTO A_TEST_1
      (ID, COL_1, COL_2, COL_3)
      VALUES
      (#ID#, #COL_1#, #COL_2#, #COL_3#)
    </insert>

    <select id="TableEntity_Select" parameterClass="TableEntity" resultMap="TableEntity_AllColumns">
      select id,
      col_1,
      col_2,
      col_3,
      refresh_date
      from scp.A_TEST_1 t where id=#ID#
    </select>

    <select id="TableEntity_Select_Datatable" parameterClass="TableEntity">
      select id,
      col_1,
      col_2,
      col_3,
      refresh_date
      from A_TEST_1 t where id=#ID#
    </select>
  </statements>
</sqlMap>