Mybatis with nested and

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

 

Thx,

Andrew

Started by Andrew at April 01, 2017 - 9:13 AM