异步导出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;
	}
	
	@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&amp;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添加完毕。