排除节假日的功能

Jdbc数据库连接

package com.test.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class DBConn {
        public static final String url = "jdbc:mysql://localhost:3306/test";  
        public static final String name = "com.mysql.jdbc.Driver";  
        public static final String user = "root";  
        public static final String password = "152152";  

        Connection conn = null;
        public PreparedStatement pst = null;  

        public Connection Conn() {  
            try {  
                Class.forName(name);
                conn = DriverManager.getConnection(url, user, password);
            } catch (Exception e) {  
                e.printStackTrace();  
            }  
            return conn;
        }  

        public void close() {  
            try {  
                this.conn.close();  
            } catch (SQLException e) {  
                e.printStackTrace();  
            }  
    }  
}

首先生成休息日和节假日到数据库

package test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import com.test.pojo.Days;

public class Dtaetest {
    public static void main(String[] args){
            //驱动程序名
             String driver = "com.mysql.jdbc.Driver"; 
             //要插入的数据库，表
             String url = "jdbc:mysql://127.0.0.1:3306/test";  
             String user = "root"; 
             String password = "152152";
             try {    
                       //加载驱动程序
                       Class.forName(driver);  
                       //连续MySQL 数据库
                       Connection conn = DriverManager.getConnection(url, user, password);
                       if(!conn.isClosed())
                       System.out.println("Succeeded connecting to the Database!");
                       //statement用来执行SQL语句
                       Statement statement = conn.createStatement();

                       SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                       java.util.Date start = sdf.parse("2017-01-01");//开始时间
                       java.util.Date end = sdf.parse("2017-12-31");//结束时间
                       List<Date> lists = dateSplit(start, end);

                       //-------------------插入周末时间---------------
                       if (!lists.isEmpty()) {
                           for (Date date : lists) {
                          Calendar cal = Calendar.getInstance();
                           cal.setTime(date);
                               if(cal.get(Calendar.DAY_OF_WEEK)==Calendar.SATURDAY||cal.get(Calendar.DAY_OF_WEEK)==Calendar.SUNDAY)
                               {
                              System.out.println("插入日期:" + sdf.format(date) + ",周末");
                              String insertSql = "INSERT INTO fn_all_holiday (title,holiday_date) VALUES("+"'周末','"+sdf.format(date)+"')";
                              statement.executeUpdate(insertSql);
                               }
                           }
                       }

                       //---------------插入节假日时间------------------
                       List<Days> holidays = new ArrayList<Days>();

                       holidays.add(new Days(1,"元旦", sdf.parse("2017-01-01")));
                       holidays.add(new Days(2,"元旦", sdf.parse("2017-01-02")));
                       holidays.add(new Days(3,"元旦", sdf.parse("2017-01-03")));

                       holidays.add(new Days(4,"春节", sdf.parse("2017-02-07")));
                       holidays.add(new Days(5,"春节", sdf.parse("2017-02-08")));
                       holidays.add(new Days(6,"春节", sdf.parse("2017-02-09")));
                       holidays.add(new Days(7,"春节", sdf.parse("2017-02-10")));
                       holidays.add(new Days(8,"春节", sdf.parse("2017-02-11")));
                       holidays.add(new Days(9,"春节", sdf.parse("2017-02-12")));
                       holidays.add(new Days(10,"春节", sdf.parse("2017-02-13")));

                       holidays.add(new Days(11,"清明节", sdf.parse("2017-04-02")));
                       holidays.add(new Days(12,"清明节", sdf.parse("2017-04-03")));
                       holidays.add(new Days(13,"清明节", sdf.parse("2017-04-04")));

                       holidays.add(new Days(14,"劳动节", sdf.parse("2017-04-30")));
                       holidays.add(new Days(15,"劳动节", sdf.parse("2017-05-01")));
                       holidays.add(new Days(16,"劳动节", sdf.parse("2017-05-02")));

                       holidays.add(new Days(17,"端午节", sdf.parse("2017-06-09")));
                       holidays.add(new Days(18,"端午节", sdf.parse("2017-06-10")));
                       holidays.add(new Days(19,"端午节", sdf.parse("2017-06-11")));

                       holidays.add(new Days(20,"中秋节", sdf.parse("2017-09-15")));
                       holidays.add(new Days(21,"中秋节", sdf.parse("2017-09-16")));
                       holidays.add(new Days(22,"中秋节", sdf.parse("2017-09-17")));

                       holidays.add(new Days(23,"国庆节", sdf.parse("2017-10-01")));
                       holidays.add(new Days(24,"国庆节", sdf.parse("2017-10-02")));
                       holidays.add(new Days(25,"国庆节", sdf.parse("2017-10-03")));
                       holidays.add(new Days(26,"国庆节", sdf.parse("2017-10-04")));
                       holidays.add(new Days(27,"国庆节", sdf.parse("2017-10-05")));
                       holidays.add(new Days(28,"国庆节", sdf.parse("2017-10-06")));
                       holidays.add(new Days(29,"国庆节", sdf.parse("2017-10-07")));
                       for(Days day:holidays) {
                      //跟周末冲突的，不重复插入
                           String sql = "select count(1) as numbers from fn_all_holiday where holiday_date ='" + sdf.format(day.getDate()) + "'";
                           //结果集
                           ResultSet rs = statement.executeQuery(sql);
                           boolean hasRecord = false;
                           while(rs.next()) {

                          if(!"0".equals(rs.getString("numbers"))) {
                          hasRecord = true;
                          }
                           }
                           if(!hasRecord) {
                          System.out.println("插入日期：" + sdf.format(day.getDate()) + "," + day.getTitle());
                          String insertSql = "INSERT INTO fn_all_holiday (title,holiday_date) VALUES('"+day.getTitle()+"','"+sdf.format(day.getDate())+"')";
                          statement.executeUpdate(insertSql);
                       }
                   }
                       conn.close();
                 }
              catch(ClassNotFoundException e) { 
                System.out.println("Sorry,can't find the Driver!");
                e.printStackTrace();
               }
              catch(SQLException e) {
                e.printStackTrace();
              }
              catch(Exception e) {  
                e.printStackTrace(); 
               }
          }

    private static List<Date> dateSplit(java.util.Date start, Date end) throws Exception {
        if (!start.before(end))
            throw new Exception("开始时间应该在结束时间之后");
        Long spi = end.getTime() - start.getTime();
        Long step = spi / (24 * 60 * 60 * 1000);// 相隔天数

        List<Date> dateList = new ArrayList<Date>();
        dateList.add(end);
        for (int i = 1; i <= step; i++) {
            dateList.add(new Date(dateList.get(i - 1).getTime() - (24 * 60 * 60 * 1000)));// 比上一天减一
        }
        return dateList;
    }
    }

在写测试类

package com.test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import com.test.jdbc.DBConn;

/**  
 * <p>Title: main </P> 
 * <p>Description: TODO </P> 
 * @param args 
 * return void    返回类型  
 * throws  
 * date 2014-11-24 上午09:11:47 
 */  
public class DateUtil{
public static void main(String[] args) {  
    try {  
         DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
         DBConn conn = new DBConn();
         Connection conn2 = conn.Conn();
         String sql= "select * from lian";
         PreparedStatement statement = conn2.prepareStatement(sql);

         ResultSet resultSet = statement.executeQuery();
         //定义集合封装立案时间
         List<Calendar> lians=new ArrayList<>();
         while(resultSet.next()){
             String holiday = resultSet.getString(2);
             Calendar calendar= Calendar.getInstance();
             Date date = df.parse(holiday);
             calendar.setTime(date);
             lians.add(calendar);
             System.out.println("所有立案时间:"+df.format(date));
         }
         for (Calendar calendar : lians) {
             //初始化集合
             initHolidayList();
             //10个工作日排除法定节日和双休后的日期
             String c = addDateByWorkDay(calendar,10);  
//           System.out.println("开始日期:"+df.format(calendar));
             System.out.println("10个工作日排除法定节日和双休后的日期:"+c);  

        }

    } catch ( Exception e) {  
        // TODO: handle exception  

        e.printStackTrace();  
    }  

}  

 private static List<Calendar> holidayList = new ArrayList<Calendar>();  //节假日列表  

 /** 
  *  
  * <p>Title: addDateByWorkDay </P> 
  * <p>Description: TODO  计算相加day天，并且排除节假日和周末后的日期</P> 
  * @param calendar  当前的日期 
  * @param day  相加天数 
  * @return    
  * return Calendar    返回类型   返回相加day天，并且排除节假日和周末后的日期 
  * throws  
  * date 2014-11-24 上午10:32:55 
  */  
 public static String addDateByWorkDay(Calendar calendar,int day){  

     try {  
        for (int i = 0; i < day; i++) {  
              //天数加1
             calendar.add(Calendar.DAY_OF_MONTH, 1);  
               //校验是否是休息日
             if(checkHoliday(calendar)){ 
                 //是,这天不算
                 i--;  
             }  
        }  
    } catch (Exception e) {  
        e.printStackTrace();  
    }  
     DateFormat df = new SimpleDateFormat("yyyy-MM-dd");  
    return  df.format(calendar.getTime());  
 }  

 /** 
  *  
  * <p>Title: checkHoliday </P> 
  * <p>Description: TODO 验证日期是否是节假日</P> 
  * @param calendar  传入需要验证的日期 
  * @return  
  * return boolean    返回类型  返回true是节假日，返回false不是节假日 
  * throws  
  * date 2014-11-24 上午10:13:07 
  */  
 public static boolean checkHoliday(Calendar calendar) throws Exception{  

     //判断日期是否是周六周日  
     if(calendar.get(Calendar.DAY_OF_WEEK) == Calendar.SUNDAY ||   
             calendar.get(Calendar.DAY_OF_WEEK) == Calendar.SATURDAY){  
         return true;  
     }  
     //判断日期是否是节假日  
     for (Calendar ca : holidayList) {  
        if(ca.get(Calendar.MONTH) == calendar.get(Calendar.MONTH) &&  
                ca.get(Calendar.DAY_OF_MONTH) == calendar.get(Calendar.DAY_OF_MONTH)&&  
                ca.get(Calendar.YEAR) == calendar.get(Calendar.YEAR)){  
            return true;  
        }  
    }  

     return false;  
 }  

 /** 
  *  
  * <p>Title: initHolidayList </P> 
  * <p>Description: TODO  把所有节假日放入list，验证前要先执行这个方法</P> 
  * @param date  从数据库查 查出来的格式2014-05-09 
  * return void    返回类型  
  * throws  
  * date 2014-11-24 上午10:11:35 
 * @throws Exception 
  */  
public static  void initHolidayList() throws Exception {  
  //创建数据库连接
    DBConn conn = new DBConn();
    Connection connection = conn.Conn();
    //查询表
    String sql = "select * from fn_all_holiday";

    PreparedStatement statement = connection.prepareStatement(sql);
    //得到结果集
    ResultSet resultSet = statement.executeQuery();

    while (resultSet.next()) {
        //去结果集的时间字段
        String string = resultSet.getString(3);
        String[] da = string.split("-");
        Calendar calendar = Calendar.getInstance();
        calendar.set(Calendar.YEAR, Integer.valueOf(da[0]));
        calendar.set(Calendar.MONTH, Integer.valueOf(da[1]) - 1);// 月份比正常小1,0代表一月
        calendar.set(Calendar.DAY_OF_MONTH, Integer.valueOf(da[2]));
        //把结果集放入集合
        holidayList.add(calendar);
    }

}
}

运行结果如下: