Normally, we only worked with a single object type in the results, even when joining multiple tables. If you have more complex objects, you can also use iBATIS to load them.
This capability is useful if you like to have your application's model look like your data model. It is possible to use iBATIS to define your data model in terms of related objects, and have iBATIS load them all at once. For example, if you have a database in which Account records have related Order records that have related Orderitem records, those relationships can be set up so that when you request an Account, you also get all of the Order objects and all of the OrderItem objects as well.
<?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">
<!-- Part 1 -->
<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>
<!-- Part 2 -->
<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>
<!-- Part 3 -->
<resultMap id="ResultOrderItemMap" class="org.apache.mapper2.examples.bean.OrderItem">
<result property="orderId" column="orderId" />
<result property="orderItemId" column="orderItemId" />
</resultMap>
<!-- Part 4 -->
<select id="getAccountInfoList" resultMap="ResultAccountInfoMap" >
select accountId from Account </select>
<!-- Part 5 -->
<select id="getOrderInfoList" resultMap="ResultOrderInfoMap">
select orderId from orders where accountId = #value#
</select>
<!-- Part 6 -->
<select id="getOrderItemList" resultMap="ResultOrderItemMap">
select orderId, orderItemId from orderItem where orderid = #value# </select>
</sqlMap>
Part 1 and 2 use the select attribute for one of the mapped properties. The presence of that attribute tells iBATIS that the property is to be set using the results of another mapped statement, which is named by its value.. For example, when we run the getAccountInfoList mapped statement(Part 4), the ResultAccountInfoMap result map has <result Property="orderList" select="C6.getOrderInfoList" column="accountId" />. That tells iBATIS to get the value for the orderList property running the "Ch6.getOrderInfoList" mapped Statement(Part 5), passing it the value of the acccountId column, and then putting the resutrned data into orderList.
Similarly, the getOrderItemList(Part 6) mapped statement is executed to get the value of the orderItemList property in the result map ResultOrderInofMap(Part 2).
Inspite of the convenience this functionality offers, two issues can arise. First the creation of lists with many objects in them can turn into a massive memory consumer. Second, this approach can cause major database I/O problems very quickly due to a phenomenon known as the "N+1 Selects" problem.
Database I/O
When reading or writing to a database, the data has to be transferred from disk to memory or from memory to disk, which are expensive operations in terms of time. Avoiding database I/O with caching will make your application faster, but this strategy can be problematic if not used with caution.
To illustrate the database I/O problems you can encounter when using related data, imagine a case where you have 1,000 Accounts, each related to 1,000 Orders that have 25 OrderItems each. If you try to load all of that into memory, it would result in the exectuion of over 1,000,000 SQL statements(one for the accounts, 1,000 for the orders, and 1,000,000 for the order items) and the creation of around 25 million Java objects - doing this will certainly get you a slap on the wrist from your system administrators.
N+1 Selects problem
This problem is caused by trying to load child records that are related to a list of parent records. So, if you run one query to get the parent records, and there are some number, N, of them, then you have to run N more queries to get the child records for the parent records, resulting in "N+1 Selects. "
Solutions to these problems
Lazy loading can mitigate some of the memory problem by breaking the loading process into smaller, more manageable pieces. However, it still leaves the database I/O problem, becuase in the worst case it will still hit your database just as hard as the non-lazy version did since it still uses the N+1 Selects approach as it loads the data. When we solve the N+1 Selects problem to reduce the database I/O, however, we can do it with a single database query, but we get all 25,000,000 rows in one big chunk.
This capability is useful if you like to have your application's model look like your data model. It is possible to use iBATIS to define your data model in terms of related objects, and have iBATIS load them all at once. For example, if you have a database in which Account records have related Order records that have related Orderitem records, those relationships can be set up so that when you request an Account, you also get all of the Order objects and all of the OrderItem objects as well.
<?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">
<!-- Part 1 -->
<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>
<!-- Part 2 -->
<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>
<!-- Part 3 -->
<resultMap id="ResultOrderItemMap" class="org.apache.mapper2.examples.bean.OrderItem">
<result property="orderId" column="orderId" />
<result property="orderItemId" column="orderItemId" />
</resultMap>
<!-- Part 4 -->
<select id="getAccountInfoList" resultMap="ResultAccountInfoMap" >
select accountId from Account </select>
<!-- Part 5 -->
<select id="getOrderInfoList" resultMap="ResultOrderInfoMap">
select orderId from orders where accountId = #value#
</select>
<!-- Part 6 -->
<select id="getOrderItemList" resultMap="ResultOrderItemMap">
select orderId, orderItemId from orderItem where orderid = #value# </select>
</sqlMap>
Part 1 and 2 use the select attribute for one of the mapped properties. The presence of that attribute tells iBATIS that the property is to be set using the results of another mapped statement, which is named by its value.. For example, when we run the getAccountInfoList mapped statement(Part 4), the ResultAccountInfoMap result map has <result Property="orderList" select="C6.getOrderInfoList" column="accountId" />. That tells iBATIS to get the value for the orderList property running the "Ch6.getOrderInfoList" mapped Statement(Part 5), passing it the value of the acccountId column, and then putting the resutrned data into orderList.
Similarly, the getOrderItemList(Part 6) mapped statement is executed to get the value of the orderItemList property in the result map ResultOrderInofMap(Part 2).
Inspite of the convenience this functionality offers, two issues can arise. First the creation of lists with many objects in them can turn into a massive memory consumer. Second, this approach can cause major database I/O problems very quickly due to a phenomenon known as the "N+1 Selects" problem.
Database I/O
When reading or writing to a database, the data has to be transferred from disk to memory or from memory to disk, which are expensive operations in terms of time. Avoiding database I/O with caching will make your application faster, but this strategy can be problematic if not used with caution.
To illustrate the database I/O problems you can encounter when using related data, imagine a case where you have 1,000 Accounts, each related to 1,000 Orders that have 25 OrderItems each. If you try to load all of that into memory, it would result in the exectuion of over 1,000,000 SQL statements(one for the accounts, 1,000 for the orders, and 1,000,000 for the order items) and the creation of around 25 million Java objects - doing this will certainly get you a slap on the wrist from your system administrators.
N+1 Selects problem
This problem is caused by trying to load child records that are related to a list of parent records. So, if you run one query to get the parent records, and there are some number, N, of them, then you have to run N more queries to get the child records for the parent records, resulting in "N+1 Selects. "
Solutions to these problems
Lazy loading can mitigate some of the memory problem by breaking the loading process into smaller, more manageable pieces. However, it still leaves the database I/O problem, becuase in the worst case it will still hit your database just as hard as the non-lazy version did since it still uses the N+1 Selects approach as it loads the data. When we solve the N+1 Selects problem to reduce the database I/O, however, we can do it with a single database query, but we get all 25,000,000 rows in one big chunk.
Comments
Post a Comment