java生成一年中假日表(包括周末和法定假期),用于计算一年中的工作日
项目中有个需求要求计算工作日天数的,于是写了段代码用于生成一年假日表,以辅助实现功能
思路:计算一个时间区间内工作日的天数,只要计算出这个时间区间的天数,再减去休息日的天数就可以了。但是有点麻烦的是休息日的统计,因为它不仅包括周末时间,还包括放假时间,放假还要考虑周末补班的,工作日放假休息的。
步骤:
1、把一年内的所有周末加入到假期表中。
2、把一年中所有的法定假期加入到假期表中(需等国务院公布,所以要一年跑一次维护假期表)。
3、把周末需要补班的日子从表中剔除掉。
上面还有一个细节就是法定假期里可能也有周末,所以插入前要检查避免重复插入。这样我们就得到了一张包括一年中所有假期的假期数据表了。具体实现代码如下,纯属能跑没仔细优化:
package com.util;
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 java.util.UUID;
/**
* 将一年内的所有假日插入到假日表里
* @author ch
* @time 2016-1-15 下午6:06:11
*/
public class InsertHolidayUtil {
public static void main(String[] args){
//驱动程序名
String driver = "com.mysql.jdbc.Driver";
//要插入的数据库,表
String url = "jdbc:mysql://127.0.0.1:3306/xx_web";
String user = "root";
String password = "123456";
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("2016-01-01");//开始时间
java.util.Date end = sdf.parse("2016-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 (id,title,holiday_date) VALUES('"+UUID.randomUUID()+"',"+"'周末','"+sdf.format(date)+"')";
statement.executeUpdate(insertSql);
}
}
}
//---------------插入节假日时间------------------
List<Days> holidays = new ArrayList<Days>();
holidays.add(new Days(UUID.randomUUID().toString(),"元旦", sdf.parse("2016-01-01")));
holidays.add(new Days(UUID.randomUUID().toString(),"元旦", sdf.parse("2016-01-02")));
holidays.add(new Days(UUID.randomUUID().toString(),"元旦", sdf.parse("2016-01-03")));
holidays.add(new Days(UUID.randomUUID().toString(),"春节", sdf.parse("2016-02-07")));
holidays.add(new Days(UUID.randomUUID().toString(),"春节", sdf.parse("2016-02-08")));
holidays.add(new Days(UUID.randomUUID().toString(),"春节", sdf.parse("2016-02-09")));
holidays.add(new Days(UUID.randomUUID().toString(),"春节", sdf.parse("2016-02-10")));
holidays.add(new Days(UUID.randomUUID().toString(),"春节", sdf.parse("2016-02-11")));
holidays.add(new Days(UUID.randomUUID().toString(),"春节", sdf.parse("2016-02-12")));
holidays.add(new Days(UUID.randomUUID().toString(),"春节", sdf.parse("2016-02-13")));
holidays.add(new Days(UUID.randomUUID().toString(),"清明节", sdf.parse("2016-04-02")));
holidays.add(new Days(UUID.randomUUID().toString(),"清明节", sdf.parse("2016-04-03")));
holidays.add(new Days(UUID.randomUUID().toString(),"清明节", sdf.parse("2016-04-04")));
holidays.add(new Days(UUID.randomUUID().toString(),"劳动节", sdf.parse("2016-04-30")));
holidays.add(new Days(UUID.randomUUID().toString(),"劳动节", sdf.parse("2016-05-01")));
holidays.add(new Days(UUID.randomUUID().toString(),"劳动节", sdf.parse("2016-05-02")));
holidays.add(new Days(UUID.randomUUID().toString(),"端午节", sdf.parse("2016-06-09")));
holidays.add(new Days(UUID.randomUUID().toString(),"端午节", sdf.parse("2016-06-10")));
holidays.add(new Days(UUID.randomUUID().toString(),"端午节", sdf.parse("2016-06-11")));
holidays.add(new Days(UUID.randomUUID().toString(),"中秋节", sdf.parse("2016-09-15")));
holidays.add(new Days(UUID.randomUUID().toString(),"中秋节", sdf.parse("2016-09-16")));
holidays.add(new Days(UUID.randomUUID().toString(),"中秋节", sdf.parse("2016-09-17")));
holidays.add(new Days(UUID.randomUUID().toString(),"国庆节", sdf.parse("2016-10-01")));
holidays.add(new Days(UUID.randomUUID().toString(),"国庆节", sdf.parse("2016-10-02")));
holidays.add(new Days(UUID.randomUUID().toString(),"国庆节", sdf.parse("2016-10-03")));
holidays.add(new Days(UUID.randomUUID().toString(),"国庆节", sdf.parse("2016-10-04")));
holidays.add(new Days(UUID.randomUUID().toString(),"国庆节", sdf.parse("2016-10-05")));
holidays.add(new Days(UUID.randomUUID().toString(),"国庆节", sdf.parse("2016-10-06")));
holidays.add(new Days(UUID.randomUUID().toString(),"国庆节", sdf.parse("2016-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 (id,title,holiday_date) VALUES('"+day.getId()+"',"+"'"+day.getTitle()+"','"+sdf.format(day.getDate())+"')";
statement.executeUpdate(insertSql);
}
}
//-------------- 剔除补班时间(周末需要补班的)---------------------
List<Days> workDays = new ArrayList<Days>();
workDays.add(new Days(UUID.randomUUID().toString(),"补班", sdf.parse("2016-02-06")));
workDays.add(new Days(UUID.randomUUID().toString(),"补班", sdf.parse("2016-02-14")));
workDays.add(new Days(UUID.randomUUID().toString(),"补班", sdf.parse("2016-05-02")));
workDays.add(new Days(UUID.randomUUID().toString(),"补班", sdf.parse("2016-06-12")));
workDays.add(new Days(UUID.randomUUID().toString(),"补班", sdf.parse("2016-09-18")));
workDays.add(new Days(UUID.randomUUID().toString(),"补班", sdf.parse("2016-10-08")));
workDays.add(new Days(UUID.randomUUID().toString(),"补班", sdf.parse("2016-10-09")));
for(Days day:workDays) {
System.out.println("剔除日期:" + sdf.format(day.getDate()) + "," + day.getTitle());
String delSql = "delete from fn_all_holiday where holiday_date ='" + sdf.format(day.getDate()) + "'";
statement.executeUpdate(delSql);
}
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.util;
import java.util.Date;
public class Days {
private String id;
private String title;
private Date date;
public Days(String id,String title,Date date) {
this.id = id;
this.title = title;
this.date = date;
}
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public Date getDate() {
return date;
}
public void setDate(Date date) {
this.date = date;
}
}