超时监控

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

import org.apache.log4j.Logger;

public class SmsMonitor
{
	private static Logger log = Logger.getLogger(SmsMonitor.class);
	
	private static final long timeoutMinutes = Long.parseLong(ConfigUtil.getProperty("sms.timeout.minutes").trim());
	private static final String senderEmail = ConfigUtil.getProperty("alert.sender.email").trim();
	private static final String[] receiverEmails = ConfigUtil.getProperty("alert.receiver.email").split(",");
	private static final String[] receiverPhoneNOs = ConfigUtil.getProperty("alert.receiver.phoneNO").split(",");
	
	private static final String alertSubject = "短厅短讯处理超时报警";
	//private static final String alertContent = "号码XXXXXXXXXXX短讯业务办理超时，请处理。";
	
	private static String url = ConfigUtil.getProperty("database.oracle.connectionURL");
	private static String username = ConfigUtil.getProperty("database.oracle.username");
	private static String password = ConfigUtil.getProperty("database.oracle.password");
	
	private static String moScanQuery = "select sequence_no, phone_no, backup_time from sms_mo_his where process_status=\'0\' and rownum<100";
	private static String statusUpdate = "update sms_mo_his set process_status=? where sequence_no=?";
	private static String mtCountQuery = "select count(1) mts from sms_mt where sequence_no=?";
	private static String mtTimeQuery = "select max(send_time) mt_time from sms_mt where sequence_no=?";
	
	private static String getSequenceNO = "select seq_sms_mo.nextval seqno from dual";
	private static String sendEmail = "insert into offon.t_EmailRcd(optsn, expeditor, recipients, subject, mailbody, dealflag, enterdate) " +
															"values(?, ?, ?, ?, ?, \'0\', sysdate)";
	private static String sendSMS = "insert into OFFON.sms_send(command_id, login_accept, msg_level, phone_no, msg, order_code, back_code, dx_op_code, login_no, op_time, srctel, begintime, endtime, smcode) " +
														"values(?, ?, 0, ?, ?, 0, 0, \'0000\', \'Z9MA66\', sysdate, \'1188\', \'000000\', \'235959\', \'XX\') ";
	
	private static Connection conn = null;
	private static PreparedStatement moScanStat = null;
	private static PreparedStatement statusUpdateStat = null;
	private static PreparedStatement mtCountStat = null;
	private static PreparedStatement mtTimeStat = null;
	
	private static PreparedStatement getSequenceNOStat = null;
	private static PreparedStatement sendEmailStat = null;
	private static PreparedStatement sendSMSStat = null;
	
	static
	{
		try
		{
			Class.forName("oracle.jdbc.driver.OracleDriver");
		}
		catch (ClassNotFoundException e1)
		{
			log.error("加载Oracle驱动失败：", e1);
			log.error("监控进程启动失败！");
			System.exit(1);
		}
		
		try
		{
			createConnection();
		}
		catch (SQLException e13)
		{
			log.error("创建数据库连接失败：", e13);
			log.error("监控进程启动失败！");
			System.exit(1);
		}
	}
	
	public static void main(String[] args)
	{
		Date backupTime = null;
		Date nowTime = null;
		Date mtTime = null;
		
		long interval1 = 0;
		long interval2 = 0;
		int mtCount = 0;
		long moSequence = 0;
		String phoneNo = "";
		
		ResultSet moRes = null;
		ResultSet mtCountRes = null;
		ResultSet mtTimeRes = null;
		
		while(true)
		{
			try
			{
				if(conn==null || !(conn.isValid(0)))
				{
					createConnection();
				}
			}
			catch (SQLException e12)
			{
				log.error("检查数据库连接时出错：", e12);
				continue;
			}
			
			try
			{
				moRes = moScanStat.executeQuery();
			}
			catch (SQLException e4)
			{
				log.error("扫描mo短信时出错：", e4);
				continue;
			}
			
			if(moRes!=null)
			{
				try
				{
					while(moRes.next())
					{
						backupTime = moRes.getTimestamp("backup_time");
						nowTime = new Date();
						
						/*
						long day=interval/(24*60*60*1000);
						log.info("天：" + day);
						
						long hour=(interval/(60*60*1000)-day*24);
						log.info("时：" + hour);
						
						long min=((interval/(60*1000))-day*24*60-hour*60);
						log.info("分：" + min);
						*/
						
						interval1 = ((nowTime.getTime()-backupTime.getTime())/60000);
						
						if(interval1<timeoutMinutes)
						{
							continue;
						}
						else
						{
							moSequence = moRes.getInt("sequence_no");
							phoneNo = moRes.getString("phone_no");
							
							mtCountStat.setLong(1, moSequence);
							mtCountRes = mtCountStat.executeQuery();
							
							mtCountRes.next();
							mtCount = mtCountRes.getInt("mts");
							
							statusUpdateStat.setLong(2, moSequence);
							if(mtCount>0)
							{
								mtTimeStat.setLong(1, moSequence);
								mtTimeRes = mtTimeStat.executeQuery();
								
								mtTimeRes.next();
								mtTime = mtTimeRes.getTimestamp("mt_time");
								
								interval2 = ((mtTime.getTime()-backupTime.getTime())/60000);
								if(interval2<timeoutMinutes)
								{
									statusUpdateStat.setString(1, "1");
								}
								else
								{
									statusUpdateStat.setString(1, "2");
									log.info("发生超时：" + moSequence);
									sendSmsAndEmail(moSequence, phoneNo);
								}
								
								closeResultSet(mtTimeRes);
							}
							else
							{
								statusUpdateStat.setString(1, "2");
								log.info("发生超时：" + moSequence);
								sendSmsAndEmail(moSequence, phoneNo);
							}
							
							statusUpdateStat.executeUpdate();
							conn.commit();
							
							closeResultSet(mtCountRes);
							
							continue;
						}
					}
				}
				catch (SQLException e5)
				{
					log.error("读取mo结果集时出错：", e5);
					continue;
				}
			}
			
			closeResultSet(moRes);
		}
		
	}
	
	
	private static void closeResultSet(ResultSet rs)
	{
		try
		{
			if(rs!=null)
			{
				rs.close();
			}
		}
		catch (SQLException e7)
		{
			log.error("关闭查询结果集出错：", e7);
		}
	}

	private static void sendSmsAndEmail(long moSeqence, String phoneNO)
	{
		ResultSet rs = null;
		Long seq = null;
		try
		{
			rs = getSequenceNOStat.executeQuery();
			rs.next();
			seq = rs.getLong(1);
			closeResultSet(rs);
		}
		catch (SQLException e14)
		{
			log.error("查询序列出错，发送告警失败！", e14);
			return;
		}
		
		String content = "号码" + phoneNO + "短讯业务办理超时，请处理。" + "[告警序列" + seq.toString() + "短信序列" + moSeqence + "]";
		try
		{
			sendEmailStat.setString(1, seq.toString());
			sendEmailStat.setString(2, senderEmail);
			sendEmailStat.setString(4, alertSubject);
			sendEmailStat.setString(5, content);
			
			for(String email : receiverEmails)
			{
				sendEmailStat.setString(3, email.trim());
				sendEmailStat.executeUpdate();
			}
			
			sendSMSStat.setLong(1, seq);
			sendSMSStat.setLong(2, seq);
			sendSMSStat.setString(4, content);
			
			for(String phoneNo : receiverPhoneNOs)
			{
				sendSMSStat.setString(3, phoneNo.trim());
				sendSMSStat.executeUpdate();
			}
			
			conn.commit();
		}
		catch(SQLException e18)
		{
			log.error("发送告警信息出错：", e18);
		}
	}
	
	private static void createConnection() throws SQLException
	{
		releaseConnection();
		
		try
		{
			//DriverManager.setLoginTimeout(0);
			conn = DriverManager.getConnection(url, username, password);
			conn.setAutoCommit(false);
		}
		catch (SQLException e2)
		{
			log.error("打开数据库连接失败：", e2);
			throw e2;
		}
		
		try
		{
			moScanStat = conn.prepareStatement(moScanQuery);
			statusUpdateStat = conn.prepareStatement(statusUpdate);
			mtCountStat = conn.prepareStatement(mtCountQuery);
			mtTimeStat = conn.prepareStatement(mtTimeQuery);
			
			getSequenceNOStat = conn.prepareStatement(getSequenceNO);
			sendEmailStat = conn.prepareStatement(sendEmail);
			sendSMSStat = conn.prepareStatement(sendSMS);
		}
		catch (SQLException e3)
		{
			log.error("创建预编译SQL语句失败：", e3);
			throw e3;
		}
		
		log.info("创建数据库连接成功.");
	}
	
	private static void releaseConnection()
	{
		try
		{
			if(moScanStat!=null)
			{
				moScanStat.close();
			}
			
			if(statusUpdateStat!=null)
			{
				statusUpdateStat.close();
			}
			
			if(mtCountStat!=null)
			{
				mtCountStat.close();
			}
			
			if(mtTimeStat!=null)
			{
				mtTimeStat.close();
			}
			
			if(getSequenceNOStat!=null)
			{
				getSequenceNOStat.close();
			}
			
			if(sendEmailStat!=null)
			{
				sendEmailStat.close();
			}
			
			if(sendSMSStat!=null)
			{
				sendSMSStat.close();
			}
		}
		catch(SQLException e9)
		{
			log.error("关闭statement语句出错：", e9);
		}
		
		if(conn!=null)
		{
			try
			{
				conn.close();
			}
			catch (SQLException e10)
			{
				log.error("关闭数据库连接时出错：", e10);
			}
		}
	}
}

pengxuelei@chinaunicom.cn