MyBatis传入数组参数进行循环

问题：需要传入一个时间范围(比如2017-07-20，2017-07-31)，查询表A,B,C每一天的记录数,这里联合查询应当用full join的，即A full join B on A.date=B.date full join C on A.date=C.date where A.date between '2017-07-20' and '2017-07-31'，这样当A在这一天没有记录，但是B或C有，这一天也会有记录。
但是所用数据库是mysql，不支持full join。还有一个问题就是即使用full join，如果这几个表都没有符合条件的记录，这一天也没有记录，当然这个代码层面可以处理。
解决思路：
不支持full join就逐表查然后用union all合并

SELECT count(1) as A_count  from A 
UNION ALL 
SELECT count(1) as B_count  from B
UNION ALL 
SELECT count(1) as C_count  from C

但是这样的结果是

image.png

很明显，这些数据的别名应该是分开的，修改为

SELECT count(1) as A_count,0 as B_count,0 as C_count from A
UNION ALL 
SELECT 0 as A_count,count(1) as B_count,0 as C_count from B
UNION ALL 
SELECT 0 as A_count,0 as B_count,count(1) as C_count from C

image.png

还不符合要求，我们需要的是一行数据，而不是三行，这里使用一个小技巧，用sum来合并这三行数据

SELECT sum(A_count) A_count ,sum(B_count) B_count,sum(C_count) C_count  from (
    SELECT count(1) as A_count,0 as B_count,0 as C_count from A
    UNION ALL 
    SELECT 0 as A_count,count(1) as B_count,0 as C_count from B
    UNION ALL 
    SELECT 0 as A_count,0 as B_count,count(1) as C_count from C
) t

image.png

成功了一半，我们需要每天这几张表的新增记录数，即使这一天一个新增记录也没有
由于数据库并没有日期表，所以需要自己得到日期集合
考虑在java层得到这个时间段的每一天{'2017-07-20","2017-07-21","2017-07-22"......''2017-07-31"},然后传入mybatis，遍历生成sql语句。

        List<String> dates = new ArrayList<>();
        dates.add(startDate);

        try {
            Date dateOne = dateFormat.parse(startDate);
            Date dateTwo = dateFormat.parse(endDate);

            Calendar calendar = Calendar.getInstance();

            calendar.setTime(dateOne);

            while (calendar.getTime().before(dateTwo)) {
                dates.add((dateFormat.format(calendar.getTime())));
                calendar.add(Calendar.DAY_OF_MONTH, 1);
            }
        } catch (Exception e) {
            logger.warn("时间参数不正确", e);
            return null;
        }
        dates.add(endDate);
        List<Map<String, Object>> list = mapper.selectWarning(dates);

mapper.java

List<Map<String, Object>> selectWarning(@Param(value="dates")List<String> dates);

mapper.xml

<select id="selectWarning" resultType="java.util.HashMap">
     <foreach collection="dates" item="date" index="index" open="" separator=" union " close="">
        SELECT #{date} as date,sum(A_count) A_count ,sum(B_count) B_count,sum(C_count) C_count  from (
          SELECT count(1) as A_count,0 as B_count,0 as C_count from A where date(create_date)=#{date}
          UNION ALL 
          SELECT 0 as A_count,count(1) as B_count,0 as C_count from B where date(create_date)=#{date}
          UNION ALL 
          SELECT 0 as A_count,0 as B_count,count(1) as C_count from C where date(create_date)=#{date}
        ) t
    </foreach>
        order by date desc
</select>

image.png