需求:每年的法定节假日都是不固定的,我们需要把固定的那些日子存入数据库中,每年12月份颁布下一年的节假日,将这些节假日和调班数据更新到库表中。这样就可以知道下一年的节假日情况了
数据库表设计:
drop table if exists t_holiday_bill;
/*==============================================================*/
/* Table: t_holiday_bill */
/*==============================================================*/
create table t_holiday_bill
(
id varchar(32) not null,
"date" varchar(20) null,
serial_no varchar(20) null,
weekday varchar(20) null,
remark varchar(20) null,
pass_flag varchar(1) null,
constraint PK_T_HOLIDAY_BILL primary key clustered (id)
);
comment on table t_holiday_bill is
'节假日表';
comment on column t_holiday_bill.id is
'主键';
comment on column t_holiday_bill."date" is
'日期';
comment on column t_holiday_bill.serial_no is
'假期日期联号';
comment on column t_holiday_bill.weekday is
'周一、二、三、四、五、六、日';
comment on column t_holiday_bill.remark is
'备注';
comment on column t_holiday_bill.pass_flag is
'是否上班 1=上班;0=休息';
入库的代码逻辑:
//1、初始化数据库配置
Connection conn = null;
try {
// 加载mysql驱动程序
Class.forName("com.mysql.jdbc.Driver");
// 连接localhost上的mysql,并指定使用test数据库,用户名为root,密码为***
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/dbname?allowMultiQueries=true",
"dbname", "j!xsxlax2$xzx");
if (!conn.isClosed()) {
System.out.println("数据库连接成功!"); //验证是否连接成功
}
long id = System.currentTimeMillis();
Calendar cal = Calendar.getInstance();
int count = 0;
String sql_tmp = "insert into t_holiday_bill values";
// StringBuilder sb = new StringBuilder(sql);
while (cal.get(Calendar.YEAR) < 2028) {//2031
Statement statement = conn.createStatement();
id+=1;
cal.setTime(new Date());
cal.add(Calendar.DAY_OF_YEAR, count);
Date date = cal.getTime();
String date8 = DateUtil.getDateStr(date, DatePattern.FULL_DATE_8);
// String date8 = DateUtil.getCurrentDateStr(DatePattern.FULL_DATE_8);
String week = getWeekShortName(cal.get(Calendar.DAY_OF_WEEK));
String banStr = getBanStr(cal.get(Calendar.DAY_OF_WEEK));
int banInt = getBanInt(cal.get(Calendar.DAY_OF_WEEK));
String sql = sql_tmp + "('"+id+"','"+date8+"',''"+",'"+week+"','"+banStr+"',"+banInt+")";
int ret = statement.executeUpdate(sql);
System.out.println(ret);
statement.close();
count++;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (conn != null) {
try {
conn.close();
conn = null;
} catch (Exception e) {
e.printStackTrace();
}
}
}
这样,未经调整的固定的家假日数据就到数据库里了,可根据需要填写年份,例如cal.get(Calendar.YEAR) < 2028,就是到2028年的了。
入库的逻辑源码免积分下载:点击跳转下载页面