Reusing dynamic sql fragments

Question

Hei there, I'm working on a Primefaces app and as a persistence layer I chose Mybatis.

This is how a regular sql would look in my mapper:

<select id="getAllTransportUnit" resultMap="TransportUnitMap">
    SELECT * FROM SSLS_GUI.VW_TU
    <if test="( hasFilters == 'yes' ) and ( parameters != null )">
        <where>
            <foreach item="clause" collection="parameters" separator=" AND "
                open="(" close=")">
                UPPER(${clause.column}) ${clause.operator} #{clause.value}
            </foreach>
        </where>
    </if>
    <if test="sort == 'true'">
        ORDER BY ${sortField}
        <if test="sortOrder == 'DESC'"> DESC</if>
        <if test="sortOder == 'ASC'"> ASC</if>
    </if>
</select>

Almost all my queries use the dynamic sql part starting from the <if test...>. Is it possible to put it in a separate file and then reuse it all over my queries?

Answer 1

There are several options how to reuse sql snippets.

SQL snippets and include

The first one is using include. Create separate mapper Common.xml:

<mapper namespace="com.company.project.common">
    <sql id="orderBy>
      <if test="sort == 'true'">
        ORDER BY ${sortField}
        <if test="sortOrder == 'DESC'"> DESC</if>
        <if test="sortOder == 'ASC'"> ASC</if>
      </if>
    </sql>
    

    <sql id="filters">
     <if test="( hasFilters == 'yes' ) and ( parameters != null )">
      <where>
        <foreach item="clause" collection="parameters" separator=" AND "
            open="(" close=")">
            UPPER(${clause.column}) ${clause.operator} #{clause.value}
        </foreach>
     </where>
    </if>
  </sql>
</mapper>

And the use it in other mappers MyMapper.xml:

<select id="getAllTransportUnit" resultMap="TransportUnitMap">
  SELECT * FROM SSLS_GUI.VW_TU
  <include refid="com.company.project.common.filters"/>
  <include refid="com.company.project.common.orderBy"/>
</select>

To avoid duplicating namespace in every include you can create shortcut snippets in MyMapper.xml:

<sql id="orderBy">
  <include refid="com.company.project.common.orderBy"/> 
</sql>

<select id="getAllTransportUnit" resultMap="TransportUnitMap">
  SELECT * FROM SSLS_GUI.VW_TU
  <include refid="orderBy"/>
</select>

Mybatis-velocity macro

Another possible option is to use mybatis scripting. Using mybatis-velocity scripting engine you can define velocity macro and use it like this.

In Commons.xml:

<sql id="macros"
  #macro(filters)
    #if ( $_parameter.hasFilters )
      #repeat( $_parameter.parameters $clause "AND" " (" ")" )
        ${clause.column} ${clause.operator} @{clause.value}
      #end
    #end
  #end
  
  #macro(order_by)
  .. 
  #end
</sql>

In MyMapper.xml:

<select id="getAllTransportUnit" resultMap="TransportUnitMap">
  <include refid="macros"/>
  SELECT * FROM SSLS_GUI.VW_TU
  #filters()
  #order_by()
</select>

Including macros via sql snippet is not the most clean way to reuse macros. It is just an idea how this is used.

Much better option is to configure mybatis-velocity and specify what global macros are available. In this case there will be no need to include macros snippet and result query will be like this:

<select id="getAllTransportUnit" resultMap="TransportUnitMap">
  SELECT * FROM SSLS_GUI.VW_TU
  #filters()
  #order_by()
</select>