之前做了一个sql任务调度平台,sql根据配置的cron表达式执行;调度平台需要灵活的根据当前时间去计算并替换sql中特殊的时间宏;举个例子
select * from order a join order order_item b on a.id=b.order_id and a.day = ${date-1,yyyyMMdd}
and b.day=${date- 1,yyyyMMdd} and a.signed_time>${date-2}
如果今天是20181119,那调度平台执行的sql就需要替换${date-1,yyyyMMdd}
,${date-2}
,替换后如下
select * from order a join order order_item b on a.id=b.order_id and a.day = 20181118 and b.day=20181118
and a.signed_time>1542463989123
代码也很简单
public class Test{
public static void main(String[] args) {
String sql = "select * from order a join order order_item b on a.id=b.order_id and a.day = ${date-1,yyyyMMdd} " +
"and b.day=${date- 1,yyyyMMdd} and a.signed_time>${date-2}";
String pattern = "\\$\\{(.*?)\\}";
Pattern r = Pattern.compile(pattern);
Matcher m = r.matcher(sql);
Set<String> dates = new HashSet<String>();
while(m.find()){
dates.add(m.group(1));
}
System.out.println("需要格式化的时间串:"+dates);
for(String s:dates){
String st = "\\$\\{"+s+"\\}";
sql = sql.replaceAll(st,date_str(s));
// System.out.println(line.replaceAll("\\$\\{date-1,yyyyMMdd\\}","20181109"));
}
System.out.println(sql);
System.out.println(date_str("date-1,yyyyMMdd"));
System.out.println(date_str("date-1"));
System.out.println(date_str("date-1,yyyyMMddmmSS"));
}
public static String date_str(String str){
String[] arr = str.replaceAll(" +","").split(",");
Calendar cal = Calendar.getInstance();
int interval = Integer.parseInt(arr[0].replaceAll("date",""));
cal.add(Calendar.DATE,interval);
DateFormat df = null;
if(arr.length==1){
return cal.getTime().getTime()+"";
}else if(arr.length==2){
df = new SimpleDateFormat(arr[1]);
}else{
throw new RuntimeException("date error:"+str);
}
return df.format(cal.getTime());
}
}
输出如下
需要格式化的时间串:[date-2, date- 1,yyyyMMdd, date-1,yyyyMMdd]
select * from order a join order order_item b on a.id=b.order_id and a.day = 20181118 and b.day=20181118 and a.signed_time>1542463989123
20181118
1542550389166
2018111813166
end