how to use include property in <if>, <foreach> and <choose> inside <sql> ?

[gutsul...@gmail.com]

Hi,

I'm trying to implement select for searchable form using mybatis dynamic sql.
To prevent duplication of the similar parts I would like to use <sql> construction. I'm using the Mybatis 3.4.2 with Postgres
(I'm passing Range<Date> for createdOn and signedOn; some1Ids is custom interface that i can't modify)

  <sql id="filterIdSet">
    <bind name="idSet_" value="${idSet}"/>
    <if test="idSet_ != null and idSet_.ids.size > 0">
      AND ${column} IN
      <foreach item="item" index="index" collection="idSet_.ids" open="(" separator="," close=")">
        #{item}
      </foreach>
    </if>
  </sql>

  <sql id="filterDateRange">
    <bind name="rng" value="${range}"/>
    <if test="rng != null">
      <choose>
        <when test="rng.minimum != null and rng.maximum != null">
          AND ${column} AT TIME ZONE '#{timezone}' &gt;= ${range}.minimum AT TIME ZONE '#{timezone}'
          AND ${column} AT TIME ZONE '#{timezone}' &lt;= ${range}.maximum AT TIME ZONE '#{timezone}'
        </when>
        <when test="rng.minimum == null and rng.maximum != null">
          AND ${column} AT TIME ZONE '#{timezone}' &lt;= ${range}.maximum AT TIME ZONE '#{timezone}'
        </when>
        <when test="rng.minimum != null and rng.maximum == null">
          AND ${column} AT TIME ZONE '#{timezone}' &gt;= ${range}.minimum AT TIME ZONE '#{timezone}'
        </when>
        <otherwise/>
      </choose>
    </if>
  </sql>

  <select id="list" parameterType="map" resultMap="someMapper">
      <bind name="some1Ids" value="filter.some1Ids"/>
      <bind name="some2Ids" value="filter.some2Ids"/>
      <bind name="createdOn" value="filter.createdOn"/>
      <bind name="signedOn" value="filter.signedOn"/>
      SELECT
      <include refid="someColumns"/>
      FROM
      some_table ec
      WHERE
      ec."user_id" = #{user.id}
      <include refid="filterIdSet">
        <property name="column" value="ec.&quot;some1_ids&quot;"/>
        <property name="idSet" value="#{some1Ids}"/>
      </include>     
      <include refid="filterIdSet">
        <property name="column" value="ec.&quot;some2_ids&quot;"/>
        <property name="idSet" value="#{some2Ids}"/>
      </include>
      <include refid="filterDateRange">
        <property name="column" value="ec.&quot;created_on&quot;"/>
        <property name="range" value="#{createdOn}"/>
      </include>
      <include refid="filterDateRange">
        <property name="column" value="ec.&quot;signed_on&quot;"/>
        <property name="range" value="#{signedOn}"/>
      </include>
  </select>


I have checked documentation for <sql>  using http://www.mybatis.org/mybatis-3/sqlmap-xml.html and it looks like passed property from <include> element can be used directly in text (${prefix}Table) and inside xml attribute like:
<sql id="someinclude">
  from
    <include refid="${include_target}"/>
</sql>

But I'm not able to make it workable because of

MyBatisSystemException: nested exception is org.apache.ibatis.builder.BuilderException: Error evaluating expression '${idSet}'. Cause: org.apache.ibatis.ognl.ExpressionSyntaxException: Malformed OGNL expression: ${idSet} [org.apache.ibatis.ognl.ParseException: Encountered " "$" "$ "" at line 1, column 1.

So, how to do that ? Is there any example how to create some custom tag (similar to <foreach/> or <choose/>) like
<dateRange column="columnName" startDate="#{range.minimum}" endDate="#{range.maximum}" timezone="UTC"/>
that will produce
column AT TIME ZONE 'UTC' BETWEEN startDate AT TIME ZONE 'UTC' AND endDate AT TIME ZONE 'UTC'
 

Thanks,
Andrew

[Iwao AVE!]

Hi Andrew,

Currently, variable substitution is applied only to the text node of included sql fragment.

But it would be possible to enhance the feature to apply substitution to attributes as well.

I'll look into it when I have time.

Thank you for the post!

Iwao

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mybatis-user+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.