基于.Net下整合IBatis

一. 准备工作

　　1. 点击此下载支持.Net4.0的 iBatis.Net，工程中引用release文件夹下的dll

　　　　最新版(目前已不再更新)，有稍作修改使其支持.NET4.0

　　2. 点击此可查看 iBatis.Net 的帮助文档

　　3. 点击此下载 iBatis in Action 的中文版电子书，作为参考

　　4. 点击此可查看适用于.NET，基于 iBatis in Action 的中文翻译，作为参考

 

二. 相关代码文件介绍

　　1. dao.config　　dao支持配置

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

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

<context id="SqlMapDao">
  <properties embedded="psi.properties.config, psi"/>
    
  <database>
    <provider name="MySql"/>
    <dataSource name="psi" connectionString="${connectionString}" />
  </database>

  <daoSessionHandler id="SqlMap">
    <property name="resource" value="./config/ibatisNet/SqlMap.config"/>
  </daoSessionHandler>

  <daoFactory>
    <dao interface="psi.Persistence.MapperDao.Interfaces.IUserDao, psi"
         implementation="psi.Persistence.MapperDao.Implementations.UserDao, psi"/>
  </daoFactory>
</context>

</daoConfig>     

　　2. 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="MySql" 
    description="MySQL, MySQL provider 6.7.9.0" 
    enabled="true" 
    assemblyName="MySql.Data, Version=6.7.9.0, 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"    
  />

</providers>

　

　　3. 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>
    <setting useStatementNamespaces="true"/>
    <setting cacheModelsEnabled="true"/>
    <setting validateSqlMap="false"/>
  </settings>

  <sqlMaps>
    <sqlMap embedded="psi.Persistence.Maps.User.xml, psi"/>    
  </sqlMaps> 
</sqlMapConfig>

　　4. properties.config　　数据库连接等对安全性有要求的配置

<?xml version="1.0" encoding="utf-8" ?>
<settings>
  <!--   User application and configured property settings go here.-->
  <!--   Example: <add key="settingName" value="settingValue"/> -->
  <add
        key="connectionString"
        value="Host=localhost;UserName=root;Password=123456;Database=psi;Port=3306;" />
</settings>

　　5. DaoConfigManager.cs　　程序启动时调用 DaoConfigManager.Instance(); 进行初始化

using IBatisNet.DataAccess;
using IBatisNet.DataAccess.Configuration;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace psi.Persistence
{
    public class DaoConfigManager
    {
        private static IDaoManager daoManager = null;

        public static IDaoManager GetSqlMapDao()
        {
            return Instance();
        }

        public static IDaoManager Instance()
        {
            if (daoManager == null)
            {
                lock (typeof(DaoManager))
                {
                    if (daoManager == null) // double-check
                    {
                        InitDao();
                    }
                }
            }
            return daoManager;
        }

        protected static void InitDao()
        {
            DomDaoManagerBuilder builder = new DomDaoManagerBuilder();
            builder.Configure(".//config//ibatisNet//dao.config");
            daoManager = DaoManager.GetInstance("SqlMapDao");
        }
    }
}

　　6. BaseSqlMapDao.cs　　对SqlMap的Dao操作进行的封装

using IBatisNet.Common.Pagination;
using IBatisNet.DataAccess;
using IBatisNet.DataAccess.DaoSessionHandlers;
using IBatisNet.DataAccess.Exceptions;
using IBatisNet.DataAccess.Interfaces;
using IBatisNet.DataMapper;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace psi.Persistence.MapperDao
{
    /// <summary>
    /// Summary description for BaseSqlMapDao.
    /// </summary>
    public class BaseSqlMapDao : IDao
    {
        protected const int PAGE_SIZE = 4;
        protected SqlMapper sqlMapper = null;

        /// <summary>
        /// Looks up the parent DaoManager, gets the local transaction
        /// (which should be a SqlMapDaoTransaction) and returns the
        /// SqlMap associated with this DAO.
        /// </summary>
        /// <returns>The SqlMap instance for this DAO.</returns>
        protected SqlMapper GetLocalSqlMap()
        {
            if (sqlMapper == null)
            {
                DaoManager daoManager = (DaoManager)DaoManager.GetInstance(this);
                SqlMapDaoSession sqlMapDaoSession = (SqlMapDaoSession)daoManager.LocalDaoSession;
                sqlMapper = (SqlMapper)sqlMapDaoSession.SqlMap;
            }
            return sqlMapper;
        }


        /// <summary>
        /// Simple convenience method to wrap the SqlMap method of the same name.
        /// Wraps the exception with a DataAccessException to isolate the SqlMap framework.
        /// </summary>
        /// <param name="statementName"></param>
        /// <param name="parameterObject"></param>
        /// <returns></returns>
        protected IList<T> ExecuteQueryForList<T>(string statementName, object parameterObject)
        {
            SqlMapper sqlMap = GetLocalSqlMap();
            try
            {
                return sqlMap.QueryForList<T>(statementName, parameterObject);
            }
            catch (Exception e)
            {
                throw new DataAccessException("Error executing query '" + statementName + "' for list.  Cause: " + e.Message, e);
            }
        }

        /// <summary>
        /// Simple convenience method to wrap the SqlMap method of the same name.
        /// Wraps the exception with a DataAccessException to isolate the SqlMap framework.
        /// </summary>
        /// <param name="statementName"></param>
        /// <param name="parameterObject"></param>
        /// <param name="skipResults"></param>
        /// <param name="maxResults"></param>
        /// <returns></returns>
        protected IList ExecuteQueryForList(string statementName, object parameterObject, int skipResults, int maxResults)
        {
            SqlMapper sqlMap = GetLocalSqlMap();
            try
            {
                return sqlMap.QueryForList(statementName, parameterObject, skipResults, maxResults);
            }
            catch (Exception e)
            {
                throw new DataAccessException("Error executing query '" + statementName + "' for list.  Cause: " + e.Message, e);
            }
        }

        /// <summary>
        /// Simple convenience method to wrap the SqlMap method of the same name.
        /// Wraps the exception with a DataAccessException to isolate the SqlMap framework.
        /// </summary>
        /// <param name="statementName"></param>
        /// <param name="parameterObject"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        protected IPaginatedList ExecuteQueryForPaginatedList(string statementName, object parameterObject, int pageSize)
        {
            SqlMapper sqlMap = GetLocalSqlMap();
            try
            {
                return sqlMap.QueryForPaginatedList(statementName, parameterObject, pageSize);
            }
            catch (Exception e)
            {
                throw new DataAccessException("Error executing query '" + statementName + "' for paginated list.  Cause: " + e.Message, e);
            }
        }

        /// <summary>
        /// Simple convenience method to wrap the SqlMap method of the same name.
        /// Wraps the exception with a DataAccessException to isolate the SqlMap framework.
        /// </summary>
        /// <param name="statementName"></param>
        /// <param name="parameterObject"></param>
        /// <returns></returns>
        protected object ExecuteQueryForObject(string statementName, object parameterObject)
        {
            SqlMapper sqlMap = GetLocalSqlMap();

            try
            {
                return sqlMap.QueryForObject(statementName, parameterObject);
            }
            catch (Exception e)
            {
                throw new DataAccessException("Error executing query '" + statementName + "' for object.  Cause: " + e.Message, e);
            }
        }

        /// <summary>
        /// Simple convenience method to wrap the SqlMap method of the same name.
        /// Wraps the exception with a DataAccessException to isolate the SqlMap framework.
        /// </summary>
        /// <param name="statementName"></param>
        /// <param name="parameterObject"></param>
        /// <returns></returns>
        protected int ExecuteUpdate(string statementName, object parameterObject)
        {
            SqlMapper sqlMap = GetLocalSqlMap();

            try
            {
                return sqlMap.Update(statementName, parameterObject);
            }
            catch (Exception e)
            {
                throw new DataAccessException("Error executing query '" + statementName + "' for update.  Cause: " + e.Message, e);
            }
        }

        protected int ExecuteDelete(string statementName, object parameterObject)
        {
            SqlMapper sqlMap = GetLocalSqlMap();

            try
            {
                return sqlMap.Delete(statementName, parameterObject);
            }
            catch (Exception e)
            {
                throw new DataAccessException("Error executing query '" + statementName + "' for delete.  Cause: " + e.Message, e);
            }
        }

        /// <summary>
        /// Simple convenience method to wrap the SqlMap method of the same name.
        /// Wraps the exception with a DataAccessException to isolate the SqlMap framework.
        /// </summary>
        /// <param name="statementName"></param>
        /// <param name="parameterObject"></param>
        /// <returns></returns>
        protected object ExecuteInsert(string statementName, object parameterObject)
        {
            SqlMapper sqlMap = GetLocalSqlMap();

            try
            {
                return sqlMap.Insert(statementName, parameterObject);
            }
            catch (Exception e)
            {
                throw new DataAccessException("Error executing query '" + statementName + "' for insert.  Cause: " + e.Message, e);
            }
        }
    }
}

　　

　　7. User.xml　　Sql语句存放位置

<?xml version="1.0" encoding="UTF-8" ?>
<sqlMap namespace="User"
xmlns="http://ibatis.apache.org/mapping"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" >

  <resultMaps>
    <resultMap id="user" class="psi.Domain.User">
        <result property="guid" column="guid"/>
        <result property="userCode" column="userCode"/>
        <result property="userName" column="userName"/>
        <result property="password" column="password"/>
        <result property="isLocked" column="isLocked"/>
        <result property="flagOnline" column="flagOnline"/>
        <result property="lastLoginTime" column="lastLoginTime"/>
        <result property="loginCounter" column="loginCounter"/>
        <result property="createDate" column="createDate"/>
        <result property="createBy" column="createBy"/>
        <result property="updateDate" column="updateDate"/>
        <result property="updateBy" column="updateBy"/>
    </resultMap>
  
  </resultMaps>
  
  <statements>
    <select id = "getForLogin" parameterClass = "psi.Domain.User" resultMap="user">
        select * from tb_user WHERE userCode=#userCode# AND password=#password#
    </select>

    <update id="updateForLogin" parameterClass = "psi.Domain.User">
        update tb_user SET flagOnline='Y',lastLoginTime=NOW(),loginCounter=IFNULL(loginCounter,0)+1  
            WHERE userCode=#userCode# AND password=#password#
    </update>
  </statements>

</sqlMap>

　　8. UserDao　　数据访问接口

using IBatisNet.DataMapper;
using psi.Common;
using psi.Domain;
using psi.Persistence.MapperDao.Interfaces;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

namespace psi.Persistence.MapperDao.Implementations
{
    public class UserDao : BaseSqlMapDao, IUserDao
    {
        public void Login(User user)
        {
            if (string.IsNullOrWhiteSpace(user.userCode))
            {
                throw new Exception("帐户不能为空！");
            }

            IList<User> list = ExecuteQueryForList<User>("User.getForLogin", user);
            if (list == null || list.Count == 0)
            {
                throw new Exception("用户名或密码错误！");
            }
            user = list[0];
            if (user.isLocked == 1)
            {
                throw new Exception("用户已经锁定，禁止该用户登录！");
            }
            ExecuteUpdate("User.updateForLogin", user);
            LoginLog log = new LoginLog();
            log.userCode = user.userCode;
            log.loginType = 'I';
            ExecuteInsert("LoginLog.insertLog", log);

        }
    }
}

 

三. 测试

　　1. 前端控制器代码

User user = new User();
user.userCode = userCode;
user.password = password;
try
{
    _bllUser.BeginTransaction();
    _bllUser.Login(user);
    _bllUser.CommitTransaction();
}
catch
{
    _bllUser.RollBackTransaction();
    throw new Exception();
}

　　2. bllBase　　业务逻辑处理基类

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using psi.Common;
using IBatisNet.DataAccess;
using psi.Persistence;

/************************************************************************* 
 * 程序说明: 业务逻辑层基类
 **************************************************************************/
namespace psi.Business
{
    /// <summary>
    /// 业务逻辑层基类
    /// </summary>
    public abstract class bllBase
    {
        private IDaoManager daoManager;
        protected IDaoManager DaoManager { get { return this.daoManager; } }

        public bllBase(){
            daoManager = DaoConfigManager.GetSqlMapDao();
        }

        public void BeginTransaction()
        {
            if (!daoManager.IsDaoSessionStarted())
                daoManager.BeginTransaction();
        }

        public void CommitTransaction()
        {
            daoManager.CommitTransaction();
        }

        public void RollBackTransaction()
        {
            daoManager.RollBackTransaction();
        }
    }
}

　　3. bllUser　　业务逻辑处理代码

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using psi.Common;
using System.Data;
using psi.Persistence.MapperDao.Interfaces;
using psi.Persistence;
using IBatisNet.DataAccess;
using psi.Persistence.MapperDao.Implementations;
using psi.Domain;

/************************************************************************* 
 * 程序说明: 用户管理的业务逻辑层
 **************************************************************************/
namespace psi.Business
{
    /// <summary>
    /// 用户管理的业务逻辑层
    /// </summary>
    public class bllUser : bllBase
    {
        private IUserDao userDao;

        public bllUser()
            : base()
        {
            userDao = DaoManager[typeof(IUserDao)] as IUserDao;
        }

        public static void ValidateLogin(string userID, string password)
        {
            if (userID.Trim() == "")
                throw new Exception("用户编号不正确或不能为空!");

            if (password.Trim() == "")
                throw new Exception("密码不正确或不能为空!");
        }

        public void Login(User user)
        {           
            userDao.Login(user);                
            
            //用户实例,登录成功
            Loginer loginer = new Loginer();
            loginer.UserCode = user.userCode;
            loginer.UserName = user.userName;
            loginer.LoginTime = DateTime.Now;

            Loginer.CurrentUser = loginer;//保存当前用户    
        }

    }
}