二、sqlDATEDIFF()函数、定时器xml配置（一）

DATEDIFF( XXXX-XX-XX,XXXX-XX-XX)

1：

SELECT
    SUM(
        ABS( datediff( NOW() ,sal_end_date ) ) = 205
    ) AS aa
FROM
    k_insurance_bill

datediff ( 当前时间，结束时间 )

2：

select sum(abs(datediff(now(),sal_end_date)) in(207,205,30)) as aa from k_insurance_bill

一句代码算出三个变量的和

3：

select 
	90,
	sum(ABS( datediff( NOW() ,sal_end_date ) ) = 205 ) as aa
from k_insurance_bill
union
select 
	60,
	sum(ABS( datediff( NOW() ,sal_end_date ) ) = 207 ) as aa
from k_insurance_bill
union
select 
	30,
	sum(ABS( datediff( NOW() ,sal_end_date ) ) = 30 ) as aa
from k_insurance_bill

可以查到一个二列三行的表

4：

SELECT 
	b.CREATE_BY,
	SUM(DATEDIFF(sal_end_date,NOW()   ) = 205 ) AS  '90',
	SUM(DATEDIFF(sal_end_date,NOW()   ) = 207 ) AS  '60', 
	SUM(DATEDIFF(sal_end_date,NOW()   ) = 30 ) AS  '30' 
from k_insurance_bill a
left join
k_ins_requirement b
on a.r_id=b.id
group by b.CREATE_BY

根据id分组查询

定时器XML配置

<!-- 导航条 -->
<beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:context="http://www.springframework.org/schema/context" xmlns:aop="http://www.springframework.org/schema/aop"
	xmlns:tx="http://www.springframework.org/schema/tx"
	xsi:schemaLocation="http://www.springframework.org/schema/beans
           http://www.springframework.org/schema/beans/spring-beans-2.5.xsd"
	default-lazy-init="false">
	<!--cronExpression: Seconds Minutes Hours DayofMonth Month DayofWeek [Year] -->
	
	

<!-- 项目结项提醒 -->
<bean class="org.springframework.scheduling.quartz.MethodInvokingJobDetailFactoryBean" name="projectFinishBean">
<!--bean名字 -->
<property name="targetObject" ref="oaNotifyService"/>
<!--bean方法 -->
<property name="targetMethod">
<value>findCountByDeadline</value>
</property>
<property name="concurrent">
<value>false</value>
</property>
</bean>

<bean class="org.springframework.scheduling.quartz.CronTriggerBean" id="projectFinishTrigger1">
<!-- 触发bean配置 -->
<property name="jobDetail">

<ref bean="projectFinishBean"/>

</property>

<!--触发时间配置 每天的0点、18点、21点都执行一次-->

<property name="cronExpression">

<!-- <value>0 0 0,18,21 * * ?</value> -->
<value>0/5 * * * * ?</value>

</property>

</bean>
	
	
	
		<!-- quartz触发器管理 -->
	<bean id="sfb" class="org.springframework.scheduling.quartz.SchedulerFactoryBean">
		<!-- 添加触发器 -->
		<property name="triggers">
			<list>
				<ref local="projectFinishTrigger1" />
	
			</list>
		</property>
	</bean>
</beans>