I would like to pass in a map and use mybatis to iterate through the map and execute multiple select statements and collect the results in a map. Is this possible? The below does an AND as one query.
<select id="populate" parameterType="Map" resultType="hashmap">
<foreach collection="dataMap" index="key" item="value" open=""
separator=" and " close="">
select count(*) as countS from tabl where
id LIKE #{key,jdbcType=VARCHAR} || '%'
</foreach>
</select>
The like as it is written will not work, the % must be part of the bound parameter. I would do something like this:
<bind name="currentKey" value = 'key + "%"' />
id LIKE #{currentKey,jdbcType=VARCHAR}
Edit: About the LIKE, I've given the most common answer, I have found other posts stating your method works and that it may even be better regarding security (injection) since the % is not part of bound parameter.
Is the % to be considered as part of the string or as operator?
Does it yield the same? Same results? Same performance? (if performance could be expected from a LIKE).
If there is 2 keys in the parameter map, then produced SQL will be:
SELECT COUNT(*) AS countS FROM tabl WHERE id LIKE ?
AND
SELECT COUNT(*) AS countS FROM tabl WHERE id LIKE ?
I'm pretty sure this cannot work.
The separator between Selects must be ;. If your DB allows multiple command statements.
If you are using only the keys from the input Map, then better pass only the keySet()
Eventually, I would write a single select that will return a single Map: key => count:
SELECT
<foreach collection="dataMap.keySet()" value="key", separator=", ">
<bind name="currentKey" value = 'key + "%"' />
SUM(CASE WHEN id LIKE #{currentKey,jdbcType=VARCHAR} THEN 1 ELSE 0 END) AS count_${key}
</foreach>
FROM tabl
Of course replace the CASE WHEN with the equivalent structure used by your DB dialect.
<foreach collection="dataMap" index="key" item="value" open="" separator="," close=""> SUM(CASE WHEN id LIKE #{key,jdbcType=VARCHAR} || '%' THEN 1 ELSE 0 END) AS "${key}" </foreach>