activitydaoimpl.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.util.ArrayList;
import java.util.List;

import com.coshare.joyteam.projectMgr.dao.ActivityDAO;
import com.coshare.joyteam.projectMgr.dao.DAOException;
import com.coshare.joyteam.projectMgr.dto.ActivityDTO;
import com.coshare.joyteam.projectMgr.dto.TemplateDTO;
import com.coshare.joyteam.util.ID;

public class ActivityDAOImpl extends AbstractMssqlDAO implements ActivityDAO {
	
	static String SQL_INSERT = "insert into [WfActivity] ([TemplateId], [ActivityName], [ActivityType], [theDescription]) values (?,?,?,?)";
	static String SQL_UPDATE = "update [WfActivity] set [TemplateId] = ?, [ActivityName] = ?, [ActivityType] = ?, [theDescription] = ? where [activityId] = ?";
	static String SQL_DELETE = "delete from [WfActivity] where [ActivityId] = ?";
	static String SQL_SELECT_ByActivityId = "select * from [WfActivity] where [ActivityId] = ?";
	static String SQL_SELECT_BytplIdNameType = "select * from [WfActivity] where [TemplateId] = ? and [ActivityName] = ? and [ActivityType] = ?";
	static String SQL_SELECT_SUC = "select [WfActivity].* from [WfActivity] left join [WfActivityRel] on [WfActivity].[ActivityId] = [WfActivityRel].[SucActivity] where [WfActivityRel].[PreActivity] = ?)";
	static String SQL_SELECT_PRE = "select [WfActivity].* from [WfActivity] left join [WfActivityRel] on [WfActivity].[ActivityId] = [WfActivityRel].[PreActivity] where [WfActivityRel].[SucActivity] = ?)";
	static String SQL_SELECT_ALL = "select * from [WfActivity]";
	static String SQL_SELECT_ByTemplateId = "select * from [WfActivity] where [TemplateId] = ?";
	
	public boolean AddNew(ActivityDTO dtoObj) throws DAOException {
		PreparedStatement ps = da.prepareStatement(SQL_INSERT);		
		boolean rt = false;
		try {
			ps.setInt(1, dtoObj.getTemplateId().getId());
			ps.setString(2, dtoObj.getActivityName());
			ps.setInt(3, dtoObj.getActivityType());
			ps.setCharacterStream(4, new StringReader(dtoObj.getTheDescription()), dtoObj.getTheDescription().length());
			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(ActivityDTO dtoObj) throws DAOException {
		PreparedStatement ps = da.prepareStatement(SQL_UPDATE);		
		boolean rt = false;
		try {
			ps.setInt(1, dtoObj.getTemplateId().getId());
			ps.setString(2, dtoObj.getActivityName());
			ps.setInt(3, dtoObj.getActivityType());
			ps.setCharacterStream(4, new StringReader(dtoObj.getTheDescription()), dtoObj.getTheDescription().length());
			ps.setInt(5, dtoObj.getActivityId().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(ActivityDTO dtoObj) throws DAOException {
		PreparedStatement ps = da.prepareStatement(SQL_DELETE);		
		boolean rt = false;
		try {
			ps.setInt(1, dtoObj.getActivityId().getId());
			rt = ps.executeUpdate() > 0;
		} catch (SQLException e) {
			throw new DAOException("delete failed.", e);
		}
		finally {
			try {
				ps.close();
			} catch (SQLException e) {
			}
		}
		return rt;
	}

	public ActivityDTO getActivity(ID activityId) throws DAOException {
		PreparedStatement ps = da.prepareStatement(SQL_SELECT_ByActivityId);
		ActivityDTO rt = null;
		try {
			ps.setInt(1, activityId.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 ActivityDTO getActivity(ID templateId, String activityName,
			int activityType) throws DAOException {
		PreparedStatement ps = da.prepareStatement(SQL_SELECT_BytplIdNameType);
		ActivityDTO rt = null;
		try {
			ps.setInt(1, templateId.getId());
			ps.setString(2, activityName);
			ps.setInt(3, activityType);
			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 ActivityDTO getSucActivity(ID activityId) throws DAOException {
		PreparedStatement ps = da.prepareStatement(SQL_SELECT_SUC);
		ActivityDTO rt = null;
		try {
			ps.setInt(1, activityId.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 ActivityDTO getPreActivity(ID activityId) throws DAOException {
		PreparedStatement ps = da.prepareStatement(SQL_SELECT_PRE);
		ActivityDTO rt = null;
		try {
			ps.setInt(1, activityId.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 getActivities(ID templateId) throws DAOException {
		PreparedStatement ps = da.prepareStatement(SQL_SELECT_ByTemplateId);
		ArrayList rt = new ArrayList();
		try {
			ps.setInt(1, templateId.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 List getActivities() 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;
	}
	
	private ActivityDTO getDTOFromRs(ResultSet rs) throws SQLException {
		ActivityDTO dto = new ActivityDTO();
		dto.setActivityId(rs.getInt("ActivityId"));
		dto.setTemplateId(TemplateDTO.intToID(rs.getInt("TemplateId")));
		dto.setActivityName(rs.getString("ActivityName"));
		dto.setActivityType(rs.getInt("ActivityType"));
		dto.setTheDescription(rs.getString("theDescription"));	
		return dto;
	}

}