MyBatis include same fragment multiple times for joined tables of same type

Question

Update 2016-06-07 - see my answer below for solution

Trying to find out if there is a way to reuse same fragment in one query.

Consider this:

<sql id="personFields">
    per.id                  person_id,
    per.created_at          person_created_at,
    per.email_address       person_email_address,
    per.first_name          person_first_name,
    per.last_name           person_last_name,
    per.middle_name         person_middle_name
</sql>

The "per." alias is used to avoid column name clashing when using in queries with muiltiple joined tables. It is included like this:

SELECT
<include refid="com.acme.data.mapper.PersonMapper.personFields"/>
FROM Person per

The problem is that it cannot be used more than once per query because we have the "per." alias.

Would be great to have something like this:

<sql id="personFields">
    #{alias}.id                  #{alias}_person_id,
    #{alias}.created_at          #{alias}_person_created_at,
    #{alias}.email_address       #{alias}_person_email_address,
    #{alias}.first_name          #{alias}_person_first_name,
    #{alias}.last_name           #{alias}_person_last_name,
    #{alias}.middle_name         #{alias}_person_middle_name
</sql>

And include it like this:

SELECT
<include refid="com.acme.data.mapper.PersonMapper.personFields" alias="per1"/>,
<include refid="com.acme.data.mapper.PersonMapper.personFields" alias="per2"/>
FROM Person per1
JOIN Person per2 ON per2.parent_id = per1.id

score 10 · Accepted Answer · edited Aug 01 '17 at 08:56

This is currently possible (not sure since what version):

Define it:

<sql id="AddressFields">
    ${alias}.id                 ${prefix}id,
    ${alias}.created_at         ${prefix}created_at,
    ${alias}.street_address     ${prefix}street_address,
    ${alias}.street_address_two ${prefix}street_address_two,
    ${alias}.city               ${prefix}city,
    ${alias}.country            ${prefix}country,
    ${alias}.region             ${prefix}region,
    ${alias}.sub_region         ${prefix}sub_region,
    ${alias}.postal_code        ${prefix}postal_code
</sql>

Select it:

<sql id="PurchaseSelect">
SELECT
purchase.*,
<include refid="foo.bar.mapper.entity.AddressMapper.AddressFields">
    <property name="alias" value="billing_address"/>
    <property name="prefix" value="billing_address_"/>
</include>,
<include refid="foo.bar.mapper.entity.AddressMapper.AddressFields">
    <property name="alias" value="shipping_address"/>
    <property name="prefix" value="shipping_address_"/>
</include>
FROM purchase
LEFT JOIN address billing_address ON purchase.billing_address_id = billing_address.id
LEFT JOIN address shipping_address ON purchase.shipping_address_id = shipping_address.id

</sql>

Map it:

<resultMap id="PurchaseResult" type="foo.bar.entity.sales.Purchase">    
    <id property="id" column="id"/>
    <!-- any other purchase fields -->
    <association property="billingAddress" columnPrefix="billing_address_" resultMap="foo.bar.mapper.entity.AddressMapper.AddressResult"/>
    <association property="shippingAddress" columnPrefix="shipping_address_" resultMap="foo.bar.mapper.entity.AddressMapper.AddressResult"/>    
</resultMap>

score 5 · Answer 2 · edited Aug 01 '17 at 08:57

Unfortunately you can't do that, others have already tried (see some issues here or here). The includes are inlined and take no parameters.

One solution off the top of my head would be something like this:

<sql id="fragment">
  <foreach collection="list" separator="," item="alias">
    ${alias}.id                  ${alias}_person_id,
    ${alias}.created_at          ${alias}_person_created_at,
    ${alias}.email_address       ${alias}_person_email_address,
    ${alias}.first_name          ${alias}_person_first_name,
    ${alias}.last_name           ${alias}_person_last_name,
    ${alias}.middle_name         ${alias}_person_middle_name
  </foreach>
</sql>

include it just once like:

<select id="getPersons" parameterType="java.util.List" ... >
  SELECT
  <include refid="fragment"/>
  FROM Person per1
  JOIN Person per2 ON per2.parent_id = per1.id
</select>

and have a parameterType="java.util.List" sent from the mapper interface:

public interface PersonMapper {
  public List<String> getPersons(List<String> aliases); 
  // called with aliases = ["per1", "per2"]
}

This is ugly because your (higher level) code will have to know the aliases used inside the (lower) queries and also uses string substitutions for the fragment (${...} instead of #{...}) which can be dangerous if not handled properly... but if you can live with that...

Thanks. Yeah this is ugly adds clutter and doesn't really solve the problem. I think just copy pasting the fragments will work for now. — Vitali Carbivnicii, Jan 13 '14 at 16:57

score 3 · Answer 3 · edited Aug 01 '17 at 08:57

This feature is asked to be implemented for more than 2 years (https://code.google.com/p/mybatis/issues/detail?id=652).

This static parameters in include can be found implemented in this fork: https://github.com/kmoco2am/mybatis-3

It is fully working and it has the same syntax as standard configuration parameters or static variables:

<sql id="sometable">
  ${prefix}Table
</sql>

<select id="select" resultType="map">
    select 
        field1, field2, field3
    from
    <include refid="sometable">
        <placeholder name="prefix" value="Some"/>
    </include>
</select>

Hopefully, it will be soon accepted for the main source repository.

MyBatis include same fragment multiple times for joined tables of same type

Update 2016-06-07 - see my answer below for solution

3 Answers3

Linked