sql中时间宏替换

之前做了一个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