How can I reuse an SQL fragment with parameters?

Question

I'm intending to make a fragment for reusing with parameters.

<insert ...>
  <selectKey keyProperty="id" resultType="_long" order="BEFORE">
    <choose>
      <when test="_databaseId == 'derby'">
        VALUES NEXT VALUE FOR SOME_ID_SEQ
      </when>
      <otherwise>
        SELECT SOME_ID_SEQ.NEXTVAL FROM DUAL
      </otherwise>
    </choose>
  </selectKey>
  INSERT INTO ...
</insert>

Can I make an SQL fragment using parameters?

<sql id="selectKeyFromSequence">
  <selectKey keyProperty="id" resultType="_long" order="BEFORE">
    <choose>
      <when test="_databaseId == 'derby'">
        VALUES NEXT VALUE FOR #{sequenceName}
      </when>
      <otherwise>
        SELECT #{sequenceName}.NEXTVAL FROM DUAL
      </otherwise>
    </choose>
  </selectKey>
</sql>

So that I can reuse them like this?

<insert ...>
  <include refid="...selectKeyFromSequence"/> <!-- How can I pass a parameter? -->
  INSERT INTO ...
</insert>

Is this possible?

You can reuse sql fragments with parameters by using mybatis scripting http://stackoverflow.com/questions/25055276/reusing-dynamic-sql-fragments — Roman-Stop RU aggression in UA, Nov 14 '14 at 12:40

score 18 · Accepted Answer · answered Feb 15 '16 at 10:15

18

As of version 3.3.0 you can do it like this:

<sql id="myinclude">
  from ${myproperty}
</sql>

<include refid="myinclude">
  <property name="myproperty" value="tablename"/>
</include>

See section SQL in http://www.mybatis.org/mybatis-3/sqlmap-xml.html

answered Feb 15 '16 at 10:15

Vlasta Dolejs

368
3
8

score 1 · Answer 2 · edited May 23 '17 at 12:17

You cannot pass parameter to tags. There is a similar SO question, iBatis issue and a MyBatis issue.

Includes are in-lined when the xmls are parsed so the do not exist as their own once the startup process finishes (from MyBatis issue).

However, you can use variables inside tags. You do not pass it as a parameter but you can give it as a parameter to the function that has the include tag. You need to use the same variable name in all functions, i.e. #{sequenceName}.

How can I reuse an SQL fragment with parameters?

2 Answers2

Linked