iBatis開(kāi)發(fā)詳解（9）---查詢(xún)復(fù)雜集合

     通常我們使用iBatis的select查詢(xún)都是映射的簡(jiǎn)單對(duì)象，即便在一個(gè)查詢(xún)中連接多個(gè)表也是如此，那么既然iBatis是SQL Mapper，也就是說(shuō)它可以映射復(fù)雜集合，我們來(lái)看看如何讓對(duì)象模型向數(shù)據(jù)模型（關(guān)系型數(shù)據(jù)模型）靠攏。 

    我們的數(shù)據(jù)庫(kù)設(shè)計(jì)如下： 

CREATE TABLE `user` (  
`userId`  int(11) NOT NULL AUTO_INCREMENT ,  
`userName`  varchar(50) NULL DEFAULT NULL ,  
`password`  varchar(32) NULL DEFAULT NULL ,  
`mobile`  varchar(11) NULL DEFAULT NULL ,  
`email`  varchar(50) NULL DEFAULT NULL ,  
`age`  int(3) NULL DEFAULT NULL ,  
PRIMARY KEY (`userId`)  
)  

CREATE TABLE `order` (  
`orderId`  int(11) NOT NULL AUTO_INCREMENT ,  
`orderName`  varchar(50) NULL DEFAULT NULL ,  
`generateTime`  datetime NULL DEFAULT NULL ,  
`userId`  int(11) NULL DEFAULT NULL ,  
PRIMARY KEY (`orderId`)  
)  

CREATE TABLE `orderItem` (  
`orderItemId`  int(11) NOT NULL AUTO_INCREMENT ,  
`itemName`  varchar(50) NULL DEFAULT NULL ,  
`quantity`  int(3) NULL DEFAULT NULL ,  
`price`  float NULL DEFAULT NULL ,  
`orderId`  int(11) NOT NULL ,  
PRIMARY KEY (`orderItemId`)  
)  

package ibatis.model;  
public class OrderItem implements java.io.Serializable {  
    private Integer orderItemId;  
    private String itemName;  
    private int quantity;  
    private float price;  
    private Integer orderId;  
    public OrderItem() {  
    }  
    public OrderItem(Integer orderItemId, String itemName, int quantity,  
            float price, Integer orderId) {  
        super();  
        this.orderItemId = orderItemId;  
        this.itemName = itemName;  
        this.quantity = quantity;  
        this.price = price;  
        this.orderId = orderId;  
    }  
    //Setters And Getters  
    public String toString() {  
        return "OrderItem [itemName=" + itemName + ", orderItemId=" + orderItemId+ ", orderId=" + orderId + ", price=" + price + ", quantity="  
                + quantity + "]";  
    }  
}  

package ibatis.model;  
import java.util.Arrays;  
public class OrderInfo {  
    private Order order;  
    private OrderItem[] orderItemList;  
    public Order getOrder() {  
        return order;  
    }  
    public void setOrder(Order order) {  
        this.order = order;  
    }  
    public OrderItem[] getOrderItemList() {  
        return orderItemList;  
    }  
    public void setOrderItemList(OrderItem[] orderItemList) {  
        this.orderItemList = orderItemList;  
    }  
    public String toString() {  
        return "OrderInfo [order=" + order + ", orderItemList="  
                + Arrays.toString(orderItemList) + "]";  
    }  
}  

package ibatis.model;  
import java.util.Arrays;  
public class UserInfo {  
    private User user;  
    private OrderInfo[] orderList;  
    public User getUser() {  
        return user;  
    }  
    public void setUser(User user) {  
        this.user = user;  
    }  
    public OrderInfo[] getOrderList() {  
        return orderList;  
    }  
    public void setOrderList(OrderInfo[] orderList) {  
        this.orderList = orderList;  
    }  
    public String toString() {  
        return "UserInfo [orderList=" + Arrays.toString(orderList) + ", user="+ user + "]";  
    }  
}  

<resultMap class="ibatis.model.OrderItem" id="ResultOrderItemMap">  
    <result property="orderId" column="orderId" />  
    <result property="orderItemId" column="orderItemId" />  
    <result property="itemName" column="itemName" />  
    <result property="quantity" column="quantity" />  
    <result property="price" column="price" />  
</resultMap>  

<resultMap class="ibatis.model.OrderInfo" id="ResultOrderInfoMap">  
    <result property="order.orderId" column="orderId" />  
    <result property="order.orderName" column="orderName" />  
    <result property="order.generateTime" column="generateTime" />  
    <result property="orderItemList" select="pioneer.getOrderItemList"  
        column="orderId" />  
</resultMap>  
<select id="getOrderItemList" resultMap="ResultOrderItemMap">  
    select  
        orderId,  
        orderItemId,  
        itemName,  
        quantity,  
        price  
    from  
        orderItem  
    where  
        orderId = #orderId#  
</select>  

<resultMap class="ibatis.model.UserInfo" id="ResultUserInfoMap">  
    <result property="user.userId" column="userId" />  
    <result property="user.userName" column="userName"/>  
    <result property="orderList" select="pioneer.getOrderInfoList"  
        column="userId" />  
</resultMap>  
<select id="getOrderInfoList" resultMap="ResultOrderInfoMap">  
    select  
        orderId,  
        orderName,  
        generateTime  
    from  
        test.order  
    where  
        userId = #userId#  
</select>  

<select id="getUserInfoList" resultMap="ResultUserInfoMap">  
    select  
        userId,  
        userName  
    from  
        user  
</select>  

package ibatis;  
import ibatis.model.UserInfo;  
import java.io.IOException;  
import java.io.Reader;  
import java.util.ArrayList;  
import com.ibatis.common.resources.Resources;  
import com.ibatis.sqlmap.client.SqlMapClient;  
import com.ibatis.sqlmap.client.SqlMapClientBuilder;  
public class UserInfoDemo {  
    private static String config = "ibatis/SqlMapConfig.xml";  
    private static Reader reader;  
    private static SqlMapClient sqlMap;  
    static {  
        try {  
            reader = Resources.getResourceAsReader(config);  
        } catch (IOException e) {  
            e.printStackTrace();  
        }  
        sqlMap = SqlMapClientBuilder.buildSqlMapClient(reader);  
    }  
    public static void main(String[] args) throws Exception {  
        long start = System.currentTimeMillis();  
        ArrayList<UserInfo> userInfoList = (ArrayList<UserInfo>) sqlMap  
                .queryForList("pioneer.getUserInfoList");  
        long end = System.currentTimeMillis();  
        System.out.println(userInfoList);  
        System.out.println((end - start) + " ms");  
    }}  

 查詢(xún)得到的內(nèi)容為： 
    [UserInfo [orderList=[OrderInfo [order=Order [generateTime=Mon Aug 27 21:35:27 CST 2012, orderId=1, orderItems=null, orderName=Mobile Phone], orderItemList=[OrderItem [itemName=Moto MB525, orderItemId=1, orderId=1, price=1000.0, quantity=1], OrderItem [itemName=Moto MB526, orderItemId=2, orderId=1, price=1200.0, quantity=1]]], OrderInfo [order=Order [generateTime=Mon Aug 27 22:28:38 CST 2012, orderId=2, orderItems=null, orderName=Laptop], orderItemList=[OrderItem [itemName=Lenovo X201, orderItemId=3, orderId=2, price=5000.0, quantity=1], OrderItem [itemName=Lenovo X220, orderItemId=4, orderId=2, price=7000.0, quantity=1]]]], user=User [age=0, email=null, mobile=null, password=null, userId=1, userName=Sarin]]] 

    他們都是以對(duì)象的形式來(lái)描述的，因?yàn)槲覀兠鼪](méi)有查詢(xún)user的email，mobile等信息，所以它們是null。 

    程序編寫(xiě)完了，但是問(wèn)題隨之而來(lái)。先看這么一大堆的輸出，我們僅僅有1個(gè)用戶(hù)的兩個(gè)訂單，每個(gè)訂單僅包含2個(gè)項(xiàng)。如果我們的系統(tǒng)內(nèi)有10000個(gè)用戶(hù)，每個(gè)用戶(hù)下了100個(gè)訂單，每個(gè)訂單有5項(xiàng)，那么一次查詢(xún)結(jié)果就會(huì)生成500萬(wàn)個(gè)對(duì)象，顯然在數(shù)據(jù)不是很多時(shí)，就已經(jīng)帶來(lái)了問(wèn)題。首先這是一個(gè)數(shù)據(jù)庫(kù)I/O的問(wèn)題，大量數(shù)據(jù)庫(kù)I/O和內(nèi)存對(duì)象，降低了性能，消耗了資源。其次是N+1查詢(xún)問(wèn)題。 

    N+1問(wèn)題也好理解，在本例中，我們要查詢(xún)一個(gè)用戶(hù)的N個(gè)訂單，還要查詢(xún)這N個(gè)訂單中每個(gè)訂單的N個(gè)訂單項(xiàng)，就產(chǎn)生了N+1查詢(xún)問(wèn)題。 
    iBatis提供了針對(duì)每個(gè)問(wèn)題的解決方案，但是卻不能同時(shí)解決這兩個(gè)問(wèn)題。 

    針對(duì)數(shù)據(jù)庫(kù)I/O，我們很容易想到延遲加載的特性，就是對(duì)于所有數(shù)據(jù)并不是一次全部查出，在需要的時(shí)候繼續(xù)進(jìn)行查詢(xún)，那么就會(huì)大幅度減少數(shù)據(jù)庫(kù)的I/O，開(kāi)啟iBatis的延遲加載特性非常簡(jiǎn)單，只需修改如下XML設(shè)置即可： 

<?xml version="1.0" encoding="UTF-8" ?>  
<!DOCTYPE sqlMapConfig        
    PUBLIC "-//ibatis.apache.org//DTD SQL Map Config 2.0//EN"        
    "http://ibatis.apache.org/dtd/sql-map-config-2.dtd">  
<sqlMapConfig>  
    ......  
    <settings useStatementNamespaces="true" lazyLoadingEnabled="true" />  
    ......  
</sqlMapConfig>  

   可以看到，在僅有的幾條數(shù)據(jù)時(shí)，所消耗的時(shí)間也大幅減少，說(shuō)明延遲加載特性已經(jīng)啟用。 
   下面我們來(lái)看針對(duì)N+1查詢(xún)問(wèn)題的解決方案，那就是使用iBatis在resultMap中為我們提供的groupBy屬性。我們將上面的XML文件修改如下： 

<resultMap class="ibatis.model.UserInfo" id="ResultUserInfoMapN" groupBy="user.userId">  
    <result property="user.userId" column="userId" />  
    <result property="user.userName" column="userName"/>  
    <result property="orderList" resultMap="pioneer.ResultOrderInfoMapN" />  
</resultMap>  
<resultMap class="ibatis.model.OrderInfo" id="ResultOrderInfoMapN" groupBy="order.orderId">  
    <result property="order.orderId" column="orderId" />  
    <result property="order.orderName" column="orderName" />  
    <result property="order.generateTime" column="generateTime" />  
    <result property="orderItemList" resultMap="pioneer.ResultOrderItemMapN" />  
</resultMap>  
<resultMap class="ibatis.model.OrderItem" id="ResultOrderItemMapN">  
    <result property="orderId" column="orderId" />  
    <result property="orderItemId" column="orderItemId" />  
    <result property="itemName" column="itemName" />  
    <result property="quantity" column="quantity" />  
    <result property="price" column="price" />  
</resultMap>  
<select id="getUserInfoListN" resultMap="ResultUserInfoMapN">  
    select  
        user.userId as userId,  
        user.userName as userName,  
        test.order.orderId as orderId,  
        test.order.orderName as orderName,  
        test.order.generateTime as generateTime,  
        orderItem.*  
    from  
        user join test.order on user.userId=test.order.userId join orderItem on test.order.orderId=orderItem.orderId  
    order by  
        userId,test.order.orderId,orderItemId  
</select>  

    也可以看到查詢(xún)效率的顯著提升。 
    綜上所述，延遲加載適用于大型數(shù)據(jù)集合，但是并非其中的每條記錄都會(huì)被用到。此方法前期的性能大幅度提高，但是后期仍需加載所需數(shù)據(jù)。而N+1查詢(xún)解決方案適用于小型數(shù)據(jù)集合或者是所有數(shù)據(jù)都肯定會(huì)被用到的數(shù)據(jù)集合，使得整體性能得到提高。