MyBatis | Mapping Query Results to Arbitrary Java Objects | Splitting SELECT Execution

Splitting SELECT execution for single object mapping

Source code

sample_mapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
  PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="sample.mybatis">

  <resultMap type="sample.mybatis.Foo" id="fooResultMap">
    <id property="id" column="id" />
    <association property="bar" column="bar_id" select="selectBar" />
  </resultMap>

  <select id="selectFoo" resultMap="fooResultMap">
    select * from foo_table
  </select>

  <select id="selectBar" resultType="sample.mybatis.Bar">
    select * from bar_table where id = #{id}
  </select>
</mapper>

Execution result of selectFoo

[DEBUG] s.m.selectFoo   - ==>  Preparing: select * from foo_table 
[DEBUG] s.m.selectFoo   - ==> Parameters: 
[DEBUG] s.m.selectBar   - ====>  Preparing: select * from bar_table where id = ? 
[DEBUG] s.m.selectBar   - ====> Parameters: 1(Integer)
[DEBUG] s.m.selectBar   - <====      Total: 1
[DEBUG] s.m.selectBar   - ====>  Preparing: select * from bar_table where id = ? 
[DEBUG] s.m.selectBar   - ====> Parameters: 2(Integer)
[DEBUG] s.m.selectBar   - <====      Total: 1
[DEBUG] s.m.selectBar   - ====>  Preparing: select * from bar_table where id = ? 
[DEBUG] s.m.selectBar   - ====> Parameters: 3(Integer)
[DEBUG] s.m.selectBar   - <====      Total: 1
[DEBUG] s.m.selectFoo   - <==      Total: 3
Foo [id=3, bar=Bar [id=1]]
Foo [id=2, bar=Bar [id=2]]
Foo [id=1, bar=Bar [id=3]]

Explanation

• After querying foo_table, MyBatis performs a separate bar_table query for each row.
• You can perform this kind of query by specifying the statement ID of a <select> tag in the select attribute of the <association> tag.
• In the column attribute, specify the column used to JOIN the child table.

When there are multiple JOIN keys

DB tables

foo_table

bar_table

Source code

Bar.java

package sample.mybatis;

public class Bar {
    private int key1;
    private String key2;

    @Override
    public String toString() {
        return "Bar [key1=" + key1 + ", key2=" + key2 + "]";
    }
}

sample_mapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
  PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="sample.mybatis">

  <resultMap type="sample.mybatis.Foo" id="fooResultMap">
    <id property="id" column="id" />
                                <!-- How to specify multiple keys -->
    <association property="bar" column="{key1=bar_key1,key2=bar_key2}" select="selectBar" />
  </resultMap>

  <select id="selectFoo" resultMap="fooResultMap">
    select * from foo_table
  </select>

  <select id="selectBar" resultType="sample.mybatis.Bar">
    select *
      from bar_table
     where key1 = #{key1}
       and key2 = #{key2}
  </select>
</mapper>

Execution result of selectFoo

[DEBUG] s.m.selectFoo   - ==>  Preparing: select * from foo_table 
[DEBUG] s.m.selectFoo   - ==> Parameters: 
[DEBUG] s.m.selectBar   - ====>  Preparing: select * from bar_table where key1 = ? and key2 = ? 
[DEBUG] s.m.selectBar   - ====> Parameters: 1(Integer), fuga(String)
[DEBUG] s.m.selectBar   - <====      Total: 1
[DEBUG] s.m.selectBar   - ====>  Preparing: select * from bar_table where key1 = ? and key2 = ? 
[DEBUG] s.m.selectBar   - ====> Parameters: 1(Integer), hoge(String)
[DEBUG] s.m.selectBar   - <====      Total: 1
[DEBUG] s.m.selectBar   - ====>  Preparing: select * from bar_table where key1 = ? and key2 = ? 
[DEBUG] s.m.selectBar   - ====> Parameters: 2(Integer), hoge(String)
[DEBUG] s.m.selectBar   - <====      Total: 1
[DEBUG] s.m.selectFoo   - <==      Total: 3
Foo [id=2, bar=Bar [key1=1, key2=fuga]]
Foo [id=3, bar=Bar [key1=1, key2=hoge]]
Foo [id=1, bar=Bar [key1=2, key2=hoge]]

Explanation

• When multiple columns are used for a JOIN, specify the column attribute in the form {prop1=column1, prop2=column2}.

Splitting SELECT execution for collection mapping

Source code

sample_mapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
  PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="sample.mybatis">

  <resultMap type="sample.mybatis.Foo" id="fooResultMap">
    <id property="id" column="id" />
    <collection property="barList" column="id" select="selectBar" />
  </resultMap>

  <select id="selectFoo" resultMap="fooResultMap">
    select * from foo_table
  </select>

  <select id="selectBar" resultType="sample.mybatis.Bar">
    select * from bar_table where foo_id = #{id}
  </select>
</mapper>

Execution result of selectFoo

[DEBUG] s.m.selectFoo   - ==>  Preparing: select * from foo_table 
[DEBUG] s.m.selectFoo   - ==> Parameters: 
[DEBUG] s.m.selectBar   - ====>  Preparing: select * from bar_table where foo_id = ? 
[DEBUG] s.m.selectBar   - ====> Parameters: 1(Integer)
[DEBUG] s.m.selectBar   - <====      Total: 3
[DEBUG] s.m.selectBar   - ====>  Preparing: select * from bar_table where foo_id = ? 
[DEBUG] s.m.selectBar   - ====> Parameters: 2(Integer)
[DEBUG] s.m.selectBar   - <====      Total: 2
[DEBUG] s.m.selectBar   - ====>  Preparing: select * from bar_table where foo_id = ? 
[DEBUG] s.m.selectBar   - ====> Parameters: 3(Integer)
[DEBUG] s.m.selectBar   - <====      Total: 1
[DEBUG] s.m.selectFoo   - <==      Total: 3
Foo [id=1, barList=[Bar [id=1], Bar [id=2], Bar [id=3]]]
Foo [id=2, barList=[Bar [id=4], Bar [id=5]]]
Foo [id=3, barList=[Bar [id=6]]]

Explanation

• Configure it in the same way as <association>.
• Lazy loading is also possible.