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

Question

select * from users where id in ()

The request 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 Param is idsempty, Mybatis will throw a BadSqlGrammarException error that throws a request of type " select * from users where id in".

How to skip a request and return an empty list if idsempty?

Answer 1

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

To skip a request (do not execute it), just do not call Mybatis. The calling code should check for identifiers:

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

This is exactly what is being asked in the question.

, Mybatis , , ( ), . , - id = <!-- a value that will never exist in the table -->, , , ( ) . :

    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>

"" , , .

Answer 2

java-

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

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

, u.

Answer 3

:

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