Java 在数据库中生成一年的假日与工作日信息
最近写了个在数据库中自行插入一年中假日与工作日数据的小程序,数据库表字段含有(年,月,日,日期,周几,假日标识,上一个工作日,下一个工作日),因每年的法定节假日及调休信息不同,故每一年的假日与工作日需要自行维护,改程序只需要将几个节日假期及补班的日期手动录入
[java]
view plain
copy
1. package com.java;
2.
3. import java.sql.Connection;
4. import java.sql.DriverManager;
5. import java.sql.Statement;
6. import java.text.SimpleDateFormat;
7. import java.util.ArrayList;
8. import java.util.Calendar;
9. import java.util.Date;
10. import java.util.List;
11.
12. /**
13. * Created by lime on 2016/7/31.
14. */
15. public class InsertHolidayUtil {
16.
17. public static void main(String[] args){
18. //驱动程序名
19. //String driver = "com.mysql.jdbc.Driver";
20. "oracle.jdbc.driver.OracleDriver";
21. //要插入的数据库
22. //String url = "jdbc:mysql://127.0.0.1:3306/xx_web";
23. "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
24. "payadm";
25. "payadm";
26. try {
27. //加载驱动程序
28. Class.forName(driver);
29. //连接数据库
30. Connection conn = DriverManager.getConnection(url, user, password);
31. if(!conn.isClosed())
32. "Succeeded connecting to the Database!");
33. //statement用来执行SQL语句
34. Statement statement = conn.createStatement();
35.
36. new SimpleDateFormat("yyyyMMdd");
37. "20170101");//开始时间
38. "20171231");//结束时间
39. List<Date> lists = dateSplit(start, end);
40.
41. "20180102");//下一个工作日,默认1月2日,1日为元旦
42. "20171229");//上一个工作日
43.
44. if (!lists.isEmpty()) {
45. for (Date date : lists) {
46. Calendar cal = Calendar.getInstance();
47. cal.setTime(date);
48. "********插入日期:" + sdf.format(date) + "***********");
49.
50. int year = cal.get(Calendar.YEAR);
51. int month = cal.get(Calendar.MONTH) + 1;
52. int day = cal.get(Calendar.DATE);
53. int week = cal.get(Calendar.DAY_OF_WEEK)-1;
54.
55. "",dayStr = "";
56. if(month/10 == 0){
57. "0"+String.valueOf(month);
58. else {
59. monthStr = String.valueOf(month);
60. }
61. if(day/10 == 0){
62. "0"+String.valueOf(day);
63. else {
64. dayStr = String.valueOf(day);
65. }
66.
67. lastWorkDate = getLastWorkDay(date);
68.
69. //T_CMM_TCLD表字段:年,月,日,日期,标识(1为假日,0为工作日),周几,上一个工作日,下一个工作日
70. "INSERT INTO T_CMM_TCLD (CLD_YEAR, CLD_MONTH, CLD_DAY, CLD_DATE, CLD_FLG, WEEK, LAST_WK_DT, NEXT_WK_DT) " +
71. "VALUES('"+year+"','"+monthStr+"','"+dayStr+"','"+sdf.format(date)+"','"+isHoliday(date)+"','"+week+"','"+sdf.format(lastWorkDate)+"','"+sdf.format(nextWorkDate)+"')";
72. // System.out.println(year+","+monthStr+","+dayStr+","+sdf.format(date));
73. // System.out.println(insertSql);
74. statement.execute(insertSql);
75. if(isHoliday(date) == 0){
76. nextWorkDate = date;
77. }
78. }
79. }
80.
81. conn.close();
82. }
83. catch(Exception e) {
84. e.printStackTrace();
85. }
86. }
87.
88. private static List<Date> dateSplit(Date start, Date end) throws Exception {
89. if (!start.before(end))
90. throw new Exception("开始时间应该在结束时间之后");
91. Long spi = end.getTime() - start.getTime();
92. 24 * 60 * 60 * 1000);// 相隔天数
93.
94. new ArrayList<Date>();
95. dateList.add(end);
96. for (int i = 1; i <= step; i++) {
97. new Date(dateList.get(i - 1).getTime() - (24 * 60 * 60 * 1000)));// 比上一天减一
98. }
99. return dateList;
100. }
101.
102. /**
103. * 判断是否为节假日,若是返回1,否则返回0
104. * @param date
105. * @return
106. */
107. private static int isHoliday(Date date){
108. Calendar cal = Calendar.getInstance();
109. cal.setTime(date);
110. List<String> holidays = getHolidays();
111. List<String> workdays = getWorkDays();
112. new SimpleDateFormat("yyyyMMdd");
113. // System.out.println(sdf.format(date));
114. if(((cal.get(Calendar.DAY_OF_WEEK)==Calendar.SATURDAY||cal.get(Calendar.DAY_OF_WEEK)==Calendar.SUNDAY)&&!workdays.contains(sdf.format(date))) || holidays.contains(sdf.format(date)) ) {
115. return 1;
116. }
117. return 0;
118. }
119.
120. private static Date getLastWorkDay(Date date){
121. Calendar cal = Calendar.getInstance();
122. cal.setTime(date);
123. 1);
124. Date dateTemp = cal.getTime();
125.
126. while (isHoliday(dateTemp) != 0){
127. 1);
128. dateTemp = cal.getTime();
129. }
130. return dateTemp;
131. }
132.
133. private static List<String> getHolidays(){
134. new ArrayList<String>();
135. /*holidays.add("20170101");//元旦
136. holidays.add("20170102");
137. holidays.add("20170103");
138.
139. holidays.add("20170218");//春节
140. holidays.add("20170219");
141. holidays.add("20170220");
142. holidays.add("20170221");
143. holidays.add("20170222");
144. holidays.add("20170223");
145. holidays.add("20170224");
146.
147. holidays.add("20170404");//清明节
148. holidays.add("20170405");
149. holidays.add("20170406");
150.
151. holidays.add("20170429");//劳动节
152. holidays.add("20170430");
153. holidays.add("20170501");
154.
155. holidays.add("20170620");//端午节
156. holidays.add("20170621");
157. holidays.add("20170622");
158.
159. holidays.add("20170927");//中秋节
160.
161. holidays.add("20171001");//国庆节
162. holidays.add("20171002");
163. holidays.add("20171003");
164. holidays.add("20171004");
165. holidays.add("20171005");
166. holidays.add("20171006");
167. holidays.add("20171007");*/
168. return holidays;
169. }
170.
171. private static List<String> getWorkDays(){
172. new ArrayList<String>();
173. /*workDays.add("20170104");//补班
174. workDays.add("20170215");
175. workDays.add("20170228");
176. workDays.add("20171010");*/
177. return workDays;
178. }
179. }
最近写了个在数据库中自行插入一年中假日与工作日数据的小程序,数据库表字段含有(年,月,日,日期,周几,假日标识,上一个工作日,下一个工作日),因每年的法定节假日及调休信息不同,故每一年的假日与工作日需要自行维护,改程序只需要将几个节日假期及补班的日期手动录入
[java]
view plain
copy
1. package com.java;
2.
3. import java.sql.Connection;
4. import java.sql.DriverManager;
5. import java.sql.Statement;
6. import java.text.SimpleDateFormat;
7. import java.util.ArrayList;
8. import java.util.Calendar;
9. import java.util.Date;
10. import java.util.List;
11.
12. /**
13. * Created by lime on 2016/7/31.
14. */
15. public class InsertHolidayUtil {
16.
17. public static void main(String[] args){
18. //驱动程序名
19. //String driver = "com.mysql.jdbc.Driver";
20. "oracle.jdbc.driver.OracleDriver";
21. //要插入的数据库
22. //String url = "jdbc:mysql://127.0.0.1:3306/xx_web";
23. "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
24. "payadm";
25. "payadm";
26. try {
27. //加载驱动程序
28. Class.forName(driver);
29. //连接数据库
30. Connection conn = DriverManager.getConnection(url, user, password);
31. if(!conn.isClosed())
32. "Succeeded connecting to the Database!");
33. //statement用来执行SQL语句
34. Statement statement = conn.createStatement();
35.
36. new SimpleDateFormat("yyyyMMdd");
37. "20170101");//开始时间
38. "20171231");//结束时间
39. List<Date> lists = dateSplit(start, end);
40.
41. "20180102");//下一个工作日,默认1月2日,1日为元旦
42. "20171229");//上一个工作日
43.
44. if (!lists.isEmpty()) {
45. for (Date date : lists) {
46. Calendar cal = Calendar.getInstance();
47. cal.setTime(date);
48. "********插入日期:" + sdf.format(date) + "***********");
49.
50. int year = cal.get(Calendar.YEAR);
51. int month = cal.get(Calendar.MONTH) + 1;
52. int day = cal.get(Calendar.DATE);
53. int week = cal.get(Calendar.DAY_OF_WEEK)-1;
54.
55. "",dayStr = "";
56. if(month/10 == 0){
57. "0"+String.valueOf(month);
58. else {
59. monthStr = String.valueOf(month);
60. }
61. if(day/10 == 0){
62. "0"+String.valueOf(day);
63. else {
64. dayStr = String.valueOf(day);
65. }
66.
67. lastWorkDate = getLastWorkDay(date);
68.
69. //T_CMM_TCLD表字段:年,月,日,日期,标识(1为假日,0为工作日),周几,上一个工作日,下一个工作日
70. "INSERT INTO T_CMM_TCLD (CLD_YEAR, CLD_MONTH, CLD_DAY, CLD_DATE, CLD_FLG, WEEK, LAST_WK_DT, NEXT_WK_DT) " +
71. "VALUES('"+year+"','"+monthStr+"','"+dayStr+"','"+sdf.format(date)+"','"+isHoliday(date)+"','"+week+"','"+sdf.format(lastWorkDate)+"','"+sdf.format(nextWorkDate)+"')";
72. // System.out.println(year+","+monthStr+","+dayStr+","+sdf.format(date));
73. // System.out.println(insertSql);
74. statement.execute(insertSql);
75. if(isHoliday(date) == 0){
76. nextWorkDate = date;
77. }
78. }
79. }
80.
81. conn.close();
82. }
83. catch(Exception e) {
84. e.printStackTrace();
85. }
86. }
87.
88. private static List<Date> dateSplit(Date start, Date end) throws Exception {
89. if (!start.before(end))
90. throw new Exception("开始时间应该在结束时间之后");
91. Long spi = end.getTime() - start.getTime();
92. 24 * 60 * 60 * 1000);// 相隔天数
93.
94. new ArrayList<Date>();
95. dateList.add(end);
96. for (int i = 1; i <= step; i++) {
97. new Date(dateList.get(i - 1).getTime() - (24 * 60 * 60 * 1000)));// 比上一天减一
98. }
99. return dateList;
100. }
101.
102. /**
103. * 判断是否为节假日,若是返回1,否则返回0
104. * @param date
105. * @return
106. */
107. private static int isHoliday(Date date){
108. Calendar cal = Calendar.getInstance();
109. cal.setTime(date);
110. List<String> holidays = getHolidays();
111. List<String> workdays = getWorkDays();
112. new SimpleDateFormat("yyyyMMdd");
113. // System.out.println(sdf.format(date));
114. if(((cal.get(Calendar.DAY_OF_WEEK)==Calendar.SATURDAY||cal.get(Calendar.DAY_OF_WEEK)==Calendar.SUNDAY)&&!workdays.contains(sdf.format(date))) || holidays.contains(sdf.format(date)) ) {
115. return 1;
116. }
117. return 0;
118. }
119.
120. private static Date getLastWorkDay(Date date){
121. Calendar cal = Calendar.getInstance();
122. cal.setTime(date);
123. 1);
124. Date dateTemp = cal.getTime();
125.
126. while (isHoliday(dateTemp) != 0){
127. 1);
128. dateTemp = cal.getTime();
129. }
130. return dateTemp;
131. }
132.
133. private static List<String> getHolidays(){
134. new ArrayList<String>();
135. /*holidays.add("20170101");//元旦
136. holidays.add("20170102");
137. holidays.add("20170103");
138.
139. holidays.add("20170218");//春节
140. holidays.add("20170219");
141. holidays.add("20170220");
142. holidays.add("20170221");
143. holidays.add("20170222");
144. holidays.add("20170223");
145. holidays.add("20170224");
146.
147. holidays.add("20170404");//清明节
148. holidays.add("20170405");
149. holidays.add("20170406");
150.
151. holidays.add("20170429");//劳动节
152. holidays.add("20170430");
153. holidays.add("20170501");
154.
155. holidays.add("20170620");//端午节
156. holidays.add("20170621");
157. holidays.add("20170622");
158.
159. holidays.add("20170927");//中秋节
160.
161. holidays.add("20171001");//国庆节
162. holidays.add("20171002");
163. holidays.add("20171003");
164. holidays.add("20171004");
165. holidays.add("20171005");
166. holidays.add("20171006");
167. holidays.add("20171007");*/
168. return holidays;
169. }
170.
171. private static List<String> getWorkDays(){
172. new ArrayList<String>();
173. /*workDays.add("20170104");//补班
174. workDays.add("20170215");
175. workDays.add("20170228");
176. workDays.add("20171010");*/
177. return workDays;
178. }
179. }