hibernate 调用存储过程(删除没激活的用户)

有些时候用户注册了到不激活，超过30天后我们要删除掉

首先来写存储过程

BEGIN
	#删除错过30天没激活的用户
	declare now_time dateTime default now();	
	delete from userdetail  where ud_id in (select u_id from user   where u_status = 0 and (UNIX_TIMESTAMP(now_time) - UNIX_TIMESTAMP(u_registerTime))/60*60*24*7>1);
	delete from user   where u_status = 0 and (UNIX_TIMESTAMP(now_time) - UNIX_TIMESTAMP(u_registerTime))/60*60*24*7>1 ;	

END

然后在service里调用

/**
	 * 删除未激活的用户
	 */
	@SuppressWarnings("unchecked")
	public void delNoActiveUser(){
		Connection connection = null;
		CallableStatement callableStatement = null;
		Session session = userDao.getHibernateTemplate().getSessionFactory().openSession();
		try {
			Transaction ts = session.beginTransaction();
			connection = session.connection();
			connection.setAutoCommit(false);
			//connection  = SessionFactoryUtils.getDataSource(userDao.getHibernateTemplate().getSessionFactory()).getConnection();
			callableStatement = connection.prepareCall("{call pro_delete_overdue_user() }");
			callableStatement.executeUpdate();
			ts.commit();
			callableStatement.close();
			connection.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			try {
				callableStatement.close();
				connection.close();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			
		}	
	}

 

最后放在定时任务里执行

<!-- 定期删除超过30天没激活的用户 -->
	<bean id="jobDetailDelNoActiveUser" 
		class="org.springframework.scheduling.quartz.MethodInvokingJobDetailFactoryBean">
		<property name="targetObject" ref="registerService" />
		<property name="targetMethod" value="delNoActiveUser" />
	</bean>
	<bean id="cronTriggerDelNoActiveUser"
		class="org.springframework.scheduling.quartz.CronTriggerBean">
		<property name="jobDetail" ref="jobDetailDelNoActiveUser" />
		<property name="cronExpression" value="0 0 2 * * ? "   />
	</bean>
	<bean class="org.springframework.scheduling.quartz.SchedulerFactoryBean">
		<property name="triggers">
			<list>
				<ref bean="cronTriggerDelNoActiveUser" />	
			</list>
		</property>
   	</bean>