Skip to main content

Learning iBATIS - Complex collections

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.


Comments

Popular posts from this blog

Stretch a row if data overflows in jasper reports

It is very common that some columns of the report need to stretch to show all the content in that column. But  if you just specify the property " stretch with overflow' to that column(we called text field in jasper report world) , it will just stretch that column and won't change other columns, so the row could be ridiculous. Haven't find the solution from internet yet. So I just review the properties in iReport one by one and find two useful properties(the bold highlighted in example below) which resolve the problems.   example:
<band height="20" splitType="Stretch"> <textField isStretchWithOverflow="true" pattern="" isBlankWhenNull="true"> <reportElement stretchType="RelativeToTallestObject" mode="Opaque" x="192" y="0" width="183" height="20"/> <box leftPadding="2"> <pen lineWidth="0.25"/> …

JasperReports - Configuration Reference

Spring - Operations with jdbcTemplate

This class manages all the database communication and exception handling using a java.sql.Connection that is obtained from the provided DataSource. JdbcTemplate is a stateless and threadsafe class and you can safely instantiate a single instance to be used for each DAO.


Use of Callback Methods
JdbcTemplate is based on a template style of programming common to many other parts of Spring. Some method calls are handled entirely by the JdbcTemplate, while others require the calling class to provide callback methods that contain the implementation for parts of the JDBC workflow. This is another form of Inversion of Control. Your application code hands over the responsibility of managing the database access to the template class. The template class in turn calls back to your application code when it needs some detail processing filled in. These callback methods are allowed to throw a java.sql.SQLException, since the framework will be able to catch this exception and use its built-in excepti…