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 dates = new HashSet();

        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