Recursion in Mybatis SQL causing stack overflow while compile

Question

I have a class with recursive relation like below.

   Class A{
      String id;
      String op;
      String val;
      List<A> aList;
   }

I am using MySQL MyBatis to generate a query like below.


 <sql id="testRecursion">
        <foreach collection="A.aList" item="aList" open="(" separator=" UNION " close=")">
            <if test="aList.op == null">
                (
                SELECT sum(val) as val FROM
                FROM test_data
                WHERE id = #{aList.id} 
                )
            </if>

            <if test="aList.op== 'AND'">
                SELECT max(val) as val FROM
                <include refid="testRecursion"/>
            </if>

            <if test="aList.op== 'OR'">
                SELECT min(val) as val FROM
                <include refid="testRecursion"/>
            </if>
       </foreach>
  </sql>

It is giving below error with stack overflow exception like this while on the compilation.

at org.apache.ibatis.builder.xml.XMLIncludeTransformer.applyIncludes(XMLIncludeTransformer.java:74) ~[mybatis-3.4.5.jar:3.4.5]
at org.apache.ibatis.builder.xml.XMLIncludeTransformer.applyIncludes(XMLIncludeTransformer.java:62) ~[mybatis-3.4.5.jar:3.4.5]

Can someone please help me out here, how to implement recursion in mybatis.

Roman Konoval Roman Konoval · Accepted Answer · 2019-02-07T00:44:26

This does not work because include element does not support recursion, that is you can't include the sql snippet from itself.

If you switch to velocity scripting engine you can implement what you need with a macro that is allowed to reference itself.

Unfortunately, you haven't provided the invoking code for your snippet so my example may differ a bit and you need to adjust it but it should give you the idea.

Given this definition of A:

public class A {
  Integer id;
  String op;
  Integer val;
  List<A> nodes;
  // setters/getters omitted for brevity

You can define the mapper like this:

class MyMapper {
   Integer getRecursive(@Param("A") A a);
}

And query in xml:

<select id="getRecursive" lang="velocity" resultType="int">
    #macro(node $a)
        #if( ! $a.op )
            SELECT sum(val) as val FROM A
            WHERE id = ${a.id} 
        #else
            SELECT
            #if( $a.op == "AND" )
                max(val) as val
            #else
                min(val) as val
            #end
            FROM (
                #repeat( $a.nodes $aNode "UNION ALL" )
                   #node($aNode)
                #end
            )
        #end
    #end

    select val FROM (
        #node($_parameter.A)
    )
</select>

You need to configure the project to make velocity available.

The downside is that you can't bind parameters via prepared statement (note id = ${a.id} and not id = @{a.id}).

Here's how I invoked the mapper method:

@Test
public void testRecursive() {
    A a = or(leaf(1), and(leaf(2), leaf(3)));
    assertThat(sut.getRecursive(a), equalTo(1));
}

private A leaf(int id) {
    A a = new A();
    a.setId(id);
    return a;
}

private A or(A ... ops) {
    return operation("OR", ops);
}

private A and(A ... ops) {
    return operation("AND", ops);
}

private A operation(String operation, A ... ops) {
    A a = new A();
    a.setOp(operation);
    a.setNodes(Arrays.asList(ops));
    return a;
}

Recursion in Mybatis SQL causing stack overflow while compile

2 Answers