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.