Bulk saving and updating using iBatis

Recently I had the need to save records to an association table. All the object values were the same except one. I was looping through the array list saving them one at a time, but kept getting unable to get database connection errors. I lamented that I couldn't do a bulk save. A co-worker thought that iBatis supported what I wanted to do, so after some investigation we were able to get both a bulk save and a bulk update to work.

For our example let us assume we have a table that contains a list of companies and another table that contains a list of Locations. From the client when you edit a company it allows you to multi-select a list of locations to associate with that company. On the back end you could loop through the locations and store each separately, but iBatis allows you to save them all at once. Let's examine how to do that.

Here are the Company and Location classes, minus getters/setters and imports;

package com.projectA;
public class Company {
    private int companyId;
    private String name;
    private List locations;
}

package com.projectA;
public class Location {
    private int locationId;
    private String address;
    private String city;
    private String state;
    private String zipCode;
    private String phoneNumber;
}

The database contains a table containing all the companies, and a table containing all the locations. There is also a third table that contains the company - location associations. To do this we need to take advantage of the iBatis dynamic fields. Here is the iBaits XML file that contains the bulk save and update methods for the association table:

<sqlMap namespace="Company">

    <typeAlias alias="company" type="com.projectA.Company"/>
    <typeAlias alias="loation" type="com.projectA.Location"/>

    <resultMap id="companyResultMap" class="company">
        <result column="company_id" property="companyId" />
        <result column="company_name" property="name" />
        <result column="{companyId=company_id}" property="locations"
                   select="Company.getActiveLocations"/>
    </resultMap>

    <resultMap id="locationResultMap" class="location">
        <result column="location_id" property="locationId" />
        <result column="location_address" property="address" />
        <result column="location_city" property="city" />
        <result column="location_state" property="state" />
        <result column="location_zip_code" property="zipCode" />
        <result column="location_phone_number" property="phoneNumber" />
    </resultMap>

    <sql id="select_company_fragment">
        SELECT company_id,
               company_name,
        FROM company
    </sql>

    <sql id="select_location_fragment">
        SELECT cl.location_id,
               loc.location_address,
               loc.location_city,
               loc.location_state,
               loc.location_zip_code,
               loc.location_phone_number
        FROM company_location cl, location loc
        WHERE cl.company_id=#companyId#
          AND cl.location_id = loc.location_id
    </sql>

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

    <select id="getActiveLocations" 
               resultMap="int">
        <include refid="select_location_fragment"/>
          AND active_flag = 1
        ORDER BY location_state, location_city
    </select>

    <insert id="bulkInsertCompanyLocation" parameterClass="map">
        INSERT INTO company_location
            (company_id,
            location_id,
            active_flag)
        SELECT
            #companyId#,
            #activeFlag#,
            loc.location_id,
        FROM location loc LEFT JOIN company_location cl ON cl.company_id=#companyId# 
                                                AND cl.location_id = loc.location_id
        WHERE  cl.company_id IS NULL
        <dynamic>
            <isNotNull property="locations">
                <iterate property="locations" 
                         prepend="AND " open=" loc.location_id IN ("
                         close=")" conjunction=",">
                    #locations[]#
                </iterate>
            </isNotNull>
        </dynamic>
    </insert>

    <update id="bulkUpdateCompanyLocation" parameterClass="map">
        UPDATE company_location
        SET
           active_flag = #activeFlag#
        WHERE company_id=#companyId#
        <dynamic>
            <isNotNull property="locations">
                <iterate property="locations" prepend="AND " open=" location_id IN ("
                         close=")" conjunction=",">
                    #locations[]#
                </iterate>
            </isNotNull>
        </dynamic>        
    </update>

</sqlMap>

The map that is passed into both the insert and update methods, contains the company_id, an active flag, and an array list of locationId's. These methods save or update multiple records in one shot. That's all there is to it. By changing to the bulk insert and update methods, the out of database connections errors went away.

The resulting SQL that iBatis generates looks like this:

   INSERT INTO company_location
       (company_id,
        location_id,
        active_flag)
   SELECT
       #companyId#,
       #activeFlag#,
       loc.location_id,
   FROM location loc LEFT JOIN company_location cl ON cl.company_id=#companyId# 
                           AND cl.location_id = loc.location_id
   WHERE  cl.company_id IS NULL
     AND loc.location_id IN ("1","3","10")

   UPDATE company_location
   SET
      active_flag = #activeFlag#
   WHERE company_id=#companyId#
     AND location_id IN ("4","6","12")

You can read more about the dynamic and iterate methods in the iBatis documentation.

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.