Included SQL fragment doesn't substitute parameter

Question

My mapper interface looks like this.

public interface CommonMapper {
    long selectSequenceNumber(String sequenceName);
}

And I prepared sql mapper looks like this.

<sql id="sequenceNumber">
  <choose>
    <when test="_databaseId == 'derby'">
      VALUES NEXT VALUE FOR #{sequenceName}
    </when>
    <otherwise>
      SELECT #{sequenceName}.NEXTVAL FROM DUAL
    </otherwise>
  </choose>
</sql>
<select id="selectSequenceNumber" resultType="_long">
  <!--<include refid="....mapper.CommonMapper.sequenceNumber"/>-->
  <include refid="sequenceNumber"/>
</select>

When I test mapper #{sequenceName} part is not substituted with given.

14:36:09.492 [main] DEBUG ....selectSequenceNumber - ==>  Preparing: VALUES NEXT VALUE FOR ? 
14:36:09.724 [main] ERROR ....PersistenceTests - failed to apply function for SqlSession
org.apache.ibatis.exceptions.PersistenceException: 
### Error querying database.  Cause: java.sql.SQLSyntaxErrorException: Syntax error: Encountered "?" at line 1, column 23.
### The error may exist in .../CommonMapper.xml
### The error may involve ....CommonMapper.selectSequenceNumber
### The error occurred while executing a query
### SQL: VALUES NEXT VALUE FOR ?
### Cause: java.sql.SQLSyntaxErrorException: Syntax error: Encountered "?" at line 1, column 23.

Is this normal? How can I fix this?

Answer 1

You need to use string substitution ${sequenceName} instead of setting the parameter with #{} for direct string placement in your queries (such as for giving object names, dynamic sql etc...)

<sql id="sequenceNumber">
  <choose>
    <when test="_databaseId == 'derby'">
      VALUES NEXT VALUE FOR ${sequenceName}
    </when>
    <otherwise>
      SELECT ${sequenceName}.NEXTVAL FROM DUAL
    </otherwise>
  </choose>
</sql>
<select id="selectSequenceNumber" resultType="_long">
  <!--<include refid="....mapper.CommonMapper.sequenceNumber"/>-->
  <include refid="sequenceNumber"/>
</select>