6. 使用高级查询技术

1. 在ibatis中使用XML

1.1 XML参数

既可以通过String值，也可以通过DOM对象

一段格式良好的XML片段。

<parameter><accountId>3</accountId></parameter>

例如：

<select id="getByXmlId" resultClass="Account" parameterClass="xml">
	select
	accountId,
	username,
	password,
	firstName,
	lastName,
	address1,
	address2,
	city,
	state,
	postalCode,
	country
	from Account
	where accountId = #accountId#
</select>

String parameter = "<parameter><accountId>3</accountId></parameter>";
Account account = (Account) sqlMapClient.queryForObject(
"Account.getByXmlId",
parameter);

同样，也可以使用DOM对象来给ibatis传参数

<select id="getByDomId" resultClass="Account" parameterClass="dom">
	select
	accountId,
	username,
	password,
	firstName,
	lastName,
	address1,
	address2,
	city,
	state,
	postalCode,
	country
	from Account
	where accountId = #accountId#
</select>

Document parameterDocument = DocumentBuilderFactory.newInstance()
.newDocumentBuilder().newDocument();
Element paramElement = parameterDocument
.createElement("parameterDocument");
Element accountIdElement = parameterDocument
.createElement("accountId");
accountIdElement.setTextContent("3");
paramElement.appendChild(accountIdElement);
parameterDocument.appendChild(paramElement);
Account account = (Account) sqlMapClient.queryForObject(
"Account.getByXmlId", parameterDocument);

1.2 XML结果

当执行一条返回XML的已映射语句时，ibatis会为 每一个返回对象返回一份完整的XML文档。

<select id="getByIdValueXml" resultClass="xml" xmlResultName="account">
	select
	accountId,
	username,
	password
	from Account
	where accountId = #value#
</select>

String xmlData = (String) sqlMap.queryForObject(
"Account.getByIdValueXml",
new Integer(1));

返回结果：

<?xml version="1.0" encoding="UTF-8"?>
<account>
	<accountid>1</accountid>
	<username>lmeadors</username>
	<password>blah</password>
</account>

利用反射将bean自动转换为XML：（片段）

public class XmlReflector {
	private Class sourceClass;
	private BeanInfo beanInfo;
	private String name;

	XmlReflector(Class sourceClass, String name) throws Exception {
		this.sourceClass = sourceClass;
		this.name = name;
		beanInfo = Introspector.getBeanInfo(sourceClass);
	}

	public String convertToXml(Object o) throws Exception {
		StringBuffer returnValue = new StringBuffer("");
		if (o.getClass().isAssignableFrom(sourceClass)) {
			PropertyDescriptor[] pd = beanInfo.getPropertyDescriptors();
			if (pd.length > 0) {
				returnValue.append("<" + name + ">");
				for (int i = 0; i < pd.length; i++) {
					returnValue.append(getProp(o, pd[i]));
				}
				returnValue.append("</" + name + ">");
			} else {
				returnValue.append("<" + name + "/>");
			}
		} else {
			throw new ClassCastException(
					"Class " + o.getClass().getName() + " is not compatible with " + sourceClass.getName());
		}
		return returnValue.toString();
	}

	private String getProp(Object o, PropertyDescriptor pd) throws Exception {
		StringBuffer propValue = new StringBuffer("");
		Method m = pd.getReadMethod();
		Object ret = m.invoke(o);
		if (null == ret) {
			propValue.append("<" + pd.getName() + "/>");
		} else {
			propValue.append("<" + pd.getName() + ">");
			propValue.append(ret.toString());
			propValue.append("</" + pd.getName() + ">");
		}
		return propValue.toString();
	}
}

2. 用已映射语句关联对象

2.1 复杂集合

Mapping a complex collection

</pre><pre name="code" class="html"><?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="Ch6">
	<resultMap id="ResultAccountInfoMap" class="org.apache.mapper2.examples.bean.AccountInfo">
		<result property="account.accountId" column="accountId" />
		<result property="orderList" select="Ch6.getOrderInfoList"
			column="accountId" />
	</resultMap>
	<resultMap id="ResultOrderInfoMap" class="org.apache.mapper2.examples.bean.OrderInfo">
		<result property="order.orderId" column="orderId" />
		<result property="orderItemList" column="orderId" select="Ch6.getOrderItemList" />
	</resultMap>
	<resultMap id="ResultOrderItemMap" class="org.apache.mapper2.examples.bean.OrderItem">
		<result property="orderId" column="orderId" />
		<result property="orderItemId" column="orderItemId" />
	</resultMap>
	<select id="getAccountInfoList" resultMap="ResultAccountInfoMap">
		select accountId
		from Account
	</select>
	<select id="getOrderInfoList" resultMap="ResultOrderInfoMap">
		select orderId
		from orders
		where accountId = #value#
	</select>
	<select id="getOrderItemList" resultMap="ResultOrderItemMap">
		select
		orderId,
		orderItemId
		from orderItem
		where orderid = #value#
	</select>
</sqlMap>

使用select属性带来问题：

1、数据库I/O

2. N+1查询

2.2 延迟加载

lazyLoadingEnabled

2.3  避免N+1查询问题

一种方式：使用ibatis提供的groupBy 属性。

另一种方式：使用一个称为RowHandler的自定义组件。

Using the N+1 Selects solution

<resultMap id="ResultAccountInfoNMap" class="AccountInfo"
<span style="white-space:pre">	</span>groupBy="account.accountId"><span style="white-space:pre">					</span>//groupBy属性的改变决定创建新实例
<span style="white-space:pre">	</span><result property="account.accountId" column="accountId" />
<span style="white-space:pre">	</span><result property="orderList" resultMap="Ch6.ResultOrderInfoNMap" />
</resultMap>
<resultMap id="ResultOrderInfoNMap" class="OrderInfo" groupBy="order.orderId">
<span style="white-space:pre">	</span><result property="order.orderId" column="orderId" />
<span style="white-space:pre">	</span><result property="orderItemList" resultMap="Ch6.ResultOrderItemNMap" />
</resultMap>
<resultMap id="ResultOrderItemNMap" class="OrderItem">
<span style="white-space:pre">	</span><result property="orderId" column="orderId" />
<span style="white-space:pre">	</span><result property="orderItemId" column="orderItemId" />
</resultMap>
<select id="getAccountInfoListN" resultMap="ResultAccountInfoNMap">
<span style="white-space:pre">	</span>select
<span style="white-space:pre">	</span>account.accountId as accountid,
<span style="white-space:pre">	</span>orders.orderid as orderid,
<span style="white-space:pre">	</span>orderitem.orderitemid as orderitemid
<span style="white-space:pre">	</span>from account
<span style="white-space:pre">	</span>join orders on account.accountId = orders.accountId
<span style="white-space:pre">	</span>join orderitem on orders.orderId = orderitem.orderId
<span style="white-space:pre">	</span>order by accountId, orderid, orderitemid
</select>

groupBy属性使性能得到改善，但内存消耗仍然和非延迟加载版一样

3. 继承

映射继承：ibatis使用一个特殊的被称为鉴别器（discriminator）的结果映射来支持继承体系。和switch语句工作原理相似。

<resultMap id="document" class="testdomain.Document">
	<result property="id" column="DOCUMENT_ID" />
	<result property="title" column="TITLE" />
	<result property="type" column="TYPE" />
	<discriminator column="TYPE" javaType="string">
		<subMap value="Book" resultMap="book" />
		<subMap value="Newspaper" resultMap="news" />
	</discriminator>
</resultMap>

如果鉴别器不能找到一个值来匹配其中的某个子映射，那么就会应用父结果映射。

<resultMap id="book" class="testdomain.Book" extends="document">
	<result property="pages" column="DOCUMENT_PAGENUMBER" />
</resultMap>

4 其他用途

4.1 使用语句类型和DDL

<statement>   没有对应的方法可以调用。

<statement id="dropTable">
DROP TABLE Account CASCADE;
</statement>

<pre name="code" class="java">sqlMap.update("Account.dropTable", null);

4.2 处理超大型数据集

RowHandler接口用来解决大型数据集问题。

public interface RowHandler {
void handleRow(Object valueObject);
}

允许在某个已映射语句的结果集的处理中插入自己的动作。

public class AccountXmlRowHandler implements RowHandler {
	private StringBuffer xmlDocument = new StringBuffer("<AccountList>");
	private String returnValue = null;

	public void handleRow(Object valueObject) {
		Account account = (Account) valueObject;
		xmlDocument.append("<account>");
		xmlDocument.append("<accountId>");
		xmlDocument.append(account.getAccountId());
		xmlDocument.append("</accountId>");
		xmlDocument.append("<username>");
		xmlDocument.append(account.getUsername());
		xmlDocument.append("</username>");
		xmlDocument.append("<password>");
		xmlDocument.append(account.getPassword());
		xmlDocument.append("</password>");
		xmlDocument.append("</account>");
	}

	public String getAccountListXml() {
		if (null == returnValue) {
			xmlDocument.append("</AccountList>");
			returnValue = xmlDocument.toString();
		}
		return returnValue;
	}
}

AccountXmlRowHandler rh = new AccountXmlRowHandler();
sqlMapClient.queryWithRowHandler("Account.getAll", null, rh);
String xmlData = rh.getAccountListXml();

需要提供一份被订购的产品的列表，以及一份订购这些产品的账户列表，一份账户列表（表中每一个账户关联一份它订购的产品的制造商列表）

<resultMap id="AmpRHExample"
	class="org.apache.mapper2.examples.chapter6.AccountManufacturerProduct">
	<result property="account.accountId" column="accountId" />
	<result property="manufacturer.manufacturerId" column="manufacturerId" />
	<result property="product.productId" column="productId" />
</resultMap>
<select id="AMPRowHandlerExample" resultMap="AmpRHExample">
	select distinct
	p.productId as productId,
	o.accountId as accountId,
	m.manufacturerId as manufacturerId
	from product p
	join manufacturer m
	on p.manufacturerId = m.manufacturerId
	join orderitem oi
	on oi.productId = p.productId
	join orders o
	on oi.orderId = o.orderId
	order by 1,2,3
</select>

public class AMPRowHandler implements RowHandler {
	private Map<Integer, AccountManufacturers> accountMap = new HashMap<Integer, AccountManufacturers>();
	private Map<Integer, Manufacturer> manufacturerMap = new HashMap<Integer, Manufacturer>();
	private Map<Integer, ProductAccounts> productMap = new HashMap<Integer, ProductAccounts>();
	private List<ProductAccounts> productAccountList = new ArrayList<ProductAccounts>();
	private List<AccountManufacturers> accountManufacturerList = new ArrayList<AccountManufacturers>();

	public void handleRow(Object valueObject) {
		AccountManufacturerProduct amp;
		amp = (AccountManufacturerProduct) valueObject;
		Account currentAccount = amp.getAccount();
		Manufacturer currentMfgr = amp.getManufacturer();
		AccountManufacturers am;
		ProductAccounts pa;
		Product currentProduct = amp.getProduct();
		if (null == accountMap.get(currentAccount.getAccountId())) {
			// this is the first time we have seen this account
			am = new AccountManufacturers();
			am.setAccount(currentAccount);
			accountMap.put(currentAccount.getAccountId(), am);
			accountManufacturerList.add(am);
		} else {
			// Use the accoutn from the account map
			am = accountMap.get(currentAccount.getAccountId());
			currentAccount = am.getAccount();
		}
		// am is now the current account / manufacturerlist
		if (null == manufacturerMap.get(currentMfgr.getManufacturerId())) {
			// we have not seen this manufacturer yet
			manufacturerMap.put(currentMfgr.getManufacturerId(), currentMfgr);
		} else {
			// we already have this manufacturer loaded, reuse it
			currentMfgr = manufacturerMap.get(currentMfgr.getManufacturerId());
		}
		am.getManufacturerList().add(currentMfgr);
		if (null == productMap.get(currentProduct.getProductId())) {
			// this is a new product
			pa = new ProductAccounts();
			pa.setProduct(currentProduct);
			productMap.put(currentProduct.getProductId(), pa);
			productAccountList.add(pa);
		} else {
			// this prodcut has been loaded already
			pa = productMap.get(currentProduct.getProductId());
		}
		// pa is now the current product's product / account list
		pa.getAccountList().add(currentAccount);
		am.getManufacturerList().add(currentMfgr);
	}

	public List<ProductAccounts> getProductAccountList() {
		return productAccountList;
	}

	public List<AccountManufacturers> getAccountManufacturerList() {
		return accountManufacturerList;
	}

	public Map<Integer, ProductAccounts> getProductMap() {
		return productMap;
	}

	public Map<Integer, AccountManufacturers> getAccountMap() {
		return accountMap;
	}
}