How can I reuse an SQL fragment with parameters?

9.9k views Asked by At

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?

2

There are 2 answers

0
Vlasta Dolejs On BEST ANSWER

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

0
Yigitalp Ertem On

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}.