基於C#的.Net下整合IBatis_ZenDei技術網路在線

一. 準備工作 1. 點擊此下載支持.Net4.0的 iBatis.Net，工程中引用release文件夾下的dll 最新版(目前已不再更新)，有稍作修改使其支持.NET4.0 2. 點擊此可查看 iBatis.Net 的幫助文檔 3. 點擊此下載 iBatis in Action 的中文版電子書， ...

一. 準備工作

　　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>

View Code

　　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>

View Code　

　　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>

View Code

　　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>

View Code

　　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");
        }
    }
}

View Code

　　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);
            }
        }
    }
}

View Code　　

　　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>

View Code

　　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);

        }
    }
}

View Code

三. 測試

　　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();
}

View Code

　　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();
        }
    }
}

View Code

　　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;//保存當前用戶    
        }

    }
}

View Code