IBatis的使用

2020年01月16日 阅读数:38
这篇文章主要向大家介绍IBatis的使用,主要内容包括基础应用、实用技巧、原理机制等方面,希望对大家有所帮助。

1、前言:html

    本文档主要介绍了使用Ibatis的数据访问和Log4Net的日志管理在开发项目中一些经常使用的操做,关于dll等的引入等暂不做介绍了sql

参考资料:    Doc-DataMapper- 帮助文档数据库

              网上的部分资料apache

2、实战:缓存

    咱们以一个例子来介绍和说明如何在项目中使用Ibatis中的数据访问app

    A.数据访问ide

    咱们以SQL2000中Northwind数据库中的表Employees为例来介绍如何进行数据访问工具

    1. 用CodeSmith或其余工具生成表Employees对应的类(这里咱们取名为Employee)post

using  System;

using  System.Data;

using  System.Configuration;

using  System.Web;

using  System.Web.Security;

using  System.Web.UI;

using  System.Web.UI.WebControls;

using  System.Web.UI.WebControls.WebParts;

using  System.Web.UI.HtmlControls;

namespace  UsingIBatisNet

{

    
#region  Employee

    
///   <summary>

    
///  数据库[Northwind] 中表[dbo.Employees] 的实体类.

    
///   </summary>

    
///  建立人: Steven Pei

    
///  建立日期:

    
///  修改人:

    
///  修改日期:

    
///  修改内容:

    
///  版   本:

    
public   class  Employee

    {

        
private   int  _EmployeeID;

        
private   string  _Lastname  =  String.Empty;

        
private   string  _Firstname  =  String.Empty;

        
private   string  _Title  =  String.Empty;

        
private   string  _Titleofcourtesy  =  String.Empty;

        
private  DateTime _Birthdate;

        
private  DateTime _Hiredate;

        
private   string  _Address  =  String.Empty;

        
private   string  _City  =  String.Empty;

        
private   string  _Region  =  String.Empty;

        
private   string  _Postalcode  =  String.Empty;

        
private   string  _Country  =  String.Empty;

        
private   string  _Homephone  =  String.Empty;

        
private   string  _Extension  =  String.Empty;

        
private   byte [] _Photo;

        
private   string  _Notes  =  String.Empty;

        
private   int  _Reportsto;

        
private   string  _Photopath  =  String.Empty;

        
//  Instantiate empty Employees for inserting

        
public  Employee() { }

        
//  Retrieve Employees with Id for updating

        
public  Employee( int  EmployeeID)

        {

            
this ._EmployeeID  =  EmployeeID;

        }

        
#region  Public Properties

        
public   int  EmployeeID

        {

            
get  {  return  _EmployeeID; }

            
set  { _EmployeeID  =  value; }

        }

        
public   string  LastName

        {

            
get  {  return  _Lastname; }

            
set  { _Lastname  =  value; }

        }

        
public   string  FirstName

        {

            
get  {  return  _Firstname; }

            
set  { _Firstname  =  value; }

        }

        
public   string  Title

        {

            
get  {  return  _Title; }

            
set  { _Title  =  value; }

        }

        
public   string  TitleOfCourtesy

        {

            
get  {  return  _Titleofcourtesy; }

            
set  { _Titleofcourtesy  =  value; }

        }

        
public  DateTime BirthDate

        {

            
get  {  return  _Birthdate; }

            
set  { _Birthdate  =  value; }

        }

        
public  DateTime HireDate

        {

            
get  {  return  _Hiredate; }

            
set  { _Hiredate  =  value; }

        }

        
public   string  Address

        {

            
get  {  return  _Address; }

            
set  { _Address  =  value; }

        }

        
public   string  City

        {

            
get  {  return  _City; }

            
set  { _City  =  value; }

        }

        
public   string  Region

        {

            
get  {  return  _Region; }

            
set  { _Region  =  value; }

        }

        
public   string  PostalCode

        {

            
get  {  return  _Postalcode; }

            
set  { _Postalcode  =  value; }

        }

        
public   string  Country

        {

            
get  {  return  _Country; }

            
set  { _Country  =  value; }

        }

        
public   string  HomePhone

        {

            
get  {  return  _Homephone; }

            
set  { _Homephone  =  value; }

        }

        
public   string  Extension

        {

            
get  {  return  _Extension; }

            
set  { _Extension  =  value; }

        }

        
public   byte [] Photo

        {

            
get  {  return  _Photo; }

            
set  { _Photo  =  value; }

        }

        
public   string  Notes

        {

            
get  {  return  _Notes; }

            
set  { _Notes  =  value; }

        }

        
public   int  ReportsTo

        {

            
get  {  return  _Reportsto; }

            
set  { _Reportsto  =  value; }

        }

        
public   string  PhotoPath

        {

            
get  {  return  _Photopath; }

            
set  { _Photopath  =  value; }

        }

        
#endregion

    }

    
#endregion     

}

 

    2.创建数据库和Employee类之间的映射关系(这里的映射文件名用Employee.xml)ui

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

< sqlMap  namespace = " ks_zthb "  xmlns = " http://ibatis.apache.org/mapping "  xmlns:xsi = " http://www.w3.org/2001/XMLSchema-instance " >

    
< alias >

        
< typeAlias alias = " Employee "  type = " UsingIBatisNet.Employee, UsingIBatisNet "   />

    
</ alias >

    
< resultMaps >

        
< resultMap  class = " Employee "  id = " Employees_Result " >

            
<! —数据库中表与类中元素的一一对应关系 -->

            
< result column = " EmployeeID "  property = " EmployeeID "   />

            
< result column = " LastName "  property = " LastName "   />

            
< result column = " FirstName "  property = " FirstName "   />

            
< result column = " Title "  property = " Title "   />

            
< result column = " TitleOfCourtesy "  property = " TitleOfCourtesy "   />

            
< result column = " BirthDate "  property = " BirthDate "   />

            
< result column = " HireDate "  property = " HireDate "   />

            
< result column = " Address "  property = " Address "   />

            
< result column = " City "  property = " City "   />

            
< result column = " Region "  property = " Region "   />

            
< result column = " PostalCode "  property = " PostalCode "   />

            
< result column = " Country "  property = " Country "   />

            
< result column = " HomePhone "  property = " HomePhone "   />

            
< result column = " Extension "  property = " Extension "   />

            
< result column = " Photo "  property = " Photo "   />

            
< result column = " Notes "  property = " Notes "   />

            
< result column = " ReportsTo "  property = " ReportsTo "   />

            
< result column = " PhotoPath "  property = " PhotoPath "   />

        
</ resultMap >

</ resultMaps >

    <statements>

       <!— Select操做的部分属性说明

          id :该存储过程的惟一标识符

          parameterClass : 传入的参数的类型(能够是string,类等)

          listClass :查询后结果集的类型

          resultMap:查询后结果集的输出形式(此处为resultMap中id为Employees_Result的输出形式) -->

        <select id="Employees_Select" parameterClass="string" listClass="ArrayList"                                                      resultMap="Employees_Result" >
                       SELECT[EmployeeID],[LastName],[FirstName],[Title],[TitleOfCourtesy],[BirthDate],[HireDate],[Address],[City],[Region],[PostalCode],[Country],[HomePhone],[Extension],[Photo],[Notes],[ReportsTo],[PhotoPath] FROM [dbo].[Employees]    WHERE [EmployeeID] = #EmployeeID#

        </select>

        <select id="Employees_SelectAll" listClass="ArrayList" resultMap="Employees_Result" >

            SELECT [EmployeeID],[LastName],[FirstName],[Title],[TitleOfCourtesy],[BirthDate],[HireDate],[Address],[City],[Region],[PostalCode],[Country],[HomePhone],[Extension],[Photo],[Notes],[ReportsTo],[PhotoPath]

            FROM [dbo].[Employees]

        </select>

        <!-- 加入extends(继承Employees_SelectAll查询的数据后根据字段IssueDate排序) -->

<select id="Ticket_SelectAll_Order" extends="Ticket_SelectAll" resultMap="Ticket_Result">

            order by IssueDate

       </select>

        <!—Insert操做的部分属性说明

            id :该存储过程的惟一标识符

            parameterClass : 传入的参数的类型(能够是string,类等)-->

        <insert id="Employees_Insert" parameterClass="Employee">

            INSERT INTO [dbo].[Employees] ([EmployeeID],[LastName],[FirstName],[Title],[TitleOfCourtesy],[BirthDate],[HireDate],[Address],[City],[Region],[PostalCode],[Country],[HomePhone],[Extension],[Photo],[Notes],[ReportsTo],[PhotoPath])

            VALUES (#EmployeeID#,#LastName#,#FirstName#,#Title#,#TitleOfCourtesy#,#BirthDate#,#HireDate#,#Address#,#City#,#Region#,#PostalCode#,#Country#,#HomePhone#,#Extension#,#Photo#,#Notes#,#ReportsTo#,#PhotoPath#)

        </insert>      

        <!—Delete操做的部分属性说明

            id :该存储过程的惟一标识符

            parameterClass : 传入的参数的类型(能够是string,类等)此处传递的为一个实例-->

        <delete id="Employees_Delete" parameterClass="Employee">

            DELETE FROM [dbo].[Employees] WHERE [EmployeeID] = #EmployeeID#

        </delete>

        <!—Update操做的部分属性说明

            id :该存储过程的惟一标识符

            parameterClass : 传入的参数的类型(能够是string,类等)此处传递的为一个实例-->

        <update id="Employees_Update" parameterClass="Employee">

            UPDATE [dbo].[Employees] SET [LastName] = #LastName#,[FirstName] = #FirstName#,[Title] = #Title#,[TitleOfCourtesy] = #TitleOfCourtesy#,[BirthDate] = #BirthDate#,[HireDate] = #HireDate#,[Address] = #Address#,[City] = #City#,[Region] = #Region#,[PostalCode] = #PostalCode#,[Country] = #Country#,[HomePhone] = #HomePhone#,[Extension] = #Extension#,[Photo] = #Photo#,[Notes] = #Notes#,[ReportsTo] = #ReportsTo#,[PhotoPath] = #PhotoPath#     WHERE [EmployeeID] = #EmployeeID#

        </update>

        <!-- procedure中的属性说明

             id :该存储过程的惟一标识符

             parameterMap : 对应于下面 parameterMaps中的 id

             resultClass :说明返回的结果的类型

        -->

        <procedure id="pro_ViewTicket" parameterMap="proview" resultMap="Ticket_Result">

            <!--对应的存储过程名称-->

            ViewTicket

        </procedure>

    </statements>

     <!-- parameterMaps :存储过程当中用到的参数 -->

    <parameterMaps>

        <!-- parameterMap参数说明

             id :该存储过程的惟一标识符

             class : 传入的参数的类型(传入多个参数的时候将参数放入Hashtable)

        -->

        <parameterMap id ="proview" class="hashtable">

            <!-- parameter中的属性说明

                 property :须要传入的参数

                 column :数据库中相对应的列

            -->

            <parameter property ="ip" column="IP"/>

            <parameter property ="status" column="Status"/>

        </parameterMap>

    </parameterMaps>

</sqlMap>

    说明:在这个映射文件中咱们能够看到不少没有碰到过的属性名,如alias、resultMap、result等等,下面来介绍一下这些经常使用元素的用法

    Alias (必须的)

    typeAlias

    alias :咱们所标识的该类的别名,必须惟一

    type : 指明该类存放的具体位置,格式为: (命名空间.类名,程序集的名称,Version=[*], Culture=[*],PublicKeyToken=[*]) 绿色为可选项

resultMap

    id :在resultMap中的惟一标识

    class :对应于typeAlias中哪个alias中的类

    extends:从某一resultMap中的父类中继承下来的类的全部属性

    result

    property:对应的类中的属性

    column:对应于操做后返回的结果显示的相应数据库字段名

    columnIndex:对应列的索引

    statements

id: 在SqlMap中的惟一标识

parameterMap:定义了将字段名和对象的属性对应起来值的规则, 一般匹配?号(以下:)

<parameterMap id="insert-product-param" class="Product">

    <parameter property="id"/>

    <parameter property="description"/>

</parameterMap>

<statement id="insertProduct" parameterMap="insert-product-param">

    insert into PRODUCT (PRD_ID, PRD_DESCRIPTION) values (?,?);

</statement>

这里的(?,?)其实是类Product中的属性 (id, description)

cacheModel:将查询的结果放入缓存中(举例以下:)

<cacheModel id="product-cache" implementation="LRU">

  <flushInterval hours="24"/>

  <flushOnExecute statement="insertProduct"/>

  <flushOnExecute statement="updateProduct"/>

  <flushOnExecute statement="deleteProduct"/>

  <property name="size" value="1000" />

</cacheModel>
 

<statement id="selectProductList" parameterClass="int" cacheModel="product-cache">

    select * from PRODUCT where PRD_CAT_ID = #value#

</statement>

extends:即在已有的Sql语句上添加条件等,extends后跟已有的Sql操做的id


    3.根据须要获取或操做相应的结果集

    Ibatis在返回的结果集上主要有如下几种:

    Ilist、Idictionary、Object等,没有咱们DataSet、DataTable,这些须要咱们扩展

    4.  根据须要生成动态的Sql

    在动态生成的Sql中主要有如下几种:(咱们在此配置上增长)

    <select id="Employees_SelectAll" listClass="ArrayList" resultMap="Employees_Result">

        SELECT * FROM [dbo].[Employees]

    </select>

    1)条件为:当传入的EmployeeID字段值不为空的时候根据EmployeeID来查询

<dynamic prepend="WHERE">
      <isNotNull prepend="AND" property=" EmployeeID ">
        EmployeeID = #EmployeeID#
      <isNotNull>
</dynamic>


    2)条件为:出生日期大于1981

        <dynamic prepend="WHERE">
      <isGreaterEqual prepend="AND" property="Birthdate" compareValue="1981">
           Birthdate = #Birthdate#
      </isGreaterEqual>

        </dynamic>

    3)条件为:City不为空

    <dynamic prepend="WHERE">

        <isNotEmpty prepend="AND" property="City">

            City like # City # + '%'

        </isNotEmpty>

    </dynamic>                                   

     这里只是介绍了三种经常使用的生成动态Sql是如何实现的,其余可参照帮助文档   

    B.Ibatis的日志管理

        首先,在应用程序中引入log4net.dll和IBatisNet.Common.Logging.Log4Net.dll这两个dll。接着    在应用程序的配置文件中加入配置节信息:(这里以Web应用程序为例)

    若是咱们要获取      

    一、在自定义配置节中加入

    <configSections>

         <sectionGroup name="iBATIS">

            <section name="logging"                                                                          type="IBatisNet.Common.Logging.ConfigurationSectionHandler, IBatisNet.Common" />

          </sectionGroup>

            <section name="log4net"

                type="log4net.Config.Log4NetConfigurationSectionHandler, log4net" />

</configSections>

 
<iBATIS>

        <logging>

        <logFactoryAdapter       type="IBatisNet.Common.Logging.Impl.Log4NetLoggerFA,IBatisNet.Common.Logging.Log4Net">

                <arg key="configType" value="inline" />

           </logFactoryAdapter>

        </logging>

    </iBATIS>

 

    <log4net>

        <appender name="FileAppender" type="log4net.Appender.FileAppender">//文件保存方式

            <file value="e:\InsertsUpdatesDeletes.txt" />        //文件保存的路径

            <appendToFile value="true" />

            <layout type="log4net.Layout.SimpleLayout" />

        </appender>

        <root>

            <level value="ALL" />                  //日志输出的级别

            <appender-ref ref="FileAppender" />    //日志输出的格式(文件、控制台等)  
        </root>

    <log4net>

    二、在appSetting配置节中加入

<add key="log4net.Internal.Debug" value="true"/>

    三、记录加载的一些程序集信息则在加入以下配置信息

           <system.diagnostics>

        <trace autoflush="true">

              <listeners>

                <add name="textWriterTraceListener"

                 type="System.Diagnostics.TextWriterTraceListener"

                 initializeData="e:\log4net.txt" />     //信息保存的路径

              </listeners>

           </trace>

 </system.diagnostics>

    四、若是想输出已经配置好的 SQL 命令,则使得IBatisNet.DataMapper.Configuration.Statements.DefaultPreparedCommand可用,在<configuration>节点中添加以下信息

<logger name="IBatisNet.DataMapper.Commands.DefaultPreparedCommand">

<level value="ALL" />

</logger>

           同理,还可添加其它输出信息,具体参数设置参见帮助文档

    3、小结

Ibatis的功能很是强大,这里只是简单介绍了Ibatis的一些基本操做,事务的处理等也没有介绍,你们在使用的时候能够参照 Doc-DataMapper-1.5.1 帮助文档

 (相关下载: Doc-DataMapper-1.5.1 帮助文档

             本篇Blog的Word文档)

转载于:https://www.cnblogs.com/shimeng3344518/archive/2007/04/19/719203.html