Is it possible to write common condition in one place and reuse it (in MyBatis)?

Question

I have some queries inside mapper.xml which have almost the same condition. Is it possible to write the common part in one place and reuse it?

For example:

  select count(*) from table1 t1 inner join table2 t2 where t1.id = t2.id;

Similarly I have another query:

  select id, name from table1 t1 inner join table2 t2 where t1.id = t2.id;

I want to put table1 t1 inner join table2 t2 where t1.id = t2.id; in one place and reuse it in both queries.

Additionally I have something like:

<if test="id != 0">
    AND id = #{id,jdbcType=INTEGER}
</if>
<if test="assignTo != 0">
    AND assign_to = #{assignTo,jdbcType=INTEGER}
</if>
<if test="status != 0">
    AND status = #{status,jdbcType=INTEGER}
</if>   

and this is also common in 2 queries.

Answer 1

You can use SQL fragments.

For example, you can have:

<sql id="yourFragmentId1">
  table1 t1 inner join table2 t2 where t1.id = t2.id;
</sql>

or

<sql id="yourFragmentId2">
  <if test="id != 0">
    AND id = #{id,jdbcType=INTEGER}
  </if>
  <if test="assignTo != 0">
    AND assign_to = #{assignTo,jdbcType=INTEGER}
  </if>
  <if test="status != 0">
    AND status = #{status,jdbcType=INTEGER}
  </if>
</sql>

and then, in your queries, refer to them with <include refid="yourFragmentId1" /> and <include refid="yourFragmentId2" />, respectively.