运营JobDetail和Trigger定时调用存储过程

applicationContext.xml

<!--jdbcTemplate-START-->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource">
            <ref bean="dataSource"/>
        </property>
    </bean>

 <!-- 定时任务  -->
	<bean id="callStatDataJob" class="org.springframework.scheduling.quartz.MethodInvokingJobDetailFactoryBean">
	  	<property name="targetObject" ref="statDataImpl" />
	    <property name="targetMethod" value="callStatData" />
	</bean>
	
	<!-- 0-8点和18-24点，50和55分分别调用一次存储过程 -->
	<bean id="callStatDataTrigger1" class="org.springframework.scheduling.quartz.CronTriggerBean">
	    <property name="jobDetail" ref="callStatDataJob" />
	    <property name="cronExpression" value="0 50,55 0,1,2,3,4,5,6,7,18,19,20,21,22,23 * * ?" />
	</bean>
	
	<!-- 每隔5分钟调用一次存储过程 -->
	<bean id="callStatDataTrigger2" class="org.springframework.scheduling.quartz.CronTriggerBean">
	    <property name="jobDetail" ref="callStatDataJob" />
	    <property name="cronExpression" value="0 0/5 8,9,10,11,14,15,16,17 * * ?" />
	</bean>
	
	<!-- 12点30和13点30，分别调用一次存储过程 -->
	<bean id="callStatDataTrigger3" class="org.springframework.scheduling.quartz.CronTriggerBean">
	    <property name="jobDetail" ref="callStatDataJob" />
	    <property name="cronExpression" value="0 30 12,13 * * ?" />
	</bean>
	
	<!-- 13点和14点整，分别调用一次存储过程 -->
	<bean id="callStatDataTrigger4" class="org.springframework.scheduling.quartz.CronTriggerBean">
	    <property name="jobDetail" ref="callStatDataJob" />
	    <property name="cronExpression" value="0 0 13,14 * * ?" />
	</bean>
	
	<bean class="org.springframework.scheduling.quartz.SchedulerFactoryBean">
	    <property name="triggers">
	        <list>
	            <ref bean="callStatDataTrigger1" />
	            <ref bean="callStatDataTrigger2" />
	            <ref bean="callStatDataTrigger3" />
	            <ref bean="callStatDataTrigger4" />
	        </list>
	    </property>
	</bean>	 

statDataImpl.java

package com.mvc.service;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.HashMap;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import com.mvc.utils.DateTimeUI;
import com.mvc.utils.TextUtil;
@Service("statDataImpl")
public class StatDataImpl {
	
	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	public void callStatData() {
		String currentDate = DateTimeUI.getCurrentDate();
		String currentTime = DateTimeUI.getCurrentDateTime();
		//yyyy-MM-dd hh:mi:ss
		int currentHour = Integer.parseInt(currentTime.substring(11, 13));
		int currentMinute = Integer.parseInt(currentTime.substring(14, 16));
		
		//每天8点前和18点后每到55分执行一次统计
		/*if((currentHour<8||currentHour>=18)){
			if(currentMinute<50){
				System.out.println(currentTime+"未到高峰期，不执行存储过程计算");
				return;
			}
		}
		//中午12点到14点每半个小时执行一次统计
		if(((currentHour<14)&&(currentHour>=12))){
			if((currentMinute%30)!=0){
				System.out.println(currentTime+"未到高峰期，不执行存储过程计算");
				return;
			}
		}*/
		
	    CallableStatement st = null;
	    
	    Connection con =  null; 
	  
	    try {
	    	Long startTime = System.currentTimeMillis();
	    	con = this.jdbcTemplate.getDataSource().getConnection();
	    	String sql = "call proc_statData(?,?)";  
	    	st = con.prepareCall(sql);
	    	
	    	st.setString(1, currentDate);
	    	st.setString(2, currentTime);
	    	
	    	//System.out.println("存储过程："+sql);
	    	boolean result = st.execute();
	    	
	    	//System.out.println("执行存储过程返回的结果："+result);
	    	Long endTime = System.currentTimeMillis();
	    	
	    	System.out.println(currentTime+" 执行存储过程耗时:"+(endTime-startTime)+"ms");		  
	    }catch (Exception e) {	  
	       e.printStackTrace();	  
	    }finally{ 
	    	try {
	    		if(st!=null) st.close();  
	    		if(con!=null) con.close();  
	    	} catch (SQLException e) {  
	    		e.printStackTrace();
	    	}	  
	    }

	}

	public void callStatDataByClick(HashMap conditionMap) {
		String startTime = (String)conditionMap.get("startTime");
		String endTime = (String)conditionMap.get("endTime");
		String startDate = "";
		String endDate = "";
		
		String currentTime = DateTimeUI.getCurrentDateTime();
		
	    CallableStatement st = null;
	    
	    Connection con =  null; 
	  
	    try {
	    	if (!TextUtil.blankOrNull(startTime)) {
		    	startDate = DateTimeUI.format(DateTimeUI.parse(startTime), "yyyyMMdd");
			    endDate = DateTimeUI.format(DateTimeUI.parse(endTime), "yyyyMMdd");
		    }
	    	Long startTimeLong = System.currentTimeMillis();
	    	con = this.jdbcTemplate.getDataSource().getConnection();
	    	String sql = "call proc_statData(?,?)";
	    	String formatDate = startTime;
	    	while(Integer.parseInt(startDate)<=Integer.parseInt(endDate)){
	    		
	    		formatDate = DateTimeUI.format(DateTimeUI.parse(startDate, "yyyyMMdd"),"yyyy-MM-dd");
	    		st = con.prepareCall(sql);
		    	st.setString(1, formatDate);
		    	st.setString(2, currentTime);
		  
		    	boolean result = st.execute();
		    	Long endTimeLong = System.currentTimeMillis();
		    	
		    	System.out.println(currentTime+" 手动执行存储过程("+formatDate+"):"+(endTimeLong-startTimeLong)+"ms");
		    	startDate = DateTimeUI.format(DateTimeUI.addDate(DateTimeUI.parse(startDate,"yyyyMMdd"), 1),"yyyyMMdd");
	    	}
	  
	    }catch (Exception e) {	  
	       e.printStackTrace();	  
	    }finally{ 
	    	try {
	    		if(st!=null) st.close();  
	    		if(con!=null) con.close();  
	    	} catch (SQLException e) {  
	    		e.printStackTrace();
	    	}	  
	    }

	}

}