定时任务创建分表，转移数据

//定时任务执行

@Scheduled(cron = "0 10 16 5 * ?")
public void doReportDateHistory() throws Exception {
if (!InitUtil.isVideoShowMain) return;
MyUtil mu = MyUtil.getInstance();
Date date = mu.get_N_Month(mu.getCurrenTime(),-1); //往前推一个月
SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM");
SimpleDateFormat formatter2 = new SimpleDateFormat("yyyyMM");
Date date2 = mu.get_N_Month(mu.getCurrenTime(),0);
String dateStart = formatter.format(date);
String dateEnd = formatter.format(date2);
String dateString = formatter2.format(date);

Map<String,Object> paramMap = new HashMap<>();
paramMap.put("dateStart",dateStart);
paramMap.put("dateEnd",dateEnd);
paramMap.put("dateString",dateString);
reportDataService.doReportDateHistory(paramMap);
}

//下面具体的代码

public void doReportDateHistory(Map<String, Object> paramMap) {
MyUtil mu = MyUtil.getInstance();
//1.先得到参数
String dateStart = paramMap.get("dateStart").toString();
String dateEnd = paramMap.get("dateEnd").toString();
String dateString = paramMap.get("dateString").toString();

//2.先创建表
String sql = "CREATE TABLE IF NOT EXISTS bi_appdata.t_ad_report_date_history_" + dateString + " (" +
" fb_ad_network_revenue decimal(50,6) DEFAULT '0.000000' COMMENT '预计收入'," +
" fb_ad_network_request int(11) DEFAULT '0' COMMENT '广告请求数量'," +
" country varchar(25) NOT NULL COMMENT '国家'," +
" ad_space varchar(50) NOT NULL COMMENT 'ad_space'," +
" ctime date NOT NULL," +
" business_id varchar(50) NOT NULL," +
" type int(5) NOT NULL COMMENT '1,2" +
" PRIMARY KEY (business_id,ctime,ad_space,)" +
") ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='广告收入表'";
System.out.println(sql);
this.getBaseDao().excuteBySql(sql,null);

//3.转移数据
String sql2 = "INSERT INTO bi_appdata.t_ad_report_date_history_" + dateString + "" +
" SELECT * FROM bi_appdata.t_ad_report_date_history WHERE ctime >= '" +
dateStart + "-01' AND ctime < '" + dateEnd + "-01'";
System.out.println(sql2);
this.getBaseDao().excuteBySql(sql2,null);

//4.删除原表的数据
String sql3 = "delete from bi_appdata.t_ad_report_date_history WHERE ctime >= '"
+ dateStart + "-01' AND ctime < '" + dateEnd + "-01'";
System.out.println(sql3);
this.getBaseDao().excuteBySql(sql3,null);
}