数据库,创建一张辅助表,循环插入日期段日期,字段为:day(主键):日期;holiday:节日状态,0正常上班,1表示周末,假日(非法定),2表示法定假日
1、数据库层
- truncate calendar;
- set @date1='2010/01/01';
- set @date2='2010/12/31';
- delimiter $$
- drop procedure if exists test;
- create procedure test()
- begin
- declare tday VARCHAR(30);
- set tday='2000/01/01';
- while (tday <= '2100/12/31') do
- insert into calendar(day) values(tday);
- set tday=date_add(tday,interval 1 day);
- end while;
- end$$
- call test();
- update calendar set dow=dayofweek(day)-1;
- update calendar set dow=7 where dow=0;
- set @date1='2014/04/01';
- set @date2='2014/04/30';
- SELECT COUNT(*) FROM calendar
- WHERE day BETWEEN @date1 AND @date2
- AND ((DAYOFWEEK(day) NOT IN(1,7) AND holiday=0) or holiday=2);
- SELECT COUNT(*)
- FROM calendar
2:java 后台服务调用,每年跑一次,之前的可以一次性跑
//接口获取日期上班状态,1表示正常假日,2表示法定假日,0表示正常上班 每年一月一号
- /*
- * 日期是否节假日状态
- */
- public static final String GET_DATAHOLIDAYSTATUS_URL = "http://tool.bitefu.net/jiari/?d={date}";
- public String getUpdateDateYearHoliday(String sdate,String edate) throws Exception {
- List<String> listDate = new ArrayList<>();
- SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
- SimpleDateFormat dateFormat2 = new SimpleDateFormat("yyyyMMdd");
- try{
- Date startDate = dateFormat.parse(sdate);
- Date endDate = dateFormat.parse(edate);
- Calendar calendar = Calendar.getInstance();
- calendar.setTime(startDate);
- while(calendar.getTime().before(endDate)){
- System.out.println(dateFormat.format(calendar.getTime()));
- //listDate.add(dateFormat.format(calendar.getTime()));
- String date_main = dateFormat.format(calendar.getTime());
- String date = dateFormat2.format(calendar.getTime());
- //String date = calendar.YEAR+""+calendar.MONTH+""+calendar.DATE;
- try{
- // 1.获取请求的url
- String get_dataholidaystatus_url = GET_DATAHOLIDAYSTATUS_URL.replace("{date}", date);
- log.debug("get_dataholidaystatus_url------begin" + get_dataholidaystatus_url);
- JSONObject newapprovalJson = new JSONObject();
- String jsonStatus = WeiXinUtil.doPostString(get_dataholidaystatus_url, newapprovalJson);
- DataParam newDataParam = new DataParam();
- newDataParam.put("day",date_main);
- newDataParam.put("holiday",jsonStatus);
- //calendar
- MiddleDataService MiddleDataService = (MiddleDataService) this
- .lookupService("MiddleDataService");
- MiddleDataService.updateHolidayDayRecord(newDataParam);
- }
- catch(Exception e){
- System.out.println(date+"出现错误!");
- }
- calendar.add(Calendar.DAY_OF_MONTH, 1);
- }
- //return listDate;
- }
- catch(Exception e){
- e.printStackTrace();
- }
- return null;
- }
附加请求返回方法
//获取接口,字符串返回
- public static String doPostString(String url,JSONObject json){
- DefaultHttpClient client = new DefaultHttpClient();
- HttpPost post = new HttpPost(url);
- String response = "";
- try {
- StringEntity s = new StringEntity(json.toString());
- s.setContentEncoding("UTF-8");
- s.setContentType("application/json");//发送json数据需要设置contentType
- post.setEntity(s);
- HttpResponse res = client.execute(post);
- if(res.getStatusLine().getStatusCode() == HttpStatus.SC_OK){
- HttpEntity entity = res.getEntity();
- String result = EntityUtils.toString(res.getEntity());// 返回json格式:
- response = result;
- }
- } catch (Exception e) {
- throw new RuntimeException(e);
- }
- return response;
- }
自己添加一个方法,跑起来,添加服务,每年一月一号凌晨0点自动跑一次,辅助表中的节假日状态就完全出来了。如果想用,调用这个辅助表联动状态即可。