[FIXED] sqlMapClientTemplate: empty array parameter causes BadSqlGrammarException

Issue

Java version is Java 8, and MySQL version is 5.6

I am running this line in Java Spring:

private SqlMapClientTemplate template = getSqlMapClientTemplate();
List<String> topicList = licReq.getTopicCategories();
template.queryForList("get_topic_ids_from_list", topicList);

where the "get_topic_ids_from_list" is defined in the following way in pom.xml file:

<select id="get_topic_ids_from_list" parameterClass="list" resultClass="Long">
  select id from topic_categories where topic_name in
    <iterate open="(" close=")" conjunction=",">
        #[]#
    </iterate>
</select>

When the parameter topicList is not empty, it works fine. However, if topicList is empty, it causes the following error:

org.springframework.jdbc.BadSqlGrammarException: SqlMapClient operation; bad SQL grammar []; nested exception is com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred while applying a parameter map.
--- Check the get_topic_ids_from_list-InlineParameterMap.
--- Check the statement (query failed).
--- Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

Is there a better way to define "get_topic_ids_from_list" query in pom.xml so that it just returns empty array (of type long), or do I have to make if statements for the empty array case?

Solution

You need to make sure topicList is not empty before querying

List<String> topicList = licReq.getTopicCategories();

if (topicList.size() > 0) {
    template.queryForList("get_topic_ids_from_list", topicList);
} else {
    // return empty result
}

Answered By - lucumt