Retrieving complex objects from a database using iBatis

We have been using iBatis for 2 years now on the project I am on. We had need to retrieve a complex object from the database.
Basically, we have an object obA that has some fields(string, int, ..., and one that is List where obB has some fields(String, int, date, ...). Instead of retrieving all of the obA objects then looping through each of those to get the array of obB objects for each, I wanted to do it in one shot.

A little digging and I found that iBatis supports this and it really wasn't that hard. This simple example will generate retrieve an array of Company objects that each contain an array of Employee objects
.
First here are the two Java Classes for Company and Employee. To simplify, imports, getter/setter methods, etc have been omitted.

package com.projectA;
public class Company {
    private int id;
    private String name;
    private String address;
    private String city;
    private String state;
    private String zipCode;
    private List employees;
}

package com.projectA
public class Employee {
    private int companyId;
    private int id;
    private String firstName;
    private String lastName;
    private String phoneNumber;
    private String ssn;
    private Date dob;
}

The following is the iBatis XML file that will retrieve a list of all companies sorted by company name, each containing a list of employees sorted alphabetically by last name, then first name.

<sqlMap namespace="Company">

    <typeAlias alias="company" type="com.projectA.Company"/>
    <typeAlias alias="employee" type="com.projectA.Employee"/>

    <resultMap id="companyResultMap" class="company">
        <result column="company_id" property="id" />
        <result column="company_name" property="name" />
        <result column="company_address" property="address" />
        <result column="company_city" property="city" />
        <result column="company_state" property="state" />
        <result column="company_zip_code" property="zipCode" />
        <result column="{companyId=company_id}" property="employees"
                   select="Company.getEmployees"/>
    </resultMap>

    <resultMap id="employeeResultMap" class="employee">
        <result column="employee_company_id" property="companyId" />
        <result column="employee_id" property="id" />
        <result column="employee_first_name" property="firstName" />
        <result column="employee_last_name" property="lastName" />
        <result column="employee_phone_number" property="phoneNumber" />
        <result column="employee_ssn" property="ssn" />
        <result column="employee_dob" property="dob" />
    </resultMap>

    <sql id="select_company_fragment">
        SELECT company_id,
               company_name,
               company_address,
               company_city,
               company_state,
               company_zip_code
        FROM company
    </sql>

    <sql id="select_employee_fragment">
        SELECT employee_company_id,
               employee_id,
               employee_first_name,
               employee_last_name,
               employee_phone_number
               employee_ssn,
               employee_dob
        FROM employee
    </sql>

    <select id="getAllCompanies" 
               resultMap="companyResultMap">
        <include refid="select_company_fragment"/>
        ORDER BY company_name
    </select>

    <select id="getEmployees" 
               resultMap="employeeResultMap">
        <include refid="select_employee_fragment"/>
        WHERE employee_company_id=#companyId#
        ORDER BY employee_last_name, employee_first_name
    </select>

</sqlMap>

That's all there is to it. So when getAllCompanies is called, it will automatically call the getEmployees method to fill in the list of employees for each company. You can see this by looking at the companyResultMap, it has a column called employees that is mapped to a select statement and passes the current company id into that select.

Some benefits are simplicity and performance. One dao call retrieves everything I want, instead of getting a list of companies and needing to iterate over each to get the list of employees.

A nice ibatis feature is the alias. The benefit of using the alias in the ibatis XML file is that instead of needing to put com.projectA.Company in multiple places in the file, you do it once and reference it. Then if the package structure is refactored, you only have to change it once.

Another nice ibatis feature is the sql sections. This allows you to have SQL fragments, that can be used by more than one select. So in our example if you wanted to select all the companies for a given city you could add the following:

    <select id="getAllCompanies" 
               parameterClass="String" 
               resultMap="companyResultMap">
        <include refid="select_company_fragment"/>
        WHERE company_city=#city#
        ORDER BY company_name
    </select>

This is the same as the select statement above, except it has the additional constraint on the city.

More iBatis posts to follow.