msdbjob.java

MM7彩信对接网关示例

package com.rainbow.msdbjob;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.LinkedList;
import java.util.List;

import org.hibernate.Session;
import org.hibernate.Transaction;

import com.rainbow.util.tools.HibernateUtil;

/**
 * MSSQLSERVER的作业管理，
 * 包括作业创建；将步骤和调度加入到作业中；将步骤和调度从作业中删除；
 * 删除作业
 * @author TrWorks
 *
 */
public class MsDbJob {

	private String jobName;
	private String jobID = null;
	
	private int enabled;
	private String description;
	private String ownerLoginName;
	
	private List jobSteps = new LinkedList();
	private List jobSchedules = new LinkedList();
	
	/**
	 * 通过此构造函数，可以从数据库中将符合此作业名称的作业对象加载进来。
	 * 如果加载成功，jobID将被赋值，否则为null
	 * @param jobName 要加载的作业的名称
	 */
	public MsDbJob(String jobName){
		
		if (jobName == null || jobName.equals("")){
			return;
		}
		
		// 执行 MSSqlserver的系统存储过程 sp_help_job
		try{
			Session sess = HibernateUtil.currentSession();
			Connection con = sess.connection();
			
			CallableStatement statement = 
				con.prepareCall("{? = call msdb.dbo.sp_help_job(?, ?)}");//?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}");
			statement.registerOutParameter(1, Types.INTEGER);
			statement.setString("@job_id", null);
			statement.setString("@job_name", jobName);	
			
			Transaction tx = sess.beginTransaction();
			statement.execute();
			ResultSet jobSet = null;
			ResultSet jobStepSet = null;
			ResultSet jobScheduleSet = null;
			ResultSet jobServerSet = null;
			
			int updateCount = -1;
			
			do {
				// 获取Job的结果集
				ResultSet rs = statement.getResultSet();
				if (rs != null) {
					if (jobSet == null) {
						jobSet = rs;
						loadJob(jobSet);
					} 
					else if (jobStepSet == null) {
						jobStepSet = rs;
						loadStep(jobStepSet);
					} 
					else if (jobScheduleSet == null){
						jobScheduleSet = rs;
						loadSchedule(jobScheduleSet);
					}
					else if (jobServerSet == null){
						jobServerSet = rs;
					}
				}
				
				updateCount = statement.getUpdateCount();
			} while ((statement.getMoreResults() == true)
					|| (statement.getUpdateCount() != -1));						
		}
		catch(Exception e){
			e.printStackTrace();
		}
		finally{
			HibernateUtil.closeSession();
		}
	}
	
	/**
	 * 通过此构造函数,可以创建一个MSSqlserver的作业。创建对象后,jobID将被赋值。
	 * @param jobName 作业名称
	 * @param description 描述
	 * @param ownerLoginName 改作业隶属的DB用户，该字段已被废弃，无效
	 * @param enabled 是否启用，1表示启用，0表示不启用
	 */
	public MsDbJob(String jobName, String description, 
			String ownerLoginName, int enabled){
		
		this.jobName = jobName;
		this.description = description;
		this.ownerLoginName = ownerLoginName;
		this.enabled = enabled;
		
		// 执行 MSSqlserver的系统存储过程 sp_add_job
		try{
			Session sess = HibernateUtil.currentSession();
			Connection con = sess.connection();
			
			CallableStatement statement = 
				con.prepareCall("{? = call msdb.dbo.sp_add_job(?, ?, ?, ?, ?)}");//?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}");
			statement.registerOutParameter(1, Types.INTEGER);
			statement.setString("@job_name", jobName);
			statement.setString("@description", description);
			statement.setInt("@enabled", enabled);
			statement.setString("@owner_login_name", null);
			statement.registerOutParameter("@job_id", Types.VARCHAR);		
			
			Transaction tx = sess.beginTransaction();
			statement.execute();
			tx.commit();
			
			int ret = statement.getInt(1);
			if (ret == 0){
				this.jobID = statement.getString("@job_id");
			}
						
		}
		catch(Exception e){
			e.printStackTrace();
		}
		finally{
			HibernateUtil.closeSession();
		}
	}
	
	/**
	 * 获得 作业名称
	 * @return 作业名称
	 */
	public String getJobName() {
		return jobName;
	}
	
	/**
	 * 设置 作业名称
	 * @param jobName 作业名称
	 */
	public void setJobName(String jobName) {
		this.jobName = jobName;
	}
	
	/**
	 * 获得 该作业的拥有者登陆DB的名称 该字段已被废弃，无效
	 * @return 该作业的拥有者登陆DB的名称 该字段已被废弃，无效
	 */
	public String getOwnerLoginName() {
		return ownerLoginName;
	}
	
	/**
	 * 设置 该作业的拥有者登陆DB的名称
	 * @param ownerLoginName 该作业的拥有者登陆DB的名称
	 */
	public void setOwnerLoginName(String ownerLoginName) {
		this.ownerLoginName = ownerLoginName;
	}
	
	/**
	 * 获得 作业编号
	 * @return 作业编号
	 */
	public String getJobID() {
		return jobID;
	}
	
	/**
	 * 获得 作业调度队列
	 * @return 作业调度队列
	 */
	public List getJobSchedules() {
		return jobSchedules;
	}
	
	/**
	 * 获得 作业步骤队列
	 * @return 作业步骤队列
	 */
	public List getJobSteps() {
		return jobSteps;
	}
	
	/**
	 * 添加属于该作业的一个调度
	 * @param s 作业的一个调度
	 * @return 成功与否
	 */
	public boolean addJobSchedule(MsDbJobSchedule s){
		
		boolean ok = false;
		if (this.jobID == null || this.jobID.equals("")){
			return false;
		}
		
		// 执行 MSSqlserver的系统存储过程 sp_add_job
		try{
			Session sess = HibernateUtil.currentSession();
			Connection con = sess.connection();
			
			int i = -1;
			CallableStatement statement = 
				con.prepareCall("{? = call msdb.dbo.sp_add_jobschedule(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}");//?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}");
			statement.registerOutParameter(1, Types.INTEGER);
			statement.setString("@job_id", jobID);
			statement.setString("@job_name", null);
			statement.setString("@name", s.getScheduleName());
			statement.setInt("@enabled", s.getEnabled());
			statement.setInt("@freq_type", s.getFreqType());
			statement.setInt("@freq_interval", s.getFreqInterval());
			statement.setInt("@freq_subday_interval", s.getFreqSubdayInterval());
			statement.setInt("@freq_subday_type", s.getFreqSubdayType());
			statement.setInt("@freq_relative_interval", s.getFreqRelativeInterval());
			statement.setInt("@freq_recurrence_factor", s.getFreqRecurrenceFactor());
			statement.setInt("@active_start_date", s.getActiveStartDate());
			statement.setInt("@active_end_date", s.getActiveEndDate());
			statement.setInt("@active_start_time", s.getActiveStartTime());
			statement.setInt("@active_end_time", s.getActiveEndTime());	
			
			
			Transaction tx = sess.beginTransaction();
			statement.execute();
			tx.commit();
			
			int ret = statement.getInt(1);
			if (ret == 0){
				this.jobSchedules.add(s);
				ok = true;
			}
		}
		catch(Exception e){
			e.printStackTrace();
			ok =false;
		}
		finally{
			HibernateUtil.closeSession();
		}
		
		return ok;
	}
	
	/**
	 * 添加该作业的一个步骤
	 * @param s  该作业的一个步骤
	 * @return 成功与否
	 */
	public boolean addJobStep(MsDbJobStep s){
		
		// 执行 MSSqlserver的系统存储过程 sp_add_job
		boolean ok = false;
		if (this.jobID == null || this.jobID.equals("")){
			return false;
		}
		
		try{
			Session sess = HibernateUtil.currentSession();
			Connection con = sess.connection();
			
			int i = -1;
			CallableStatement statement = 
				con.prepareCall("{? = call msdb.dbo.sp_add_jobstep(?, ?, ?, ?, ?, ?, ?, ?)}");
			statement.registerOutParameter(1, Types.INTEGER);
			statement.setString("@job_id", jobID);
			statement.setString("@job_name", null);
			statement.setInt("@step_id", s.getStepID());
			statement.setString("@step_name", s.getStepName());
			statement.setString("@server", s.getServer());
			statement.setString("@database_name", s.getDbName());
			statement.setString("@database_user_name", s.getUserName());
			statement.setString("@command", s.getCommand());
			
			Transaction tx = sess.beginTransaction();
			statement.execute();
			//set.
			tx.commit();
			
			int ret = statement.getInt(1);
			if (ret == 0){
				this.jobSteps.add(s);
				ok = true;
			}
		}