How to implement one-to-many relationships in Ibatis?

Question

Let's say I have this Class:

    Class A {
        int id;
        int[] b;
        // Other properties
    }

    Class B {
        int id;
        // Other properties
    }

The Class A has one-to-many relation with class B. I already have a service which caches B objects and return them on id.

Table schema looks something like this

    Table a:
    -------
      int id,
      prop1,
      etc

    Table a_to_b_map
    ----------------
      int a_id,
      int b_id

Now, how do I map this in iBatis?

Since, B objects are already cached, I want to get the list of ids into A objects and then use the service to enrich As.

Can someone suggest how to go about it?

Two possible alternative I can think of are:

1. Create an inner class in A (AtoB map) and use a select query in iBatis config to populate this
2. Inside the iBatis resultMap/select use another select to get the list of B ids (not too sure on how to do this in config)

Answer 1

Not sure if I have understood your question correctly.

Assuming you will quey based on A's id, how about writing a query in ibatis that joins the two tables?

select * from a, a_to_b_map where a.id=#id# and a.id = a_to_b_map.a_id

You can then use a 'queryForMap' to return a hashmap of a_id vs (collection of records from the query). Use a custom method to convert this data structure into an object of 'A'

Answer 2

Well, I could find some useful information in iBatis 2 developer documentation at http://svn.apache.org/repos/asf/ibatis/trunk/java/ibatis-2/ibatis-2-docs/en/iBATIS-SqlMaps-2_en.pdf (specificatlly, pages 36,37)

Answer 3

in mybatis 3 it's little bit different. You can do it by specify two select statement or you can use join then create resultMap with collection tag.

<resultMap id=”blogResult” type=”Blog”>
   <collection property="posts" javaType=”ArrayList” column="blog_id"
      ofType="Post" select=”selectPostsForBlog”/>
</resultMap>

<select id=”selectBlog” parameterType=”int” resultMap=”blogResult”>
    SELECT * FROM BLOG WHERE ID = #{id}
    </select>
<select id=”selectPostsForBlog” parameterType=”int” resultType="Author">
    SELECT * FROM POST WHERE BLOG_ID = #{id}
    </select>

or you can use join

<select id="selectBlog" parameterType="int" resultMap="blogResult">
select
B.id as blog_id,
B.title as blog_title,
B.author_id as blog_author_id,
P.id as post_id,
P.subject as post_subject,
P.body as post_body,
from Blog B
left outer join Post P on B.id = P.blog_id
where B.id = #{id}
</select>

and do result map

<resultMap id="blogResult" type="Blog">
<id property=”id” column="blog_id" />
<result property="title" column="blog_title"/>
<collection property="posts" ofType="Post">
<id property="id" column="post_id"/>
<result property="subject" column="post_subject"/>
<result property="body" column="post_body"/>
</collection>
</resultMap>

you can get complete totorial from ibatis user guide here :

http://svn.apache.org/repos/asf/ibatis/java/ibatis-3/trunk/doc/en/iBATIS-3-User-Guide.pdf