iBatis開發(fā)詳解（9）---查詢復(fù)雜集合
通常我們使用iBatis的select查詢都是映射的簡(jiǎn)單對(duì)象，即便在一個(gè)查詢中連接多個(gè)表也是如此，那么既然iBatis是SQL Mapper，也就是說它可以映射復(fù)雜集合，我們來看看如何讓對(duì)象模型向數(shù)據(jù)模型（關(guān)系型數(shù)據(jù)模型）靠攏。
我們的數(shù)據(jù)庫設(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`)
)
下面我們?cè)O(shè)計(jì)POJO來描述這三個(gè)對(duì)象：
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 + "]";
}
} 那么實(shí)體對(duì)象和數(shù)據(jù)表我們就都有了，下面來編寫iBatis的sqlMap文件來描述它們之間的關(guān)系，我們自定義resultMap來說明：
<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>
那么示例程序就很簡(jiǎn)單了：
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");
}}
查詢得到的內(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ì)象的形式來描述的，因?yàn)槲覀兠鼪]有查詢user的email，mobile等信息，所以它們是null。
程序編寫完了，但是問題隨之而來。先看這么一大堆的輸出，我們僅僅有1個(gè)用戶的兩個(gè)訂單，每個(gè)訂單僅包含2個(gè)項(xiàng)。如果我們的系統(tǒng)內(nèi)有10000個(gè)用戶，每個(gè)用戶下了100個(gè)訂單，每個(gè)訂單有5項(xiàng)，那么一次查詢結(jié)果就會(huì)生成500萬個(gè)對(duì)象，顯然在數(shù)據(jù)不是很多時(shí)，就已經(jīng)帶來了問題。首先這是一個(gè)數(shù)據(jù)庫I/O的問題，大量數(shù)據(jù)庫I/O和內(nèi)存對(duì)象，降低了性能，消耗了資源。其次是N+1查詢問題。
N+1問題也好理解，在本例中，我們要查詢一個(gè)用戶的N個(gè)訂單，還要查詢這N個(gè)訂單中每個(gè)訂單的N個(gè)訂單項(xiàng)，就產(chǎn)生了N+1查詢問題。
iBatis提供了針對(duì)每個(gè)問題的解決方案，但是卻不能同時(shí)解決這兩個(gè)問題。
針對(duì)數(shù)據(jù)庫I/O，我們很容易想到延遲加載的特性，就是對(duì)于所有數(shù)據(jù)并不是一次全部查出，在需要的時(shí)候繼續(xù)進(jìn)行查詢，那么就會(huì)大幅度減少數(shù)據(jù)庫的I/O，開啟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í)間也大幅減少，說明延遲加載特性已經(jīng)啟用。
下面我們來看針對(duì)N+1查詢問題的解決方案，那就是使用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>
也可以看到查詢效率的顯著提升。
綜上所述，延遲加載適用于大型數(shù)據(jù)集合，但是并非其中的每條記錄都會(huì)被用到。此方法前期的性能大幅度提高，但是后期仍需加載所需數(shù)據(jù)。而N+1查詢解決方案適用于小型數(shù)據(jù)集合或者是所有數(shù)據(jù)都肯定會(huì)被用到的數(shù)據(jù)集合，使得整體性能得到提高。
更多建議：