How can I skip query if where_in clause is empty in MyBatis 3?

Question

select * from users where id in ()

The query is shown above.

<select id="getByIds" resultMap="BaseResultMap">
    SELECT
    <include refid="BaseColumnList"/>
    FROM users
    WHERE id IN
    <foreach item="id" collection="ids"
             open="(" separator="," close=")">
        #{id}
    </foreach>
</select>

If the Param ids is empty, Mybatis will throw BadSqlGrammarException, which generate a query like 'select * from users where id in'.

How can I skip query and return empty list if ids is empty?

Answer 1

How can I skip query and return empty list if ids is empty?

To skip the query (not execute it), just don't call Mybatis. The calling code should check if ids is empty:

return null == ids || ids.isEmpty() ? new ArrayList<User>() : session.select("getByIds", ids);

This is exactly what is asked in the question.

If you really want Mybatis to handle this, then produced query must be valid because must be executed (then not skipped) to return empty result quickly. that means forget something like id = <!-- a value that will never exist in the table --> because it could surely involve a (free and useless) full scan to search the unexisting value. Then:

    WHERE 
    <choose>
        <when test="ids==null || ids.isEmpty()">
            1 = 0 <!-- a test returning false, to adapt depending on you DB vendor -->
        </when>
        <otherwise>
            id IN <foreach item="id" collection="ids" open="(" separator="," close=")">#{id}</foreach>
        </otherwise>
    </choose>

Another option to confirm would consist in using interceptors to "cancel" the query before its execution, but this is definitely overkill complexity for what has to be achieved here.

Answer 2

java code function

 List<ApiPriceChlogEntity> getApiAndDevPrice(@Param("apiKeys") List<String> currentApiKey, @Param("devKeys") List<String> currentDevKey, @Param("startDate") Date startDate);

the mapper file

<select id="getApiAndDevPrice"  resultMap="BaseResultMap">
SELECT
<include refid="Base_Column_List"/>
FROM t_api_price_chlog tab1
<where>
    <if test="apiKeys.size() > 0">
      tab1.api_key IN
      <foreach collection="apiKeys" item="item" separator="," open="(" close=")" index="">
        #{item}
      </foreach>
    </if>
    <if test="devKeys.size() > 0">
      AND tab1.dev_key IN
      <foreach collection="devKeys" item="item" separator="," open="(" close=")" index="">
        #{item}
      </foreach>
    </if>

    <if test="startDate != null">
      AND tab1.change_date >= #{startDate}
    </if>
</where>

I have test it,hope to help u.