看看 iBATIS SQL Maps 是怎樣從數據庫按照 one-to-many 關系查詢數據的：
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE sqlMap??? PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN"??? " http://www.ibatis.com/dtd/sql-map-2.dtd "> <sqlMap>??? <resultMap id="get-autoInfo-result" class="bo.AutoInfo">??????? <result property="id" column="auto_id"/>??????? <result property="licensePlate" column="license_plate"/>??? </resultMap> ??? <resultMap id="get-people-result" class="bo.People">??????? <result property="id" column="owner_id"/>??????? <result property="name" column="name"/>??????? <result property="address" column="address"/>??????? <result property="autoInfoList" column="owner_id" select="getAutoInfo"/>??? </resultMap> ? <select id="getPeople" resultMap="get-people-result" parameterClass="bo.People">?????? <![CDATA[ ?????? select * from people where owner_id=#id#??????? ]]>? </select> ? <select id="getAutoInfo" resultMap="get-autoInfo-result" parameterClass="int">?????? <![CDATA[ ?????? select * from auto_info where owner_no=#id#??????? ]]>? </select></sqlMap> ? resultMap id="get-autoInfo-result" class="bo.AutoInfo"
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMap??? PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN"??? " http://www.ibatis.com/dtd/sql-map-2.dtd ">
<sqlMap>??? <resultMap id="get-autoInfo-result" class="bo.AutoInfo">??????? <result property="id" column="auto_id"/>??????? <result property="licensePlate" column="license_plate"/>??? </resultMap>
??? <resultMap id="get-people-result" class="bo.People">??????? <result property="id" column="owner_id"/>??????? <result property="name" column="name"/>??????? <result property="address" column="address"/>??????? <result property="autoInfoList" column="owner_id" select="getAutoInfo"/>??? </resultMap>
? <select id="getPeople" resultMap="get-people-result" parameterClass="bo.People">?????? <![CDATA[ ?????? select * from people where owner_id=#id#??????? ]]>? </select>
? <select id="getAutoInfo" resultMap="get-autoInfo-result" parameterClass="int">?????? <![CDATA[ ?????? select * from auto_info where owner_no=#id#??????? ]]>? </select></sqlMap>
??? resultMap 是 iBATIS SQL Maps 框架中重要組件之一， 你也許還記得 resultClass 吧？兩者概念基本一致。 resultMap 則是可定制 Mapped Statement 返回對象的。可定制表現在：比如我有一張數據表包含 10 個字段，但我的 POJO 只定義了 5 個屬性，這時，只要取出我關心的字段就行。和 Hibernate 類似， resultMap 的 result 元素定義了 POJO 屬性到數據表字段的映射。
需要說明的是 result 元素 select 屬性，該屬性類似于加載了一個子查詢，并自動載入，如果這個子查詢包含多條結果，就自動把結果裝載進 List 類型。該 result 元素 對應 People 中的屬性為 ” autoInfoList” ，而由 Hibernate 生成的 People 卻是 Set 類型 ” autoInfoSet” ，這兩種類型無法直接互轉，所以我修改了 POJO 屬性。
select id="getPeople" resultMap="get-people-result" parameterClass="bo.People"
此為 select 類型 Mapped Statement 。傳入 People 實例，返回為自己定制、包含 ” autoInfoList” 的 People 實例。傳入 People 類型參數是為了便于多字段匹配查詢，今天也許我只需利用 People.id 單字段匹配就能得到結果，但下次的新需求也許是 People.address 和 People.name 聯合匹配！
相應程序代碼：
package test; import java.io.Reader;import java.util.List; import com.ibatis.sqlmap.client.*;import com.ibatis.common.resources.*; import bo.*; public class AutoMag { ?private Reader reader;?private People people=new People();?private SqlMapClient sqlMap;?private String resource = "SqlMapConfig.xml";??public People findPeople() throws Exception{??reader = Resources.getResourceAsReader(resource);?? sqlMap=SqlMapClientBuilder.buildSqlMapClient(reader);???? people.setId(new Integer("1"));?? people=(People)sqlMap.queryForObject("getPeople",people);?? return people;?}} ??
package test;
import java.io.Reader;import java.util.List;
import com.ibatis.sqlmap.client.*;import com.ibatis.common.resources.*;
import bo.*;
public class AutoMag {
?private Reader reader;?private People people=new People();?private SqlMapClient sqlMap;?private String resource = "SqlMapConfig.xml";??public People findPeople() throws Exception{??reader = Resources.getResourceAsReader(resource);?? sqlMap=SqlMapClientBuilder.buildSqlMapClient(reader);???? people.setId(new Integer("1"));?? people=(People)sqlMap.queryForObject("getPeople",people);?? return people;?}}
sqlMap.queryForObject(String arg0, Object arg1)
此方法類似 Hibernate 的 session.load(…) ，傳入 Mapped Statement id ，再傳入包含主鍵的對象實例。除我上面列舉的 sqlMap. queryForObject(String arg0, Object arg1) 外，還有重載方法 ：
people=(People)sqlMap.queryForObject("getPeople",people);
替換成
sqlMap.queryForObject("getPeople", people, people);
下面我會講到如何以集合類持有多個 People 對象實例。
在車輛管理應用中，需要把人員一一列出，選中某個再顯示詳細內容。類似于這樣的需求， iBATIS SQL Maps 引入 sqlMap.queryForList(String arg0, Object arg1) 來滿足。
?????? 還記得我們的映射文件怎么寫的？對了，傳入主鍵值再查詢！
但是，新需求不要任何條件，直接列出人員啊！難道還要再添加新的 Mapped Statement 來滿足？動態 Mapped Statement 能滿足在不改變映射文件的前提下提供有參數和無差數查詢：
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE sqlMap??? PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN"??? " http://www.ibatis.com/dtd/sql-map-2.dtd "> <sqlMap>??? <resultMap id="get-autoInfo-result" class="bo.AutoInfo">??????? <result property="id" column="auto_id"/>??????? <result property="licensePlate" column="license_plate"/>??? </resultMap> ??? <resultMap id="get-people-result" class="bo.People">??????? <result property="id" column="owner_id"/>??????? <result property="name" column="name"/>??????? <result property="address" column="address"/>??????? <result property="autoInfoList" column="owner_id" select="getAutoInfo"/>??? </resultMap> ? <select id="getPeople" resultMap="get-people-result" parameterClass="bo.People">?????? <![CDATA[ ?????? select * from people?????? ]]>?????? <dynamic prepend="where">???????? <isNotNull property="id">?????????? <![CDATA[ ?????????? owner_id=#id#?????????? ]]>???????? </isNotNull>?????? </dynamic>? </select> ? <select id="getAutoInfo" resultMap="get-autoInfo-result" parameterClass="int"??????????????????? resultClass="bo.AutoInfo">?????? <![CDATA[ ?????? select * from auto_info where owner_no=#id#??????? ]]>? </select></sqlMap> ?
? <select id="getPeople" resultMap="get-people-result" parameterClass="bo.People">?????? <![CDATA[ ?????? select * from people?????? ]]>?????? <dynamic prepend="where">???????? <isNotNull property="id">?????????? <![CDATA[ ?????????? owner_id=#id#?????????? ]]>???????? </isNotNull>?????? </dynamic>? </select>
? <select id="getAutoInfo" resultMap="get-autoInfo-result" parameterClass="int"??????????????????? resultClass="bo.AutoInfo">?????? <![CDATA[ ?????? select * from auto_info where owner_no=#id#??????? ]]>? </select></sqlMap>
dynamic prepend="where"
select 類型 Mapped Statement 的本質并無改變，只是通過 dynamic 元素定義了動態 where 子句。 dynamic 元素屬性 prepend=”where” 在一元判定元素 isNotNull 返回 ”true” 時有效。 isNotNull 元素屬性 property="id" 用于判定 People.id 是否為 null ，假如未傳入包含主鍵值的 People 實例， dynamic 元素將會無效，反之則亦然，這樣就達到了在不改變映射文件的前提下實現動態 SQL 的目的。關于一元判定元素和二元判定元素的詳細資料請查閱官方文檔，如能恰當利用這些判定元素，動態 SQL 將會更靈活。
package test; import java.io.Reader;import java.util.List; import com.ibatis.sqlmap.client.*;import com.ibatis.common.resources.*; import bo.*; public class AutoMag { ?private Reader reader;?private People people=new People();?private SqlMapClient sqlMap;?private String resource = "SqlMapConfig.xml";??public List findPeople() throws Exception{??reader = Resources.getResourceAsReader(resource);??sqlMap=SqlMapClientBuilder.buildSqlMapClient(reader);????? ??List list=sqlMap.queryForList("getPeople",null);??return list;?}} ?
?private Reader reader;?private People people=new People();?private SqlMapClient sqlMap;?private String resource = "SqlMapConfig.xml";??public List findPeople() throws Exception{??reader = Resources.getResourceAsReader(resource);??sqlMap=SqlMapClientBuilder.buildSqlMapClient(reader);????? ??List list=sqlMap.queryForList("getPeople",null);??return list;?}}
傳入 Mapped Statement id ，再傳入未包含主鍵的對象實例，也可以干脆傳 null 進去。重載方法 sqlMap .queryForList(String arg0, Object arg1, int arg2, int arg3) 用于分頁 ， arg2 、 arg3 分別代表頁碼和每頁個數。
int arg2, int arg3
好了，使用動態 Mapped Statement ，再試試 queryForObject(String arg0, Object arg1) 吧！請注意！引用、轉貼本文應注明原作者：Rosen Jiang 以及出處：http://www.aygfsteel.com/rosen
Powered by: BlogJava Copyright © Rosen