How would I pass parameter and check it in mybatis dynamic query creation

Question

I have written a select query inside my mapper.xml

<select id="getCatCount" parameterType="String" resultType="int">
    select count(*) from Categories where status is not null  
    <if test="catTypeId != 0">
    AND cat_type_id = #{catTypeId,jdbcType=INTEGER}
    </if>  
</select>

and in mapper.java method is

int getCatCount(int catTypeId);

How can I check for catTypeId in if condition. I know the above statement is incorrect but I want to put condition like this so I check if catTypeId not zero then only add AND condition. Or do I need to pass whole object of Category class?

Answer 1

You do not need to pass the whole Category class. Just do it as you described:

int getCatCount(int catTypeId);

Your mapper.xml should look like this:

<select id="getCatCount" parameterClass="map" resultType="int">
  select count(*) from Categories where status is not null  
   <if test="param1 != 0">
     AND cat_type_id = #{param1,jdbcType=INTEGER}
   </if>  
</select>

Notice that as parameterClass you need to specify a map.

Let's say that now, you want to pass two parameters:

int getCatCount(int catTypeId, int mySecondParam);

In the mapper, you should work with param1 and param2

See, how you need to use "paramX" nomenclature. But let's say, that instead using that nomenclature, you would like to use a customized parameter name as "catTypeId". For that, in you Java code you need to do this:

int getCatCount( @Param("cat_type_id ") String cat_type_id);

The XML mapper would be the one that I put it about, but using cat_type_id instead param1.