ibatis.net 学习笔记

现在的位置: 首页 > 综合 > 正文

ibatis.net 学习笔记

2012年08月25日 ⁄ 综合 ⁄ 共 16075字 ⁄ 字号小中大 ⁄ 评论关闭

现在的使用IBatis。顺序可以是这样，首先项目经理会分析整个项目，可以分成为几个对象，每个对象具有什么属性，什么方法。同时会用visio画出UML图来。这样可以说把面向对象的优势完全体现出来了。然后程序员会根据UML图来操作对象，实现每个对象的属性和方法。这样就可以完全脱离数据库，去考虑程序的问题。这种开发方式特别适合于团队开发，团队中有专门的人去负责数据的建立，他可以根据项目中的Maps图清晰的知道每个数据表中的字段

Separating SQL code from programming code
Passing input parameters to the library classes and extracting the output
Separating data access classes from business logic classes
Caching often-used data until it changes
Managing transactions and threading

So, how do you decide whether to OR/M or to DataMap? As always, the best advice is to implement a representative part of your project using either approach, and then decide. But, in general, OR/M is a good thing when you

Have complete control over your database implementation
Do not have a Database Administrator or SQL guru on the team
Need to model the problem domain outside the database as an object graph.

Likewise, the best time to use a Data Mapper, like iBATIS, is when:

You do not have complete control over the database implementation, or want to continue to access a legacy database as it is being refactored.
You have database administrators or SQL gurus on the team.
The database is being used to model the problem domain, and the application's primary role is to help the client use the database model.

Table 3.1. The six statement-type elements

Statement Element	Attributes	Child Elements	Methods
<statement>	id parameterClass resultClass listClass parameterMap resultMap cacheModel	All dynamic elements	Insert Update Delete All query methods
<insert>	id parameterClass parameterMap	All dynamic elements <selectKey> <generate>	Insert Update Delete
<update>	id parameterClass parameterMap extends	All dynamic elements <generate>	Insert Update Delete
<delete>	id parameterClass parameterMap extends	All dynamic elements <generate>	Insert Update Delete
<select>	id parameterClass resultClass listClass parameterMap resultMap cacheModel extends	All dynamic elements <generate>	All query methods
<procedure>	id parameterMap resultClass resultMap cacheModel	All dynamic elements	Insert Update Delete All query

存储过程Stored Procedures

<procedure id="SwapEmailAddresses" parameterMap="swap-params">
  ps_swap_email_address
</procedure>
...
<parameterMap id="swap-params">
  <parameter property="email1" column="First_Email" />
  <parameter property="email2" column="Second_Email" />
</parameterMap>

<procedure id="InsertCategory" parameterMap="insert-params">
prc_InsertCategory
</procedure>
...
<parameterMap id="insert-params">
<parameter property="Name" column="p_Category_Name"/>
<parameter property="GuidString" column="p_Category_Guid" dbType="VarChar"/>
<parameter property="Id" column="p_Category_Id" dbType="Int32" type="Int"/>
</parameterMap>

Reusing SQL Fragments重用sql【注意加载顺序比如sql在另外的map中】

<sql id="selectItem_fragment">
FROM items
WHERE parentid = 6
</sql>

<select id="selectItemCount" resultClass="int">
SELECT COUNT(*) AS total
<include refid="selectItem_fragment"/>
</select>

<select id="selectItems" resultClass="Item">
SELECT id, name
<include refid="selectItem_fragment"/>
</select>

3.3.4.8. extends

When writing Sql, you often encounter duplicate fragments of SQL. iBATIS offers a simple yet powerful attribute to reuse them.

<select id="GetAllAccounts"
  resultMap="indexed-account-result">
select
  Account_ID,
  Account_FirstName,
  Account_LastName,
  Account_Email
from Accounts
</select>
		
<select id="GetAllAccountsOrderByName"
  extends="GetAllAccounts"
  resultMap="indexed-account-result">
    order by Account_FirstName
</select>

Escaping XML symbols去除xml特殊标记

<![CDATA[
SELECT * FROM PERSON WHERE AGE > #value#
]]>

Auto-Generated Keys自动生成主键标识符

<!—Oracle SEQUENCE Example using .NET 1.1 System.Data.OracleClient -->
<insert id="insertProduct-ORACLE" parameterClass="product">
  <selectKey resultClass="int" type="pre" property="Id" >
     SELECT STOCKIDSEQUENCE.NEXTVAL AS VALUE FROM DUAL
  </selectKey>
  insert into PRODUCT (PRD_ID,PRD_DESCRIPTION) values (#id#,#description#)
</insert>

<!— Microsoft SQL Server IDENTITY Column Example -->
<insert id="insertProduct-MS-SQL" parameterClass="product">
  insert into PRODUCT (PRD_DESCRIPTION)
  values (#description#)
<selectKey resultClass="int" type="post" property="id" >
   select @@IDENTITY as value
</selectKey>
</insert>

<insert id="insertProduct-MYSQL" parameterClass="product">
  insert into PRODUCT (PRD_DESCRIPTION)
  values (#description#)
<selectKey resultClass="int" type="post" property="id" >
   select LAST_INSERT_ID() as value
</selectKey>
</insert>

自动生成简单sql

3.3.3.4. <generate> tag
You can use iBATIS to execute any SQL statement your application requires. When the requirements for a statement are simple and obvious, you may not even need to write a SQL statement at all. The <generate> tag can be used to create simple SQL statements automatically, based on a <parameterMap> element. The four CRUD statement types (insert, select, update, and delete) are supported. For a select, you can select all or select by a key (or keys). Example 3.8 shows an example of generating the usual array of CRUD statements.

Example 3.10. Creating the "usual suspects" with the <generate> tag

</parameterMaps>

  <insert id="InsertCategoryGenerate" parameterMap="insert-generate-params">
    <selectKey property="Id" type="post" resultClass="int">
      select @@IDENTITY as value
    </selectKey>
    <generate table="Categories" />
  </insert>

</statements>

Attribute	Description	Required
table	specifies the table name to use in the SQL statement.	yes
by	specifies the columns to use in a WHERE clause	no

3.3.4.7. cacheModel 缓存方式

If you want to cache the result of a query, you can specify a Cache Model as part of the <statement> element. Example 3.15 shows a <cacheModel> element and a corresponding <statement>.

Example 3.17. A <cacheModel> element with its corresponding <statement>

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

Example 3.46. Resolving complex properties with a join 表连接问题用resultMapping

<resultMaps>
  <resultMap id="select-product-result" class="product">
    <result property="id" column="PRD_ID"/>
    <result property="description" column="PRD_DESCRIPTION"/>
    <result property="category" resultMapping="Category.CategoryResult" />
  </resultMap>
</resultMaps>

<statements>
  <statement id="selectProduct" parameterClass="int" resultMap="select-product-result">
    select *
    from PRODUCT, CATEGORY
    where PRD_CAT_ID=CAT_ID
    and PRD_ID = #value#
  </statement>
</statements>

Example 3.48. N+1 Select Lists (1:M and M:N), example of problem 在大数据量时候有问题的sql
<resultMaps>

  <resultMap id="select-category-result" class="Category">
    <result property="Id" column="CAT_ID"/>
    <result property="Description" column="CAT_DESCRIPTION"/>
   <result property="ProductList" column="CAT_ID" select="selectProductsByCatId"/>
  </resultMap>

  <resultMap id="Product-result" class="Product">
    <result property="Id" column="PRD_ID"/>
    <result property="Description" column="PRD_DESCRIPTION"/>
  </resultMap>
<resultMaps>

<statements>

  <!-- This statement executes 1 time -->
  <statement id="selectCategory" parameterClass="int" resultMap="select-category-result">
    select * from CATEGORY where CAT_ID = #value#
  </statement>

  <!-- This statement executes N times (once for each category returned above) 
       and returns a list of Products (1:M) -->
  <statement id="selectProductsByCatId" parameterClass="int" resultMap="select-product-result">
    select * from PRODUCT where PRD_CAT_ID = #value#
  </statement>
</statements>

iBATIS fully solves the N+1 selects problem. Here is the same example solved :

Example 3.49. N+1 Select Lists (1:M and M:N) resolution
<sqlMap namespace="ProductCategory">
<resultMaps>

  <resultMap id="Category-result" class="Category" groupBy="Id">
    <result property="Id" column="CAT_ID"/>
    <result property="Description" column="CAT_DESCRIPTION"/>
    <result property="ProductList" resultMapping="ProductCategory.Product-result"/>
  </resultMap>

  <resultMap id="Product-result" class="Product">
    <result property="Id" column="PRD_ID"/>
    <result property="Description" column="PRD_DESCRIPTION"/>
  </resultMap>
<resultMaps>

<statements>

  <!-- This statement executes 1 time -->
  <statement id="SelectCategory" parameterClass="int" resultMap="Category-result">
    select C.CAT_ID, C.CAT_DESCRIPTION, P.PRD_ID, P.PRD_DESCRIPTION
    from CATEGORY C
    left outer join PRODUCT P
    on C.CAT_ID = P.PRD_CAT_ID
    where CAT_ID = #value#
  </statement>




Example 3.50. Mapping a composite key 组合键
<resultMaps>
  <resultMap id="select-order-result" class="order">
    <result property="id" column="ORD_ID"/>
    <result property="customerId" column="ORD_CST_ID"/>
    ...
    <result property="payments" column="itemId=ORD_ID, custId=ORD_CST_ID"
      select="selectOrderPayments"/>
  </resultMap>
<resultMaps>

<statements>

  <statement id="selectOrderPayments" resultMap="select-payment-result">
    select * from PAYMENT
    where PAY_ORD_ID = #itemId#
    and PAY_CST_ID = #custId#
  </statement>
</statements>

 
 
xml 中的方法是全局的 在那个xml里面都能访问所以要保持方法id的唯一性
 
ibatis的优点
1.学习简单 最简单的持久层框架
2.自己写相应的sql语句
3.分离数据库设计和对象模型的设计减少耦合
 不使用ibatis的情况
1、对应用程序和数据库拥有完全的控制权
2、 
nullValue
   <resultMaps>
    <resultMap id="SelectproductResult" class="productEntity">
      <result property="Id" column="Id" />
      <result property="Name" column="Name" />
      <result property="UserId" column="UserId" nullValue="0" />
    </resultMap>
  </resultMaps>
组合键的使用
<resultMaps>
  <resultMap id="select-order-result" class="order">
    <result property="id" column="ORD_ID"/>
    <result property="customerId" column="ORD_CST_ID"/>
    ...
    <result property="payments" column="itemId=ORD_ID, custId=ORD_CST_ID"
      select="selectOrderPayments"/>
  </resultMap>
<resultMaps>

<statements>

  <statement id="selectOrderPayments" resultMap="select-payment-result">
    select * from PAYMENT
    where PAY_ORD_ID = #itemId#
    and PAY_CST_ID = #custId#
  </statement>
</statements>
ibatis高级特性
数据关联
延迟加载
动态映射
事务管理
缓存
 
重用sql
<sql id="selectall">
select *  from selectall
</sql>
 
 
<select id="selectallbyname">
<include refid="selectall"/> where name="11"
</select >
 
配置的重用
Extends
resultMapping
 
过滤特殊字符
>=  号   <![CDATA[>=]]>   
 
parameterClass
parameterMap
Example 3.18. An external Parameter Map
<parameterMap id="parameterMapIdentifier" 
  [class="fullyQualifiedClassName, assembly|typeAlias"]
  [extends="[sqlMapNamespace.]parameterMapId"]>
  <parameter 
    property ="propertyName" 
    [column="columnName"]
    [direction="Input|Output|InputOutput"]
    [dbType="databaseType"] 
    [type="propertyCLRType"]
    [nullValue="nullValueReplacement"] 
    [size="columnSize"] 
    [precision="columnPrecision"] 
    [scale="columnScale"]  
    [typeHandler="fullyQualifiedClassName, assembly|typeAlias"]  
  <parameter ... ... />
  <parameter ... ... /> 
</parameterMap>


Example 3.19. A typical <parameterMap> element
<parameterMap id="insert-product-param" class="Product">
  <parameter property="description" />
  <parameter property="id"/>
</parameterMap>

<statement id="insertProduct" parameterMap="insert-product-param">
  insert into PRODUCT (PRD_DESCRIPTION, PRD_ID) values (?,?);
</statement>

resultClass
resultMap（局部的在其他xml中引用得加上相应的命名空间）
Example 3.26. The structure of a <resultMap> element.
<resultMap id="resultMapIdentifier" 
           [class="fullyQualifiedClassName, assembly|typeAlias"] 
           [extends="[sqlMapNamespace.]resultMapId"]>

   <constructor > 
       <argument property="argumentName" 
           column="columnName"
           [columnIndex="columnIndex"] 
           [dbType="databaseType"] 
           [type="propertyCLRType"]
           [resultMapping="resultMapName"]
           [nullValue="nullValueReplacement"] 
           [select="someOtherStatementName"] 
           [typeHandler="fullyQualifiedClassName, assembly|typeAlias"] />
   </constructor > 

   <result property="propertyName" 
           column="columnName"
           [columnIndex="columnIndex"] 
           [dbType="databaseType"] 
           [type="propertyCLRType"]
           [resultMapping="resultMapName"]
           [nullValue="nullValueReplacement"] 
           [select="someOtherStatementName"] 
           [lazyLoad="true|false"]
           [typeHandler="fullyQualifiedClassName, assembly|typeAlias"]
   />
   <result ... .../>
   <result ... .../>
    // Inheritance support
   <discriminator column="columnName" 
                     [type|typeHandler="fullyQualifiedClassName, assembly|typeAlias"]
   />
    <subMap value="discriminatorValue" 
               resultMapping="resultMapName"
   />
   <subMap .../> 
</resultMap>


 Cache Models
Example 3.51. Configuation a cache using the Cache Model element
<cacheModel id="product-cache" implementation="LRU" readOnly="true" serialize="false">
  <flushInterval hours="24"/>
  <flushOnExecute  statement="insertProduct"/>
  <flushOnExecute  statement="updateProduct"/>
  <flushOnExecute  statement="deleteProduct"/>
  <property name="CacheSize" value="100"/>
</cacheModel>

Cache Implementation
MEMORY：MEMORY cache使用reference类型来管理cache的行为。也就是说，垃圾收集器可以决定谁可以停留在缓存中。MEMORY cache是应用程序一个很好的选择（没有对象重用的识别模式。与内存较少的应用程序）
LRU：
最少使用的优先被删除
FIFO：FIFO的缓存实现使用先进先出算法来确定如何从缓存中自动删除对象。当Cache满时，最古老的对象将被从缓存中删除。
 FIFO缓存有利于使用这种模式（一个特定的查询接连几次被引用，但后来一段时间可能不用）。
Dynamic SQL动态sql语句
Table 3.7. Binary conditional attributes
Element Description
<isEqual> Checks the equality of a property and a value, or another property. Example Usage:<isEqual prepend="AND" 
             property="status" 
             compareValue="Y">
MARRIED = ‘TRUE'
</isEqual>               



<isNotEqual>
Checks the inequality of a property and a value, or another property. Example Usage:
<isNotEqual prepend="AND" 
             property="status" 
             compareValue="N">
MARRIED = ‘FALSE'
</isNotEqual>   



<isGreaterThan>
Checks if a property is greater than a value or another property. Example Usage:
<isGreaterThan prepend="AND" 
             property="age" 
             compareValue="18">
ADOLESCENT = ‘FALSE'
</isGreaterThan>   



<isGreaterEqual>
Checks if a property is greater than or equal to a value or another property. Example Usage:
<isGreaterEqual prepend="AND" 
             property="shoeSize" 
             compareValue="12">
BIGFOOT = ‘TRUE'
</isGreaterEqual>



<isLessEqual>
Checks if a property is less than or equal to a value or another property. Example Usage:
<isLessEqual prepend="AND" 
             property="age" 
             compareValue="18">
ADOLESCENT = ‘TRUE'
</isLessEqual>





 Table 3.8. Unary conditional attributes






Element
Description




<isPropertyAvailable>
Checks if a property is available (i.e is a property of the parameter object). Example Usage:
<isPropertyAvailable property="id" >
  ACCOUNT_ID=#id# 
</isPropertyAvailable>



<isNotPropertyAvailable>
Checks if a property is unavailable (i.e not a property of the parameter object). Example Usage:
<isNotPropertyAvailable property="age" >
  STATUS='New' 
</isNotEmpty>



<isNull>
Checks if a property is null. Example Usage:
<isNull prepend="AND" property="order.id" >
  ACCOUNT.ACCOUNT_ID = ORDER.ACCOUNT_ID(+) 
</isNotEmpty>



<isNotNull>
Checks if a property is not null. Example Usage:
<isNotNull prepend="AND" property="order.id" >
  ORDER.ORDER_ID = #order.id#
</isNotEmpty>



<isEmpty>
Checks to see if the value of a Collection, String property is null or empty ("" or size() < 1). Example Usage:
<isEmpty property="firstName" >
  LIMIT 0, 20
</isNotEmpty>



<isNotEmpty>
Checks to see if the value of a Collection, String property is not null and not empty ("" or size() < 1). Example Usage:
<isNotEmpty prepend="AND" property="firstName" >
  FIRST_NAME LIKE '%$FirstName$%'
</isNotEmpty>




Table 3.9. Testing to see if a parameter is present






Element
Description




<isParameterPresent>
Checks to see if the parameter object is present (not null).
<isParameterPresent prepend="AND">
  EMPLOYEE_TYPE = #empType#
</isParameterPresent>



<isNotParameterPresent>
Checks to see if the parameter object is not present (null). Example Usage:
<isNotParameterPresent prepend="AND">
  EMPLOYEE_TYPE = ‘DEFAULT'
</isNotParameterPresent>




1.IBatis.Net 中的数据类型转换 http://www.cnblogs.com/aaa6818162/archive/2011/05/21/2053019.html
2.存储过程的使用
3.调试时获取sql语句 GetStatementName

Element	Description
<isEqual>	Checks the equality of a property and a value, or another property. Example Usage: <isEqual prepend="AND" property="status" compareValue="Y"> MARRIED = ‘TRUE' </isEqual>
<isNotEqual>	Checks the inequality of a property and a value, or another property. Example Usage: <isNotEqual prepend="AND" property="status" compareValue="N"> MARRIED = ‘FALSE' </isNotEqual>
<isGreaterThan>	Checks if a property is greater than a value or another property. Example Usage: <isGreaterThan prepend="AND" property="age" compareValue="18"> ADOLESCENT = ‘FALSE' </isGreaterThan>
<isGreaterEqual>	Checks if a property is greater than or equal to a value or another property. Example Usage: <isGreaterEqual prepend="AND" property="shoeSize" compareValue="12"> BIGFOOT = ‘TRUE' </isGreaterEqual>
<isLessEqual>	Checks if a property is less than or equal to a value or another property. Example Usage: <isLessEqual prepend="AND" property="age" compareValue="18"> ADOLESCENT = ‘TRUE' </isLessEqual>

Element	Description
<isPropertyAvailable>	Checks if a property is available (i.e is a property of the parameter object). Example Usage: <isPropertyAvailable property="id" > ACCOUNT_ID=#id# </isPropertyAvailable>
<isNotPropertyAvailable>	Checks if a property is unavailable (i.e not a property of the parameter object). Example Usage: <isNotPropertyAvailable property="age" > STATUS='New' </isNotEmpty>
<isNull>	Checks if a property is null. Example Usage: <isNull prepend="AND" property="order.id" > ACCOUNT.ACCOUNT_ID = ORDER.ACCOUNT_ID(+) </isNotEmpty>
<isNotNull>	Checks if a property is not null. Example Usage: <isNotNull prepend="AND" property="order.id" > ORDER.ORDER_ID = #order.id# </isNotEmpty>
<isEmpty>	Checks to see if the value of a Collection, String property is null or empty ("" or size() < 1). Example Usage: <isEmpty property="firstName" > LIMIT 0, 20 </isNotEmpty>
<isNotEmpty>	Checks to see if the value of a Collection, String property is not null and not empty ("" or size() < 1). Example Usage: <isNotEmpty prepend="AND" property="firstName" > FIRST_NAME LIKE '%$FirstName$%' </isNotEmpty>

Element	Description
<isParameterPresent>	Checks to see if the parameter object is present (not null). <isParameterPresent prepend="AND"> EMPLOYEE_TYPE = #empType# </isParameterPresent>
<isNotParameterPresent>	Checks to see if the parameter object is not present (null). Example Usage: <isNotParameterPresent prepend="AND"> EMPLOYEE_TYPE = ‘DEFAULT' </isNotParameterPresent>

【上篇】5. Factory Method 工厂方法（创建型模式的基础）
【下篇】基于FMS的在线录制例子…

作者: stemware

该日志由 stemware 于12年前发表在综合分类下，最后更新于 2012年08月25日.
转载请注明: ibatis.net 学习笔记 | 学步园 +复制链接

抱歉!评论已关闭.

学步园