多个分区表 mybaties foreach动态查询
获取两个日期之间的日期
比如 分区表 是按天进行分区 分区名 partition (p20191001) partition (p20191002)
public void test(String time1, String time2,String tableName) {
List<String> tableNames = new ArrayList<>(16);
try {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
Date start_date = sdf.parse(time1);
Date end_date = sdf.parse(time2);
Calendar tempStart = Calendar.getInstance();
tempStart.setTime(start_date);
Calendar tempEnd = Calendar.getInstance();
tempEnd.setTime(end_date);
while (tempStart.before(tempEnd) || tempStart.equals(tempEnd)) {
String data = sdf.format(tempStart.getTime());
String yy = data.substring(0, 4);
String mm = data.substring(5, 7);
String day = data.substring(8, 10);
tableNames.add(tableName+ " partition" + " (p" + yy+mm+day + ")" );
System.out.println(tableName+ " partition" + " (p" + yy+mm+day + ")===";
tempStart.add(Calendar.DAY_OF_YEAR, 1);
}
} catch (Exception e) {
e.printStackTrace();
}
//最后tableNames 为 : [tableName partition(p20191001),tableName partition(p20191002) ]
}
mybaties sql 动态拼接表名
mybaties foreach 循环前 加一对 括号, open 和 close 为空,separator为 union all
foreach 里的 from 后的表名不能用 ? 代替, 所以用${}
SELECT STREET_NAME as name,ROUND(SUM( NUM ) , 0) as num
FROM (
//tableNames为上一段代码传过来的参数
//foreach 前后 有一对 括号
//括号里的内容(也就是循环的SQL 结果) 为 下边三行
//select street_o, num from tableName partition(p20191001)
//union all
//select street_o, num from tableName partition(p20191002)
<foreach collection="tableNames" open="" close="" separator="union all" index="index" item="name">
select street_o, num from ${name}
</foreach>
)
LEFT JOIN ( SELECT DISTINCT STREET,STREET_NAME FROM TBL_POINT_XZQH ) ON STREET_O=STREET