iBatis and n+1 selects problem..

Recently I have started using iBatis and Spring with Java in my recent project and have stumbled upon the classic n+1 selects problem. For starters, I’ll try to explain this very briefly here…

‘n+1’ selects problem is a situation where a table ‘A’ in database has 1 to many relationship with an other table ‘B’, and for every row that we need from table A, there could be n rows in table B and in a straight forward mapping situation, we might have to write ‘1’ select query for table A and ‘n’ select queries for table B to build the appropriate domain objects. Obviously, there are clever ways to doing this.. for instance, we can join the tables and write just one query, which would return ‘n’ rows, that’s where my problem begins…. well that wasn’t as brief as I wanted, but I hope you got the basic idea… in case you are still not clear, why don’t you google “n+1 selects problem”? 😉

So I had my query which returns me n rows with each row containing data from table A and table B. Each of the n rows contain the same table A information, with unique table B information… so on the java side, this should convert to one domain object (of A) which containins n objects (of B) as a collection. After reading a bit, I quickly found that iBatis has a “groupBy” attribute for the resultMap element which does this…

To make things clear, let me explain the whole thing in a little more technical fashion..

So lets assume our tables were:

Table A: {a_id: number, attr1: varchar}
Table B: {b_id: number, a_id: number, attr2: varchar}

Our domain objects (aka. value objects) are:

public class A {
int      id;
String attr1;
List     bList;  //A collection of Object 'B's
}
public class B {
int           id;
String attr2;
}

Lets assume table A and B had the following data that we want  to retrieve as a single instance of A, with a list of Bs (2 Bs in the list).

Table A: [1, 'item a1']
Table B: [1, 1, 'item b1'], [2, 1, 'item b2']

From the above sample data, we expect to see the following object in java.. (JSON representation)

A a = { id:1, attr1:'item a1', bList:[ { id:1, attr2:'item b1' },
                                       { id:2, attr2:'item b2' } ] };

To acheive this result using iBatis, all we need to do in your java code is to call this one statement.. (assuming you are using Spring with iBatis).

A a = (A) getSqlMapClientTemplate().queryForObject("queryA");

or for you who hate, fear or just ignorant of spring framework, replace the getSqlMapClientTemplate() with your own instance of SqlMapClient 😉

And now for the actual SQL Map XML which instructs iBatis to do what we wanted..

<sqlMap>
   <resultMap id="result_queryA" class="your.path.A" groupBy="a_id">
      <result property="id" column="a_id" jdbcType="NUMERIC" />
      <result property="attr1" column="attr1"  jdbcType="VARCHAR" />
      <result property="bList" resultMap="result_queryB" />
   </resultMap>

   <resultMap id="result_queryB" class="your.path.B">
      <result property="id" column="b_id" />
      <result property="attr2" column="attr2" />
   </resultMap>

   <select id="queryA" resultMap="result_queryA">
      select * from A,B where A.id = B.id
   </select>
</sqlMap>

That’s it.. that should work in our scenario here… even if we have more records in our tables, this SQL Map would still be good, and you would query for List instead of Object from java… that was easy, wasn’t it?

Well, this was really not the motivation behind my post here.. there was plenty of help to this problem, and similar examples were all over the net. Then, one might wonder why I took the time in writing so much when it is already written over and over again? I wrote this up, because I wanted to discuss something that I couldn’t find help for.. and I had to repeat the above thing to make this post complete.. 🙂

My actual problem was a little different than the example from above. That is, when you need a list of primitives in your domain object instead of a list of another domain object. In our example terms, my object A didn’t have a list of Object Bs, instead it had just the list of Strings (attr2).

There wasn’t much help available to my problem, and I really spent a shameful amount of time trying to solve this problem with a similar solution as above.. as it turns out, it wasn’t much different than the solution above. Instead of my “result_queryB” resultMap return an instance of object B, it will return an instance of java.lang.String. That was simple, but my confusion was with what to use for property attribute, as it is a required attribute for “result” element in the map and I really didn’t know how it would work… again, as it turns out, you just don’t use any value for property 😀 … so in my case the resultMap “result_queryB” would look like this..

   <resultMap id="result_queryB" class="java.lang.String">
      <result property="" column="attr2" />
   </resultMap>

I guess the above resultMap would work even if you enter some value for property, as iBatis mapper would not even read this attribute for primitive types like String, Long etc., It is misleading to have the property attribute of result element as a required attribute when there are situations like this for which there is no use for property…

I hope this post would help somebody like me in not chasing around a simple problem like this..

pal 🙂