异步导出excel表格(3)生成的excel定时删除
? ? ? ? 当异步导出使用一段时间后，会出现大量的没有用的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;}@Overrideprotected 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>() {@Overridepublic 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"value="com.mysql.jdbc.Driver"></property><property name="username" value="root"></property><property name="password" value="root"></property></bean><bean id="jdbcTemplate" ref="dataSource" /></bean> <bean id="asynchronyExportDeleteJobDetail" name="code">@Componentpublic 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" value="applicationContextKey"/> <property name="configLocation" value="classpath:quartz.properties"/> </bean>
? ? ? ? ?至此，定时删除Job添加完毕。