[Enhancement] can mapper.xml provide a better support for code reusing?

[xhd2015]

[Enhancement] can mapper.xml provide a better support for code reusing?

mapper interface in conjunction with mapper xml works well, but because current mybatis xml parser(3.4.6) lacks some key features, such as importing files from other file, or expanding predefined macros, or calling custom method from java code, or extending the xml with custom xmlns declarement, today in our work we repeat coding like below:

    <select id="query" parameterType="common.PageMatrix" resultType="dal.entity.Blog">
        SELECT * 
        FROM t_blog t
        <where>
            <if test="id!=null">
                t.id = #{id}
            </if>
            <if test="status!=null">
               AND    t.status = #{status}
            </if>
        </where>
        <if test="limit!=null">
            LIMIT
            <if test="offset!=null">
                #{offset},#{limit}
            </if>
            <if test="offset==null">
                #{limit}
            </if>
        </if>
   </select>

the full outcome sql would be like

SELECT * FROM t_blog t WHERE  t.id = ? AND t.status = ? LIMIT ?,?

Thanks to mybatis, it can also be something like following if we do not assign offset a value

SELECT * FROM t_blog t WHERE  t.id = ? AND t.status = ? LIMIT ?

However, for every standalone xml, I have to write the same code,

        <if test="limit!=null">
            LIMIT
            <if test="offset!=null">
                #{offset},#{limit}
            </if>
            <if test="offset==null">
                #{limit}
            </if>
        </if>

what if we have a macro or a custom xml tag, say limit, when we write down

  <limit />

it can be automatically converted to

        <if test="limit!=null">
            LIMIT
            <if test="offset!=null">
                #{offset},#{limit}
            </if>
            <if test="offset==null">
                #{limit}
            </if>
        </if>

That saves our life ! And the same thing applies to the where clause, if

 <eq keys="id,status"/>

could be converted to

            <if test="id!=null">
                t.id = #{id}
            </if>
            <if test="status!=null">
               AND    t.status = #{status}
            </if>

To conclude,if we could develop custom tags like limit and eq and many many other tags that could be defined on the users demand, then the above select statement could be simplified,

    <select id="query" parameterType="common.PageMatrix" resultType="dal.entity.Blog">
        SELECT * 
        FROM t_blog t
        <where>
             <eq keys="id,status"/>
        </where>
        <limit />
   </select>

And considering that hundreds of such work could be saved(I mean in you project lies hundreds of such code), this work deserves our attempt!

In addition to that, the custom tag also provides a better explanation of its purpose

[kazuki43zoo]

The MyBatis provide <sql> and <include> tag for support sql fragment.

• http://www.mybatis.org/mybatis-3/sqlmap-xml.html#sql

Can you solve this issue using it?

[xhd2015]

I tried, and found two problems:

1. <sql> tag cannot be shared across different mapper xml files, thus <include> can help reducing limited repeating code
2. <include> tag is in some degree very general, there is no way to make an alias for it.And with using lots of <include> in xml, the structure of the code is destroyed.

[xhd2015]

On earth, there is no an easy way in current version of mybatis to define custom tag or something similar to it

[mnesarco]

@xhd2015 What about scripting drivers? In mybatis-velocity for example, you can define your own macros.

https://github.com/mybatis/velocity-scripting

There are other drivers too:

https://github.com/mybatis/thymeleaf-scripting

https://github.com/mybatis/freemarker-scripting

[kazuki43zoo]

1. <sql> tag cannot be shared across different mapper xml files, thus <include> can help reducing limited repeating code

Can share an sql fragment at multiple mapper xml (Can include an sql fragment that defined a different mapper xml). See https://github.com/mybatis/mybatis-3/tree/master/src/test/java/org/apache/ibatis/submitted/includes

[xhd2015]

Sure, share is possible.

Comparing the code I proposed above with the code built with include in practice, the latter one is less intuitional and import too much unnecessary and unrelated code.

The xml parser mybatis using does not pasrse xsd and namespace, in my opinion, to custom tags,we can intercept the xml parsing process, do some additional transform before passing the xml input stream to the xml builder.That's beyond mybatis.

[sxgkwei]

@xhd2015 What about scripting drivers? In mybatis-velocity for example, you can define your own macros.

https://github.com/mybatis/velocity-scripting

There are other drivers too:

https://github.com/mybatis/thymeleaf-scripting

https://github.com/mybatis/freemarker-scripting

@mnesarco
Your answer gave me a lot of inspiration. Found that they can not meet my hopes in the xml text itself， It's too bad . mapper tag not allowed include as its direct child i hope include script file by marking the lang attribute . So I can dynamically generate properties such as resultType/parameterType and table names. Even I can parse the corresponding java entity class attribute, generate the where part of the query statement that conforms to certain rules, and the set part of the update statement. mybatis-generator just doing a similar job.but will produce a lot of similar xml text, this is not the best way I hope. What advice do you have?

[romank0]

I think scripting drivers solve the DRY problem. Here's example of query using velocity from the real project:

<select id="queryForOne" resultMap="documentMap">
   WITH #user_info_cte
   SELECT #user_permission AS permissions,
           #document_and_file_columns('d')
   FROM document_head AS d
           #join_file_head('d')
           #join_security
           #join_document('d')
   WHERE $_parameter.whereClause
    AND d.state != 'DELETED'
</query>

And here are some examples of the macros used in the query:

#macro(unfilteredSecurityJoin $securityContext)
    INNER JOIN document_security ds
    ON (d.id = ds.document_id AND ds.acl_name = '$securityContext')
    INNER JOIN merged_acl macl
    ON (macl.id = ds.acl_id)
    LEFT JOIN document_head proj ON (proj.id = ds.project_id)
#end

#macro(join_security)
    #if (!$_parameter.invContext.user.superUser and !$_parameter.invContext.unrestricted)
        #unfilteredSecurityJoin($_parameter.invContext.securityContext)
    #end
#end

#macro(user_info_cte)
    #if (!$_parameter.invContext.user.superUser and !$_parameter.invContext.unrestricted)
        user_info AS (SELECT get_user_info($_parameter.invContext.user.id))
    #else
        pass AS (SELECT 1)
    #end
#end