Use an property of an object which is array of strings in mapper XML

Question

I want to refer to a property in an object in my mapper file which is an array of strings in a SQL IN criteria. The query does a count, so all it needs to return is a numeric value. The query needs to adjust its count based on a flexible set of criteria defined in a filter object. Some filters will be present (ie. not null), and others will be absent.

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="TotalUniqueUsers">
    <select id="getTotalUniqueUsers" 
            resultType="int" 
            parameterType="RequestFilter">
         SELECT *
         FROM MY_TABLE
         WHERE 
         <if test="quarterList!=null and quarterList.length>0">
             AND trim(FISCAL_QUARTER_NAME) IN #{quarterList javaType=list}
         </if>
    </select>
</mapper>


public class RequestFilter {
    private String[] quarterList;
    public String[] getQuarterList(){
        return this.quarterList;
    }
    public void setQuarterList(String[] quarterList){
        this.quarterList=quarterList;
    }
}

Note, there is no type handler for RequestFilter. I did not think I needed one. I'm not trying to take an object and condense it into say one field in some weird way. All I want to do is have an input parameter to the

With the above, I get org.apache.ibatis.exceptions.PersistenceException: ... Caused by: org.apache.ibatis.reflection.ReflectionException: There is no getter for property named 'quarterList javaType=list' in 'class RequestFilter'

I tried javaType=Array also, but get the same result. If I change the

#{quarterList javaType=list}

to

#{quarterList}

it says the typeHandler is null for the RequestFilter.

score 0 · Accepted Answer · answered Aug 28 '19 at 22:32

There is no portable way to set the list or array to IN prepared statement parameter in JDBC and therefore in mybatis (there are ways to do that if you are using postgres).

So in the general case you need to dynamically generate the query with a parameter per element in the list:

<select id="getTotalUniqueUsers" 
        resultType="int" 
        parameterType="RequestFilter">
     SELECT *
     FROM MY_TABLE
     WHERE 
     <if test="quarterList!=null and quarterList.length>0">
         trim(FISCAL_QUARTER_NAME) IN (             
            <foreach item='quarter' collection='quarterList' separator=','>
               #{quarter}
            </foreach>
         )
     </if>
</select>

Use an property of an object which is array of strings in mapper XML

1 Answers1