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:

Our domain objects (aka. value objects) are:

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).

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

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).

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..

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..

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 🙂

  • Q

    The doc says the following. Have you tried “value” ?

  • coolpal

    do you mean an attribute “value=…” for <result> element?
    I am using ibatis 2.1.6 and I don’t see that attribute…

    pal 🙂

  • Q

    I meant property=”value”
    At least in 2.3.3, you can say

    p.s I came across your blog when searching for proper usage of resultMap attribute in result element.

    Thanks,
    Q

  • KienPham

    good. I have same problem with you. And you help to solve it.
    thanks.

  • coolpal

    thanks… glad it helped.
    pal 🙂

  • Jagmal Singh

    Cool. I spent a shameful amount of time figuring this out (mostly by hit-and-trial) and I am glad I came across this post. Thanks.

  • CBC

    leaving the property blank for me didn’t work, but thanks otherwise – that solved a major headache!

  • Pingback: one to many(N+1 problem) in iBatis | Bondrk's Blog()

  • Paresh M Radadiya

    Please give a simple ibatis  one to many to example

  • A simple example would be customer to order relation in a classic shopping site scenario.

    A customer (pk: customer_id) can have 1 or more orders placed (pk: order_id, fk: customer_id).

    The scenario in this article arises if you want to pull list of customers which contains customer information and each customer has a list of orders all with one select mapping in iBatis.