Ibatis Tutorial: Aggregation with groupBy

This tutorial builds on the knowledge from the Ibatis Inheritance Tutorial. As such the configuration and schema won't be repeated here for brevity.

The next feature I will introduce is dynamic grouping. To explain this let me pose a question: have you ever done an aggregation query (meaning it has a GROUP BY clause) and wanted to return columns other than the ones you're grouping by or aggregating in some way? Well, Ibatis has an answer for you.

Consider the example from the previous tutorial: we are now returning a list of parties that have different types. Those parties have contact information (eg email addresses and various types of phone numbers). What if we wanted to return a list of all our parties as well as their contact information?

In straight SQL there would be a number of options:

  1. Join party to contact and handle the duplicate parties;
  2. Retrieve the contacts individually for each party we process; or
  3. Query the parties and then do a separate query for all the contacts.

All of these options have downsides. The last one results in two queries and is quite efficient but requires record matching in code. It's also fine if you really need all the contact records but what if you're only returning a subset of the parties? In that case you need to query more contacts than you need or join to the party table anyway.

Ibatis has a solution that is very much like (1) from above but Ibatis does most of the legwork for you. Let's start with the Java classes we need.

Contact.java

package com.cforcoding;

public class Contact {
    private Long id;
    private ContactType type;
    private String value;

    public Long getId() { return id; }
    public void setId(Long id) { this.id = id; }
    public ContactType getType() { return type; }
    public void setType(ContactType type) { this.type = type; }
    public String getValue() { return value; }
    public void setValue(String value) { this.value = value; }
}

ContactType.java

package com.cforcoding;

public enum ContactType {
    EMAIL("E", "Email"),
    PHONE("P", "Phone"),
    WORK("W", "Work"),
    MOBILE("M", "Mobile");

    private final String code;
    private final String desc;

    ContactType(String code, String desc) {
        this.code = code;
        this.desc = desc;
    }

    public String getCode() {
        return code;
    }

    public String getDescription() {
        return desc;
    }

    public static ContactType find(String code) {
        for (ContactType type : values()) {
            if (type.code.equals(code)) {
                return type;
            }
        }
        return null;
    }
}

ContactTypeHandler.java

package com.cforcoding;

import com.ibatis.sqlmap.client.extensions.TypeHandlerCallback;
import com.ibatis.sqlmap.client.extensions.ParameterSetter;
import com.ibatis.sqlmap.client.extensions.ResultGetter;

import java.sql.SQLException;

public class ContactTypeHandler implements TypeHandlerCallback {
    @Override
    public void setParameter(ParameterSetter setter, Object parameter) throws SQLException {
        setter.setString(parameter == null ? null : ((ContactType)parameter).getCode());
    }

    @Override
    public Object getResult(ResultGetter getter) throws SQLException {
        return valueOf(getter.getString());
    }

    @Override
    public Object valueOf(String s) {
        return s == null ? null : ContactType.find(s);
    }
}

All the above are a straightforward adaption of the previous tutorial.

Party.java

package com.cforcoding;

import java.util.List;
import java.util.ArrayList;

public class Party {
    private Long id;
    private PartyType type;
    private String name;
    private List contacts = new ArrayList();

    public Long getId() { return id; }
    public void setId(Long id) { this.id = id; }
    public PartyType getType() { return type; }
    public void setType(PartyType type) { this.type = type; }
    public String getName() { return name; }
    public void setName(String name) { this.name = name; }
    public List getContacts() { return contacts; }
    public void setContacts(List contacts) { this.contacts = contacts; }
}

The only change here is the addition of a list of contacts. Ibatis will populate that for us.

Party.xml

<resultMap id="contact" class="com.cforcoding.Contact">
    <result property="id" column="contact_id"/>
    <result property="type" column="contact_type" typeHandler="com.cforcoding.ContactTypeHandler"/>
    <result property="value"  column="details"/>
</resultMap>

<resultMap id="party" class="com.cforcoding.Party" groupBy="id">
    <result property="id" column="ID"/>
    <result property="type" column="party_type" typeHandler="com.cforcoding.PartyTypeHandler"/>
    <result property="name" column="NAME"/>
    <result property="contacts" resultMap="Party.contact"/>
    <discriminator javaType="com.cforcoding.PartyType" column="PARTY_TYPE" typeHandler="com.cforcoding.PartyTypeHandler">
        <subMap value="PERSON" resultMap="person"/>
        <subMap value="COMPANY" resultMap="company"/>
    </discriminator>
</resultMap>

<resultMap id="person" class="com.cforcoding.Person" extends="party">
    <result property="title" column="TITLE"/>
    <result property="givenNames" column="GIVEN_NAMES"/>
    <result property="lastName" column="LAST_NAME"/>
    <result property="dateOfBirth" column="DATE_OF_BIRTH"/>
</resultMap>

<resultMap id="company" class="com.cforcoding.Company" extends="party">
    <result property="comapnyNumber" column="COMPANY_NUMBER"/>
    <result property="stateOfIncorporation" column="STATE_OF_INCORPORATION"/>
</resultMap>

<select id="selectAllParties" resultMap="party">
    SELECT pt.id, party_type, name, title, given_names, last_name, date_of_birth, company_number, state_of_incorporation, ct.id contact_id, contact_type, details
    FROM party pt
    LEFT OUTER JOIN person ps ON pt.id = ps.id
    LEFT OUTER JOIN company cy ON pt.id = cy.id
    LEFT OUTER JOIN contact ct ON pt.id = ct.party_id
</select>

This has a couple of new features.

The first is the groupBy attribute on line 7. You can specify one or more property names here. For every distinct set of values for the grouping criteria, a row will be in the result list. Here we're grouping by party id so we will get one row per party.

Internally Ibatis is loading all the rows and effectively sorting them. I believe it may be actually bucketing them in a map of some sort but that is merely an implementation detail. All you need to know is that sort order does not matter for the purpose of grouping.

This is both a good and bad feature. I personally feel that sorting can be done efficiently and effectively in the database, which would still allow effective paging. This also negatively impacts row handlers (I subject I shall soon address) to the point where the Ibatis powers-that-be have decreed row handlers and groupBy to be incompatible.

I raised a JIRA issue to create at least a non-sorting version of this but there has been no action to date.

Line 11 demonstrates the other part of this behaviour. The contacts property is a list. With this result element we are saying that we should fill the contacts list with objects and map them using the Party.contact result map. Note: The namespace needs to be specified for this to work even though namespaces (an optional feature) are disabled. I consider this a bug.

Running the Example

Now to prove this SQL works, we should have a party with no contacts. Run this:

INSERT INTO party VALUES (party_seq.nextval, 'C', 'Kaos Inc');
INSERT INTO company VALUES (party_seq.currval, '111 222 333', 'SA');

Run the query to verify the results.

Now run this sample program:

public static void main(String args[]) {
    ClassPathXmlApplicationContext ctx = new ClassPathXmlApplicationContext("applicationContext.xml");
    PartyDAO partyDAO = (PartyDAO) ctx.getBean("partyDAO");
    List parties = partyDAO.selectAllParties();
    int i = 0;
    for (Party party : parties) {
        List contacts = party.getContacts();
        System.out.printf("[%d/%d] %s: %s has %d contacts%n", ++i, parties.size(), party.getType().getDescription(), party.getName(), contacts.size());
        int j = 0;
        for (Contact contact : party.getContacts()) {
            System.out.printf("  [%d/%d] %s: %s%n", ++j, contacts.size(), contact.getType().getDescription(), contact.getValue());
        }
    }
}

This will output the following results:

[1/4] Person: Mr Tom Smith has 3 contacts
  [1/3] Email: tom.smith@example.com
  [2/3] Phone: 919-223-1234
  [3/3] Mobile: 919-223-1235
[2/4] Person: Test has 3 contacts
  [1/3] Email: susan.jones@example.com
  [2/3] Mobile: 919-223-1237
  [3/3] Phone: 919-223-1236
[3/4] Company: Acme Inc has 2 contacts
  [1/2] Email: roger@acme.com
  [2/2] Work: 919-223-1238
[4/4] Company: Kaos Inc has 0 contacts

Consider for a moment just how much work is being done with these few lines of XML.

Conclusion

Dynamic grouping is an incredibly powerful and easy-to-use feature. It is worth noting that groupings can be nested. That means you could easily create a query that constructs a list, one row per party type, where each element has a list of parties of that type and each party has a list of contacts.

JPA would handle the above situation with eager or lazy fetching, as desired. The batch mode in EclipseLink handles this situation very well. Still the point here is to demonstrate that you can achieve extremely sophisticated functionality with just SQL and a few lines of XML.

Soon I shall explore an alternative strategy using lazy fetching.

Next: Ibatis Tutorial: Dynamic SQL

1 comments:

Anonymous said...

Wow cool.
Would it then also be possible to have Contact.xml and Party.xml
and have
[resultMap id="party" class="com.cforcoding.Party" groupBy="id"]
[..]
[result property="contacts" resultMap="Contact.contact"/]
[..]
[/resultMap]


I've tried this but I can't seem to get it to work.

Post a Comment