taskdaoimpl.java

这是一个工作流管理的后端EJB实现

package com.coshare.joyteam.projectMgr.dao.mssql;

import java.io.StringReader;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import com.coshare.joyteam.projectMgr.dao.DAOException;
import com.coshare.joyteam.projectMgr.dao.TaskDAO;
import com.coshare.joyteam.projectMgr.dto.ActivityDTO;
import com.coshare.joyteam.projectMgr.dto.InstanceDTO;
import com.coshare.joyteam.projectMgr.dto.TaskDTO;
import com.coshare.joyteam.util.ID;

public class TaskDAOImpl extends AbstractMssqlDAO implements TaskDAO {

	static String SQL_INSERT = "insert into [WfTask] ([InstanceId], [TaskName], [ActivityId], [ActivityType], [theDescription], [StartTime], [EndTime], [ExpireTime], [StatusKey]) values (?,?,?,?,?,?,?,?,?)";
	static String SQL_UPDATE = "update [WfTask] set [InstanceId] = ?, [TaskName] = ?, [ActivityId] = ?, [ActivityType] = ?, [theDescription] = ?, [StartTime] = ?, [EndTime] = ?, [ExpireTime] = ?, [StatusKey] = ? where [TaskId] = ?";
	static String SQL_DELETE = "delete from [WfTask] where [TaskId] = ?";
	static String SQL_SELECT_ByTaskId = "select * from [WfTask] where [TaskId] = ?";
	static String SQL_SELECT_SUC = "select [WfTask].* from [WfTask] left join [WfTaskRel] on [WfTask].[TaskId] = [WfTaskRel].[SucTask] where [WfTaskRel].[PreTask] = ?)";
	static String SQL_SELECT_PRE = "select [WfTask].* from [WfTask] left join [WfTaskRel] on [WfTask].[TaskId] = [WfTaskRel].[PreTask] where [WfTaskRel].[SucTask] = ?)";
	static String SQL_SELECT_ByInstanceId = "select * from [WfTask] where [InstanceId] = ?";
	static String SQL_SELECT_ALL = "select * from [WfTask]";

	public boolean AddNew(TaskDTO dtoObj) throws DAOException {
		PreparedStatement ps = da.prepareStatement(SQL_INSERT);		
		boolean rt = false;
		try {
			ps.setInt(1, dtoObj.getInstanceId().getId());
			ps.setString(2, dtoObj.getTaskName());
			ps.setInt(3, dtoObj.getActivityId().getId());
			ps.setInt(4, dtoObj.getActivityType());
			ps.setCharacterStream(5, new StringReader(dtoObj.getTheDescription()), dtoObj.getTheDescription().length());
			ps.setTimestamp(6, new Timestamp(dtoObj.getStartTime().getTime()));
			ps.setTimestamp(7, new Timestamp(dtoObj.getEndTime().getTime()));
			ps.setInt(8, dtoObj.getExpireTime());
			ps.setInt(9, dtoObj.getStatusKey());
			rt = ps.executeUpdate() > 0;
		} catch (SQLException e) {
			throw new DAOException("insert failed.", e);
		}
		finally {
			try {
				ps.close();
			} catch (SQLException e) {
			}
		}
		return rt;
	}

	public boolean Update(TaskDTO dtoObj) throws DAOException {
		PreparedStatement ps = da.prepareStatement(SQL_UPDATE);		
		boolean rt = false;
		try {
			ps.setInt(1, dtoObj.getInstanceId().getId());
			ps.setString(2, dtoObj.getTaskName());
			ps.setInt(3, dtoObj.getActivityId().getId());
			ps.setInt(4, dtoObj.getActivityType());
			ps.setCharacterStream(5, new StringReader(dtoObj.getTheDescription()), dtoObj.getTheDescription().length());
			ps.setTimestamp(6, new Timestamp(dtoObj.getStartTime().getTime()));
			ps.setTimestamp(7, new Timestamp(dtoObj.getEndTime().getTime()));
			ps.setInt(8, dtoObj.getExpireTime());
			ps.setInt(9, dtoObj.getStatusKey());
			ps.setInt(10, dtoObj.getTaskId().getId());
			rt = ps.executeUpdate() > 0;
		} catch (SQLException e) {
			throw new DAOException("update failed.", e);
		}
		finally {
			try {
				ps.close();
			} catch (SQLException e) {
			}
		}
		return rt;
	}

	public boolean Delete(TaskDTO dtoObj) throws DAOException {
		PreparedStatement ps = da.prepareStatement(SQL_DELETE);		
		boolean rt = false;
		try {
			ps.setInt(1, dtoObj.getTaskId().getId());
			rt = ps.executeUpdate() > 0;
		} catch (SQLException e) {
			throw new DAOException("delete failed.", e);
		}
		finally {
			try {
				ps.close();
			} catch (SQLException e) {
			}
		}
		return rt;
	}

	public TaskDTO getTask(ID taskId) throws DAOException {
		PreparedStatement ps = da.prepareStatement(SQL_SELECT_ByTaskId);
		TaskDTO rt = null;
		try {
			ps.setInt(1, taskId.getId());
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				rt = getDTOFromRs(rs);
			}
			else {
				throw new DAOException("not found.");
			}
		} catch (SQLException e) {
			throw new DAOException("select failed.", e);
		}
		finally {
			try {
				ps.close();
			} catch (SQLException e) {
			}
		}
		return rt;
	}

	public TaskDTO getSucTask(ID taskId) throws DAOException {
		PreparedStatement ps = da.prepareStatement(SQL_SELECT_SUC);
		TaskDTO rt = null;
		try {
			ps.setInt(1, taskId.getId());
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				rt = getDTOFromRs(rs);
			}
			else {
				throw new DAOException("not found.");
			}
		} catch (SQLException e) {
			throw new DAOException("select failed.", e);
		}
		finally {
			try {
				ps.close();
			} catch (SQLException e) {
			}
		}
		return rt;
	}

	public TaskDTO getPreTask(ID taskId) throws DAOException {
		PreparedStatement ps = da.prepareStatement(SQL_SELECT_PRE);
		TaskDTO rt = null;
		try {
			ps.setInt(1, taskId.getId());
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				rt = getDTOFromRs(rs);
			}
			else {
				throw new DAOException("not found.");
			}
		} catch (SQLException e) {
			throw new DAOException("select failed.", e);
		}
		finally {
			try {
				ps.close();
			} catch (SQLException e) {
			}
		}
		return rt;
	}

	public List getTasks() throws DAOException {
		PreparedStatement ps = da.prepareStatement(SQL_SELECT_ALL);
		ArrayList rt = new ArrayList();
		try {
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				rt.add(getDTOFromRs(rs));
			}
		} catch (SQLException e) {
			throw new DAOException("select failed.", e);
		}
		finally {
			try {
				ps.close();
			} catch (SQLException e) {
			}
		}
		return rt;
	}

	public List getTasks(ID instanceId) throws DAOException {
		PreparedStatement ps = da.prepareStatement(SQL_SELECT_ByInstanceId);
		ArrayList rt = new ArrayList();
		try {
			ps.setInt(1, instanceId.getId());
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				rt.add(getDTOFromRs(rs));
			}
		} catch (SQLException e) {
			throw new DAOException("select failed.", e);
		}
		finally {
			try {
				ps.close();
			} catch (SQLException e) {
			}
		}
		return rt;
	}
	
	public TaskDTO getDTOFromRs(ResultSet rs) throws SQLException {
		TaskDTO dto = new TaskDTO();
		dto.setTaskId(rs.getInt("TaskId"));
		dto.setInstanceId(InstanceDTO.intToID(rs.getInt("InstanceId")));
		dto.setTaskName(rs.getString("TaskName"));
		dto.setActivityId(ActivityDTO.intToID(rs.getInt("ActivityId")));
		dto.setActivityType(rs.getInt("ActivityType"));
		dto.setTheDescription(rs.getString("theDescription"));	
		dto.setStartTime(rs.getTimestamp("StartTime"));
		dto.setEndTime(rs.getTimestamp("EndTime"));
		dto.setExpireTime(rs.getInt("ExpireTime"));
		dto.setStatusKey(rs.getInt("StatusKey"));
		return dto;
	}

}