October 1, 2010

Understanding MyBatis Multiple Table Joins

This is a MyBatis tutorial that attempts to explain howto implement multi-table joins using MyBatis,Java and MySQL

Let's start with an example.

Say you have 3 tables

address
id address_data

address_type
id type

address_mapping
id address_id address_type_id

This layout allows multiple addresses of different types for a particular user for example. You can have a home address, a work address and a vacation home address,etc.

The SQL statement to get all addresses (and their associated type) looks like this

  
select * from address a 
  left outer join address_mapping am on a.id = am.address_id
  left outer join address_type at on at.id = am.address_type_id

The Java Model

At this point you probably have 2 Java POJOs called Address and AddressType which map to the first two tables as shown here

  
Integer Address.getId()
String Address.getData()
AddressType Address.getType()

Integer AddressType.getId()
String AddressType.getType()

If you have created a POJO for AddressMapping, delete it now. You don't need this object and it serves no purpose as the mapping table address_mapping will only be used in the join

Adding the mappings to MyBatis

The following code snippets should all be part of your MyBatis mapper xml files

First you define the query to retrieve all the addresses. This will require a join on the three tables above. It's always a good idea to test this SQL against your database to make sure it works first.

  
<select id="getAddress" resultMap="addressResultMap">
  select 
      a.id as "a_id",
      a.address_data as "a_data",      
      at.id  as "at_id",
      at.type as "at_type",
      am.id as "am_id",
      am.address_id as "am_address_id",
            am.address_type_id as "am_address_type_id"
  left outer join address_mapping am on a.id = am.address_id
  left outer join address_type at on at.id = am.address_type_id
</select>

Note: Note how you can label the column names with a different label. Whenever you have multiple joins you should make sure your columns have unique names because otherwise you will get some hard to track down bugs because of naming conflicts. (Eg. without the unique column names, MyBatis would get confused by a.id, at.id and am.id)

Next we need a resultMap for the AddressType object. Nothing fancy here.

  
<!-- simple result map -->
<resultMap id="addressTypeResultMap" type="AddressType">
 <id property="id" column="at_id" />  
 <result property="type" column="at_type" />
</resultMap>

Finally, we need a result map for the Address object. Which has a reference to an object of type AddressType. Because this is a 1-to-1 association, we'll need an 'association' element in the result map. 

  
<!-- complex result map -->
<resultMap id="addressResultMap" type="Address">
 <id property="id" column="a_id" />  
 <result property="data" column="a_data" />
 <association property="type" column="at_id" javaType="AddressType" resultMap="addressTypeResultMap"/> 
 OR
 <association property="type" column="am_address_type_id" javaType="AddressType" resultMap="addressTypeResultMap"/> 
</resultMap>

Note: It doesn't really matter which column you use in the 'column' attribute of an or element, as long as it's one of the fields you are joining on, which in our case is: am_address_type_id or at_id.

Note: Here we're making use of the resultMap chaining capability which MyBatis provides which really saves you a lot of time. Chaining means that one resultMap can reference another resultMap in order to pull out table data into complex object references (eg. properties of the POJO which are not primitive types, like Address.getAddressType() in our case)

Things to Remember

The main things to remember when dealing with multiple joins in MyBatis are:

  1. You don't need to create java classes or resultMaps for 'mapping tables' as they will only be used during the join query.
  2. You can use any column name in the 'column' attribute of an or element, inside the resultMap, as long as it is one of the columns you are joining on.
  3. Make sure your join query returns unique column names by using the "as" element, in order to avoid naming conflicts. And don't forget to update your resultMaps to map those unique column names to the Java object's properties.

Thats it. I hope this makes MyBatis multiple table joins easier to understand.

Tags: ,