Let’s say you have a list of objects and each of those objects contains a list of another object. Filling these objects from your database involves a select statement to gather the first parent list of objects, followed by another select statement for every object in that list (N select statements). Add those together and you’re left with N+1 SQL statements to build one object. Now, this may not necessarily be view as a bad thing if you must use every sub-list in the objects, but, if you’re anything like me, you’ll use two of them. So now you’ve hit your database N+1 times to get the functionality that could be handled in one call. That’s a problem.
Luckily, most object/relational mapping frameworks (like iBatis for example) provide a solution. There are several ways of getting around this excess of statements. The first is to merge your two select statements into one bigger statement using joins and telling iBatis what should be a part of the sub-list of objects. This provides a nice option if you have simple hierarchies of objects, but your statement will quickly get messy if you have four or more levels of sublists. The second option is to map your list object to a select statement and use lazy joining. What this means is that the parent object’s sublist will be filled with unloaded select statements that will only be run when you try to access something in one of the subobjects.
Before I give the solutions, let me first provide our starting functionality (ridden with N+1 statements).
<sqlMap namespace="someNamespace">
<resultMap
id="parentResult"
class="com.adamdoyle.project.domain.ParentObject">
<result property="id" column="id" />
<result property="name" column="name" />
</resultMap>
<resultMap
id="childResult"
class="com.adamdoyle.project.domain.ChildObject">
<result property="id" column="id" />
<result property="parentId" column="parent_id" />
</resultMap>
<select
id="selectParent"
parameterClass="map"
resultMap="parentResult">
SELECT * FROM parent_table;
</select>
<select
id="selectChildrenForParent"
parameterClass="int"
resultMap="childResult">
SELECT * FROM child_table
WHERE parent_id = #parentId:NUMERIC#;
</select>
</sqlMap>
And then somewhere in your java code you would need something similar to the following:
List parents =
executeQueryForList("someNamespace.selectParent", null);
for (int parentCount = 0;
parentCount < parents.size();
parentCount++) {
ParentObject parent =
(ParentObject)parents.remove(parentCount);
List children =
executeQueryForList("someNamespace.selectChildrenForParent",
parent.getId())
parent.setChildList(children);
parents.add(parentCount, parent);
}
So now not only are you using N+1 select statements, you are also doing extra work to map your objects to your database. Unnecessary extra work, that is.
Prerequisite:
Before the following two solutions will work, you’ll need to make sure that you have lazy loading enabled in iBatis. In your main sqlMap.xml file, verify that lazyLoadingEnabled=”true” is present in the settings portion.
Solution one:
For this solution we are going to merge our two select statements into one all encompassing statement using joins. Here is the new SQL map.
<sqlMap namespace="someNamespace">
<resultMap
id="parentResult"
class="com.adamdoyle.project.domain.ParentObject"
groupBy="id">
<result property="id" column="id" />
<result property="name" column="name" />
<result property="childList"
resultMap="someNamespace.childResult"/>
</resultMap>
<resultMap
id="childResult"
class="com.adamdoyle.project.domain.ChildObject">
<result property="id" column="child_id" />
<result property="parentId" column="parent_id" />
</resultMap>
<select
id="selectParent"
parameterClass="map"
resultMap="parentResult">
SELECT * FROM parent_table
LEFT JOIN child_table
ON parent_table.id = child_table.parent_id
GROUP BY id;
</select>
</sqlMap>
In your java code you will only need this now:
List parents =
executeQueryForList("someNamespace.selectParent", null);
Simple, no? Every parent object inside the list will now have a SQL statement lazily loaded for its children list, ready to run as soon as you try and access it. The points of interest with this new method is the groupBy parameter of the parentResult. This prevents loading identical objects into the original parent list. Second is the childList property of the parentResult map which tells which variable (childList) is defined by which resultMap (childResult). Other than that the only change is the actual syntax involved in the SQL statement.
Although the above code handles the problem, I don’t really like it. First, as I stated earlier, your SQL statement can start to get incredibly bloated, ugly, and illegible (job security – yes, headaches later – also yes). Also, I’ve had issues with children-less parents giving errors. There’s probably some way to fix this, I just don’t know it (someone?).
I prefer the second solution to this problem, because it looks much more elegant in your SQL map files and has no real issues that I can find. Here it is:
<sqlMap namespace="someNamespace">
<resultMap
id="parentResult"
class="com.adamdoyle.project.domain.ParentObject">
<result property="id" column="id" />
<result property="name" column="name" />
<result property="childList" column="id"
select="someNamespace.selectChildrenForParent"/>
</resultMap>
<resultMap
id="childResult"
class="com.adamdoyle.project.domain.ChildObject">
<result property="id" column="id" />
<result property="parentId" column="parent_id" />
</resultMap>
<select
id="selectParent"
parameterClass="map"
resultMap="parentResult">
SELECT * FROM parent_table;
</select>
<select
id="selectChildrenForParent"
parameterClass="int"
resultMap="childResult">
SELECT * FROM child_table
WHERE parent_id = #parentId:NUMERIC#;
</select>
</sqlMap>
The java code will be identical to the first solution, the only thing that changes is how things are handled in your SQL map. What this does is maps your list (named childList in this example) to a select statement. In the column attribute you place the variable that you would like to pass onto the child select statement (in this case the parent’s id). In my opinion, this works stupendously. All of your SQL statements are broken up cleanly yet you still only run one SQL statement (further SQL statements will be run later if you need to access the data). Fantastic.
But what if you need to pass two variables to your child select statement? Luckily, that is also handled by iBatis. In your resultMap for parentResult, simply change column=”id” to column=”{parentId=id,someOtherId=some_other_id}”. Inside your child select statement, you can then use #parentId# and #someOtherId# to further narrow your search. Double fantastic.
Both solutions can handle more complex examples where your child objects have their own children, which have their own children, which have their own children, etc… Handy dandy.
If you noticed any glaringly bad errors in my code or explanation, please don’t hesitate to let me know. Thanks.
On a side note, if any knows of an easy way for me to display my code samples, I would surely appreciate it. I don’t just want to escape the problem characters (like <) but would enjoy a box around it with line numbers and whatnot (I’m sure it’s there – I’ve seen it – I’m just new to the whole WordPress thing). That would be amazing. Let me know.
