映射文件總體形式：

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

<!DOCTYPE sqlMap

PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"

"http://ibatis.apache.org/dtd/sql-map-2.dtd">

<sqlMap namespace=”Product”>

<cacheModel id=”productCache” type=”LRU”>

<flushInterval hours=”24”/>

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

</cacheModel>

<typeAlias alias=”product” type=”com.ibatis.example.Product” />

<parameterMap id=”productParam” class=”product”>

<parameter property=”id”/>

</parameterMap>

<resultMap id=”productResult” class=”product”>

<result property=”id” column=”PRD_ID”/>

<result property=”description” column=”PRD_DESCRIPTION”/>

</resultMap>

<select id=”getProduct” parameterMap=”productParam”

resultMap=”productResult” cacheModel=”product-cache”>

select * from PRODUCT where PRD_ID = ?

</select>

</sqlMap>

映射的查詢語句形式：

<statement id=”statementName”

[parameterClass=”some.class.Name”]

[resultClass=”some.class.Name”]

[parameterMap=”nameOfParameterMap”]

[resultMap=”nameOfResultMap”]

[cacheModel=”nameOfCache”]

[timeout=“5”]>

select * from PRODUCT where PRD_ID = [?|#propertyName#]

order by [$simpleDynamic$]

</statement>

<insert id=”insertTestProduct” >

insert into PRODUCT (PRD_ID, PRD_DESCRIPTION) values (1, “Shih Tzu”)

</insert>

<select id="getPersonsByAge" parameterClass=”int” resultClass="examples.domain.Person">

SELECT *

FROM PERSON

WHERE AGE <![CDATA[ > ]]> #value#

</select>

下面的表格說明了所有支持的查詢語句形式及其屬性：

?

可定義SQL Fragments對部分SQL語句進行復用：

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

?

對某些數據庫的自動增長鍵有支持，但不同的支持策略，例：

?

<!—Oracle SEQUENCE Example -->

<insert id="insertProduct-ORACLE" parameterClass="com.domain.Product">

<selectKey resultClass="int" >

SELECT STOCKIDSEQUENCE.NEXTVAL AS ID 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="com.domain.Product">

insert into PRODUCT (PRD_DESCRIPTION)

values (#description#)

<selectKey resultClass="int" >

SELECT @@IDENTITY AS ID

</selectKey>

</insert>

更具體內容參見幫助文檔。

?

存儲過程：

<parameterMap id="swapParameters" class="map" >

<parameter property="email1" jdbcType="VARCHAR" javaType="java.lang.String" mode="INOUT"/>

<parameter property="email2" jdbcType="VARCHAR" javaType="java.lang.String" mode="INOUT"/>

</parameterMap>

<procedure id="swapEmailAddresses" parameterMap="swapParameters" >

{call swap_email_address (?, ?)}

</procedure>

?

存儲過程影響數據庫表，同時也影響參數對象，mode若是INOUT或OUT，參數對象會被改變，否則不改變。

?

對于映射查詢，iBatis建議：參數使用inline-parameter，也就是parameterClass(不是parameterMap)，而結果使用resultMap（不是resultClass）。

?

parameterMap格式：

<parameterMap id=”parameterMapName” [class=”com.domain.Product”]>

<parameter property =”propertyName” [jdbcType=”VARCHAR”] [javaType=”string”]

[nullValue=“-9999”]

[typeName=”{REF or user-defined type}”]

[resultMap=someResultMap]

[mode=IN|OUT|INOUT]

[typeHandler=someTypeHandler]

[numericScale=2]/>

<parameter …… />

<parameter …… />

</parameterMap>

?

resultMap格式：

<resultMap id=”resultMapName” class=”some.domain.Class”

[extends=”parent-resultMap”]

[groupBy=“some property list”]>

<result property=”propertyName” column=”COLUMN_NAME”

[columnIndex=”1”] [javaType=”int”] [jdbcType=”NUMERIC”]

[nullValue=”-999999”] [select=”someOtherStatement”]

[resultMap=“someOtherResultMap”]

[typeHandler=“com.mydomain.MyTypehandler”]

/>

<result ……/>

<result ……/>

<result ……/>

</resultMap>

?

java.util.Map接口的實現類也可作為parameterMap或resultMap的class。

?

復雜查詢：

N+1查詢（結合cache的話會比連接查詢好，沒有cache很糟）：

<resultMap id=”get-product-result” class=”com.ibatis.example.Product”>

<result property=”id” column=”PRD_ID”/>

<result property=”description” column=”PRD_DESCRIPTION”/>

<result property=”category” column=” PRD_CAT_ID ” select=”getCategory”/>

</resultMap>

<resultMap id=”get-category-result” class=”com.ibatis.example.Category”>

<result property=”id” column=”CAT_ID”/>

<result property=”description” column=”CAT_DESCRIPTION”/>

</resultMap>

<select id=”getProduct” parameterClass=”int” resultMap=”get-product-result”>

select * from PRODUCT where PRD_ID = #value#

</select>

<select id=”getCategory” parameterClass=”int” resultMap=”get-category-result”>

select * from CATEGORY where CAT_ID = #value#

</select>

?

連接查詢（iBatis解決復雜查詢的通常方式，但有cache時候可能不如N+1查詢）：

<resultMap id=”get-product-result” class=”com.ibatis.example.Product”>

<result property=”id” column=”PRD_ID”/>

<result property=”description” column=”PRD_DESCRIPTION”/>

<result property=”category.id” column=” CAT_ID ” />

<result property=”category.description” column=” CAT_DESCRIPTION ” />

</resultMap>

<select id=”getProduct” parameterClass=”int” resultMap=”get-product-result”>

select *

from PRODUCT, CATEGORY

where PRD_CAT_ID=CAT_ID

and PRD_ID = #value#

</select>

或者：

<resultMap id=”get-product-result” class=”com.ibatis.example.Product”>

<result property=”id” column=”PRD_ID”/>

<result property=”description” column=”PRD_DESCRIPTION”/>

<result property=”category” resultMap=“get-category-result” />

</resultMap>

<resultMap id=”get-category-result” class=”com.ibatis.example.Category”>

<result property=”id” column=” CAT_ID ” />

<result property=”description” column=” CAT_DESCRIPTION ” />

</resultMap>

<select id=”getProduct” parameterClass=”int” resultMap=”get-product-result”>

select *

from PRODUCT, CATEGORY

where PRD_CAT_ID=CAT_ID

and PRD_ID = #value#

</select>

復雜集合查詢：

N+1法：

<resultMap id=”get-category-result” class=”com.ibatis.example.Category”>

<result property=”id” column=”CAT_ID”/>

<result property=”description” column=”CAT_DESCRIPTION”/>

<result property=”productList” column=” CAT_ID ” select=” getProductsByCatId”/>

</resultMap>

<resultMap id=”get-product-result” class=”com.ibatis.example.Product”>

<result property=”id” column=”PRD_ID”/>

<result property=”description” column=”PRD_DESCRIPTION”/>

</resultMap>

<select id=”getCategory” parameterClass=”int” resultMap=”get-category-result”>

select * from CATEGORY where CAT_ID = #value#

</select>

<select id=”getProductsByCatId” parameterClass=”int” resultMap=”get-product-result”>

select * from PRODUCT where PRD_CAT_ID = #value#

</select>

groupBy法：

<sqlMap namespace="ProductCategory">

<resultMap id=”categoryResult” class=”com.ibatis.example.Category” groupBy=”id”>

<result property=”id” column=”CAT_ID”/>

<result property=”description” column=”CAT_DESCRIPTION”/>

<result property=”productList” resultMap=”ProductCategory.productResult”/>

</resultMap>

<resultMap id=”productResult” class=”com.ibatis.example.Product”>

<result property=”id” column=”PRD_ID”/>

<result property=”description” column=”PRD_DESCRIPTION”/>

</resultMap>

<select id=”getCategory” parameterClass=”int” resultMap=”categoryResult”>

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#

</select>

</sqlMap>

注：groupBy屬性不能和queryForPaginatedList()調用同時使用。

N+1查詢中的符合查詢：

<resultMap id=”get-order-result” class=”com.ibatis.example.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=”getOrderPayments”/>

</resultMap>

<select id=”getOrderPayments” resultMap=”get-payment-result”>

select * from PAYMENT

where PAY_ORD_ID = #itemId#

and PAY_CST_ID = #custId#

</select>

數據類型表：

?

自定義TypeHandler:

public class YesNoBoolTypeHandlerCallback implements TypeHandlerCallback {

private static final String YES = "Y";

private static final String NO = "N";

public Object getResult(ResultGetter getter)

throws SQLException {

String s = getter.getString();

if (YES.equalsIgnoreCase(s)) {

return new Boolean (true);

} else if (NO.equalsIgnoreCase(s)) {

return new Boolean (false);

} else {

throw new SQLException (

"Unexpected value " + s + " found where " + YES + " or " + NO + " was expected.");

}

}

public void setParameter(ParameterSetter setter, Object parameter)

throws SQLException {

boolean b = ((Boolean)parameter).booleanValue();

if (b) {

setter.setString(YES);

} else {

setter.setString(NO);

}

}

public Object valueOf(String s) {

if (YES.equalsIgnoreCase(s)) {

return new Boolean (true);

} else {

return new Boolean (false);

}

}

然后在sqlMapConfig文件里加上如下配置：

<typeHandler

javaType="boolean"

jdbcType=”VARCHAR”

callback="org.apache.ibatis.sqlmap.extensions.YesNoBoolTypeHandlerCallback"/>

緩存設置：

<cacheModel id="product-cache" type ="LRU" readOnly=”true” serialize=”false”>

<flushInterval hours="24"/>

<flushOnExecute statement="insertProduct"/>

<flushOnExecute statement="updateProduct"/>

<flushOnExecute statement="deleteProduct"/>

<property name=”cache-size” value=”1000” />

</cacheModel>

<select id=”getProductList” cacheModel=”product-cache”>

select * from PRODUCT where PRD_CAT_ID = #value#

</select>

Readonly屬性默認為true，但若想要在返回的對象上做更改的話，設為false。

Serialize屬性設置為false后，該cache為全局共享cache，這種cache不能是readOnly的（因為沒意義）。

Cache的種類：

MEMORY——java特殊引用類實現的cache

<cacheModel id="product-cache" type="MEMORY">

<flushInterval hours="24"/>

<flushOnExecute statement="insertProduct"/>

<flushOnExecute statement="updateProduct"/>

<flushOnExecute statement="deleteProduct"/>

<property name=”reference-type” value=”WEAK” />

</cacheModel>

所有的referencetype：

?

LRU——最少使用算法的cache:

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

<flushInterval hours="24"/>

<flushOnExecute statement="insertProduct"/>

<flushOnExecute statement="updateProduct"/>

<flushOnExecute statement="deleteProduct"/>

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

</cacheModel>

FIFO——先進先出算法的cache：

<cacheModel id="product-cache" type="FIFO">

<flushInterval hours="24"/>

<flushOnExecute statement="insertProduct"/>

<flushOnExecute statement="updateProduct"/>

<flushOnExecute statement="deleteProduct"/>

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

</cacheModel>

OSCACHE——插件cache，opensymphony的cache實現，參見： http://www.opensymphony.com/oscache/

<cacheModel id="product-cache" type="OSCACHE">

<flushInterval hours="24"/>

<flushOnExecute statement="insertProduct"/>

<flushOnExecute statement="updateProduct"/>

<flushOnExecute statement="deleteProduct"/>

</cacheModel>

構造動態SQL語句：

<dynamic>元素： 總的包含元素，并提供最外層的開頭預設字、開始字符串和結束字符串。

prepend – 加在本體前面的預設字符 (可選)

open – 標識本體開始的開始字符串(可選)

close – 標識本題結束的結束字符串 (可選)

dynamic元素的removeFirstPrepend屬性是強制的，所以它的第一個子元素的prepend字符串在生成語句的時候總是會被取消掉。

?

二元操作標記：

<isEqual> 檢查一個屬性和一個給定值或另一個屬性是否相等。

<isNotEqual>檢查一個屬性和一個給定值或另一個屬性是否不相等。

<isGreaterThan> 檢查一個屬性是否大于一個給定值或另一個屬性。

<isGreaterEqual> 檢查一個屬性是否大于或等于一個給定值或另一個屬性。

<isLessThan> 檢查一個屬性是否小于一個給定值或另一個屬性。

<isLessEqual> 檢查一個屬性是否小于或等于一個給定值或另一個屬性。

屬性：

prepend – the overridable SQL part that will be prepended to the statement (optional)

property – the property to be compared (required)

compareProperty – the other property to be compared (required or compareValue)

compareValue – the value to be compared (required or compareProperty)

removeFirstPrepend – removes the prepend of the first nested content producing tag (true|false, optional)

open – the string with which to open the entire resulting body content (optional)

close – the string with which to close the entire resulting body content (optional)

?

舉例：

<isLessEqual prepend=”AND” property=”age” compareValue=”18”>

ADOLESCENT = ‘TRUE’

</isLessEqual>

?

一元判斷標記：

<isPropertyAvailable> Checks if a property is available (i.e is a property of the parameter bean)

<isNotPropertyAvailable> Checks if a property is unavailable (i.e not a property of the parameter bean)

<isNull> Checks if a property is null.

<isNotNull> Checks if a property is not null.

<isEmpty> Checks to see if the value of a Collection, String or String.valueOf() property

is null or empty (“” or size() < 1).

<isNotEmpty> Checks to see if the value of a Collection, String or String.valueOf() property

is not null and not empty (“” or size() < 1).

屬性：

prepend – the overridable SQL part that will be prepended to the statement (optional)

property – the property to be checked (required)

removeFirstPrepend – removes the prepend of the first nested content producing tag (true|false, optional)

open – the string with which to open the entire resulting body content (optional)

close – the string with which to close the entire resulting body content (optional)

舉例：

<isNotEmpty prepend=”AND” property=”firstName” >

FIRST_NAME=#firstName#

</isNotEmpty>

其它標記：

<isParameterPresent> Checks to see if the parameter object is present (not null).

<isNotParameterPresent> Checks to see if the parameter object is not present (null).

屬性：

prepend – the overridable SQL part that will be prepended to the statement (optional)

removeFirstPrepend – removes the prepend of the first nested content producing tag (true|false, optional)

open – the string with which to open the entire resulting body content (optional)

close – the string with which to close the entire resulting body content (optional)

舉例：

<isNotParameterPresent prepend=”AND”>

EMPLOYEE_TYPE = ‘DEFAULT’

</isNotParameterPresent>

?

<iterate> Iterates over a property that is an implementation java.util.Collection, or

java.util.Iterator, or is an array.

例：

<iterate prepend=”AND” property=”userNameList”

open=”(” close=”)” conjunction=”OR”>

username=#userNameList[]#

</iterate>

當集合類單獨被當作參數傳進此查詢時，也可以這么寫：

<iterate prepend=”AND” open=”(” close=”)” conjunction=”OR”>

username=#[]#

</iterate>

也可以像這樣選擇集合中對象的屬性：

<iterate prepend=”AND” property=”userList”

open=”(” close=”)” conjunction=”OR”>

firstname=#userList[].firstName# and

lastname=#userList[].lastName#

</iterate>

該標簽還可以自己嵌套：

<dynamic prepend="where">

<iterate property="orConditions" conjunction="or">

(

<iterate property="orConditions[].conditions"

conjunction="and">

$orConditions[].conditions[].condition$

#orConditions[].conditions[].value#

</iterate>

)

</iterate>

</dynamic>

若只是想生成一些簡單的動態sql語句，這樣寫就可以：

<select id=”getProduct” resultMap=”get-product-result”>

select * from PRODUCT order by $preferredOrder$

</select>

注意“$”，不是“#”，這是和為preparedStatement賦值的普通屬性的取得的不同之處。

iBatis學習筆記（映射文件小結）