当异步导出使用一段时间后,会出现大量的没有用的excel文件,占用大量系统硬盘空间,因此,异步excel表格处理还需要有定时删除一定时间前的数据功能。
针对上面的问题,我采用quartz的CronTrigger触发方法,定时触发删除excel文件的任务。定时删除Job工作流程如下:
job首先查询数据库里一定时间前的标记未删除结果文件的任务,然后通过File类去判断文件是否依然存在。如果存在,就删除文件。Job及配置代码如下:
public class AsynchronyExportDeleteJob extends QuartzJobBean implements Job {
JdbcTemplate jdbcTemplate;
String filePrefix;
public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
public void setFilePrefix(String filePrefix) {
this.filePrefix = filePrefix;
}
@Override
protected void executeInternal(JobExecutionContext context)
throws JobExecutionException {
if(jdbcTemplate != null) {
final String querySql = "select id, generate_excel_file_path from ht_table_asynchrony_export_job_params where is_delete_exported_file=0 and job_finish_time >= ?";
// final String execSql = "update ht_table_asynchrony_export_job_params set is_delete_exported_file = 1 where job_finish_time >= ?";
final String execSingleSql = "update ht_table_asynchrony_export_job_params set is_delete_exported_file = 1 where id=?";;
jdbcTemplate.execute(new ConnectionCallback<Integer>() {
@Override
public Integer doInConnection(Connection connection)
throws SQLException, DataAccessException {
// get job field id, template_file_path of one month ago.
PreparedStatement pstmt = connection.prepareStatement(querySql);
Calendar startCalendar = Calendar.getInstance();
startCalendar.setTimeZone(TimeZone.getTimeZone("GMT+8:00"));
startCalendar.add(Calendar.MONTH, -1);
pstmt.setDate(1, new java.sql.Date(startCalendar.getTimeInMillis()), startCalendar);
ResultSet rs = pstmt.executeQuery();
Map<Integer, String> idAndPath = new HashMap<Integer, String>();
try {
while(rs.next()) {
idAndPath.put(rs.getInt(1), rs.getString(2));
}
} catch(SQLException e) {
e.printStackTrace();
return null;
}
pstmt.close();
// update pending job parameter record status.
// pstmt = connection.prepareStatement(execSql);
// pstmt.setDate(1, new java.sql.Date(startCalendar.getTimeInMillis()), startCalendar);
// pstmt.executeUpdate();
// pstmt.close();
// deal with the temporary files.
try {
pstmt = connection.prepareStatement(execSingleSql);
for(Entry<Integer, String> iap : idAndPath.entrySet()) {
String filePath = filePrefix + iap.getValue();
File file = new File(filePath);
if(file.exists()) {
if(file.delete()) {
pstmt.setInt(1, iap.getKey());
pstmt.executeUpdate();
}
} else {
pstmt.setInt(1, iap.getKey());
pstmt.executeUpdate();
}
}
} catch(NullPointerException e) {
e.printStackTrace();
} catch(SQLException e) {
e.printStackTrace();
} finally {
pstmt.close();
}
return null;
}
});
}
}
}
Job中数据库的访问方式,我采用jdbc方式,减少复杂度,提高效率。Job采用springMVC中的QuartzJobBean基类,通过注入方式,生成JobDetail。
配置代码如下:
<bean id="dataSource"
class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="url">
<value>jdbc:mysql://localhost:3306/hterp_130105?useUnicode=true&characterEncoding=UTF-8
</value>
</property>
<property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
<property name="username" value="root"></property>
<property name="password" value="root"></property>
</bean>
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource" />
</bean>
<bean id="asynchronyExportDeleteJobDetail" class="org.springframework.scheduling.quartz.JobDetailBean">
<property name="jobClass">
<value>com.hetang.impl.job.asynchronyexport.AsynchronyExportDeleteJob</value>
</property>
<property name="jobDataAsMap">
<map>
<entry key="jdbcTemplate">
<ref bean="jdbcTemplate"/>
</entry>
</map>
</property>
</bean>
增加Job操作Service,并将Job添加到schedule中:
@Component
public class AsynchronyExportDeleteJobService {
@Autowired
@Qualifier("quartzScheduler")
Scheduler scheduler;
@Autowired
@Qualifier("asynchronyExportDeleteJobDetail")
JobDetail jobDetail;
public void schedule() {
CronTrigger trigger = new CronTrigger();
trigger.setName("asynchronyExportDeleteTrigger");
trigger.setGroup("asynchronyExportGroup");
try {
// set job execute at every 4:00 per day.
trigger.setCronExpression("0 0 4 ? * *");
trigger.getJobDataMap().put("filePrefix", Erp.contextPath + "export\\generate\\");
} catch (java.text.ParseException e) {
e.printStackTrace();
return;
}
try {
scheduler.scheduleJob(jobDetail, trigger);
} catch(SchedulerException e) {
e.printStackTrace();
return;
}
}
}
schedule采用springMVC中SchedulerFactoryBean产生。配置如下:
<bean name="quartzScheduler" class="org.springframework.scheduling.quartz.SchedulerFactoryBean">
<property name="applicationContextSchedulerContextKey" value="applicationContextKey"/>
<property name="configLocation" value="classpath:quartz.properties"/>
</bean>
至此,定时删除Job添加完毕。